Exporting to XML Using the ExportXML Method

Use the Microsoft Office Access 11.0 Object Library ExportXML method of the Application object to export XML data, schemas (XSD), and presentation information (XSL) from a Microsoft Access database (.mdb), Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), or Microsoft SQL Server 6.5 or later.

The ExportXML method takes a number of arguments that are shown in Table 29-1.

Table 29-1: Arguments of the ExportXML method (in order of appearance)

Argument Type

Data Type


ObjectType (required)

AcExportXMLObjectType Use one of the following constants: Constant Value acExportDataAccessPage 6 acExportForm 2 acExportFunction 10 acExportQuery 1 acExportReport 3 acExportServerView 7 acExportStoredProcedure 9 acExportTable 0

Specifies the type of Access object to export.

DataSource (required)


Indicates the name of the Access object specified in the ObjectType argument.

DataTarget (optional)


Specifies the path and filename for the exported data. Omit this argument only if you don't want the data to be exported.

SchemaTarget (optional)


Specifies the path and filename for the exported schema information. Omit this argument only if you don't want the schema to be exported to a separate file.

PresentationTarget (optional)


Specifies the path and filename for the exported presentation information. Omit this argument only if you don't want the presentation information to be exported.

ImageTarget (optional)


Specifies the path for the exported images. Omit this argument if you don't want to export images.

Encoding (optional)

AcExportXMLEncoding Use one of the following constants: Constant Value acUTF16 1 acUTF8 0 The default is acUTF8.

Specifies the text encoding for the exported data.

Part V

Argument Type

Data Type


OtherFlags (optional)


Use one or more of the following constants:

Constant Value acEmbedSchema 1 acExcludePrimary- 2

KeyAndIndexes acLiveReportSource 8 acPersistReportML 16 acRunFromServer 4

Specifies behaviors associated with exporting to XML. Values can be added to specify a combination of behaviors. Here is the meaning of the constants:

(1) Write schema information into a separate document specified by the DataTarget argument. This value takes precedence over the SchemaTarget argument.

(2) Does not export primary key and index schema properties. (8) Used only when exporting reports bound to SQL Server 2000. Will create a live link to a Microsoft SQL Server database.

(16) Persists the exported object's ReportML file.

(4) Used only when exporting reports. Creates an Active Server Pages (ASP) or HTML wrapper. The default is HTML.

WhereCondition (optional)


Specifies subset of records to export.

AdditionalData (optional)


AdditionalData is an Access object that represents the collection of tables and queries that will be included with the parent table that is exported by the ExportXML method (see Hands-On 29-8).

Specifies additional tables to export. This argument is ignored if the OtherFlags argument is set to acLiveReportSource (8).

In its simplest form, the ExportXML method looks like this:

Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Customers", _

DataTarget:= "C:\Learn_XML\Northwind_Customers.xml"

The above statement, when typed in the Visual Basic Editor's Immediate window or in a Visual Basic module (inside a VBA procedure stub), will render the Customers table in the XML format in the Northwind_Customers.xml file.

Using Table 29-1 you can easily write the command to export the XML Products table with its schema and presentation information placed in separate files:

Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Products", _

DataTarget:= "C:\Learn_XML\Northwind_Products.xml", _ SchemaTarget:= "C:\Learn_XML\Northwind_ProdSchema.xsd", _ PresentationTarget:= "C:\Learn_XML\Northwind_ProdReport.xsl"

Taking Your VBA Programming Skills to the Web

To export a specific customer's data to an XML data file, use the following statement:

Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Customers", _ DataTarget:="C:\Learn_XML\OneCustomer.xml", _ WhereCondition:="CustomerID = 'GROSR'"

^^ Note: You can try the above statements in the Immediate window. Recall that you must not type the underscore character (type the entire statement on one line with arguments separated by commas).

Hands-On 29-8 demonstrates how to export to XML three tables: Customers, Orders, and Order Details.

0 0

Post a comment