Using the XML Features of the Access 2003 Object Model

Frank Rice
Microsoft Corporation

September 2004

Applies to:
    Microsoft Office Access 2003

Summary: Microsoft Office 2003 adds a number of significant features for working with XML data. This article explores the XML-related objects, methods, and properties for Microsoft Office Access 2003. Detailed information on these new members is provided as well as code samples illustrating their use. After completing this article, you will have a better understanding of how you can put these features to use in your own solutions. (10 pages)

Note   This is one of two articles previously published as "Using the XML Features of the Microsoft Office Access 2003 and Microsoft Office Excel 2003 Object Models." Based on customer feedback, we republished this article separately. For more information about XML features of Excel, see XmlMaps Collection.

Contents

Background
Changes to the XML Object Model in Access 2003
Code Samples Using XML in Access
Using the Access 2003 XML Object Model
Conclusion

Background

Microsoft Access 2002 was the first version of Access to add support for Extensible Markup Language (XML). In Access 2002, you can import the structure of XML data as contained in an XML Schema Definition (XSD) language file and, optionally, you can import the XML data. The XSD language defines the structure of XML tags and the data they contain. You can also export XML data along with its schema, either to an external XSD file or embedded directly in the XML data file. And finally, in Access 2002 you can include an Extensible Stylesheet Language (XSL) file when exporting XML in order to define the appearance of the data. The XSL file is created along with a Hypertext Markup Language (HTML) file for data that will be displayed on a local computer, or created with an Active Server Page (ASP) to display the data on a server.

Programmatically, Access 2002 added two new methods to work with XML: ImportXML and ExportXML. The ImportXML method is used to import XML data and schema information into Microsoft SQL Server 2000 Desktop Engine, Microsoft SQL Server 7.0 or later, or the Microsoft Jet Database Engine. The ExportXML method allows you to export XML data, schemas, and presentation information from SQL Server 2000 Desktop Engine, SQL Server 6.5 or later, or the Microsoft Jet Database Engine.

Changes to the XML Object Model in Access 2003

Access 2003 updates the ExportXML method to support such features as exporting related table and persisting existing sorts and filters by adding the optional parameters OtherFlags, UseSort, UseFilter, and AdditionalData. In addition, it adds the AdditionalData object and the TransformXML method. This section presents a series of sample code segments illustrating the use of these methods and objects. Details of these Access object model additions and changes are also detailed in this section.

Code Samples Using XML in Access

The examples below present sample code for performing various operations using the XML-related members of the Access object model:

Export Data, Schema, Formatting, and Related Tables to XML

This subroutine exports the table called Orders in the current database as well as a number of related tables to an XML file. The schema and the formatting are also exported as separate .xsd and .xsl files, respectively. Existing files are overwritten. This procedure assumes that you have a database with tables named Categories, Customers, Employees, Orders, Orders Details, Orders Details Details, Products, Product Details, Product Details Details, Shippers, and Suppliers. You also need a hard drive formatted as C: to run the procedure as is.

Private Sub ExportRelTables()
   ' Purpose: Exports the Orders table as well as 
   ' a number of related database to an XML file.
   ' XSD and XSL files are also created.

   Dim objAD As AdditionalData

   ' Create the AdditionalData object.
   Set objAD = Application.CreateAdditionalData

   ' Add the related tables to the object.
   With objAD
      .Add "Order Details"
      objAD(Item:="Order Details").Add "Order Details Details"
      .Add "Customers"
      .Add "Shippers"
      .Add "Employees"
      .Add "Products"
      objAD(Item:="Products").Add "Product Details"
      objAD(Item:="Products")(Item:="Product Details").Add _
         "Product Details Details"
      .Add "Suppliers"
      .Add "Categories"
   End With
   Application.ExportXml acExportTable, "Orders", _
       "C:\Orders.xml", "C:\OrdersSchema.xsd", _
       "C:\OrdersStyle.xsl", AdditionalData:= objAD
End Sub

Export an Access Report to XML

This procedure exports the Invoice report in the current database to an XML file. And it also exports presentation information, and places images in the Images folder. The procedure exports the report to the default HTML wrapper. In addition, it creates a file containing the ReportML list. ReportML is a language that describes the structure of Access forms, reports, and data access pages, along with their events and properties. For more information about ReportML, see the article About ReportML in Access 2002. This procedure assumes that you have a database with a report named Invoice. You also need a hard drive formatted as C: to run the procedure as is.

Private Sub ExportReport()
   ' Purpose: Exports the Invoice table as well as 
   ' the presentation and image files. In addition,
   ' a file containing the ReportML is created as 
   ' denoted by setting the OtherFlags flag equal 
   ' to 16.
   Const CREATE_REPORTML = 16

    Application.ExportXML _
        ObjectType:=acExportReport, _
        DataSource:="Invoice", _
        DataTarget:="C:\Invoice.xml", _
        PresentationTarget:="C:\InvoiceReport.xsl", _
        ImageTarget:="C:\Images", _
        OtherFlags:=CREATE_REPORTML

End Sub

Import the Structure and Data of an XML File

The following subroutine imports the data and structure of an XML file into a table in the current database. The name of the table is the root element in the XML file. Access appends a number to the table, if a table with that name already exists.

Private Sub ImportXMLFile()
   ' Purpose: Imports the data and structure of an 
   ' XML file.
   Const STRUCTURE_AND_DATA = 1

   Application.ImportXML DataSource:="C:\Orgchart.xml", _
       ImportOptions:=STRUCTURE_AND_DATA

End Sub

Transform Data By Using the DOM

This subroutine loads the data from an XML file into a DOMDocument object, applies the XSL file to transform the data, and saves the result to a different XML file. For more information about the DOMDocument object and the DOM, see Unlocking Office XML Data Through the XML Document Object Model. For more information about transforming XML files by using XSL Transformations (XSLT), see Transforming Microsoft Access XML Files into HTML with XSLT.

Sub XformXML()
    ' Purpose: Loads XML data into a DOMDocument object,
    ' applies a transform, and then save the result to
    ' a different XML file.

    Application.TransformXML _
        DataSource:="C:\EmployeesMapped.xml", _
        TransformSource:="C:\SortNames.xsl", _
        OutputTarget:="C:\XformedEmployees.xml", _
        WellFormedXMLOutput:=False

End Sub

Using the Access 2003 XML Object Model

The following tables provide more detailed information about the objects and methods illustrated in the previous code samples.

ExportXML Method

Exports data, schema, and/or presentation information for the specified Access object as XML files.

Syntax

expression.ExportXML (ObjectType As AcExportXMLObjectType, Datasource As String, 
Optional DataTarget As String, Optional SchemaTarget As String, 
Optional PresentationTarget As String, Optional ImageTarget As String, 
Optional Encoding As AcExportXMLEncoding, Optional OtherFlags As Long, 
Optional UseFilter As Boolean, Optional UseSort as Boolean, Optional 
AdditionalData As AdditionalData)

Table 1. The parameters for the ExportXML Method

Table 2. More information about the AcExportXMLOtherFlags parameter

The following code sample exports the table named Customers in the current database to an XML file; the data and schema are exported as separate files.

Application.ExportXML _
    ObjectType:=acExportTable, _
    DataSource:="Customers", _
    DataTarget:="Customers.xml", _
    SchemaTarget:="CustomersSchema.xml"

The following example exports the report called Fall2000 in the current database to an XML file. To be example also exports presentation information, and places images in the specified directory. It exports the report with an ASP wrapper rather than the default HTML wrapper.

Application.ExportXML _
    ObjectType:=acExportReport, _
    DataSource:="Fall2000", _
    DataTarget:="Fall2000.xml", _
    PresentationTarget:="Fall2000Report.xsl", _
    ImageTarget:="Images", _
    OtherFlags:=acRunFromServer

AdditionalData Object

Contains any additional tables, besides the base table, that are exported when exporting related tables.

Add Method

Adds AdditionalData objects.

Example

This statement adds an AdditionalData object named Name and returns it to the calling procedure.

AdditionalData.Add(Name As String) As AdditionalData

The following example exports the contents of the Customers table in the Northwind Traders sample database, along with the contents of the Orders and Orders Details tables, to an XML data file named Customer Orders.xml.

Sub ExportCustomerOrderData()
    Dim objOrderInfo As AdditionalData
    
    Set objOrderInfo = Application.CreateAdditionalData
    
    ' Add the Orders and Order Details tables to the data to be exported.
    objOrderInfo.Add "Orders"
    objOrderInfo.Add "Order Details"
    
    ' Export the contents of the Customers table. The Orders and Order
    ' Details tables will be included in the XML file.
    Application.ExportXML ObjectType:=acExportTable, DataSource:="Customers", _
                          DataTarget:="Customer Orders.xml", _
                          AdditionalData:=objOrderInfo
End Sub

Name Property

Returns the Name of the AdditionalData object as specified in the Add method. This is a read-only property.

Example

AdditionalData.Name As String

ImportXML Method

Imports data and/or presentation information for an Access object from an XML file or files.

Syntax

expression.ImportXML(DataSource As String, ImportOptions As Long)

Table 3. Parameters for the ImportXML Method

Table 4. Enumerations for the ImportXML Method

The following example imports an XML file into a new table named Employees in the current database.

Application.ImportXML _
    DataSource:="employees.xml", _
    ImportOptions:=acStructureAndData

TransformXML Method

This method loads the DataSource argument into a DOMDocument object, applies the TransformSource argument, and saves the result to the OutputTarget argument.

Syntax

expression.TransformXML(DataSource As String, TransformSource As String, 
OutputTarget As String, WellFormedXMLOutput As Boolean = False, 
ScriptOption As AcTransformXMLScriptOption = acPromptObjects)

Table 5. Parameters for the TransformXML Method

The following example converts a file called Input.xml into a file called Newfile.xml using an XSLT transform called Transform.xsl:

Application.TransformXML "C:\Input.xml", _
 "C:\Transform.xsl","C:\Newfile.xml"

Conclusion

In this article, we reviewed the XML features of the latest version of Access. Access 2003 adds several new XML features which enable you, among others things, to interact with XML by importing and exporting data. Add these objects and methods to your own applications to make them more versatile and useful.