Deep Copy or Clone an ADODB recordset in VBA


, , , , , ,

Inspired by this post on SO.

Problem description:

I have been searching for a way of duplicating or copying a recordset in VBA. And by that I mean, having the undelying data independent of each other.

I have tried

Set copyRS = origRS.Clone
Set copyRS = origRS

When I use any of the methods I cant modify one recordset without modifying the other. So in this example:

  1. I create a recordset
  2. I populate the recordset with the name John
  3. I clone the recordset
  4. I modify the cloned one
  5. Check result

But unfortunately for me, this modifies both recordsets

My question is:

Is there a way of copying a recordset from another recordset and then modify the data independently of each other (without looping)?

I know that evidently you can do it through a loop, but is there no other way?


I usually get away with creating an ADODB.Stream and saving the current recordset into it.

Then you can use the .Open() method of a new recordset and pass the stream to it.

For example: ( add references to Microsoft ActiveX Data Objects x.x Library)

Sub Main()
 Dim rs As ADODB.Recordset
 Set rs = New ADODB.Recordset
 rs.Fields.Append "Name", adChar, 10, adFldUpdatable
 rs.AddNew "Name", "John"
 Dim strm As ADODB.Stream
 Set strm = New ADODB.Stream
 rs.Save strm
 Dim copy As New ADODB.Recordset
 copy.Open strm
 copy!Name = "hellow"
 Debug.Print "Original recordset: " & rs.Fields(0).Value
 Debug.Print "Copied recordset: " & copy.Fields(0).Value
 Set strm = Nothing
 Set rs = Nothing
 Set copy = Nothing
 End Sub



Get every new post delivered to your Inbox.