title: How to insert Records to SQL/Server from Access to SQL/Server using "Insert Into" with slow connection
body: <p>Here is what I tried.  WHAT ELSE HAVE I MISSED OR SHOULD HAVE TRIED?</p>
  
  <p>My situation:</p>
  
  <ul>
  <li>SQL/Server in another country -
  direct Internet connection
  unreliable, private line
  cost-prohibitive, line condition changes constantly (ping 180 to 500+)</li>
  <li>Access SQL/Server via VPN connection - very slow but clean/reliable</li>
  <li>Access ACCDB (ace) database in US - low volume, working fine</li>
  <li>Need to "Insert into  select * from "</li>
  </ul>
  
  <p>I have tried all of the following:</p>
  
  <p>DAO - (currentdb.execute) runs Access SQL, OLEDB connection to remote SQL/Server across VPN -- can use "insert into" are very slow.</p>
  
  <p>ADO using Access SQL - even slower.</p>
  
  <p>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.</p>
  
  <p>OLEDB Access linked table to SQL/Server.  Fast to read data, very slow to insert records.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>HAVE NOT TRIED YET: SQL/Server subscriber-publisher mirroring/replication to keep remote tables "virtually" local.</p>
  
  <p>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.</p>
  
  <p>WHAT ELSE HAVE I MISSED OR SHOULD HAVE TRIED?</p>