How to export XML file per record?

[lilihope824 via AccessMonster.com]

I have a table with hundreds of unique records. How can I export them into a
XML per record? Please help!

 

[John Nurick]

To do this you need to write a VBA procedure. You need to open a
recordset and loop through the records, exporting each one. Presumably
there's one field in the table that contains unique values that can be
used to generate the filenames: I'll assume this field is a numeric data
type and is named ID.

What follows is "air code" and will need modification to fit your exact
circumstances.

Sub ExportXMLPerRec()
Dim rsR As DAO.Recordset

Set rsR = CurrentDB.OpenRecordset("MyTable", dbOpenSnapshot)

Do Until rsR.EOF
Application.ExportXML acExportTable, "MyTable", _
"C:\Temp\wtf\" & rsR.Fields("ID").Value & ".xml", , , , , , _
"[ID] = " & rsR.Fields("ID").Value
rsR.MoveNext
Loop
rsR.Close
End Sub

If ID is a text field, you'll need to add apostrophes or quotes:

...
"[ID] = '" & rsR.Fields("ID").Value & "'"

or
"[ID] = """ & rsR.Fields("ID").Value & """"

 

[cheng]

I am new in Access. Thank you for giving the details of the VBA procedure
showing how to do this. But how do I get this procedure run? Please advise.
Thanks

To do this you need to write a VBA procedure. You need to open a
recordset and loop through the records, exporting each one. Presumably
there's one field in the table that contains unique values that can be
used to generate the filenames: I'll assume this field is a numeric data
type and is named ID.

What follows is "air code" and will need modification to fit your exact
circumstances.

Sub ExportXMLPerRec()
Dim rsR As DAO.Recordset

Set rsR = CurrentDB.OpenRecordset("MyTable", dbOpenSnapshot)

Do Until rsR.EOF
Application.ExportXML acExportTable, "MyTable", _
"C:\Temp\wtf\" & rsR.Fields("ID").Value & ".xml", , , , , , _
"[ID] = " & rsR.Fields("ID").Value
rsR.MoveNext
Loop
rsR.Close
End Sub

If ID is a text field, you'll need to add apostrophes or quotes:

...
"[ID] = '" & rsR.Fields("ID").Value & "'"

or
"[ID] = """ & rsR.Fields("ID").Value & """"

I have a table with hundreds of unique records. How can I export them into a
XML per record? Please help!

 

[John Nurick]

You should use a form. Put a commandbutton on the form, and put the body
of the code (i.e. excluding the "Sub ..." at the beginning and "End Sub"
at the end) into the commandbutton's Click event procedure.

I am new in Access. Thank you for giving the details of the VBA procedure
showing how to do this. But how do I get this procedure run? Please advise.
Thanks

To do this you need to write a VBA procedure. You need to open a
recordset and loop through the records, exporting each one. Presumably
there's one field in the table that contains unique values that can be
used to generate the filenames: I'll assume this field is a numeric data
type and is named ID.

What follows is "air code" and will need modification to fit your exact
circumstances.

Sub ExportXMLPerRec()
Dim rsR As DAO.Recordset

Set rsR = CurrentDB.OpenRecordset("MyTable", dbOpenSnapshot)

Do Until rsR.EOF
Application.ExportXML acExportTable, "MyTable", _
"C:\Temp\wtf\" & rsR.Fields("ID").Value & ".xml", , , , , , _
"[ID] = " & rsR.Fields("ID").Value
rsR.MoveNext
Loop
rsR.Close
End Sub

If ID is a text field, you'll need to add apostrophes or quotes:

...
"[ID] = '" & rsR.Fields("ID").Value & "'"

or
"[ID] = """ & rsR.Fields("ID").Value & """"

I have a table with hundreds of unique records. How can I export them into a
XML per record? Please help!

 

[cheng]

Ok, I did use the commandbutton in a form. I followed your instruction not to
include the Sub...and the End Sub...but when I click the command key, it
showed Compile error: Method or data member not found...and it highligted .
ExportXML
why? please help again.

You should use a form. Put a commandbutton on the form, and put the body
of the code (i.e. excluding the "Sub ..." at the beginning and "End Sub"
at the end) into the commandbutton's Click event procedure.

I am new in Access. Thank you for giving the details of the VBA procedure
showing how to do this. But how do I get this procedure run? Please advise.

[quoted text clipped - 40 lines]

 

[John Nurick]

Application.ExportXML is only available in recent versions of Access. As
far as I can remember it first appeared in Access 2002 and was improved
in Access 2003. What version are you using?

Ok, I did use the commandbutton in a form. I followed your instruction not to
include the Sub...and the End Sub...but when I click the command key, it
showed Compile error: Method or data member not found...and it highligted .
ExportXML
why? please help again.

You should use a form. Put a commandbutton on the form, and put the body
of the code (i.e. excluding the "Sub ..." at the beginning and "End Sub"
at the end) into the commandbutton's Click event procedure.

I am new in Access. Thank you for giving the details of the VBA procedure
showing how to do this. But how do I get this procedure run? Please advise.

[quoted text clipped - 40 lines]

Please respond in the newsgroup and not by email.

 

[lilihope824 via AccessMonster.com]

Thank you very much for being so helpful! It WORKS!

Application.ExportXML is only available in recent versions of Access. As
far as I can remember it first appeared in Access 2002 and was improved
in Access 2003. What version are you using?

Ok, I did use the commandbutton in a form. I followed your instruction not to
include the Sub...and the End Sub...but when I click the command key, it

[quoted text clipped - 15 lines]