Deep Copy or Clone an ADODB recordset in VBA

Tags

, , , , , ,

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.Open
 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
 
 strm.Close
 rs.Close
 copy.Close
 
 Set strm = Nothing
 Set rs = Nothing
 Set copy = Nothing
 
 End Sub

 

Follow

Get every new post delivered to your Inbox.