Bulk copy using DSN less connection

[jodjim]

My Access table is in my C: drive with a DSN-less connection to a database is in a server. Is it possible to copy an entire query result into my local table? If yes, how do I do it.

I experimented on a script to insert the results but it when into a table in the database.

Thanks a lot for your help.

 

[PHV]

SQL code template:
SELECT fields list
INTO myLocalTable
FROM myLinkedTable
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jodjim]

Thanks, PHV. But it created a table in my database instead.
The codes look like these:

strSQL = "SELECT Field1,Field2 INTO myLocalTable FROM LinkedTable;"

Set cmd.ActiveConnection = Conn
Conn.Execute (strSQL)

Should there be something before myLocalTable to indicate it's in my local drive?

Thanks again.

 

[PHV]

Another thought:
have you tried the DoCmd.TransferDatabase method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jodjim]

Here's my connection specs:
sConnString = "Provider=SQLOLEDB.1;User ID=UserName;password=PWD;Initial Catalog=LinkedDb;Data Source = LinkedSource;" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

The syntax for transferdatabase is:
DoCmd.TransferDatabase acLink, "ODBC Database", sConnString, acTable, "LinkedTable", "MyLocalTable"

I'm getting error: "Can't find installable ISAM".

Not sure with my Tranfer syntax.

Thanks.