Re: Export Data to Multiple XML Files

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
Skold@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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?
.

Relevant Pages

  • Re: Determining if a form has a table or query recordset source
    ... how to obtain the primary key of a query? ... the generic answer is: "who says any query has one?". ... > set for insertion into an audit trail table, ... > Dim audID As Long ...
    (microsoft.public.access.security)
  • Re: Determining if a form has a table or query recordset source
    ... There is no such thing as the primary key ... Not just any query. ... >> used the Products Table as the .RecordSource property. ... Dim audID As Long ...
    (microsoft.public.access.security)
  • Re: Form Result Source ->Query/Table (Or)
    ... i think it is possible to find primary keys by reading query sql, ... i think the best is to store primary key fields name also in tag or other ... > set for insertion into an audit trail table, ... Dim audID As Long ...
    (microsoft.public.access.modulesdaovba)
  • Re: Singling out fields by text primary key
    ... string that starts with number and may ... Dim strField As String ... So....in query, ... My primary key are numbers, but are defined as being text (as I ...
    (microsoft.public.access.queries)
  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)