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

You need to make sure when concatenating text that you still have
spaces where required (like before your FROM and WHERE...)

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'})"

If unsure, debug.print your SQL and try running it in your favorite
query tool.

Tim








On Apr 6, 4:19*am, Spike wrote:
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

i guess from your reply that the code looks ok. I will check that and see if
it makes a difference. I just copied the SQL from my MS Query that works
fine but may as you say have missed a space.
with kind regards

Spike


"Tim Williams" wrote:


You need to make sure when concatenating text that you still have
spaces where required (like before your FROM and WHERE...)

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'})"

If unsure, debug.print your SQL and try running it in your favorite
query tool.

Tim








On Apr 6, 4:19 am, Spike wrote:
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

.

With a space in front of both works a dream, many thanks for your help
--
with kind regards

Spike


"Spike" wrote:

i guess from your reply that the code looks ok. I will check that and see if
it makes a difference. I just copied the SQL from my MS Query that works
fine but may as you say have missed a space.
with kind regards

Spike


"Tim Williams" wrote:


You need to make sure when concatenating text that you still have
spaces where required (like before your FROM and WHERE...)

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'})"

If unsure, debug.print your SQL and try running it in your favorite
query tool.

Tim








On Apr 6, 4:19 am, Spike wrote:
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

.