Clone Recordset Structure

You would think that Recordset.Clone method would provide an option to clone a recordset structure. But, it does something else, actually. Here is how to do it yourself.

Add the following function to Module, Class or Form


Function CloneRecordsetStructure(oRs As ADODB.Recordset) As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim oRsCloned As ADODB.Recordset
    
    Set oRsCloned = New ADODB.Recordset
    
    For Each fld In oRs.Fields
        oRsCloned.Fields.Append fld.Name, fld.Type, fld.DefinedSize, fld.Attributes
        
        'special handling for data types with numeric scale & precision
        Select Case fld.Type
            Case adNumeric, adDecimal
                oRsCloned.Fields(oRsCloned.Fields.Count - 1).Precision = fld.Precision
                oRsCloned.Fields(oRsCloned.Fields.Count - 1).NumericScale = fld.NumericScale
        End Select
    Next
    
    'make the cloned recordset ready for business
    oRsCloned.Open
    
    'return the new recordset
    Set CloneRecordsetStructure = oRsCloned
    
    'clean up
    Set fld = Nothing
End Function

Remarks

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