Re: ADO GetRows array to XML?

rgutter_at_bctf.ca
Date: 02/22/05 

Date: 22 Feb 2005 00:09:54 -0800

Sure. This sub accepts the original Recordset, extracts the required
number of records and creates the XML file. The sub is meant to be
called iteratively, with varBookmark set to Null for the initial call.

Private Sub CreateXMLForPage(Rs As Recordset, _
    intCurrentPage As Integer, _
    intRecordsPerPage As Integer, _
    varBookmark As Variant, _
    strTargetXML As String)

    Dim varRecord As Variant
    Dim RsForXML As ADODB.Recordset
    Dim strName As String
    Dim intCols As Integer
    Dim intRows As Integer
    Dim i As Integer
    Dim j As Integer

    On Error Goto 0 'replace w/ error handler

    If Rs.RecordCount = 0 Or Rs.EOF Then Exit Sub

    If IsNull(varBookmark) Then Rs.MoveFirst 'first pass

    On Error Resume Next 'to catch eof for final page if not full
    varRecord = Rs.GetRows(intRecordsPerPage)
    Err.Clear
    On Error Goto 0 'replace w/ error handler

    intCols = UBound(varRecord, 1) + 1
    intRows = UBound(varRecord, 2) + 1

    'Set the bookmark for the next pass
    'not sure if Rs.Bookmark is valid at EOF, so do it explicitly:
    If Rs.EOF Then varBookmark = Rs.EOF Else varBookmark = Rs.Bookmark

    'Now construct the new recordset
    Set RsForXML = New ADODB.Recordset

    For j = 0 To intCols - 1 'create each column
        RsForXML.Fields.Append Rs.Fields(j).Name, Rs.Fields(j).Type, _
                   Rs.Fields(j).DefinedSize, Rs.Fields(j).Attributes
        'Remember to set scale & precision for numeric fields!
        If RsForXML.Fields(j).Type = adNumeric Then
           RsForXML.Fields(j).NumericScale = Rs.Fields(j).NumericScale
           RsForXML.Fields(j).Precision = Rs.Fields(j).Precision
        End If
    Next j

    'Now fill the recordset from the array
    'We should be able to fill 2 arrays and just do a
    'single RsForXML.AddNew(avarNamesOrPositions, avarValues) but
    'it was failing earlier so we just build it field by field

    RsForXML.Open
    For i = 0 To intRows - 1
        'since we couldn't get AddNew via array to work:
        RsForXML.AddNew
        For j = 0 To intCols - 1
            RsForXML.Fields(j) = varRecord(j, i)
        Next j
    Next i

    RsForXML.UpdateBatch 'not really needed?

    'Now create the XML file
    On Error Resume Next
    Kill strTargetXML
    Err.Clear
    On Error Goto 0 'replace w/ error handler

    RsForXML.Save strTargetXML, adPersistXML

    RsForXML.Close
    Set RsForXML = Nothing

    On Error GoTo 0
    Exit Sub

Relevant Pages

  • Re: ADO GetRows array to XML?
    ... This sub accepts the original Recordset, ... Dim RsForXML As ADODB.Recordset ... Set RsForXML = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: Error: Microsoft jet database engine does not recognize " as
    ... >>> ' Variables for Database object and Recordset. ... >>> Dim rstReport As DAO.Recordset ... >>> Private Sub InitVars() ... >>> Dim intX As Integer ...
    (microsoft.public.access.reports)
  • Re: one column listbox, multiple bookmarks, spreadsheet link
    ... Private Sub cmdCancel_Click ... Dim db As DAO.Database ... ' Retrieve the recordset ... > different bookmarks on the document should be filled out. ...
    (microsoft.public.word.vba.userforms)
  • Re: Form not updating during loop
    ... Sub LoopThroughRecordset_NumID ... Dim s as string, r As Recordset ... Dim StDocName As String ...
    (microsoft.public.access.formscoding)
  • Re: Assigning value to subform fields
    ... Sub ConnectLocal() ... Dim MyWorkspace As Workspace ... > results are in a recordset, whether the rows came from Access or elsewhere ... >> I have a form and subform created in msaccess database which used to ...
    (microsoft.public.access.formscoding)