Inspired by this post on SO.
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:
- I create a recordset
- I populate the recordset with the name John
- I clone the recordset
- I modify the cloned one
- 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