Use Recordset from one connection to Update another

[jjme88]

Deos anyone know how to use the recordset generated from one adodb connectiion to update the recrodset on a different adodb connection ponting to a differnt database??

 

[jordanking]

what have you tried?

it is possible to get recordsets and close a connection that was used to get the recordset, and keep that recordset in memory.

Get an ADO recordset from a connection, but make sure you use the AdUseClient cursor type, then you can close the connection and loop through it and open another connection and update as you loop through the initial recordset.

see these two:

http://support.microsoft.com/kb/190717/en-us

and

http://support.microsoft.com/kb/184397/en-us

Disconnecting a recordset means you can view the recordset's data after severing the connection to the data store that generated the recordset. You can create a disconnected ADO recordset in process with a recordset whose CursorLocation property is adUseClient and whose ActiveConnection property is set to NULL/Nothing. You can then pass this recordset to a remote client using either RDS or DCOM (or both together).

In ADO, you generate the recordset normally, as you would any other recordset, then disconnect it from the connection by setting the Recordset.ActiveConnection property to NULL/Nothing. Then you can close the Connection object.

.....
I'd rather be surfing

 

[jjme88]

Yea tried those i can disconnect my recordset and re-connect to a different connection... then loop through the records to add them to my new connection.. but this is really slow as youhave to build up the new record field by field using the syntax like

myrs.addnew
myrs!filedA = mySourceRS!fieldsA
myrs.update

what i really want to be able to do is run an insert or update type SQL statement on the Recordset to bulk update or append a large recrodset..

maybe it cant be done??

 

[cmmrfrds]

The solution depends on the type of ADO connections. For example, is it 2 Access connections, 2 sql server connections, 1 access and 1 sql server connection, or some other connections types. So, what are the 2 connections?