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!