Menu
When you use the Clone method, you can share bookmarks between two or more Recordset objects because their bookmarks are interchangeable. You can use the Clone method when you want to perform an operation on a Recordset that requires multiple current records.
The ADO Recordset object is used to hold a set of records from a database table. A Recordset object consist of records and columns (fields). In ADO, this object is the most important and the one used most often to manipulate data from a database.
Recordset objects can support two types of updating: immediate and batched. In immediate updating, all changes to data are written immediately to the underlying data source once you call the Update method.
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:
Code:
Dim origRS As Recordset, copyRS As Recordset
Set origRS = New Recordset
'Create field
origRS.Fields.Append "Name", adChar, 10, adFldUpdatable
origRS.Open
'Add name
origRS.AddNew "Name", "John"
'Clone/copy
Set copyRS = origRS.Clone
'Change record in cloned/copied recordset
copyRS.MoveFirst
copyRS!Name = "James"
'This should give me "JamesJohn"
MsgBox copyRS.Fields(0).Value & origRS.Fields(0)
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With