Importing From and Exporting to XML

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Wes Kim
Microsoft Corporation

April 2001

Applies to:
   Microsoft® Access 2002

Summary: Describes the new ImportXML and ExportXML methods in Microsoft Access 2002 and includes a sample file. (5 printed pages)

Download ODC_AccessXML.exe.

Contents

Introduction ImportXML Method ExportXML Method

Introduction

Transporting information across the Web and between applications has always been difficult due to data format differences. Through the use of the Extensible Markup Language (XML), data, schemas, and presentation information can be imported to and exported from Microsoft® Access 2002.

Two new methods have been added to the Microsoft Access 10.0 Object Library for handling XML: ImportXML and ExportXML. The following are descriptions of these methods and how they are used.

Note Due to changes in the implementation of these methods prior to the release of Access 2002, these descriptions replace the descriptions in the Access Visual Basic® Help.

ImportXML Method

The ImportXML method imports XML data and/or schema information into Microsoft SQL Server 2000 Desktop Engine, Microsoft SQL Server 7.0 or later, or the Microsoft Jet Database Engine.

Syntax

expression.ImportXML(DataSource, ImportOptions)

*expression

  • Required. An expression that returns an Application object.

DataSource
*** Required String*. The file name and path of the XML file to import.

ImportOptions
*** Optional AcImportXMLOption*. The option to use when importing XML files.

AcImportXMLOption can be one of these AcImportXMLOption constants.
acAppendData Only the data is imported from the specified data source; the schema information is ignored. The data is appended to a table corresponding to the name of the XML file if such a table already exists. If no such table exists, the data is appended to a new table.
acStructureAndDatadefault Both the data and the schema information are imported from the specified data source to a new table. The default name for the new table is the name of the XML file minus any file extension. If a table with the default name already exists, Access will generate a unique name for the new table.
acStructureOnly Only the schema information is imported from the specified data source to a new table; the data is ignored. The default name for the new table is the name of the XML file minus any file extension. If a table with the default name already exists, Access will generate a unique name for the new table.

Remarks

Microsoft SQL Server 2000 Desktop Edition or Microsoft SQL Server 2000 must be installed on the local machine in order to import an XML file to a database of the corresponding type.

If Access encounters errors during import, they will be recorded in a table named ImportErrors, but no runtime error will occur.

Example

The following example imports an XML file into a new table named Employees in the current database (a link to a sample XML file that can be used with this code example is included at the beginning of this article).

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

ExportXML Method

The ExportXML method allows developers to export XML data, schemas, and presentation information from the Microsoft SQL Server 2000 Desktop Engine, Microsoft SQL Server 6.5 or later, or the Microsoft Jet Database Engine.

Syntax

expression.ExportXML(ObjectType, DataSource, DataTarget, SchemaTarget, 
PresentationTarget, ImageTarget, Encoding, OtherFlags)

*expression

  • Required. An expression that returns an Application object.

ObjectType
*** Required AcExportXMLObjectType*. The type of Access object to export.

AcExportXMLObjectType can be one of these AcExportXMLObjectType constants.
acExportForm
acExportFunction
acExportQuery
acExportReport
acExportServerView
acExportStoredProcedure
acExportTable

DataSource
*** Required String*. The name of the Access object to export. The default is the currently-opened object of the type specified by ObjectType.

DataTarget
*** Optional String*. The file name and path for the exported data. If this argument is omitted, the data is not exported.

SchemaTarget
*** Optional String*. The file name and path for the exported schema information. If this argument is omitted, schema information is not exported to a separate XML file.

PresentationTarget
*** Optional String*. The file name and path for the exported presentation information. If this argument is omitted, presentation information is not exported.

ImageTarget
*** Optional String*. The path for exported images. If this argument is omitted, images are not exported.

Encoding
*** Optional AcExportXMLEncoding*. The text encoding to use for the exported XML.

AcExportXMLEncoding can be one of these AcExportXMLEncoding constants.
acUTF16
acUTF8default

OtherFlags
*** Optional Long*. A bit mask which specifies other behaviors associated with exporting to XML. The following table describes the behavior that results from specific values; values can be added to specify a combination of behaviors.

Value Description
1 Embed schema Writes schema information into the document specified by the DataTarget argument; this value takes precedence over the SchemaTarget argument.
2 Exclude primary key and indexes Does not export primary key and index schema properties.
4 Run from server Creates an Active Server Pages (ASP) wrapper; otherwise, the default is an HTML wrapper. Only applies when exporting reports.
8 Live report source Creates a live link to a remote Microsoft SQL Server 2000 database. Only valid when exporting reports bound to Microsoft SQL Server 2000.
16 Persist ReportML Persists the exported object's ReportML file.

Remarks

While the DataTarget, SchemaTarget, and PresentationTarget arguments are all optional, at least one must be specified when using this method.

When the ExportXML method is called from within an Access object, the default behavior is to overwrite any existing files specified in any of the arguments. When the ExportXML method is called from within a data access page, the default behavior is to prompt the user before overwriting any existing files specified in any of the arguments.

Example

The following example 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:="C:\XML\Customers.xml", _
    SchemaTarget:="C:\XML\CustomersSchema.xml"

The following example exports the report called Fall2000 in the current database to an XML file. Presentation information is also exported, and images are placed in the specified directory. The report is exported with an ASP wrapper rather than the default HTML wrapper.

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