sides of march (Brian DeMarzo)

Thoughts on life, liberty, and information technology

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

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

One response to “Copying an ADO RecordSet in Visual Basic”

  1. Don W Avatar
    Don W

    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

    Like

    Reply

Leave a comment