Create XML Web Reports From Access XP

July 10, 2002

download code for this article

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)

Access XP form for generating XML reports

Access XP form for generating XML reports

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.

ASP web page for generating XML reports

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:

Execute Server.CreateObject to instantiate the Access.Application.10 object
Hook into the database with OpenCurrentDatabase(Path to MDB File)
Use a QueryDef object to set the SQL for the reports recordsource
Execute the ExportXML method to create the XML and XSL files
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.

Set directory security for IIS default web user account

Set directory security for IIS default web user account

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.

See All Articles by Columnist Danny Lesandrini

Create XML Web Reports From Access XP

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos