export xml - slow Access 2007

I have a temp table in Access 2007 that is the data source for export
to XML. Outputting the XML file is very slow - even when the file is
created locally (accdb is on my C:drive -- and output is to C

The temp table contains about 700 records of with some 40 fields.
Schema file is not created. Just a single file of element-centered
XML. Takes about 35-40 seconds to generate the file locally. This is a
relatively small data set compared others that I will need to export.
They can be as large as 14,000 records. And in production, the file
must be generated to a network location, which would presumably be
even slower.

Here's the VBA used for output:

Application.ExportXML ObjectType:=acExportQuery, DataSource:="NewGL",
_
DataTarget:="C:\TApps\Pvr\NewGL.xml"

Does anyone know of ways to make the export faster? Thanks for any
suggestions.

Gary wrote:
I have a temp table in Access 2007 that is the data source for export
to XML. Outputting the XML file is very slow - even when the file is
created locally (accdb is on my C:drive -- and output is to C

The temp table contains about 700 records of with some 40 fields.
Schema file is not created. Just a single file of element-centered
XML. Takes about 35-40 seconds to generate the file locally. This is a
relatively small data set compared others that I will need to export.
They can be as large as 14,000 records. And in production, the file
must be generated to a network location, which would presumably be
even slower.

Here's the VBA used for output:

Application.ExportXML ObjectType:=acExportQuery, DataSource:="NewGL",
_
DataTarget:="C:\TApps\Pvr\NewGL.xml"

Does anyone know of ways to make the export faster? Thanks for any
suggestions.

Once you know the structure of the XML file, why not hard code strings
for the parts that don't change? Then you can base a recordset on the
query and fill arrays with the values in a single pass using ADO or DAO.
After that, printing the variable XML part to the output file should
proceed quite rapidly within, say, two loops. Note that the array index
can only go up to 32,767 if it is not mathematically shifted to utilize
the negative indices, so 14K records should fit. It would be
interesting to compare the time you get with the time that ExportXML takes.

James A. Fortune

Gary wrote:

It's working now - and I'm pleased with the result -- 5 seconds to
output instead of 45. Being new to XML, I had included the "-" and "+"
handles in the static code that I wrote out. The "Invalid at the top
level of the document" error went away after that change. Also had a
few other adjustments to make to that my output matched ExportXML
output exactly.

Thanks for your help! This technique will be very useful.

Thanks for the information. Seeing some hard numbers gives me a better
feel for how much time parts of the ExportXML process take.

James A. Fortune

Gary wrote:

It's working now - and I'm pleased with the result -- 5 seconds to
output instead of 45. Being new to XML, I had included the "-" and "+"
handles in the static code that I wrote out. The "Invalid at the top
level of the document" error went away after that change. Also had a
few other adjustments to make to that my output matched ExportXML
output exactly.

Thanks for your help! This technique will be very useful.

Thanks for the information. Seeing some hard numbers gives me a better
feel for how much time parts of the ExportXML process take.

James A. Fortune