How to insert Records to SQL/Server from Access to SQL/Server using "Insert Into" with slow connection

1
votes

Here is what I tried. WHAT ELSE HAVE I MISSED OR SHOULD HAVE TRIED?

My situation:

  • SQL/Server in another country - direct Internet connection unreliable, private line cost-prohibitive, line condition changes constantly (ping 180 to 500+)
  • Access SQL/Server via VPN connection - very slow but clean/reliable
  • Access ACCDB (ace) database in US - low volume, working fine
  • Need to "Insert into select * from "

I have tried all of the following:

DAO - (currentdb.execute) runs Access SQL, OLEDB connection to remote SQL/Server across VPN -- can use "insert into" are very slow.

ADO using Access SQL - even slower.

OLEDB only, SQL command issued to remote SQL/Server can't see local ACCDB file, so you have to loop through recordset, built a SQL statement for each record. Very slow. Lots of extra coding.

OLEDB Access linked table to SQL/Server. Fast to read data, very slow to insert records.

SQL/Server on both ends. Local SQL/Server link tables to ACCDB and to remote server. Works but does not improve speed. 1000 fairly small records take 5+ minutes to insert.

Bulk insert. Can't do that, source data is not a text file, it's ACCDB. This isn't one-time conversion, it's a daily update of new/changed records.

SSIS -- seems fast as I am able to migrate the entire database rapidly, but doesn't look appropriate or easy for daily use of ordinary inserts and deletes.

HAVE NOT TRIED YET: SQL/Server subscriber-publisher mirroring/replication to keep remote tables "virtually" local.

So, suprisingly I found DAO to remote ACCDB (no SQL/Server) works 20x faster than SQL/Server through VPN. But, I would much rather use SQL/Server.

WHAT ELSE HAVE I MISSED OR SHOULD HAVE TRIED?

1
sql-serverms-accessms-access-2007
I don't do this kind of thing, but there's a method to do execute DML in ADO that uses a batch mode. The problem with using ODBC is that it sends each record as an independent insert, so you need to force it into a batch mode insert, and so far as I'm aware, the ADO method is the only way. But I didn't post this as an answer because of the inordinate amount of hand-waving involved in what I'm suggesting! :) – David-W-Fenton
Thanks for idea -- I started to look this up with the SqlBulkCopy Class. Does anyone know how to do this in T-SQL or is it only ADO.NET? I will do a separate post on that. – pghcpa

1 Answers

0
votes

Double check TCP/IP connections are enabled on the SQL Server & and are actually being used by the client (instead of named pipes) - if you use OLEDB stick ;Network Library=DBMSSOCN on the end of the connection string to force this.

Have you tried "pulling" from the remote SQL server? i.e. your app calls a stored procedure that uses a OPENROWSET/OPENQUERY to pull data from a local SQL server? - useful on connections with asymmetric up/down speeds.