Make table query from Oracle into Access

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
 
G

Guest

I DID manage work out a solution to this issue and resolved the syntax
successfully. I guess due to lack of response, no one was interested so I
won't bother to post how to do it.
 

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

Top