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

Question

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?

Answer 1

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.