Insert INTO MS SQL 2008 R2 server Linked table too slow

Question

Hi,

In short I'm having the following situation:

MS-Access 2010, 32-bit having linked tables to MS-SQL Server 2008 R2 64-bit. The MS-SQL server is on a remote location. The MDB file is stored locally on my PC. When connecting via ODBC from MS-Access to the SQL server there is a latency of about 50 ms (because of the distance) and this is causing me a big pain.

I have to perform an insert of data from a local MS-Access table INTO a linked table on the SQL server. For the purpose in MS-Access I have structured such insert statement:

Code:

INSERT INTO SQLTable.... SELECT... from [MSaccesstable]

I run a trace on the MS-SQL server and it shows that MS-Access is passing the following insert statements line by line:

Code:

exec sp_executesql N'INSERT INTO  dbo.SQLTAble (Field1, Field2....)  VALUES (@P1, @P2 .....)
exec sp_executesql N'INSERT INTO  dbo.SQLTAble (Field1, Field2....)  VALUES (@P1, @P2 .....)
etc.... for all 1000 inserts

Every line of the above code is executed as a separate transaction and thus having a latency of 50 ms - every insert is happening with 50 ms delay after the previous. This way for 1000 inserts I have to wait about 5 minutes.

My question is: Is there a way to force MS Access to pass these INSERTs into batch opeation thus avoiding the wait time after every single INSERT?

Thank you!

Answer 1

I vote for Tom's suggestion of using SSIS. You create an import package to pull the data into your SQL Server database rather than push data from Access. The process is much faster.

Answer 2

My Bad. Quite correct Tom.

Alan

Answer 3

The OP said "from a local MS-Access table" so passthru queries won't work (they run entirely on the server).

Answer 4

You could try running your insert query as a pass through query, rather than a linked table. Create a DSN for your SQL database, and use that as the ODBC connection string.

Hope this helps

Alan

Answer 5

You don't have much control over how Access decides to execute your query. In this case you may be better off using SSIS to load the data.