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

Hi,

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

1)
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:

2)
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=RELPROD;UID=KBASE;PWD=APP5;DBQ=RELPROD;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;" _
<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.
tuteeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sebastienmCommented:
tutee,
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;" & _
           "Password=myPassword"

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

-For Standard Security
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"
 
-For a Trusted Connection
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=/;" & _
           "Password="
' Or
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "OSAuthent=1"
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;" & _
           "Pwd=myPassword"
 
-For the older Oracle ODBC Driver from Microsoft
oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
           "ConnectString=OracleServer.world;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

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

I hope this helps.
Sebastien
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tuteeAuthor Commented:
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;" & _
          "Pwd=app5"

    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)
            .MoveNext
        Loop
    End With

    cn.Close

End Sub
0
sebastienmCommented:
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?

Sebastien
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.