HandsOn Exporting Multiple Tables to an XML Data File

1. Make a copy of the Microsoft Access Northwind sample database and open it in Access.

2. In the Database window, choose Tools | Macro | Visual Basic Editor.

3. In the Visual Basic Editor window, choose Insert | Module to add a standard module to the current VBA project.

4. In the module's Code window, enter the Export_CustomerOrderDetails procedure as shown below.

Sub Export_CustomerOderDetails()

Dim objOtherTbls As AdditionalData

On Error GoTo ErrorHandle

Set objOtherTbls = Application.CreateAdditionalData

' include the Orders and OrderDetails tables in export objOtherTbls.Add "Orders" objOtherTbls.Add "Order Details"

' export Customers, Orders, and Order Details table into one XML data file Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Customers", _

DataTarget:="C:\Learn_XML\CustomerOrdersDetails.xml", _ AdditionalData:=objOtherTbls

MsgBox "Export operation completed successfully."


Exit Sub ErrorHandle:

MsgBox Err.Number & ": " & Err.Description Resume Exit_Here

End Sub

Part V

Using the AdditionalData object, you can export any set of Access tables to an XML data file. To use this object, perform the following:

■ Declare an object variable as AdditionalData: Dim objOtherTbls As AdditionalData

Create the AdditionalData object using the CreateAdditionalData method of the Application object and set the object variable to the newly created object:

Set objOtherTbls = Application.CreateAdditionalData

Use the AdditionalData object's Add method to add table names to the object:

objOtherTbls.Add "Orders" objOtherTbls.Add "Order Details"

Pass the AdditionalData object to the ExportXML method:

Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Customers", _

DataTarget:="C:\Learn_XML\CustomerOrdersDetails.xml", _ AdditionalData:=objOtherTbls

5. Place the insertion point anywhere within the Export_CustomerOrder-Details procedure code and choose Run | Run Sub/UserForm. Access executes the procedure code and displays a message.

6. Click OK to clear the informational message.

7. Switch to Windows Explorer, locate the Learn_XML folder, and open the CustomerOrdersDetails.xml file. Notice that all the requested data was placed into one file.

8. Close the browser.

Now that you know how to export Access tables to XML, let's see how Access handles other objects. Custom Project 29-2 demonstrates how to export the Invoice report from the Northwind database to an XML file together with the presentation information and images.

Taking Your VBA Programming Skills to the Web

0 0


  • uwe
    How to export multiple tables xml access file?
    8 years ago

Post a comment