Recipe 18.6 Export Unrelated Tables

18.6.1 Problem

You want to export Access data to a single XML file, but the tables you wish to export are not related to each other. How do you select tables and create a single XML output file?

18.6.2 Solution

You must write VBA code to export multiple unrelated tables to a single XML file. The Access object model provides the ExportXML method, which has an AdditionalData parameter that takes an object of type AdditionalData.

The 18-06.MDB sample database contains three unrelated tables: Car, Customer, and Dealer. There is a single module, basExport, which contains the function ExportUnrelated. The code creates an AdditionalData object, and adds the Customer and Dealer tables to it. The ExportXML method uses the Car table as the DataSource, and uses the AdditionalData object to add the Customer and Dealer data to the output:

  Dim adTables As AdditionalData
  Set adTables = Application.CreateAdditionalData
  adTables.Add "Customer"
  adTables.Add "Dealer"
  Application.ExportXML _
    ObjectType:=acExportTable, _
    DataSource:="Car", _
    DataTarget:="c:\test\Unrelated.xml", _
    AdditionalData:=adTables

Figure 18-17 shows the XML file in a browser, with only one element from each table expanded. All of the data from all of the tables has been exported.

Figure 18-17. The XML output for unrelated tables

18.6.3 Discussion

A number of enhancements were added to the Access object model to facilitate importing and exporting XML data programmatically. The full syntax and all of the optional arguments for the ExportXML method are shown here:

ExportXML (ObjectType As AcExportXMLObjectType, Datasource As String,
 [DataTarget As String], [SchemaTarget As String], [PresentationTarget as String],
 [ImageTarget As String], [Encoding As AcExportXMLEncoding], [OtherFlags As Long],
 [WhereCondition As String], [AdditionalData as AdditionalData])

The OtherFlags optional argument, which was not used in the example, allows you to specify the following self-descriptive options, which are exposed as AcExportXMLOtherFlags enumerations:

• acEmbedSchema

• acExcludePrimaryKeyAndIndexes

• acRunFromServer

• acLiveReportSource

• acPersistReportML

When you need to apply a transform programmatically for either importing or exporting XML, use the TransformXML method. The ImportXML and ExportXML methods do not have DataTransform parameters.