[FAQ's: OD] How do I export data from Access to an XML file?

[RobDog888]

For one reason or another you may need to create an XML file based upon data stored in an Access database. Starting with Access 2003 and newer versions, Access has a built in function to perform this task with a versy small amount of code.

object.ExportXML

ObjectType: [Required] AcExportXMLObjectType enumeration constants. Most Access Objects
• acExportForm
• acExportFunction
• acExportQuery
• acExportReport
• acExportServerView
• acExportStoredProcedure
• acExportTable

DataSource: [Required] The name of the AccessObject object to export. Default - currently open object of type specified by ObjectType. If its opened by another source, that source must not be Exclusive or your code wont be able to open the database to export data.

DataTarget: [Optional] The file name and path for the exported data. If this argument is omitted, data is not exported.

SchemaTarget: [Optional] 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] The file name and path for the exported presentation information. If this argument is omitted, presentation information is not exported.

ImageTarget: [Optional] The path for exported images. If this argument is omitted, images are not exported.

Encoding: [Optional] AcExportXMLEncoding enumeration constants. acUTF8 (Default) or acUTF16. The text encoding to use for the exported XML.
• acUTF8 (Default)
• acUTF16



Access 2003 - 2007 VBA Code

Application.ExportXML ObjectType:=acExportTable, DataSource:="Table1", DataTarget:="C:\Table1.xml"

Visual Basic 6 And Access 2003 - 2007 Code

Option Explicit
'Supported in Access 2003 and newer versions
'Add a reference to "Microsoft Access xx.0 Object Library"
'or change code to do Late Binding to support multiple Access versions
Private Sub Command1_Click()
    Dim oApp As Access.Application
    Set oApp = CreateObject("Access.Application")
    oApp.OpenCurrentDatabase "C:\RobDog888.mdb", Exclusive:=False
    oApp.ExportXML ObjectType:=acExportTable, _
                   DataSource:="Table1", _
                   DataTarget:="C:\Table1.xml"
    oApp.CloseCurrentDatabase
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
End Sub

Also, there is the ability to filter your resultset if you dont want an entire table or report exported.

Access 2003 - 2007 VBA "FilterCriteria" Code

Application.ExportXML ObjectType:=acExportTable, DataSource:="Table1", DataTarget:="C:\Table1.xml", WhereCondition:="ID=1"

Visual Basic 6 And Access 2003 - 2007 "FilterCriteria" Code

Private Sub Command2_Click()
    'Filter your exported resultset with a SQL WHERE condition argument
    Dim oApp As Access.Application
    Set oApp = CreateObject("Access.Application")
    oApp.OpenCurrentDatabase "C:\RobDog888.mdb", Exclusive:=False
    oApp.ExportXML ObjectType:=acExportTable, _
                   DataSource:="Table1", _
                   DataTarget:="C:\Table1.xml", _
                   WhereCondition:="ID=1"
    oApp.CloseCurrentDatabase
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
End Sub

There are a few more additional arguments that some may find helpful.


AdditionalData: [Optional] [Object] AdditionalData enumeration constants. Specifies additional tables to export. This argument is ignored if the OtherFlags argument is set to acLiveReportSource

OtherFlags: [Optional] AcExportXMLOtherFlags enumeration constants. A bit mask that 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.
• acEmbedSchema - Writes schema information into the document specified by the DataTarget argument; this value takes precedence over the SchemaTarget argument.
• acExcludePrimaryKeyAndIndexes - Does not export primary key and index schema properties.
• acLiveReportSource - Creates a live link to a remote Microsoft SQL Server 2000 database. Valid only when you are exporting reports that are bound to a Microsoft SQL Server 2000 database.
• acPersistReportML - Persists the exported object's ReportML information.
• acRunFromServer - Creates an Active Server Pages (ASP) wrapper; otherwise, default is an HTML wrapper. Applies only when you are exporting reports.