SQL INSERT INTO, using multiple databases

[gnt]

Hello-

I'm not sure if this is possible, but I would think so...

I am trying to run an INSERT INTO statement to pull data from the database on our server and insert it into a local Access table on my machine. I can't use a pass through query because the uid/pwd changes for each query and once Access makes a connection using a pt query you can't close it without closing the application.

Here's a bit of my code:

strSQL = "INSERT INTO tblTest SELECT shpnam FROM shipShipmentMaster WHERE shipno LIKE 'K9992222001'"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConnect
objConn.Execute (strSQL)

But of course this looks for tblTest (my local table) on the server and cannot find it.
Any suggestions???
Thanks-

 

[SkipVought]

Hi,

If I am not mistaken, the INSERT SQL statement is used to insert a brand spankin' new row into the table. The form of INSERT is...

INSERT INTO tablename [(columnList)] VALUES (dataList)

What are you attempting to do?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smin]

you can link tables in your server database to Access and use them as if they are your local tables. Can't you?

 

[gnt]

Correct. Only I need to insert a batch of about 50,000 records all at once. And this SQL statement works fine if I link the shipShipmentMaster table into Access rather than establishing the connection via ADO. But for the reason stated above, I can't do it this way.

..(i did forget the field name above....should be INSERT INTO tblTest(Reg)....)

 

[gnt]

smin-
I run into the same problem as with the pass-through query. Once I establish a connection uid/pwd, I can't change it. It will look like it's changed, but will only pull data from the original connection.
Linking the tables would be ideal - if you know a way around this problem.

 

[smin]

How about two seperate strSQLs. One for just getting the data. The other for inserting to the local table. Current strSQL is trying to do both in one shot, which I could see the problem.

 

[gnt]

Yeah, I thought about that. But as I'm a bit of a novice to ADO, I haven't been able to figure it out yet. Could you perhaps get me started on the code? Do I throw the data into a recordset? Then what? I need to pull about 15 fields, and about 50,000 records.
Thanks-