Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

insert from msaccess table into unlinked oracle table

Posted on 2014-02-24
2
Medium Priority
?
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 …
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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

688 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