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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 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