Export Data to Multiple XML Files

[Randy Skold]

I need to take each row of data from an Access table and export it to a
separate xml file programatically (vba) as there are over 1500 rows. How do
I do this?

 

[John Nurick]

Hi Randy,

First create a query that returns a single record from the table,
selecting on some value of the primary key. The SQL will Look
something like this, where AAA is the name of the primary key field
(assuming it's a one-field key):

SELECT * FROM MyTable WHERE AAA = 123;

Let's call the query qryExportOne. Export the query using
Application.ExportXML and make sure that the file created is
satisfactory.

After that you can do it like this (this is incomplete air code but
should give the general idea):

'Open a recordset that contains all the primary key
'values from the table

Dim dbD As DAO.Database
Dim rsR As Recordset
Dim QryName As String
Dim OutFile As String

qryName = "qryExportOne"
Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("SELECT AAA FROM MyTable;", _
dbOpenSnapshot)

'Iterate through the recordset

Do Until rsR.EoF
'Modify the query to export the current record
dbD.QueryDefs(QryName).SQL = _
"SELECT * FROM MyTable WHERE AAA = " _
& rsR.Fields("AAA").Value

'Assemble the filespec for the output file
'you'll presumably need to generate 1500 unique filenames
OutFile = blah blah blah

'Export the record
Application.ExportXML blah blah blah

'Next record
rsR.Movenext
Loop

'Tidy up
rsR.Close



On Wed, 16 Jan 2008 12:52:01 -0800, Randy Skold <Randy