Export query to "XML" file

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

I am using a report to export a query in "xml format" (adding tags,
etc). It works great except the report gets screwy truncating certain
things when I save it as text. I would like to write a procedure to
do this straight from vba but haven't been able to get anything to
work, as I am new to vba.

I found the following from Rick Brandt:
**********************************************
Here's a rude and crude DAO routine. Appropriate object
closing and error handling should be added, but it gives you
an idea.

Sub TableToXML()

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim RowTxt As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("SELECT * FROM Table1")

Open "C:\Test.xml" For Output As #1

Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) &
"?>"
Print #1, " <Table1>"
Do Until MyRS.EOF = True
RowTxt = " <Row"
For Each fld In MyRS.Fields
RowTxt = RowTxt & " " & fld.Name & "=" & Chr(34) &
fld & Chr(34)
Next fld
Print #1, RowTxt & "></Row>"
MyRS.MoveNext
Loop

Print #1, " </Table1>"
Close #1

End Sub
*****************************************************************

How can I do something like this with a query instead of a table? I
already have the special characters issue covered and I have to use
XML for reasons outside of my control.

Thanks,
Drew
 
Change the line
Set MyRS = MyDB.OpenRecordset("SELECT * FROM Table1")
to

Set MyRS = MyDB.OpenRecordset("SELECT * FROM Query1")
Click to expand...

where query 1 is the name of the query you want to output as XML.

As this is DAO code, make sure you select MS DAO 3.6 (or highest version you
have) under Tools|References when in a module.
 
Back
Top