Export (0) Print
Expand All

Using the XML Features of the Access 2003 Object Model

Office 2003
 

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

Parameter Type Description
ObjectType AcExportXMLObjectType The type of Access object to export.
Datasource String The name of the Access object to export. The default is the currently open object of the type specified by ObjectType.
DataTarget String The file name and path for the exported data. If this argument is omitted, data is not exported.
SchemaTarget String The file name and path for the exported schema information. If this argument is omitted, schema information is embedded in the data document.
PresentationTarget String The file name and path for the exported presentation information. If this argument is omitted, presentation information is not exported.
ImageTarget String The path for exported images. If this argument is omitted, images are not exported.
Encoding AcExportXMLEncoding The text encoding to use for the exported XML. The default is UTF8 encoding.
OtherFlags Long Numeric expression that is the sum of values specifying the other flags that should be used. The flags and their values are shown below:
UseFilter Boolean If the value is True, the Filter property of the object is used to construct the WHERE clause. If the value is False, nothing will be added to the WHERE clause. The default is False.
UseSort Boolean If the value is True, the Sort property is used to construct the ORDER BY clause. If the value is False, nothing will be added to the ORDER BY clause. The default is False.
AdditionalData AdditionalData Object that describes the other tables to export. This will be ignored if acLiveReportSource is specified.

Table 2. More information about the AcExportXMLOtherFlags parameter

Parameter Description Value
acEmbedSchema Embeds the schema in the XML file. 1
acExcludePrimaryKeyAndIndexes Export the ReportML. 2
acRunFromServer Creates an ASP wrapper; otherwise, default is an HTML wrapper. Only applies to reports. 4
acLiveReportSource Connection information for a report containing live data. Only applies to reports. 8
acPersistReportML Primary key fields and indices aren't exported. 16

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

Parameter Type Description
DataSource String The name and path of the XML file to import.
ImportOptions AcImportXMLOption A bit mask which specifies other behaviors associated with importing from XML. The following table describes the behavior that results from specific values; values can be added to specify a combination of behaviors.

Table 4. Enumerations for the ImportXML Method

Enumerations Description Value
acStructureOnly Import just the schema. 0
acStructureAndData Imports both the data and the schema. 1
acAppendData Append the data to the existing table. 2

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

Parameter Type Description
DataSource String The name and path of the XML file to import. This can be a URL or a file path.
TransformSource String The XSL file to apply to the DataSource.
OutputTarget String The file name and path for the results after applying the TransformSource. If OutputTarget equals DataSource, the DataSource will be overwritten with the output of the TransformSource.
WellFormedXMLOutput Boolean If True, the TransformNodeToObject method of the DOM is used. This requires that the output be well-formed XML. If False, the TransformNode method of the DOM is used. This does not require that the output be well-formed XML. Default is False.
ScriptOption AcTransformXMLScriptOptions If set to acPromptObjects, a prompt will appear before creating any objects in script in the transform. If set to acEnableObjects, any script will automatically run. If set to acDisableObjects, the transform will fail if it tries to create any object. Default is acPromptObjects.

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.

Show:
© 2014 Microsoft