How to generate an XML file, base on a table in Access, every 1500 records of that table?

Question

I have this function working to export to XML. But now I wanted to generate the file every 1500 records of the table STOCK_EXE. I already tried a While (Not .EOF) with a counter but I lost. Does anyone know the best way to do this?

First Version:

Public Function Function_XML_TEST()

    Dim rs              As ADODB.Recordset
    Dim cn              As ADODB.Connection
    Dim myXML           As MSXML2.DOMDocument
    Dim myXSLT          As MSXML2.DOMDocument
    Dim sSQL            As String
    Dim CountString     As String
    Dim LCounter        As Integer
    Dim iCount          As Integer

    Set myXML = New MSXML2.DOMDocument
    myXML.async = False
    myXML.preserveWhiteSpace = False

    Set myXSLT = New MSXML2.DOMDocument
    myXSLT.async = False
    myXSLT.preserveWhiteSpace = False

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    sSQL = "SELECT * FROM STOCK_EXE"

    CountString = "SELECT COUNT(*) FROM STOCK_EXE"

    iCount = CurrentDb.OpenRecordset(CountString).Fields(0).Value

    rs.Open sSQL, cn

    iCount = rs.RecordCount 'Determine the number of returned records

    With rs        

        For LCounter = 0 To iCount Step 1500  

            myXSLT.Load "C:\Users\STOCK_EXE.xslt"
            rs.Save myXML, adPersistXML

            Call myXML.transformNodeToObject(myXSLT.documentElement, myXML)

            If IsNull(Forms!MenuInicial!ProductStatus.Value) Or _ 
               IsNull(Forms!MenuInicial!LpnFacilityStatus.Value) Or _ 
               IsNull(Forms!MenuInicial!InitialAisle.Value) Or _ 
               IsNull(Forms!MenuInicial!FinalAisle.Value) Then
                MsgBox ("As Lovs!")
            Else
                MsgBox ("Criado XML!")
                myXML.Save "C:\Users\" & LCounter & "_" & _ 
                            Forms!MenuInicial!LpnFacilityStatus.Value & "_" & _ 
                            Forms!MenuInicial!InitialAisle.Value & "_" & _ 
                            Forms!MenuInicial!FinalAisle.Value & "_DTIM_" & _
                            Format(Now(), "DDMMYYYY_hhmm") & ".xml"
            End If

        Next LCounter

    End With

    rs.Close
    cn.Close

End Function

I think I'm close, but it gives me the following error at the point:

rs.OpensSQL, cn

"no value given for one or more required parameters"

Second Version:

Public Function Function_XML_TEST_V1()

    Dim rs              As ADODB.Recordset
    Dim cn              As ADODB.Connection
    Dim myXML           As MSXML2.DOMDocument
    Dim myXSLT          As MSXML2.DOMDocument
    Dim sSQL            As String
    Dim CountString     As String
    Dim LCounter        As Integer
    Dim iCount          As Integer

    Set myXML = New MSXML2.DOMDocument
    myXML.async = False
    myXML.preserveWhiteSpace = False

    Set myXSLT = New MSXML2.DOMDocument
    myXSLT.async = False
    myXSLT.preserveWhiteSpace = False

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    CountString = "SELECT COUNT(*) FROM STOCK_EXE_ID"

    iCount = CurrentDb.OpenRecordset(CountString).Fields(0).Value

    MsgBox ("iCount = " & iCount)

    With rs

        For LCounter = 1 To iCount Step 1500

            MsgBox ("LCounter = " & LCounter)

            sSQL = "SELECT * FROM STOCK_EXE_ID" _
                    & " WHERE STOCK_EXE_ID.ID BETWEEN LCounter and (LCounter + 1500)"

            rs.Open sSQL, cn

            myXSLT.Load "C:\Path\To\XSLT\STOCK_EXE.xslt"
            rs.Save myXML, adPersistXML

            Call myXML.transformNodeToObject(myXSLT.documentElement, myXML)

            myXML.Save "C:\Path\To\Output\" & LCounter & "_DTIM_" & _
                        Format(Now(), "DDMMYYYY_hhmm") & ".xml"

            rs.Close
            cn.Close

        Next LCounter

    End With

    MsgBox ("Passou!")

End Function

You can use the information provided by [this question](https://stackoverflow.com/questions/8627032/ms-access-limit-x-y) to limit the amount of results your query returns. Also, please don't ask double-barreled questions (`I also wanted to`). If this is relevant to you, you can ask it in a separate question. — Erik A, Sep 12 '17 at 10:47
Thank you for the answer, but I dont think this resolve by problem. I changed the code to the last thing I tried, but it inserts the entire table into the file. — TDBSANTOS, Sep 13 '17 at 10:50
I see no attempt to use any information from the question I provided. If you implement it correctly, it can solve your problem, that's why I posted it. Why do you think it can't? — Erik A, Sep 13 '17 at 12:23
My table is not static. I have a form that with different search parameters builds the table and calls my function to export to XML. I can not limit in the query, what I want is that regardless of the number of records, I generate one XML file every 1500 records of the table. Maybe I'm misunderstanding, but I do not think I can do it in the query. — TDBSANTOS, Sep 13 '17 at 14:22
That's just plain nonsense. You can limit dynamically created queries and tables. There's no reason for limiting not to work. — Erik A, Sep 13 '17 at 14:24
I tried but without success, maybe my fault and my inexperience in VBA. I'll try again, if you say it's possible I have to try harder. — TDBSANTOS, Sep 13 '17 at 14:28
You can just show your attempt, and I will try to help. If you only want the first 1500, and aren't interested in the rest, it should be pretty easy. — Erik A, Sep 13 '17 at 14:29
Thank you. I do not want only the first 1500, that would be so easy. Imagine that the table had 8000 records, wanted the first 1500 in an XML file, after 1501 to 3000 another file, 3001 to 4500 other file ... until it reached the end that would be 7501 to 8000. The problem is that the table does not have the fixed records, it can have 8000 like 15000, or 40000 ... — TDBSANTOS, Sep 13 '17 at 14:42
Yes. of course. The approach is still valid. You just need to use the information provided in the link, and keep opening new recordsets until `.RecordCount` is 0. You do need some unique identifier in your rows, if you don't have one, add one. — Erik A, Sep 13 '17 at 14:53

score 1 · Accepted Answer · answered Sep 13 '17 at 17:59

Consider the domain aggregate, DCount, in loop range and SQL with a correlated count subquery using the table's autonumber field which is assumed to exist in table as field, ID:

myXSLT.Load "C:\Path\To\XSLT\STOCK_EXE.xslt"   ' MOVED OUTSIDE LOOP

For LCounter = 1 To DCount("*", "STOCK_EXE") Step 1500

      MsgBox ("LCounter = " & LCounter)

      ' SQL WITH CORRELATED COUNT SUBQUERY IN WHERE CLAUSE SERVING AS ROW NUMBER
      sSQL = "SELECT t.* FROM STOCK_EXE t" _
               & " WHERE (SELECT Count(*) FROM STOCK_EXE sub WHERE sub.ID <= t.ID)" _
               & " BETWEEN " & LCounter & " AND " & LCounter + 1499    
      rs.Open sSQL, cn

      ' OUTPUT RAW XML
      rs.Save myXML, adPersistXML

      ' RUN XSLT (NO CALL OR .documentElement NEEDED)
      myXML.transformNodeToObject myXSLT, myXML

      ' SAVE OUTPUT TO FILE
      myXML.Save "C:\Path\To\Output\" & LCounter & "_DTIM_" & _
                  Format(Now(), "DDMMYYYY_hhmm") & ".xml"    
      rs.Close

Next LCounter 

cn.Close

' CLEAR RESOURCES
Set rs = Nothing: Set cn = Nothing
Set myXML = Nothing: Set myXSLT = Nothing

Thanks, it works exactly as I wanted it to. It was a great help :) — TDBSANTOS, Sep 14 '17 at 10:06
Great! Glad I could help. XSLT and SQL in same procedure? Of course I will help the cause! — Parfait, Sep 14 '17 at 15:07

How to generate an XML file, base on a table in Access, every 1500 records of that table?

1 Answers1