Quickly create a copy of an ADO Recordset

Quickly create a copy of an ADO Recordset

When you want to process the data in a Recordset without affecting the actual values in the database, often the Clone method isn’t what you need. For example, if you delete the records in a cloned Recordset, the original data in the database is also affected.

It seems that the only way to create a copy of a Recordset that is completely unrelated to the original Recordset is to create a disconnected Recordset that duplicates the structure and the data of the original one. However, if the original Recordset has its CursorLocation property set to adUseClient, there is a much more efficient shortcut:

Dim rsSource As New ADODB.RecordsetDim rsFiltered As ADODB.RecordsetDim rsSorted As ADODB.RecordsetDim pb As New PropertyBag' open a client-side Recordset    rsSource.CursorLocation = adUseClientrsSource.Open "Authors", "DSN=Pubs", adOpenStatic' apply a filter rsSource.Filter = "author like 'J*'"' create a copy of the filtered recordsetpb.WriteProperty "filtered", rsSourceSet rsFiltered = pb.ReadProperty("filtered")' sort the recordsetrsSource.Filter = ""rsSource.Sort = "Author"' create a copy of the sorted recordsetpb.WriteProperty "sorted", rsSourceSet rsSorted = pb.ReadProperty("sorted")' release the memorySet pb = Nothing


Share the Post: