Access Cookbook - Ken Getz [335]
Discussion
A number of enhancements were added to the Access object model to facilitate importing and exporting XML data programmatically. The full syntax and all of the optional arguments for the ExportXML method are shown here:
ExportXML (ObjectType As AcExportXMLObjectType, Datasource As String,
[DataTarget As String], [SchemaTarget As String], [PresentationTarget as String],
[ImageTarget As String], [Encoding As AcExportXMLEncoding], [OtherFlags As Long],
[WhereCondition As String], [AdditionalData as AdditionalData])
The OtherFlags optional argument, which was not used in the example, allows you to specify the following self-descriptive options, which are exposed as AcExportXMLOtherFlags enumerations:
acEmbedSchema
acExcludePrimaryKeyAndIndexes
acRunFromServer
acLiveReportSource
acPersistReportML
TIP
When you need to apply a transform programmatically for either importing or exporting XML, use the TransformXML method. The ImportXML and ExportXML methods do not have DataTransform parameters.
18.7. Export Using a Where Clause
Problem
You want to export a subset of rows in a table that match cartain search criteria instead of exporting the entire table.
Solution
There are two different approaches you can take, depending on how you want the output to look. The first approach is to design a query, and export the query to XML, as shown in the Solution in Recipe 18.5. The second is to use the ExportXML method. (You also could use an XSLT transform, but that would be inefficient unless you also need to format the data.)
The 18-07.MDB sample application has a saved query named qryCarsLessThan40. The SQL Select statement looks like this:
SELECT Car.CarID, Car.Make, Car.Model, Car.Price
FROM Car
WHERE (((Car.Price)<40000));
When you export the query to an XML file by following the steps in the Solution in Recipe 18.5, the XML generated looks like that shown in Figure 18-18.
Figure 18-18. XML generated by a query with a WHERE clause
The 18-07.MDB sample application also has a function named ExportWhere located in basExportXML. Instead of using a query, this code exports the Car table and programmatically applies a WhereCondition of "Price < 40000":
Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="Car", _
DataTarget:="c:\test\Where.xml", _
WhereCondition:="Price < 40000"
Figure 18-19 shows the output that is generated.
Figure 18-19. XML generated using ExportXML with a WhereCondition
Discussion
When you create a saved query with a Where clause and export it, each element is named with the query name, qryCarsLessThan40, as shown in Figure 18-18. When you use the ExportXML method and supply the optional WhereCondition argument, then the name of the table, Car, is used. Although you could rename the query to something less cumbersome than qryCarsLessThan40, you cannot name it Car since there already is a table by that name in the database.
Using the WhereCondition parameter rather than relying on a query also provides extra flexibility. You can use code to construct whatever criteria are needed for the WhereCondition at runtime, rather than having to hard-code the criteria into a query.
18.8. Export a Report
Problem
You want to export a report that can be displayed on your web site. You'd like to preserve the formatting of the original report.
Solution
One of the new features in Access 2003 is the ability to export reports to XML, preserving formatting and displaying aggregates (totals, counts, averages, and so on). You can export formatted reports to ASP or to HTML.
The 18-8.MDB sample database contains a report named rptCustomer that displays customer preferences, as shown in Figure 18-20. The report has an aggregate function that counts the number of cars ranked, and displays the make and model for each. The following sections show you how you can export the report to HTML and to ASP.
Figure 18-20. The rptCustomer report displaying customer preferences and rankings