Create XML Web Reports From Access XP

Transforming XML data with XSL is a great way to deliver formatted content to the web.
Although creating XSL stylesheets can be daunting for the initiated, the new ExportXML
method in Access XP makes it easy to publish even the most complicated reports. The
following article will show how you can “push” or “pull” your Access reports to the Internet.

The new ExportXML method in Microsoft Access XP works great for generating XML files from
Access tables, but its greatest value may lie in its ability to generate formatted XML web
reports. For those who have not yet explored this brave new frontier, XSL is a transformation
language (similar in some ways to SQL) that can be applied to XML data files to produce a
formatted HTML output.

According to the help file, the syntax for generating a report as transformed XML from Access XP
takes this form:

    ExportXML( ObjectType, DataSource, DataTarget, _
               SchemaTarget, PresentationTarget, _
               ImageTarget, Encoding, OtherFlags )

For example, to create an HTM page to display an Access XP report named rptBookSales001, you
could use the following code. You need to specify that the ObjectType is a report and pass the
name of the report. At a minimum, you need to provide the name of an XML file to serve as the
DataTarget. If you opt to provide a file name for the PresentationTarget, then in addition to the
XML data file, an XSL stylesheet file will be generated as well. The OtherFlags argument allows
you to specify if the web page that performs the actual transformation should be htm or asp.

    Dim sRpt As String, sXML As String, sXSL As String

    sRpt = "rptBookSales001"
    sXML = "C:\InetPub\wwwRoot\Report\rptBookSales001.xml"
    sXSL = "C:\InetPub\wwwRoot\Report\rptBookSales001.xsl"
    iFrmt = 0	     ' Substitute 4 for ASP page output

    ExportXML acExportReport, sRpt, sXML, ,sXSL, , , iFrmt

Push or Pull?

The download associated with this article includes a form to simplify the above task. That form,
when imported into any Access XP database, serves as a web report generator. You simply specify
the output folder, the web site address, the output page format and then select a report from the drop
down list. Clicking the Export Report button pushes the report to the web site folder specified.
When successful, the label on the form becomes a hyperlink to the generated page so you can
immediately test the report. (See Listing 1)

This method works great for reports whose content remains relatively static. For example, a
daily sales report can be generated after hours for that day’s activity and placed on the web site.
I call this a push report because the application (or you, the application developer) decides when
the report is generated. The report remains completely static, simply waiting for someone to request
it for viewing.

Pull, on the other hand, could be described as “report on demand”. The content is not static, but rather
is the result of specific, user-provided criteria and/or is run against current, up-to-the-minute data.
In order to take advantage of the Access Application.ExportXML functionality in this scenario, an Access
object must be created to execute the ExportXML method. This can be done from within a compiled Visual Basic
component or from an ASP page using VB Script. Though each method has its merits, as well as drawbacks, for
the sake of this discussion, we’ll be demonstrating how to hook into the Access object model using VB Script
from an ASP page.

Generating User Requested XML Reports from ASP

The first part of the ASP code (See Listing 2) is standard HTML, which
provides text boxes for criteria collection. In this sample, the list of reports is hard-coded, but it
could easily be table driven or taken from the Access XP AllReports collection as was done in the form
example above.

The VB Script portion of the page is what really does the work. After verifying that the page has indeed
been reposted to itself, user-selected criteria are validated and saved into variables. File names are then
generated for the data target, presentation target and output files. Finally, we get to the automation code
for manipulating the Access XP application object, which requires the following process:

1. Execute Server.CreateObject to instantiate the Access.Application.10 object
2. Hook into the database with OpenCurrentDatabase(Path to MDB File)
3. Use a QueryDef object to set the SQL for the reports recordsource
4. Execute the ExportXML method to create the XML and XSL files
5. Clean up objects by closing them and setting them to nothing

In order for your ASP page to accomplish the above mentioned actions, it is necessary that the directory
security user account be set up with the proper privileges. The permissions to the keys located in
the \Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\Machinekeys directory need to
be changed to allow the Administrators group and System account to have full control. There is a detailed
description of this in KB Article Q246359. Alternatively, you could change the default System account for
the web server to one with sufficient permissions for all required folders and files. If security isn’t an
issue, that is the most expeditious solution.

The final bit of script redirects the browser to the newly created HTM (or ASP) page that actually
performs the XML/XSL transformation and displays the report. To rerun the report with different criteria,
you must use the back button of the browser.

It should be noted that this process could be streamlined to reuse a previously generated XSL file by simply
omitting the PresentationTarget argument. In fact, the download associated with this article includes an
ASP page with code that executes the ExportXML method against the query that serves as the recordsource of
the report. Then, the MSXML parser is used to insert a processing instruction into this XML file that tells
it which XSL file to use to transform the data into HTML. Although I didnt run any time trials, its reasonable
to conclude that its faster to create a single XML file from a query than to create both XML and XSL files
from a report.

Conclusion

I have to admit that although I was excited to hear that Access XP would include support for XML, I was at first
hard-pressed to find a real-life application for it. Once I began to create XML reports for web apps, it became
clear to me that Access XP could greatly simplify the process. Now, even if I use a different method to refresh
the XML files used in the transform, I will continue to use the ExportXML method to generate the complex XSL
files required to produce rich web reports.

This article originally appeared in the Smart Access Newsletter, Pinnacle Publishing, Inc. (c) All rights reserved.