Retrieve data from Oracle Database

[Carlos Pérez]

Hi,

I need an code example for retrieve data from a table in oracle database via
"Microsoft ODBC for oracle" driver or something similar...

Many thanks!

 

[Guest]

The following example uses ADO with late binding, you need to replace the
"my" arguments in the connection string with yours:

Sub Oracle_Test()

Dim adoCN As Object
Dim adoRS As Object
Dim rngCell As Range
Dim lngX As Long

Set adoCN = VBA.CreateObject("ADODB.Connection")
adoCN.CursorLocation = 3
adoCN.ConnectionString = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=myInstanceName;DBQ=myDBQName;UID=myUserID;PWD=myPassword;"
adoCN.CommandTimeout = 0 'NO TIMEOUT
adoCN.Open
Set adoRS = VBA.CreateObject("ADODB.Recordset")
adoRS.MaxRecords = 0 '0 = ALL RECORDS
Set adoRS = adoCN.Execute("SELECT * FROM DUAL")

For lngX = 1 To adoRS.Fields.Count
ActiveCell.Offset(0, lngX - 1).Value = adoRS.Fields(lngX - 1).Name
Next lngX
ActiveCell.Offset(1, 0).Select

ActiveCell.CopyFromRecordset adoRS
adoRS.Close
Set adoRS = Nothing

End Sub

HTH