Insert data from MSAccess 2003 to MSSql 2000

[Webkins]

Hello and good morning. I am trying to create a query in MSAccess 2003 VBA which will select all the data from a table called tbl_4 into a MSSql 2000 table called StockReport. I have been trying since yesterday without success. Could someone please advise me on how to do this ? Thanks so much for your help. Have a beautiful day.

 

[simian336]

1. Crate an ODBC DSN to the sql server.
2. Create a Linked Table in Access to the SQL table.
3. Create an insert query to insert the records from Access to Sql Server
4. Run the query.

Simi

 

[Webkins]

I found the answer. Here is the code. Thanks to all who took the time to look and reply.

Set db = CurrentDb
strSQL = "INSERT INTO [ODBC;DSN=Network;Description=Network;DATABASE=Report;Trusted_Connection=Yes].StockReport " & "SELECT * FROM tbl_4;"
db.Execute strSQL, dbFailOnError