Possible to create xml file from table without dataroot lines & with table name as header/footer

Have been struggling with this for a while now, still stuck.
using following to export table to XML file
               Application.ExportXML acExportTable, "transaction", stFileName
Problem is, it puts "dataroot" info on line at beginning and end, and doesn't put a line at beginning and end with the table name ("Transactions")

From last question I posted a few weeks ago, an expert offered some code that is probably fine, but I can't make it work.   Hangs up on "End While"    

Dim db As Database
Dim rs As Recordset
Dim docel As IXMLDOMElement
Dim newel As IXMLDOMElement
Dim tempel As IXMLDOMElement
Set db = CurrentDb
Set rs = db.OpenRecordset("transactions", dbOpenDynaset)
Set xmldoc = New DOMDocument
Set docel = xmldoc.createElement("Transactions")
Set xmldoc.documentElement = docel
docel.setAttribute "xmlns", "urn:whatever"
 
Do While Not re.EOF
    Set newel = xmldoc.createElement("TransEntry")
    xmldoc.documentElement.appendChild newel
    Set tempel = xmldoc.createElement("Field1")
    newel.appendChild tempel
    tempel.Text = rs!Field1
    Set tempel = xmldoc.createElement("Field2")
    newel.appendChild tempel
    tempel.Text = rs!Field2
    rs.MoveNext
    
End While
 
 
 
 
 
rs.Close
 
xmldoc.Save ("C:\metal\xml\pathname.xml")

Open in new window

TechGuiseAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
also, (I assume you have already), add microsoft xml library as a reference.

I just tried your code with suggested changes and it has worked


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim docel As IXMLDOMElement
    Dim newel As IXMLDOMElement
    Dim tempel As IXMLDOMElement
    Dim xmldoc
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("transactions", dbOpenDynaset)
    Set xmldoc = New DOMDocument
    Set docel = xmldoc.createElement("Transactions")
    Set xmldoc.documentElement = docel
    docel.setAttribute "xmlns", "urn:whatever"
     
    Do While Not rs.EOF
        Set newel = xmldoc.createElement("TransEntry")
        xmldoc.documentElement.appendChild newel
        Set tempel = xmldoc.createElement("Field1")
        newel.appendChild tempel
        tempel.Text = rs!Field1
        Set tempel = xmldoc.createElement("Field2")
        newel.appendChild tempel
        tempel.Text = rs!Field2
        Debug.Print rs!Field1, rs!Field2
        rs.MoveNext
    Loop
     
    rs.Close
    
    Set rs = Nothing
    Set db = Nothing
    
    xmldoc.Save ("C:\metal\xml\pathname.xml")

Open in new window

0
 
rockiroadsCommented:
You dont end it with end while, but end it with  Loop

Also you have re.EOF instead of rs.EOF

do while not rs.EOF
  ....
loop


0
 
TechGuiseAuthor Commented:
Appreciate your help.    Still struggling with this, but looks like I'm trying something beyond my abilities.   Might end up having to pay someone to do it.     Thanks again.
0
 
rockiroadsCommented:
Did you get anywhere then with the suggested code changes? Your code was not too far off, just needed minor adjustments

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.