[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-05
4
Medium Priority
?
552 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:TechGuise
  • 3
4 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 22892578
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 22892596
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
 

Author Closing Comment

by:TechGuise
ID: 31513546
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 22962114
Did you get anywhere then with the suggested code changes? Your code was not too far off, just needed minor adjustments

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question