Deep Copy or Clone an ADODB recordset in VBA
++ Good question! btw. this way of copying object is called a deep copy.
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:
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 "orignal 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 = NothingEnd SubResults as expected:
