Using ImportXML

Using ImportXML

Data isn't always conveniently stored in the current database. Occasionally, you have to import foreign data. Fortunately, the Import Wizard does a good job of handling a number of formats. However, you won't use this wizard to import XML data. Use the ImportXML method to import XML data and schema information using the form

 Application.ImportXML datasource[, importoptions] 

where datasource is a required String value that identifies the complete path and filename to the XML file you're importing and importoptions is one of the optional intrinsic constants listed in Table 22.4.

Table 22.4. ImportXML Constants

Constant	Integer Value	Explanation
acStructureOnly	0	Import only the table's structure.
acStructureAndData	1	The default option, which imports both the file's structure and data.
acAppendData	2	Append the data into an existing table.

Importing XML data isn't quite as complicated as exporting. For the most part, you just identify the XML file that by default, Access imports into its own new Access table. Occasionally, you might need to import just the file's structure or append the data to an existing table, and then specify one of the constants listed in Table 22.4.

An Import Example

Let's review how the three constants change the result of importing the same file by importing the client data exported in the previous section. First, enter the following procedure in a standard module or use Chapter 22's sample module:

 Sub ImportXMLData(source As String, Optional impopt As Variant)   'import XML file   'include entire path if necessary   If IsMissing(impopt) = True Then     impopt = acStructureAndData   End If   Access.Application.ImportXML source, impopt End Sub  

This procedure enables you to pass the XML file's name and the constant. You can pass the actual text constant or the Integer value. The second argument is optional, but if you omit it, the procedure assumes you want to use the default, acStructureAndData.

Before you import anything, be sure to close all your browser and Word windows that contain the .xml and .html files from previous examples. An open .xml file can return an error in some cases.

In the Immediate window, run the following statement:

 ImportXMLData "path\clients.xml" 

(If you didn't save clients.xml to the current directory, you might need to include path.) After running the statement, check the Database window for the new table, Clients1. Figure 22.4 shows the new table in Datasheet view it is identical to Clients. The only difference is that the imported table doesn't contain a primary key or any indexes. During the import process, Access concatenates the 1 because the database already contains a table named Clients.

Figure 22.4. The ImportXML method easily imports XML data into a new table.

As is, you can't import clients.xml using the acAppendData option because doing so would create duplicate values and violate the existing constraints (indexes), as shown in Figure 22.5. Access imports records that don't trigger a key violation. If a record creates a problem and returns an error, Access logs error information in a table named ImportErrors.

Figure 22.5. If importing the data violates key constraints, Access returns an error and doesn't import the offending records.

The problem in this case is the ClientID (primary key) values; there's already a ClientID 1 in the Clients table. If you open clients.xml (using Word or some other XML editor) and change the ClientID value of 1 to 100, Access imports the record for Bill's Auto Glass even though the record is actually a duplicate.

To import just the table's structure, run the following statement:

 ImportXMLData pathtoclients.xml acStructureOnly 

Be sure to update pathtoclients.xml so that it includes the entire pathname and the .xml filename. Figure 22.6 shows the results a new table with no data. Notice that the new table's name is Clients2. As before, Access concatenates the next consecutive value to the table's name because Clients and Clients1 already exist.

Figure 22.6. You can import just the table's structure.

CAUTION

Importing the XML file's structure creates a new table with the same fields and properties. However, the process does not copy the original file's primary key or indexes.

In the previous section on exporting, you learned how to export related tables to an XML file using the AdditionalData object. Importing related tables is much easier because Access handles the entire process internally. The XML file knows the data is related and it is imported as such, without any additional information from you. To illustrate the ease with which Access handles related data, run the following statement in the Immediate window:

 ImportXMLData "path\clientsandprojects.html" 

(Include path if the file isn't in the current directory.) The results are shown in Figure 22.7 Clients3 and Projects1. Notice that the example imports the previously exported .html file and not an actual .xml file. The ImportXML method is flexible enough to handle .html data, although most likely you'll use the method to import .xml data.

Figure 22.7. The ImportXML method easily handles related data.

CAUTION

Access can import related data from an XML file and can even separate the data into tables, accordingly. However, Access can't create a permanent relationship between the new tables. You must create the relationship yourself.

CASE STUDY: Exporting Up-to-Date Project Information

Let's suppose your developers often spend days at a time on site at a client's office. During that time, the developer might want to access the most current project data. To do so, you can export the appropriate client records to an XML or HTML file and then email that file to the developer in the field. If your setup is sophisticated enough, you can automate the email process or even allow the developer Internet access to the database (although this example doesn't do either).

First, create a new blank form with a list box and two command buttons. Refer to Table 22.5 for the appropriate form and control properties.

Table 22.5. Form and Control Properties

Object	Property	Setting
form	Name	Export
	Caption	Export Client Projects
	Scroll Bars	Neither
	Record Selectors	No
	Navigation Buttons	No
list box	Name	lstClients
	Row Source	SELECT Clients.ClientID, Clients.Client FROM
	ClientsColumn Count	2
	Column Widths	0''
command button	Name	cmdCancel
	Caption	Cancel
command button	Name	cmdExport
	Caption	Export

Enter the following code in the form's module:

 Private Sub cmdCancel_Click()   'Set selection to nothing   lstClients.Value = "" End Sub Private Sub cmdExport_Click()   'Export projects for selected client to HTML file   'to current directory   Dim objProjects As AdditionalData   On Error GoTo HandleErr   Set objProjects = Application.CreateAdditionalData   objProjects.Add ("Projects")   Application.ExportXML acExportTable, _    "Clients", "ClientsAndProjects.HTML", _    , , , , , "ClientID = " & lstClients, objProjects   Exit Sub   ExitHere:   Set objProjects = Nothing   Exit Sub HandleErr:   MsgBox "Error " & Err.Number & ": " & _    Err.Description   Resume ExitHere End Sub 

Save the form and then open it in Form view, as shown in Figure 22.8.

Figure 22.8. Use this simple form to select a client.

Select Bill's Auto Glass in the list box. Right now, the technique handles just one client. We used a list box so you can easily adapt the technique to handle multiple clients.

After selecting a client, click the Export button. Nothing seems to happen, but the Export button's Click event just exported (to the current directory) the client and related project records for Bill's Auto Glass (or the client you selected) to an HTML file. Check the current folder for a file named ClientsAndProjects.html.

At this point, you'd email the resulting HTML file to the developer in the field. However, because you really can't do that, go ahead and launch the file in your default browser as shown in Figure 22.9. The file contains project data for only the client selected in the Export form.

Figure 22.9. Launch the HTML file in your browser to quickly view the client's projects.

CAUTION

The export process writes over the existing file of the same name that you created earlier. If you want more control, you need to add code that checks the directory for an existing file of the same name before executing the export. You can cancel the export or change the name of either file.