Attaching a disconnected recordset

[Guest_imported]

I'm trying to use ADO's XML output to transfer data between two SQLServer databses. My theory was:
1. Run a SQL query against database1 using ADO, and get back a Recordset.
2. Save the Recordset to an XML file using Recordset.Save xmlFileName,adPersistXML.
3. Create a Recordset and Recordset.Open xmlFileName to get a disconnected recordset.
4. "Attach" that disconnected Recordset to a table in database2 which matches the schema of the original query.
5. Call Recordset.UpdateBatch to write the contents of the disconnected Recordset back to the table in database2.

The step I'm having trouble with is 4. How can I "attach" a disconnected Recordset to a table in a database?

sil

 

[cfk]

Are both databases on the same server?

If so create a connection to one and get yoour record set.

Declare a string.

DIM sql

Loop through your record set writing continuous sql
statement to the string.

i.e.

sql = "INSERT INTO Table VALUE(1,2) INSERT INTO Table Values
(3,4) INSERT .....

close the recordset and connection

Then create a connection to the other database

and use

cnConn.Execute(sql)

The db will run all the commands.

 

[cmmrfrds]

You probably already have.
rs.LockType = adLockBatchOptimistic
and
Set ActiveConnection = Nothing
'''loaded up your recordset

'''To reestablish the recordset connection.
Set rs.ActiveConnection = yourconnection
rs.UpdateBatch
rs.close
set rs = Nothing

Unless you need to add some value to the second recordset after retieving the data, you could do what you want with a simple SQL statement. By Insert into ... Select from.....
It would be more efficient to do it through SQL. One way is through ADO where you fully gualify the table names. You can also link the first server to the second server through SQL Server. If you want to use SQL, post the question on the SQL Server Form, I am sure you will get excellent help.