Appending data between providers in Access

Greetings,

It seems like I run into this sort of thing every single day.  I need to create a query to append records from a SQL 7 server table to a Local table with the following criteria:

1) The select statement (and connection string) for the SQL Server data is created at runtime (based upon calculated variables, form fields etc.)  The SQL sever connection strings are defined in a Local table and are selected at runtime (the application connects to several SQL Servers)
2) I need to use the statement created in Step 1 to append records to a Local table.
3) The application runs in a WAN environment.
4) For the reason mentioned above, linked tables or user created ODBC Data Sources are not an option.

I've tried defining a passthough query, then creating a local append query based upon that passthrough query.  However, this doesn't allow me to change the Select Statement or ConnectonString properties of the passthrough query at runtime (or at least I cant figure it out !!)

It ultimately come down to this:

'Setup local connection
Dim conCurrent as New ADOB.Connection
set conCurrent = CurrentProject.Connection

'Setup connection to SQL Server
Dim conSQLServer as New ADODB.Connection
conSQLServer.Open strMyServerConnection     ' strMyServerConnection is selected at runtime from local table containing various SQL Server 7.0 connection strings

'Open local table in table view
Dim rsLocalTable as New ADODB.Recordset
rsLocalTable.Cursor.... etc
rsLocalTable.Open "SELECT * FROM LocalTable", conCurrent

'Get records to append to local table
Dim rsSQLServerRecords as New ADODB.RecordSet
rsSQLServerRecords.Open "SELECT <defined at runtime>...FROM SQLServerTable WHERE <defined at runtime>" , conSQLServer

'Click through the remote records and add them to the local table
Do While Not rsSQLServerRecords.EOF

  rsLocalTable.AddNew
  rsLocalTable.Fields("MyField1") = rsSQLServerRecords.Fields("YourField1")
  ... etc
  rsLocalTable.Update

  rsSQLServerRecords.MoveNext
Loop

There's got be a better way.  If anyone has any ideas, I would love to hear them.

Sincerely,

Afrosonic