INSERT SELECT Statements

[neoartz237]

I was wondering if you can an INSERT SELECT statement for 2 tables w/ different data source?

Like one's from Oracle and the other's from Access?

Please help

 

[llkhoutx]

You can query a linked Oracle table with a joined Access table.

A query of linked Oracle table(s) will be performed by Oracle.

A query of a linked Oracle table with a joined Access table will be performed by Access

 

[neoartz237]

Umm they want the Oracle tables to be set through a UDL file, so I cant like the tables to the access page coz, they dont like um.. ODBC

Is there something like an OPENQUERY() equivalent to access?

In SQL you can use.. INSERT INTO tbl1 OPENQUERY(<connectionstring here>, <sql syntax here>) but there aint in Access (

Please help

 

[llkhoutx]

I only use the ODBC connection.

If you user doesn't like that, let your user do it however he/she is comfortable doing it.

You user probably tells his/her surgeon where and what to cut.

 

[neoartz237]

Ehehehe I would do that if he wasnt my boss hahaha.

Anyway, I already made a work around to it weeks ago, but its sooo damn slow! I mean really really slow. It takes me 7 mins tops to transfer 70K datas. I mean, back then in SQL I can transfer 150K + datas in less than a minute because of the INSERT SELECT BUt here

Anyway my work around is something like this..

Open ADODB CONNECTION for oracle
read no of tables
Get no of fields
for each fields ill check if its numeric or string
create ang insert string, like <'data1',123,'data2'>
Then, insert it to my access tables
So I insert each datas individualy, making it oh so slow

Can anyone suggest a better way for this? Thanks so much