VB Forum / Database Access / September 2004

Does anyone have a good way to combine recordsets?  I have the following
code but, this is very memory intensive.

Function rsCombineRecordsets(ByVal rs1 As ADODB.Recordset, ByVal rs2 As
ADODB.Recordset) As ADODB.Recordset
   Dim fld As ADODB.Field

   Set rsCombineRecordsets = New ADODB.Recordset
   Dim rs As ADODB.Recordset
   Dim blnStructure As Boolean
   Dim count As Long
   Dim rsVar As Variant

For count = 1 To 2
   If count = 1 Then
       Set rs = rs1
   Else
       Set rs = rs2
   End If
'first copy the field structure
   If Not blnStructure Then
   For Each fld In rs.Fields
       rsCombineRecordsets.Fields.Append fld.Name, fld.Type,
fld.DefinedSize, _
           fld.Attributes

       Select Case fld.Type
           Case adNumeric, adDecimal
               With rsCombineRecordsets
                   .Fields(.Fields.count - 1).Precision = fld.Precision
                   .Fields(.Fields.count - 1).NumericScale =
fld.NumericScale
               End With
       End Select
   Next
    blnStructure = True
End If
On Error GoTo Done
If rsCombineRecordsets.State = adStateClosed Then rsCombineRecordsets.Open
If rs.State = adStateClosed Then rs.Open

   ' now copy al values
   Do Until rs.EOF
       rsCombineRecordsets.AddNew
       For Each fld In rs.Fields 'we dont know the colums on design time so
we use this trick
           rsCombineRecordsets.Fields(fld.Name) = fld.Value
       Next
       rs.MoveNext  'next record
   Loop
'next recordset object
Done:
Next

End Function