Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

insert from msaccess table into unlinked oracle table

Posted on 2014-02-24
2
Medium Priority
?
322 Views
Last Modified: 2014-02-28
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
0
Comment
Question by:graham-n
  • 2
2 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39884812
You'll need to use ADODB for this, create a connection, write the insert string and execute it.

Kelvin
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 840 total points
ID: 39884824
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question