|Clone ADO Recordsets The Right Way||
|NT, 9x, 2000
Contrary to popular belief, Recordset.Clone doesn't actually clone the recordset. It doesn't actually create a new object in memory - it simply returns a reference to the original recordset with the option of making the reference read-only. To verify this claim, simply delete a record from the cloned recordset and you will see that the .RecordCount on the original recordset also decreases.
So how do you actually make a true clone of the recordset with no dependencies or dangling references? One way is to save the recordset to file via the .Save method and then read it into another recordset. However, this method is very costly and time-consuming because ADO has to write the entire recordset structure, including field types and every property and every piece of data to disk. The proper answer is in the rarely used ADODB.Stream object. It turns out that you can save the entire recordset to this object (which is in memory) and then restore to another recordset. Check out the code below.
Note that ADO contains a bug which shows up when using this method. When opening a recordset from a Stream object,
the recordset retains all of the memory allocated to the Stream object, in addition to allocating its own. Subsequently
setting the Stream object to Nothing does nothing. However, when a Recordset object is deallocated,
all the memory, including that of the dead Stream object is deallocated as well. So, not a horrible bug, but
something to keep in mind when you have multiple users accessing your cloning code on the web server. I've talked
to the developer inside Microsoft on the ADO team who thought that this was not a bug, but rather a design decision.
Let me explain: Recordset assumes that since it was created from the IStream interface, it may be asked in the future to
stream it elsewhere using this interface. Thus rather than having to recreate this interface, it simply streams the
IStream memory chunk it has been created by. I think, this behavior is a bug and if it indeed was a design decision,
then it was a bad design decision.
For example, if you want to clone a really large recordset which normally takes 110 MB of RAM (11000 rows and 650 columns), you will end up "leaking" 50 MB. Your total consumption of RAM will be 270 MB (110 MB for the original RS, 110 MB for the cloned RS and 50 MB that was absorbed from the Stream object).
|Add the following function to Module, Class or Form|
Public Function Clone(ByVal oRs As ADODB.Recordset, _ Optional ByVal LockType As ADODB.LockTypeEnum = adLockUnspecified) As ADODB.Recordset Dim oStream As ADODB.Stream Dim oRsClone As ADODB.Recordset 'save the recordset to the stream object Set oStream = New ADODB.Stream oRs.Save oStream 'and now open the stream object into a new recordset Set oRsClone = New ADODB.Recordset oRsClone.Open oStream, , , LockType 'return the cloned recordset Set Clone = oRsClone 'release the reference Set oRsClone = Nothing End Function