Using ExportXML

Using ExportXML

You can export Access data to an XML file by executing VBA's ExportXML method, which takes the form

 application.ExportXML objecttype, datasource[, datatarget] _  [, schematarget],[presentationtarget] _  [, imagetarget][, encoding][, otherflags]_  [, filtercriteria][, additionaldata] 

where application represents the Access model. Table 22.1 lists this function's many arguments. Although datatarget, schematarget, and presentationtarget are optional, you must specify at least one. Tables 22.2 and 22.3 further define the constants that you can use with some of the ExportXML arguments.

Table 22.1. ExportXML Arguments

Argument	Explanation
objecttype	An intrinsic constant that defines the type of object being exported (see Table 22.2).
datasource	A string value that identifies the object being exported by name.
datatarget	The path and filename for the XML file to which the Access data is being exported.
schematarget	The path and filename for the exported schema data (XSD file).
presentationtarget	The path and filename for the exported presentation information (XSL file).
imagetarget	The path for exported graphics and image files.
encoding	One of two constants, acUTF16 and acUTF8, that determines text encoding.
otherflags	A bit mask value (see Table 22.3). You can add the values to specify more than one option.
filtercriteria	A String value that limits the records actually exported by applying criteria.
additionaldata	Specifies additional tables for the export, but is ignored if otherflags is set to acLiveReportSource.

Table 22.2. Object Constants

Object	Constant
Form	acExportForm
Function	acExportFunction
Query	acExportQuery
Report	acExportReport
View	acExportServerView
Stored Procedure	acExportStoredProcedure
Table	acExportTable

Table 22.3. Values for the otherflags Argument

Value	Constant	Description
1	acEmbedSchema	Embed schema information with data in the XML document (specified by datatarget).
2	acExcludePrimaryKeyAndIndexes	Exclude primary key and indexes from exported schema document.
4	acRunFromServer	Create an Active Server Page (ASP) wrapper for exported report.
8	acLiveReportSource	Create a live link to remote SQL Server 2000 database for exported report.
16	acPersistReportML	Persist the exported object as ReportML, a type of XML that was specifically designed for Access reports.

CAUTION

VBA's ExportXML function writes over existing files. If you don't want to overwrite existing files, you must check for the file before beginning the export process and then rename the existing file, specify a new name for the file you're about to create, or cancel the task.

An Export Example

The simplest XML export creates a single XML file. For instance, the following procedure exports the client data to an XML file:

 Sub ExportTable(source As String, path As String, _  target As String)   'Export source to XML file   Access.Application.ExportXML acExportTable, source, path & target End Sub 

Enter the procedure in a standard module or use Chapter 22's sample module. When executing the procedure, pass the name of the table that contains the data you're exporting and the complete path and filename to which you want the data exported. For instance, in the Immediate window, run the following statement to export client data:

 ExportTable "Clients", path, "Clients.xml" 

Be sure to update path to accommodate your own system's directory structure, and include the .xml extension in the target argument (otherwise, the exported file won't be recognized as XML by editors and other utilities). (Remember, path is a string, so be sure to enclose it in double quotation marks.) Figure 22.1 shows the exported XML file in Word 2003, which can display XML files.

Figure 22.1. The XML file contains tags and actual data, but isn't particularly viewable in any meaningful way.

The XML format, as is, doesn't produce a particularly readable file. You'll probably never view files in this format unless you're editing the content.

This procedure handles only tables. You can add an argument and enable the users to pass the object type.

Exporting a Web-Ready File

You can quickly create a Web-ready document by simply changing the extension in the target argument from .xml (or whatever standard you're using) to .html. The results aren't perfect, but your browser will display just the data instead of the tags and data.

To illustrate, run the previous statement in the Immediate window, but change the .xml extension to .html as follows:

 ExportTable "Clients", path, "Clients.html" 

Next, view clients.html in your browser. (Use Windows Explorer to locate the file in the current directory and double-click it to open it in your system's default browser.) Figure 22.2 shows this file in Internet Explorer. It isn't perfect, but the quick result is impressive.

Figure 22.2. Quickly produce Web-quality results by simply changing the extension.

Exporting Related Data

Exporting related data via the user interface is fairly simple. Automating the process presents a bit of a challenge. The ExportXML method's additionaldata argument enables you to specify additional tables to export, but you can't just add the table's name. Instead, you append the table using the AdditionalData object.

To export related data, you must identify the related table as an AdditionalData object and then use that reference as the additionaldata setting. The following procedure creates an AdditionalData object and then uses that object's Add method to append addtable to the collection:

 Sub ExportMultipleTables(source As String, path As String, _  target As String, addtable As String)   'export related tables to XML file   Dim objAddTable As AdditionalData   Set objAddTable = Application.CreateAdditionalData   objAddTable.Add (addtable)   Access.Application.ExportXML acExportTable, source, path & target, _    , , , , , , objAddTable  End Sub 

To illustrate this procedure, run the following statement in the Immediate window (after updating path):

 ExportMultipleTables "Clients", path, "ClientsAndProjects.html", "Projects" 

Figure 22.3 shows each client with its current projects in a browser. Although the format is lacking a bit in structure, you can see that the data's all there.

Figure 22.3. Export related data programmatically to an XML file.