I have spreadsheets that I distribute to users. I want Excel VBA to export
large tables (2000+ rows) into a remote SQL Server 2005. I am able to
successfully do this with the following generic code, but it is too slow:

Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Driver={SQL
Server};SERVER=<server;DATABASE=TestSample;UID=<i d;PWD=<pw;"
con.Execute "INSERT INTO ... SELECT * FROM ..." 'Looping this for each
row

It takes over 10 minutes because it does it one line at a time.

I've also tried looping "ADODB.Recordset.addnew" ending with
".UpdateBatch", but it also is too slow, seems to still only be able to
transfer the table data one row at a time (unless I'm doing something wrong).


Is it really true that VBA/ADO can only export one row at a time?
Is there some other way to improve performance (send the whole data range at
one time)? For example, send the entire table to a SQL Server stored
procedure or BCP (whatever that is). If so, how would it work? Is there a
way to write the stored procedure generic enough to accept a variety of
tables, for example by receiving parameters indicating what SQL table they go
into and if it replaces or appends existing data?

--
Thank You!