Copying an ADO RecordSet in Visual Basic

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

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

One thought on “Copying an ADO RecordSet in Visual Basic

  • 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


    For Each fld In rs1.Fields
    rs2(fld.Name).Value = fld.Value
    Next fld

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.