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 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

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
    '--------------------------

    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

Leave a Reply to Don W Cancel 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.