Recordset

[lash]

Hope someone can help.

I want to know if there's a way I can Insert the data in a recordset built from one connection object into a table via a different connection object.

I'm using two different ADO connection objects to open a Local access97 database and a access97 database on a file server.

I create a recordset object based on a SQL query run against tables in the database on the server, and I want to insert the resultant data into a table in the local database.

Preferably without having to step through the recordset row by row and issue an Insert stament each time.

Does anyone know of a way I can just insert the recordset objects data directly into an access table?

Any Ideas much appreciated

 

[CodeFish]

lash,

Create a Disconnected Recordset from your SQL database with code like below:-

'Declare Local Objects
Dim oRec As ADODB.Recordset
Dim oConn As ADODB.Connection

'Instantiate Objects
Set oRec = New ADODB.Recordset
Set oConn = New ADODB.Connection

'Open Connection
oConn.Open sConnection

'Set Recordset Properties
With oRec
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open sSQL, sConnection

'Release the Active Connection to Recordset
Set .ActiveConnection = Nothing
End With

'Close Connection Object
oConn.Close

then Reconnect to your Local database Using code like:-

'Re-Open Connection
oConn.Open sConnection

'Set Recordset Properties
With oRec
'Set the Active Connection to Recordset
.ActiveConnection = oConn

'Update Table
.UpdateBatch

'Release Connection
Set .ActiveConnection = Nothing
End With

'Release References
Set oConn = Nothing
Set oRec = Nothing


HTH,

Codefish