Extract data from Oracle

S

Spike

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
 
T

Tim Williams

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'})"
Click to expand...

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

Tim








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 pointout
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
Click to expand...
 
S

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 said:
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'})"
Click to expand...

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

Tim








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
Click to expand...

.
Click to expand...
 
S

Spike

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

Spike


Spike said:
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 said:
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'})"
Click to expand...

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

Tim








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
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO adStateOpen = 0 2
Help - Calling a Oracle Package function from VB Code 0
Copy from recordset errors in ADO 8
Exporting data with vba from Excel to an Access 2007 db with password 8
load data from MS Access 1
VBA SQL Statement not working 1
ADO Field Size SELECT 0
5 minute time increments 2

Top