asked on

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.

ASKER CERTIFIED SOLUTION

sebastienm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tutee

ASKER

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

sebastienm

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