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).

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

• The function assumes that the original recordset exists (i.e. not equal to Nothing)