Re: Copy Recordset

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hello Lou,
You mean as in two, separate, distinctly different copies of the same recordset? Or do you want two pointers to the same recordset?


If what you need are two copies of the same recordset that are independant of each other, then give this function a try:


Private Function DisconnectedClone(ByVal rstData As ADODB.Recordset) As ADODB.Recordset

Dim fld As ADODB.Field
Dim rst As ADODB.Recordset

On Error GoTo errHandler
'Create a recordset object
Set rst = New ADODB.Recordset
'Copy the field definition
For Each fld In rstData.Fields
rst.Fields.Append fld.Name, fld.Type, fld.DefinedSize, fld.Attributes
If fld.Precision > 0 Then
rst.Fields(fld.Name).Precision = fld.Precision
End If
If fld.NumericScale > 0 Then
rst.Fields(fld.Name).NumericScale = fld.NumericScale
End If
Next
'Use a client cursor
rst.CursorLocation = adUseClient
'Open the recordset
rst.Open , , adOpenKeyset
'loop through the source recordset and copy the data
Do While Not rstData.EOF
'Add a new records
rst.AddNew
'Copy the field values
For Each fld In rstData.Fields
rst.Fields(fld.Name).Value = rstData.Fields(fld.Name).Value
Next
'Next record
rstData.MoveNext
Loop
'If there was data to roll through,
If rst.RecordCount > 0 Then
'move to the begining of the source recordset
rst.MoveFirst
End If

'Return the clone
Set DisconnectedClone = rst
'Release objects
Set rst = Nothing
Set fld = Nothing

Exit Function

errHandler:
On Error GoTo 0
Err.Raise Err.Number, Err.Source, Err.Description

End Function



Usage:
Set rstCopyTwo = DisconnectedClone(rstOriginal)

It copies the structure, then the data. End result are two recordsets that are identical, but independant of each other.



-Chris Anderson [MVP-VB]

How can I make a copy, NOT a clone, of a recordset?

-Lou



.

Relevant Pages

  • Re: default to previous record?
    ... Dim rst As Dao.Recordset ... Dim fld As Dao.Field ... Set rst = Me.RecordsetClone.Clone ... Also, do I need to enter a value for Recordset, or can it automatically take ...
    (microsoft.public.access.forms)
  • Re: Accessing multiple fields in report function
    ... The only other option would seem to be to open a recordset, ... The database already exists. ... >>> Dim Tbl As TableDef ... >>> For Each fld In Tbl.Fields ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query running very slow
    ... As you're not writing to the recordset, you could try using a forward-only ... > Set cnn = New ADODB.Connection ... > Set rst = New ADODB.Recordset ... > For Each fld In rst.Fields ...
    (microsoft.public.data.ado)
  • Re: list of fields and their names in a form, under Access 2000 (* I am lost! *)
    ... > the access to the ME.* and ME!* stuff and the ADODB declaration I thought ... > It seems that the DAO recordset declaration in my version of VBA has been ... > Dim fld As Field ... > For Each fld In Me.Recordset ...
    (microsoft.public.access.formscoding)
  • Re: list of fields and their names in a form, under Access 2000 (* I am lost! *)
    ... "Code Window/IDE" in the menu list of my Access 2000 ... It seems that the DAO recordset declaration in my version of VBA has been ... Dim fld As Field ... For Each fld In Me.Recordset ...
    (microsoft.public.access.formscoding)