|< Day Day Up >|
You can export Access data to an XML file by executing VBA's ExportXML method, which takes the form
application.ExportXML objecttype, datasource[, datatarget] _ [, schematarget],[presentationtarget] _ [, imagetarget][, encoding][, otherflags]_ [, filtercriteria][, additionaldata]
where application represents the Access model. Table 22.1 lists this function's many arguments. Although datatarget, schematarget, and presentationtarget are optional, you must specify at least one. Tables 22.2 and 22.3 further define the constants that you can use with some of the ExportXML arguments.
VBA's ExportXML function writes over existing files. If you don't want to overwrite existing files, you must check for the file before beginning the export process and then rename the existing file, specify a new name for the file you're about to create, or cancel the task.
An Export Example
The simplest XML export creates a single XML file. For instance, the following procedure exports the client data to an XML file:
Sub ExportTable(source As String, path As String, _ target As String) 'Export source to XML file Access.Application.ExportXML acExportTable, source, path & target End Sub
Enter the procedure in a standard module or use Chapter 22's sample module. When executing the procedure, pass the name of the table that contains the data you're exporting and the complete path and filename to which you want the data exported. For instance, in the Immediate window, run the following statement to export client data:
ExportTable "Clients", path, "Clients.xml"
Be sure to update path to accommodate your own system's directory structure, and include the .xml extension in the target argument (otherwise, the exported file won't be recognized as XML by editors and other utilities). (Remember, path is a string, so be sure to enclose it in double quotation marks.) Figure 22.1 shows the exported XML file in Word 2003, which can display XML files.
Figure 22.1. The XML file contains tags and actual data, but isn't particularly viewable in any meaningful way.
The XML format, as is, doesn't produce a particularly readable file. You'll probably never view files in this format unless you're editing the content.
This procedure handles only tables. You can add an argument and enable the users to pass the object type.
Exporting a Web-Ready File
You can quickly create a Web-ready document by simply changing the extension in the target argument from .xml (or whatever standard you're using) to .html. The results aren't perfect, but your browser will display just the data instead of the tags and data.
To illustrate, run the previous statement in the Immediate window, but change the .xml extension to .html as follows:
ExportTable "Clients", path, "Clients.html"
Next, view clients.html in your browser. (Use Windows Explorer to locate the file in the current directory and double-click it to open it in your system's default browser.) Figure 22.2 shows this file in Internet Explorer. It isn't perfect, but the quick result is impressive.
Figure 22.2. Quickly produce Web-quality results by simply changing the extension.
Exporting Related Data
Exporting related data via the user interface is fairly simple. Automating the process presents a bit of a challenge. The ExportXML method's additionaldata argument enables you to specify additional tables to export, but you can't just add the table's name. Instead, you append the table using the AdditionalData object.
To export related data, you must identify the related table as an AdditionalData object and then use that reference as the additionaldata setting. The following procedure creates an AdditionalData object and then uses that object's Add method to append addtable to the collection:
Sub ExportMultipleTables(source As String, path As String, _ target As String, addtable As String) 'export related tables to XML file Dim objAddTable As AdditionalData Set objAddTable = Application.CreateAdditionalData objAddTable.Add (addtable) Access.Application.ExportXML acExportTable, source, path & target, _ , , , , , , objAddTable End Sub
To illustrate this procedure, run the following statement in the Immediate window (after updating path):
ExportMultipleTables "Clients", path, "ClientsAndProjects.html", "Projects"
Figure 22.3 shows each client with its current projects in a browser. Although the format is lacking a bit in structure, you can see that the data's all there.
Figure 22.3. Export related data programmatically to an XML file.
|< Day Day Up >|