Populate a Access table with SQL data

[Webkins]

I am trying to use Access 2003 VBA on a local PC to execute a VBA query to a remote server running SQL 2000. Here is the VBA query I am trying to use with no luck:

SQLa = "INSERT INTO tbl_Data IN '" & Path & "' SELECT * FROM Oven_Data"
Set rs = cn.Execute(SQLa, , adCmdText)

This works but creates a new SQL table on the server, I need the data available locally on the Access 2003 PC:

SQLa = "select * INTO tbl_Graph FROM ##Temperature_Temp"
Set rs = cn.Execute(SQLa, , adCmdText)

tbl_Graph is in Access 2003 on my PC I want to populate
Oven_Data is in SQL 2000 on the server which contains the data.
Path is the path to the database on my local PC

I am not having luck in making this happen. Any suggestions ? Thank you all so much in advance.

 

[PHV]

Why not use a local table linked to the remote Oven_Data ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Webkins]

I am trying to import data from a SQL table into Access 2003. Will this DoCmd.TransferDatabase work for this purpose ? Also, I do not want to use a DSN connection, rather a trusted connection. I have no experience with this command so any examples would be great. Thank you all so much.

 

[Webkins]

Can someone please help me with this issue. I have been working on this for 3 days now, searching here and the Internet for an answer. What I have found is this code but I get a runtime error of 2507, database not installed.

The code:
DoCmd.TransferDatabase acImport, "ODBC; Driver={SQL Server}; SERVER=usajrzxxp1ms004; DATABASE=impregnation; Trusted_Connection=yes; acTable dbo.oven_data; tbl_graph"

Have I missed something or usng this code incorrectly ? Thanks to all.

 

[PHV]

Again, why not use an APPEND query with a linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886