Copy data from local Access table to linked SQL Server table

[WaiveIt]

Hello All!
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple

DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"

This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).

I would like to copy the data in a bulk operation, or operations that I can execute programmatically.

Have any of you smart people ever encountered this and developed a solution?

Thanks in advance for any help!
CHAD

 

[MarlaC]

I have a database that also updates to SQL server tables from local tables. I don't have identical schema for the tables, however. On the local tables, I've added fields to indicate whether the record has been copied to the server (boolean), when, and by whom, and another boolean field to indicate if field values have been changed and the record needs to be re-copied.

Is it possible that you could use a similar structure to limit the amount of data necessary to be copied to the server and reduce the time necessary for the operation?