G
Guest
I am using Office XP on Windows 2000 with ADO 2.5.
Is it possible to run a query that retrieves data from an Oracle DB and to
port the results directly into an Access table using a "make table" SQL
statement (or some other way?). Please note, I want this to run from code not
a DB link.
I am using an ODBC connection string and I can connect fine and retrieve
data into a recordset, but it is very cumbersome to upload the data into a
table from there. My SQL is fairly long and joins several tables. The result
set could be over one hundred thousand or even up to a million records.
I have heard this is possible, but I can't figure out the syntax to make it
work. Please help me if you know how. Thanks much in advance.
Some of my current code follows:
ADOConnection = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=Example;DBQ=Example;UID=Example;PWD=Example;"
The following is in a function that returns a disconnected recordset
(instead, I want it piped directly into a new table based on the query):
'Set connection parameters and open a channel
Set cnOracle = New ADODB.Connection
With cnOracle
.CommandTimeout = 333
.ConnectionTimeout = 3
.Provider = "MSDASQL"
.CursorLocation = adUseClient
.ConnectionString = argConnection
.Open
End With
'Define recordset parameters and execute
Set rsOracle = New ADODB.Recordset
With rsOracle
Set .ActiveConnection = cnOracle
.CursorLocation = adUseClient
.Open argSQL, , adOpenStatic, adLockBatchOptimistic, adCmdText
Set .ActiveConnection = Nothing
End With
cnOracle.Close
Set cnOracle = Nothing
'Return the recordset to the calling subroutine
Set OracleImportADODisconnected = rsOracle
Is it possible to run a query that retrieves data from an Oracle DB and to
port the results directly into an Access table using a "make table" SQL
statement (or some other way?). Please note, I want this to run from code not
a DB link.
I am using an ODBC connection string and I can connect fine and retrieve
data into a recordset, but it is very cumbersome to upload the data into a
table from there. My SQL is fairly long and joins several tables. The result
set could be over one hundred thousand or even up to a million records.
I have heard this is possible, but I can't figure out the syntax to make it
work. Please help me if you know how. Thanks much in advance.
Some of my current code follows:
ADOConnection = "PROVIDER=MSDASQL.1;DRIVER={ORACLE ODBC
DRIVER};SERVER=Example;DBQ=Example;UID=Example;PWD=Example;"
The following is in a function that returns a disconnected recordset
(instead, I want it piped directly into a new table based on the query):
'Set connection parameters and open a channel
Set cnOracle = New ADODB.Connection
With cnOracle
.CommandTimeout = 333
.ConnectionTimeout = 3
.Provider = "MSDASQL"
.CursorLocation = adUseClient
.ConnectionString = argConnection
.Open
End With
'Define recordset parameters and execute
Set rsOracle = New ADODB.Recordset
With rsOracle
Set .ActiveConnection = cnOracle
.CursorLocation = adUseClient
.Open argSQL, , adOpenStatic, adLockBatchOptimistic, adCmdText
Set .ActiveConnection = Nothing
End With
cnOracle.Close
Set cnOracle = Nothing
'Return the recordset to the calling subroutine
Set OracleImportADODisconnected = rsOracle