Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Connecting to Oracle DB from EXCEL using Provider=Microsoft.Jet.OLEDB.4.0??

Posted on 2003-03-20
Medium Priority
Last Modified: 2007-12-19

In the past i've managed to connect to an MS access database by declaring the data objects and using the folling line:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Fault1.mdb;"

Then i can perform all my queries and close the connection. Job done. Now I want to do the same connecting to an Oracle DB on a network. All the Oracle ODBC settings have been created in the DSN.I recorded my actions in a macro when connecting to my Oracle DB to retrive a query, and I get the following:

With ActiveSheet.QueryTables.Add(Connection:= _
<sql query .. etc>

I want to connect to Oracle using method 1, so I can perform many queries from excel and then close the connection??

Any Ideas??
Help much appreciated.
Question by:tutee
  • 2
LVL 16

Accepted Solution

sebastienm earned 1000 total points
ID: 8174378
How did you define the DNS? Which driver? Which attributes?

Apparently you use OLE DB through ODBC, therefore using case 3 or 4 bellow. If you want to try without ODBC, use case 1 or 2.

comes from a web site... i don't remember where:
1. Using OLE DB Provider for Oracle (from Microsoft)
oConn.Open "Provider=msdaora;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=myUsername;" & _

2. Using OLE DB Provider for Oracle (from Oracle)

-For Standard Security
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=myUsername;" & _
-For a Trusted Connection
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=/;" & _
' Or
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.

3. Using ODBC Driver for Oracle (from Microsoft)

-For the current Oracle ODBC Driver from Microsoft
oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
           "Server=OracleServer.world;" & _
           "Uid=myUsername;" & _
-For the older Oracle ODBC Driver from Microsoft
oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
           "ConnectString=OracleServer.world;" & _
           "Uid=myUsername;" & _

4. Using ODBC Driver for Oracle (from Oracle)
oConn.Open "Driver={Oracle ODBC Driver};" & _
           "Dbq=myDBName;" & _
           "Uid=myUsername;" & _
   Where:  The DBQ name must be defined in the tnsnames.ora file

I hope this helps.

Author Comment

ID: 8179864
This is how i defined the SYSTEM DNS entry:

Oracle8 ODBC Driver Setup
data source name: RELPROD
description: RELPROD
Service_name: RELPROD
userID: kbase

following boxes checked:
Enable thread safety : enable LOBs : Enable set results
enable failover : enable query timeout.

i can make a connection, but need to get the results into variables i can manipulate and place on a spreadsheet where i want them?

Sub Macro1()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Debug.Print "Opening ODBC connection..."
    cn.Open "Driver={Oracle ODBC Driver};" & _
          "Dbq=RELPROD;" & _
          "Uid=kbase;" & _

    Debug.Print "Opening recordset..."
    SQLcount = "SELECT count(*), environment FROM defects where status = 'Closed' group by environment"
    Set rs = cn.Execute(SQLcount)
    Debug.Print "Show results.."
    With rs
        Do While Not .EOF
            Debug.Print , .Fields(0), .Fields(1)
    End With


End Sub
LVL 16

Expert Comment

ID: 8182396
but you are able to get the recordset without any problem (in your macro1) ?

A nice method is the CopyFromRecordset from the range object (check at it in the help system) to send the whole recordset at once in a sheet.

'Copy rs to range("A2:"xx) in one line.
Range("A2").CopyFromRecordset rs

and when i need to manipulate the fields before displaying to the user, i usually send the data to a hidden sheet, then manipulate, and finally display the final info.

Would this work for you?


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an article on how to answer questions, earn points and become an expert.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question