The ADO RecordSet object’s Clone method does a great job of making a duplicate copy of the RecordSet, with one major caveat: any changes to the clone are duplicated on the original. It’s more like a shallow copy than a deep copy.
To make an actual copy of a disconnected ADO RecordSet in Visual Basic, use a method like the one shown below, which was largely taken from Francesco Balena’s article on devx.com:
Private Function CopyRecordset(rsSource As ADODB.Recordset) As ADODB.Recordset Dim rs As ADODB.Recordset Dim pb As New PropertyBag ' create a copy of the recordset pb.WriteProperty "rs", rsSource Set rs = pb.ReadProperty("rs") ' release the memory Set pb = Nothing Set CopyRecordset = rs End Function
Don W says:
This may not work for everybody but from many complicated ideas on the web I was able to together this simple way to copy one entire record from one table to another identically designed table.
Public Sub Copy_DB_Record(ByVal id_num as string)
'Copy one entire database record from one database to another,
'identical table structures. Table name - = “MAIN”
Dim db_conn1, db_conn2 As ADODB.Connection
Dim rs1 As New ADODB.Recordset 'dec10
Dim rs2 As New ADODB.Recordset
Dim sODBCConn1, sODBCConn2 As String
Dim sQLstr1, sQLstr2 As String
Dim fld As ADODB.Field
sQLstr1 = "SELECT * FROM Main WHERE Test_ID='" & id_num & "'"
sQLstr2 = "SELECT * FROM Main"
'From' DB =1
sODBCConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=" & c:\temp\source.mdb;Persist Security Info=False"
Set db_conn1 = New ADODB.Connection
db_conn1.Open sODBCConn1
rs1.CursorType = adOpenKeyset
rs1.LockType = adLockOptimistic
rs1.Open sQLstr1, db_conn1
sODBCConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=" & c:\temp\destination.mdb;Persist Security Info=False"
Set db_conn2 = New ADODB.Connection
db_conn2.Open sODBCConn2
rs2.CursorType = adOpenKeyset
rs2.LockType = adLockOptimistic
rs2.Open sQLstr2, db_conn2
'--------------------------
rs2.AddNew
For Each fld In rs1.Fields
rs2(fld.Name).Value = fld.Value
Next fld
rs2.Update
rs2.Close: db_conn2.Close
rs1.Close: db_conn1.Close
Set rs1 = Nothing: Set db_conn1 = Nothing
Set rs2 = Nothing: Set db_conn2 = Nothing
End Sub