I will be grateful for any advice on extracting data from an Oracle data base.

I give part of the code I am using below, dimming the variables, setting up
the connection string is done before all this. It seems to connect ok but
bombs out on the RS.Open line, I will be much obliged if anyone can point out
where I have gone wrong.

I constructed the SQL from writing an MS Query to produce this data; that
query works fine but I think using ADO is the neater option as I need to
change the dates of data being imported and run this from a button on the
relevant sheet.

Many thanks


With m_cnADOConnection
.ConnectionString = m_stADOConnectionString
.Open
End With

Set rngTargetCell = Sheets("Import").[a1]

Set CM = New ADODB.Command
Set CM.ActiveConnection = m_cnADOConnection

CM.CommandText = "SELECT NEW_RATES.COB_DATE, NEW_RATES.MAT_BIN_START,
NEW_RATES.MAT_BIN_END," _
& "NEW_RATES.MAT_RATE_LOAN" _
& "FROM OPS$ORA_ADMIN.NEW_RATES NEW_RATES" _
& "WHERE (NEW_RATES.COB_DATE{ts '2010-03-25 00:00:00'})"
CM.CommandType = adCmdText

Set RS = New ADODB.Recordset
RS.CursorType = adOpenStatic
RS.LockType = adLockReadOnly
Set RS.Source = CM

RS.Open
RS.MoveFirst

' the field headings
For i = 0 To RS.Fields.Count - 1
rngTargetCell.Offset(0, i).Formula = RS.Fields(i).Name
Next i

rngTargetCell.Offset(1, 0).CopyFromRecordset RS

RS.Close
Set RS = Nothing
Set CM = Nothing

--
with kind regards

Spike