|
Extract data from Oracle
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 |
Extract data from Oracle
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 |
Extract data from Oracle
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 . |
Extract data from Oracle
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 . |
| All times are GMT +1. The time now is 08:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com