Using the XML Features of the Access 2003 Object Model
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.
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.
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.
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
The following tables provide more detailed information about the objects and methods illustrated in the previous code samples.
Exports data, schema, and/or presentation information for the specified Access object as XML files.
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
|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
|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
Contains any additional tables, besides the base table, that are exported when exporting related tables.
Adds AdditionalData objects.
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
Returns the Name of the AdditionalData object as specified in the Add method. This is a read-only property.
AdditionalData.Name As String
Imports data and/or presentation information for an Access object from an XML file or files.
expression.ImportXML(DataSource As String, ImportOptions As Long)
Table 3. Parameters for the ImportXML Method
|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
|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
This method loads the DataSource argument into a DOMDocument object, applies the TransformSource argument, and saves the result to the OutputTarget argument.
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
|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"
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.