insert from msaccess table into unlinked oracle table

graham-n
graham-n used Ask the Experts™
on
In msaccess I have to insert into an unlinked Oracle table. something like this:

insert into  [ODBC;UID=TOM;PWD=TOM_PWD;DSN=ORCLD;].T1 select [Field1] from [Table1];

where T1 is an Oracle table in TOM/TOM_PWD@ORCLD.
Table1 is an msaccess table.
note T1 must not be a linked table

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You'll need to use ADODB for this, create a connection, write the insert string and execute it.

Kelvin
It would be along the lines of

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command


Set cnn = New ADODB.Connection
cnn.ConnectionString = sConnect
cnn.Open

Set cmd = New ADODB.Command

    With cmd
        .ActiveConnection = cnn
        .CommandText = strSP
        .CommandType = adCmdText
        .CommandTimeout = 0
        .Execute
    End With
Set cmd = Nothing
Set cnn = Nothing

Where sCoonect is your connections string and strSP is the actual INSERT INTO table..... statement you want executed (in Oracle syntax)

Kelvin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial