Re: Copy Recordset

"Mark" pointed out to me a quicker (and simpler) way of doing this.

The code below asumes that rs has already been instantiated and opened.


Dim xml As MSXML2.DOMDocument,
Dim rsCopy as ADODB.Recordset

Set xml = New MSXML2.DOMDocument
Set rsCopy = New ADODB.Recordset

rs.Save xml, adPersistXML ' save to IStream exposed by xml object
rsCopy.Open xml


--

Terry Kreft


"Chris Anderson" <tgmsn@xxxxxxxxxxx> wrote in message
news:a216bb975a68c8846e54b69d5d@xxxxxxxxxxxxxxxxxxxxx
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

  • Cant Update XML Recordset
    ... We return an updated XML recordset from a web service call. ... Dim objRs As New ADODB.Recordset ... Set objXML = CreateObject ...
    (microsoft.public.data.ado)
  • Re: Exporting data from an append table into excel without field h
    ... this is the most simple code to export to excel using copy from recordset. ... Dim objXLApp As Object 'Excel.Application ... Set rst = CurrentDb.OpenRecordset ... Set objXLWs = objXLWb.Worksheets ...
    (microsoft.public.access.externaldata)
  • Re: A command button (Access) that saves the current record in a file
    ... 'In the onclick event of command button ... Dim fso As New FileSystemObject ... Set rst = Me.frmTextFileDemoSub.Form.RecordsetClone ... 'set recordset object rst to current record ...
    (microsoft.public.access.externaldata)
  • Re: How to Programmatically Export as XML & SXL
    ... Unfortunately, I've had precious little to do with XML in Access, so I'm ... you use the ADO Recordset object's Save ... >>Dim rs As ADODB.Recordset ... >>Dim strADTGFile As String ...
    (microsoft.public.access.externaldata)
  • Re: Excel ADO Crash
    ... One other important point: the recordset ... Dim cnt As New ADODB.Connection ... Set cnt = New ADODB.Connection ... Set rst = New ADODB.Recordset ...
    (microsoft.public.excel.programming)