Update query in Access Updating SQl Server back end.

[tucker61]

Graduarly moving my tables into SQL Server.

Each day at 19:00 i run a update query that copies 4 fields from a read only database, to a archive table (So i never loose the data) the read only database has approx 400,000 records, so daily i run a update query to store the data for any jobs that have been carried out today, and save that data in a archive table.

The tables are on 2 different servers - which are both connected via Access. Today i had to stop the query after about 30 minutes as it was just not running.

What would be the quickest way to run this query - I am assuming within SQL.

IF so - How do i run the query when it is linked to 2 different servers ?

Or would i be better coping a table to my Access fonr end 0 running the data from there and then copying the updated data back ?

 

[Pat Hartman]

If you have regular backups scheduled for the BE, you don't need to do your own custom, partial, copy.

I'm assuming that you are currently using an append query that selects data from one server and appends it to a table on a different server and the query is running from Access.

having never done this, I don't know that it would be slow but you are forcing Access to bring the data from server 1 to memory on your local PC and then send it back to server 2.

Two other options come to mind.
1a. Use a stored procedure on server 1 to export the data to a text file. You can execute this sp using a passthrough query from Access.
1b. Use a stored procedure on server 2 to import the data from a text file. You can run this also from Access.

2. You can create a temporary database using code. Then import the data from server 1 to the temp db. second step is to export the data to server 2. Using a temp database eliminates the bloat problem that this type of process would otherwise cause.

I'm going to take a wild guess that option 1 would be faster since all the data stays on the server. It never has to be transmitted over the LAN to memory on your PC.

 

[pbaldy]

You might also investigate whether the 2 servers can be linked, enabling you to push/pull directly.