Export query to XML file

[Boon]

Hi,

I am trying to export a select query to a XML file but didn't find any
success.

Here is the code I use......

Application.ExportXML ObjectType:=acExportTable, DataSource:="ForXML",
DataTarget:="VendorData.xml"


The ForXML is the select query. I noticed that when I export from a table it
works just fine.

How can I export from a query?

thanks!
Boon

 

[Dirk Goldgar]

Hi,

I am trying to export a select query to a XML file but didn't find any
success.

Here is the code I use......

Application.ExportXML ObjectType:=acExportTable, DataSource:="ForXML",
DataTarget:="VendorData.xml"


The ForXML is the select query. I noticed that when I export from a table
it works just fine.

How can I export from a query?

If you're exporting a query, use acExportQuery, not acExportTable.

 

[Boon]

thanks.

If I do in VBA, and have and SQL statement like "SELECT ......"

Can I export this aswell?

 

[Boon]

More finding.

I found out that I cannot use parameterized Select Query to do XMLExport.

Any suggesstion is appreciated.

 

[Dirk Goldgar]

thanks.

If I do in VBA, and have and SQL statement like "SELECT ......"

Can I export this aswell?

You mean, export the output of a SQL statement directly, without it being
embodied in a stored query or other Access object? I don't think so. A
workaround would be to have a stored query that you use for export, and
dynamically set its .SQL property to your SQL statement, then export it.
Something like:

Dim strSQL As String

strSQL = "SELECT Foo FROM Bar WHERE Baz > 0"

CurrentDb.QueryDefs("qryExport").SQL = strSQL

Application.ExportXML ObjectType:=acExportQuery, _
DataSource:="qryExport", _
DataTarget:="MyExportedQuery.xml"

Note: in the above, I'm assuming that qryExport has already been created.