Home

Microsoft Access Import/Export of XML

Visually Exporting and Importing XML

Exporting a Microsoft Access Object

If you have a well-formatted XML document somewhere in your computer or on the network, you can bring its records to your Microsoft Access database. Microsoft Access makes it possible and easy to visually import the contents of an XML and create a table. The XML file should (must) be appropriately formatted. Consider the following example:

<?xml version="1.0" encoding="utf-8"?>
<Pupils>
  <Students>
    <FirstName>Benjamin</FirstName>
    <LastName>Carson</LastName>
    <DateOfBirth>04/10/1995</DateOfBirth>
    <Gender>Male</Gender>
  </Students>
  <Students>
    <FirstName>Gertrude</FirstName>
    <LastName>Simms</LastName>
    <DateOfBirth>8/22/1993</DateOfBirth>
    <Gender>Female</Gender>
  </Students>
  <Students>
    <FirstName>Tracey</FirstName>
    <LastName>Sandt</LastName>
    <DateOfBirth>12/24/1997</DateOfBirth>
    <Gender>Unknown</Gender>
  </Students>
  <Students>
    <FirstName>Chrissie</FirstName>
    <LastName>Burchs</LastName>
    <DateOfBirth>02/06/1993</DateOfBirth>
    <Gender>Female</Gender>
  </Students>
</Pupils>

Besides the obvious root node (named Pupils), this document clearly specifies a leading element (named Students) for each section. That leading element would serve as the table name and its child nodes would serve as the columns names. In some cases, you will have a complex but valid XML document such as made of a weird organization of elements, child nodes changing from one parent element to another. As a matter of fact, consider the following example of a document made of some elements with attributes while other elements from the same levels do not have the same structure:

<?xml version="1.0" encoding="utf-8" ?>
<VideoCollection Description="Personal Video Collection">
  <Videos ISBN="0-7888-1623-3"
          Screenplay="Marty Kaplan"
          StoryBy="Marty Kaplan and Jonathan Reynold">
    <Title>The Distinguished Gentleman</Title>
    <Director>Jonathan Lynn</Director>
    <CastMembers>
	<Actor>Eddie Murphy</Actor>
	<Actor>Lane Smith</Actor>
	<Actor>Sheryl Lee Ralph</Actor>
	<Actor>Joe Don Baker</Actor>
	<Actor>Victoria Rowell</Actor>
    </CastMembers>
    <Length>112 Minutes</Length>
    <Format>DVD</Format>
    <Rating>R</Rating>
  </Videos>
  <Videos ISBN="0-7907-3900-3">
    <Title WrittenBy="Charlie Peter">Her Alibi</Title>
    <Director>Bruce Beresford</Director>
    <Length>94 Mins</Length>
    <Format>DVD</Format>
    <Rating>PG-13</Rating>
    <Actors>
	<Actor>Tom Selleck</Actor>
	<Actor>Paulina Porizkova</Actor>
	<Actor>Wllimam Daniel</Actor>
	<Actor>James Farentiona</Actor>
    </Actors>
  </Videos>
  <Videos ForeignFilm="True">
    <Title>Chalte Chalte</Title>
    <Director>Aziz Mirza</Director>
    <Length>145 Mins</Length>
    <Format>DVD</Format>
    <Rating>N/R</Rating>
  </Videos>
</VideoCollection>

Notice that one Videos element has a child node name CastMembers that has its own children. Another Videos element has a child node named Actors. If you import this type of file, the XML parser would try to create a table for each set of elements on the same level.

To visually import records from an XML file and create a table, on the Ribbon, click External Data. In the Import section, click the XML File button. This would open the Get External Data – XML File dialog box. To specify the file that has the records, click the Browse button. Locate and select the file. Then click OK. An Import XML dialog box would show up, allowing you to set some options. Once you are ready, click OK. An acknowledgement dialog box, titled Get External Data – XML File would display. Once you are ready, click Close.

Practical Learning: Importing XML Records to Microsoft Access

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the Employees.xml file database

    Importing XML Records to Microsoft Access

  3. Close the file
  4. Start Microsoft Access
  5. From the resources that accompany these lessons, open the BethesdaCarRental2a database. Notice that it has only forms:

    Bethesda Car Rental

  6. On the Ribbon, click External Data
  7. In the Import & Link section, click the XML File button
  8. In the Get External Data – XML File dialog box, click Browse
  9. Locate the folder that has the files for these lessons
  10. Click Availabilities

    File Open

  11. Click Open

    Get External Data – XML File

  12. In the Get External Data – XML File dialog box, click OK

    Import XML

  13. In the Import XML dialog box, click OK

    Get External Data – XML File

  14. In the Get External Data – XML File dialog box, click Close
  15. In the same way, import the Cars.xml file, the Conditions.xml, the Customers.xml file, the Employees.xml file, the OrderStatus.xml, the RentalRates.xml file, and the RentalsOrders.xml

Exporting a Microsoft Access Object

Microsoft Access makes it possible and easy to visually convert the records of a table or query to XML. To visually export the records of a table or view and create an XML file, perform one of the following actions:

In both cases, an Export – XML File dialog box would come up, showing the path where the file would be saved, including its name. Make the desired changes and click OK. Another dialog box would come up, asking you what to save. It also asks whether you want it to create a schema file (XSD) and a style sheet (XSL) file. Normally, all of the columns in the table or query would be included in the XML file that would be created. If you want to remove some columns, click More Options. This would open the Export XML dialog box that allows you to specify more features. To accept the options or after making your selections, click OK.

The obvious advantage of visually exporting a the records of a table or query to create an XML file is that the process is particularly easy. In fact, you don’t need to know anything about XML. So the advantage is that Microsoft Access does all the work for you. As a disadvantage, you may not like the way Microsoft Access lays out the tags.

Practical Learning: Exporting Microsoft Access Objects to XML

  1. Use Windows Explorer (or any file utility of your choice) to create a folder named BCR Repository on the C: drive
  2. From the resources that accompany these lessons, open the BethesdaCarRental2b database. Notice that it has only forms:

    Bethesda Car Rental

  3. In the Navigation Pane, double-click the Customers table to view its records
  4. Close the Customers table
  5. In the Navigation Pane, right-click the Cars table -> Export -> XML File

    Exporting Microsoft Access Objects to XML

  6. In the Export – XML File dialog box, click Browse
  7. Locate the BCR Repository folder you had created.
    Make sure Cars is specified in the File Name combo box and XML is selected in the Save As Type combo box
  8. Click Save
  9. In the Export – XML File dialog box, click OK
  10. In the Export XML dialog box, click Presentation Of  Your Data (XSL)

    Exporting Microsoft Access Objects to XML

  11. Click OK

    Get External Data – XML File

  12. In the Export – XML File dialog box, click Close
  13. In the same way, export the records of the Availabilities table, the RentalRates table, the Conditions table, the Customers table, the Employees table, the OrdersStatus table, and the RentalsOrders table

The Application Class and XML

Importing an XML File

In its large support for various types of databases, Microsoft Access has a class named Application. This class provides the ability to import records from an XML file out to export records from database objects such as tables or queries. Obviously before dealing with XML, you should create such a file and make sure it is valid. Of course, the document should start with a root element. That root should have one or more child elements. Those child elements should have the same name and the same structure (same names and same child nodes).

To provide the ability to import records from an XML file, the Application class is equipped with a method named ImportXML. Its syntax is:

Public Sub Application.ImportXML(Byval DataSource As String, _
		Optional ImportOptions As AcImportXMLOption = acStructureAndData)

This method takes one required and one optional argument. The DataSource argument is the name of the XML file that holds the records you want to import. If you provide only the file name, Microsoft Access would look in the same directory as the current database. If the file is in another directory, provide its complete path.

The optional ImportOptions argument allows you to specify whether the imported records should be placed in an existing table or a new table should be created for the records. The ImportOptions argument is based on the AcImportXMLOption enumeration. Its members are:

Value AcImportXMLOption Member Role
0 acStructureOnly A new table will be created. The tags of the XML document will be used as the names of columns
1 acStructureAndData A new table will be created using the tags of the XML document as the names of columns and the records will be added to that new table
2 acAppendData The records will be inserted into an existing table that was previously created to receive the XML records

Practical LearningPractical Learning: Importing Records From XML

  1. Use Windows Explorer (or any file utility of your choice) to create a folder named Ceil Inn on the C: drive
  2. From the resources that accompany these lessoins, from the Ceil Inn XML folder, copy the BedsTypes.xml file, the Customers.xml file, the Employees.xml file, the Occupancies.xml file, the Payments.xml file, the Rooms.xml file, the RoomsStatus.xml file, and the RoomsTypes.xml file
  3. Paste them in the Ceil Inn folder you created
  4. From the resources that accompany these lessons, from the Ceil Inn XML folder, open the Ceil Inn2

    Importing Records From XML

  5. In the Navigation Pane, double-click Rooms

    Importing Records From XML

  6. Right-click the title bar of the form and click Design View
  7. On the form, right-click the Import Rooms button and click Build Event...
  8. In the Choose Builder dialog box, double-click Code Builder
  9. Implement the event as follows: 
    Private Sub cmdImportRooms_Click()
    ImportXML "C:\Ceil Inn\Rooms.xml", acStructureAndData
    RecordSource = "Rooms"
End Sub
  10. Return to Microsoft Access
  11. From the Navigation Pane, right-click each of the other forms and click Design View
  12. On each form, right-click the Import ... button and click Build Event, then double-click Code Builder
  13. Implement the event as follows:
     
    BedsTypes 
    Private Sub cmdImportBedsTypes_Click()
    ImportXML "C:\Ceil Inn\BedsTypes.xml", acStructureAndData
    RecordSource = "BedsTypes"
End Sub
    Customers 
    Private Sub cmdImportCustomers_Click()
    ImportXML "C:\Ceil Inn\Customers.xml", AcImportXMLOption.acStructureAndData
    RecordSource = "Customers"
End Sub
    Employees 
    Private Sub cmdImportEmployees_Click()
    ImportXML "C:\Ceil Inn\Employees.xml", acStructureAndData
    RecordSource = "Employees"
End Sub
    Occupanies 
    Private Sub cmdImportOccupancies_Click()
    ImportXML "C:\Ceil Inn\Occupancies.xml", acStructureAndData
    RecordSource = "Occupancies"
End Sub
    Payments 
    Private Sub cmdImportPayments_Click()
    ImportXML "C:\Ceil Inn\Payments.xml", acStructureAndData
    RecordSource = "Payments"
End Sub
    RoomsStatus 
    Private Sub cmdImportRoomsStatus_Click()
    ImportXML "C:\Ceil Inn\RoomsStatus.xml", acStructureAndData
    RecordSource = "RoomsStatus"
End Sub
    RoomsStatus 
    Private Sub cmdImportRoomsTypes_Click()
    ImportXML "C:\Ceil Inn\RoomsTypes.xml", acStructureAndData
    RecordSource = "RoomsTypes"
End Sub
  14. Return to Microsoft Access
  15. Close a form
  16. When asked whether you want to save the form, click Yes
  17. Close each of the other forms
  18. Open the forms in the following order and click the Import ... button on each: Employees, BedsTypes, RoomsTypes, RoomsStatus, Rooms, Occupancies, Customers, and Payments
  19. Close the forms

Exporting an XML File

To easily support XML, the Application class is equipped with a method named ExportXML. Its syntax is:

Public Sub Application.ExportXML(ByVal ObjectType As AcExportXMLObjectType, _
			         ByVal DataSource As String, _
			         ByVal DataTarget As String, _
			         Optional SchemaTarget As String, _
			         Optional PresentationTarget As String, _
			         Optional ImageTarget As String, _
			         Optional Encoding As AcExportXMLEncoding = acUTF8, _
			         Optional OtherFlags As AcExportXMLOtherFlags, _
			         Optional WhereCondition As String, _
			         Optional AdditionalData As Variant)

This method takes two required arguments. The first argument specifies the type of object that holds the records. This argument is of type AcExportXMLObjectType, which is an enumeration. Its members are as follows:

Value AcExportXMLObjectType Member Type of Object
0 acExportTable Table
1 acExportQuery Query
2 acExportForm Form
3 acExportReport Report
7 acExportServer View
9 acExportStoredProcedure Stored procedure
10 acExportFunction User-defined function (Microsoft Access project only)

The DataSource argument is the name of the object that holds the records. This argument is passed as a string. If you just specify the name of the file, Microsoft Access would create the XML file in the same directory as the database. If you want, pass a complete path where you want the file created. Also provide the name of the file and the .xml extension.

The DataTarget argument is the name of the file that will be created. You must provide a name and the .xml extension. If you want the file to be created in a particular folder, provide the complete path. In most cases, the name of the file should be the same as the table or query that holds the original records. Here is an example of calling the Application. To easily support XML, the Application.ExportXML() with the required arguments:

Private Sub cmdCreateXMLFile_Click()
    Application.ExportXML acExportQuery, "PayrollSummary", "PayrollSummary.xml"
End Sub

Because the Application class is available by default, you can omit it when calling the function.

The other arguments are optional. The SchemaTarget argument specifies the name of the schema file that you want Microsoft Access to create during this operation. If you decide to pass this argumet, make sure you give the intended name of the file and an extension such as .xsd. Here is an example:

Private Sub cmdExportCustomers_Click()
    ExportXML acExportTable, "Customers", "Customers.xml", "Customers.xsd"
End Sub

The PresentationTarget argument specifies an extensible style sheet transformation file to create for the database object. If you pass this argument, specify a file name and an xsl extention.

Private Sub cmdCreateXMLFile_Click()
    Application.ExportXML acExportQuery, _
                          "SeasonalWorkers", _
                          "SeasonalWorkers.xml", _
                          "SeasonalWorkers.xsd", _
                          "SeasonalWorkers.xsl"
End Sub

If the export operation contains some images that need to be exported, the ImageTarget argument specifies the folder where the images should be stored. By default, the XML document is created with the UTF-8 encoding. If you want a different encoding, pass the Encoding argument with desired value. This argument is passed as a member of the AcExportXMLEncoding enumeartion. The available members are acUTF8 (the default) and acUTF16. Here is an example:

Private Sub cmdCreateXMLFile_Click()
    Application.ExportXML acExportQuery, _
                          "occupanciesSummary", _
                          "occupanciesSummary4.xml", _
                          "occupanciesSummary4.xsd", _
                          "occupanciesSummary4.xsl", _
                          , _
                          AcExportXMLEncoding.acUTF16
End Sub

The OtherFlags argument is passed as a logical expression that specifies what other operations should be performed when exporting XML. This argument is passed as one or a combination of members of the AcExportXMLOtherFlags enumeration. The members of this enumeration are:

Value AcExportXMLOtherFlags Member Role
1 acEmbedSchema The schema mentioned for the SchemaTarget argument will be included in the XML file. As a result, instead of creating a separate schema file, the necessary schema code will be included in the top section of the XML file
2 acExcludePrimaryKeyAndIndexes The values of the primary key(s) and those of the indexes should not be included in the XML file
4 acRunFromServer If the application is run from an ASP (Active Server Pages) server, the XML file should be kept in the server
8 acLiveReportSource If the database is run from a Microsoft SQL Server 2000 database, the operation should create a link to a report stored on the server
16 acPersistReportML If the XML file is created from a report, the operation shoupd use a language known as ReportML
32 acExportAllTableAndFieldProperties The export operation should include the properties of the table and its fields

As its name implies, the WhereCondition argument allows you to include only some records in the resulting XML file. Here is an example:

Private Sub cmdCreateXMLFile_Click()
    Application.ExportXML AcExportXMLObjectType.acExportTable, _
                          "Customers", _
                          "Customers.xml", _
                          "Customers.xsd", _
                          "Customers.xsl", _
                          , _
                          AcExportXMLEncoding.acUTF16, _
                          AcExportXMLOtherFlags.acEmbedSchema, _
                          "LastName = 'Johnson'"
                       
End Sub

If the OtherFlags argument is not passed with the acLiveReportSource value, the last argument, AdditionalData, allows you to add other tables to export.

Practical LearningPractical Learning: Importing Records From XML

  1. From the Navigation Pane, right-click each of the forms and click Design View
  2. On each form, right-click the Export ... button and click Build Event, then double-click Code Builder
  3. Implement the event as follows:
     
    BedsTypes 
    Private Sub cmdExportBedsTypes_Click()
    ExportXML acExportTable, _
              "BedsTypes", _
              "C:\Ceil Inn\BedsTypes.xml", _
              "BedsTypes.xsd", _
              "BedsTypes.xsl", _
              "BedsTypes", _
              acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "BedsTypes"
    DoCmd.DeleteObject acTable, "BedsTypes"
    DoCmd.Close acForm, "BedsTypes"
End Sub
    Customers 
    Private Sub cmdExportCustomers_Click()
    ExportXML acExportTable, _
              "Customers", _
              "C:\Ceil Inn\Customers.xml", _
              "Customers.xsd", _
              "Customers.xsl", _
              "Customers", _
              acUTF8
    
    RecordSource = ""
    DoCmd.Close acTable, "Customers"
    DoCmd.DeleteObject acTable, "Customers"
    DoCmd.Close acForm, "Customers"
End Sub
    Employees 
    Private Sub cmdExportEmployees_Click()
    ExportXML acExportTable, "Employees", _
              "C:\Ceil Inn\Employees.xml", "Employees.xsd", _
              "Employees.xsl", "Employees", acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "Employees"
    DoCmd.DeleteObject acTable, "Employees"
    DoCmd.Close acForm, "Employees"
End Sub
    Occupanies 
    Private Sub cmdExportOccupancies_Click()
    ExportXML acExportTable, "Occupancies", _
              "C:\Ceil Inn\Occupancies.xml", _
              "Occupancies.xsd", "Occupancies.xsl", _
              "Occupancies", acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "Occupancies"
    DoCmd.DeleteObject acTable, "Occupancies"
    DoCmd.Close acForm, "Occupancies"
End Sub
    Payments 
    Private Sub cmdExportPayments_Click()
    ExportXML acExportTable, "Payments", _
              "C:\Ceil Inn\Payments.xml", _
              "Payments.xsd", "Payments.xsl", "Payments", acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "Payments"
    DoCmd.DeleteObject acTable, "Payments"
    DoCmd.Close acForm, "Payments"
End Sub
    Rooms 
    Private Sub cmdExportRooms_Click()
    ExportXML acExportTable, "Rooms", "C:\CeilInn\Rooms.xml", _
              "Rooms.xsd", "Rooms.xsl", "Rooms", acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "Rooms"
    DoCmd.DeleteObject acTable, "Rooms"
    DoCmd.Close acForm, "Rooms"
End Sub
    RoomsStatus 
    Private Sub cmdExportRoomsStatus_Click()
    ExportXML acExportTable, _
              "RoomsStatus", _
              "C:\Ceil Inn\RoomsStatus.xml", _
              "RoomsStatus.xsd", _
              "RoomsStatus.xsl", _
              "RoomsStatus", _
              acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "RoomsStatus"
    DoCmd.DeleteObject acTable, "RoomsStatus"
    DoCmd.Close acForm, "RoomsStatus"
End Sub
    RoomsStatus 
    Private Sub cmdExportRoomsTypes_Click()
    ExportXML acExportTable, _
              "RoomsTypes", _
              "C:\Ceil Inn\RoomsTypes.xml", _
              "RoomsTypes.xsd", _
              "RoomsTypes.xsl", _
              "RoomsTypes", _
              acUTF8
        
    RecordSource = ""
    DoCmd.Close acTable, "RoomsTypes"
    DoCmd.DeleteObject acTable, "RoomsTypes"
    DoCmd.Close acForm, "RoomsTypes"
End Sub
  4. Close Microsoft Visual Basic and return to Microsoft Access
  5. Close a form
  6. When asked whether you want to save it, click Yes
  7. Close the other forms
  8. Open the Customers form
  9. Navigate to the record of First Name: Juliette and Last Name: Beckins
  10. Change the first name to July and and the last name to Bearking
  11. Click the Export Customers button
  12. Reopen the Customers form
  13. Click the Import Customers button
  14. Navigate to the record of First Name: July and Last Name: Bearking
  15. Close the Customers form
Previous Copyright © 2010-2022, FunctionX, Inc. Home