Developer

Use XML in Access 2002 to generate Web-enabled reports

Access 2002's ability to export data in XML format can come in handy when you have to create Web-ready files for viewing outside your client's office. Here's how this time- and sanity-saving tool works.


Recently, a colleague complained that one of her clients needed for her to prepare a lengthy report to distribute to personnel outside the home office. The routine was laborious at best, and she was desperate for an easier solution. I mentioned that extensible markup language (XML) might be a good alternative to her work-intensive method. Within just a few days, she produced an easy-to-implement solution and was busy reviewing other problems for possible XML-based remedies. The easy export process was her solution for getting the report out of the office.

In Access 2002, you can export Access data in XML format or import XML data into your Access database using the Access interface or Visual Basic for Applications (VBA). When you export data, Access produces several files, and you’ll need to understand each one’s purpose so that you can take an Access report to the Web.

The upshot of this solution is that you can share an XML file with almost any application running on any operating system. Realistically, it can be difficult to land just the right combination of XML options. Fortunately, once you do hit it right, it works great.

In this article, I’ll show you how to quickly create Web-ready files from Access reports using XML files that pass along the report’s data, structure, and formatting.

XML and Access
You can import XML files into Access or you can export Access objects as XML files. In this article, I’ll export Access reports to Web-ready HTM files using the XML export feature. In doing so, I’ll export the following file types:
  • XML: The actual data presented in what are known as element blocks that reference the original table fields
  • XSL: A special stylesheet that is applied to the XML file in order to format the data
  • XSD: Contains data type and constraint information
  • HTM: A Web-ready version of your Access object

You may end up using the actual XML files, but in this article, I’ll just view the resulting HTM file.

An interface example
Now, let’s use the Access interface to quickly create a Web-enabled report by exporting a report as XML. For this example, I’ll export the Catalogs report in the sample database, Northwind, that comes with Access:
  1. Open the Northwind Sample Database, and select the Catalogs report in the Database window.
  2. Choose Export from the File menu.
  3. In the Export dialog box, specify the appropriate folder (I’ll just work with the default), name the file “Catalog,” select XML Documents (*.XML) from the Save As Type control, and then click Export.
  4. In the next dialog box, specify both the Data (XML) and Schema Of The Data options, as shown in Figure A, and then click OK to generate the following files: Catalog.xml, Catalog.xsl, Catalog.xsd, and Catalog.htm.

Figure A
Select both enabled export options.


Using Windows Explorer, locate Catalog.xml and then open it in your browser. (You may have to use the Open With option if you have a Web-authoring tool that usurps the XML file association.) Figure B shows Catalog.xml in Internet Explorer. (Netscape doesn’t support XML.)

It isn’t exactly Web ready, is it? As I mentioned earlier, it’s just a bunch of tags and data. However, the export also produced Catalog.htm, which is a Web-ready file. Open it now and take a peek if you like. It should look very similar to the original Access report, as displayed in Access.

Figure B
The XML file isn’t exactly Web ready.


Automating the process with ExportXML
So far, we’ve used the Access interface to manually export objects as XML. As a developer, you’ll probably want to automate this process as much as possible by using the ExportXML VBA function. This function takes the form of:
expression.ExportXML(objecttype, datasource, [datatarget,] [schematarget,] [presentationtarget,] [imagetarget,] [encoding,] [otherflags])

where expression is an Application object. The first two arguments are the only two required, but you’ll want to also include at least one of the first three target arguments.

Table A lists the constants you can use for objecttype, which identifies the type of Access object you’re exporting. The datasource argument identifies the actual object. Our technique calls for the optional arguments: datatarget and presentationtarget, which identify the target path and filename for the exported files.

Table A: Object type constants
Object
MDB/ADP
Constant
Data Access Page Both acExportDataAccessPage
Form Both acExportForm
Function Both acExportFunction
Query MDB acExportQuery
Report Both acExportReport
View ADP acExportServerView
Stored Procedure ADP acExportStoredProcedure
Table Both acExportTable

An example
The following example illustrates just how easy it can be to view your Access data on the Web. I’m still working with Northwind in the example, but you can use any database you like, as long as it contains reports. The first step is to create the Access form shown in Figure C.

Figure C
I’ll use this form to choose a report and then export the XML files.


To do so, add a list box and a command button to a blank Access form, naming them lstReports and cmdExport, respectively. Then, set the following form properties accordingly:
  • Scroll Bars: Neither
  • Record Selectors: No
  • Navigation Buttons: No
  • Dividing Lines: No

To set these properties, choose Design View from the View button on the Database toolbar. Double-click the report selector, the little gray square at the intersection of the horizontal and vertical rules, to display the properties sheet. Then, locate the properties in the list and change accordingly.

Next, add the following code in Listing A to the form’s module, and then save the form as frmReports:

Open the form in Form view and the form’s Load event will display all the reports in Northwind (see Figure C). For this example, select Catalog and click the Export Report button. Doing so will export several files to the C:\Inetpub\wwwroot\XMLExamples folder, including:
  • Catalog.htm: An HTM file you can open in your browser
  • Catalog.xml: The XML file that contains the actual data you’ll view in your browser
  • Catalog.xsl: The stylesheet that will format the data properly
  • LargeLogo.bmp, NameLogo.bmp, and SmallPictureLogo.bmp: Graphic files in the report

First, the Click event procedure declares a few String variables. If you fail to select a report before clicking the Export Report button, an If statement displays a message asking you to do so. The next three statements:
strDataSource = Me!lstReports
strDataTarget = cPATH & strDataSource & ".XML"
strPresentationTarget = cPATH & strDataSource & ".XSL"


identify the object to export and where to export the files. You must add the XML extension to the datatarget as shown. Failing to do so won’t return an error, but the function won’t produce the appropriate files and you won’t be able to view the HTM file.

In contrast, it isn’t necessary to include the XSL extension to the presentationtarget argument, but I’ve done so because it’s self-documenting. Also, I included the full path as a constant named cPATH, which I defined as C:\inetpub\wwwroot\XMLExamples\. You’ll want to update this to match your system. It isn’t necessary to export these files to a Web folder (C:\inetpub\wwwroot) in order for the example in this article to work, but if you want to actually access these files from a working Web site, that’s where these files need to go.

You may have noticed that I specified only two target arguments, but I ended up with three files. That’s because the presentationtarget generates both an XSL and an HTM file.

Locate the newly exported files using Windows Explorer, and double-click Catalog.htm (don’t forget about the Open With command if you have a conflicting file association). Internet Explorer has no problem displaying the report’s text and graphics using the same formatting scheme Access employs. Keep in mind that the data is static. That means you won’t be able to actually interact with the underlying data to modify it or to further limit what you see.

ExportXML will write over existing files in the target folder when executed from any Access object other than a data access page. You may want to check first and warn the user that files exist just to make sure that’s really what the user wants. On the other hand, when ExportXML is executed from a database access page, it prompts you before writing over existing files. (My example is intentionally simple and doesn’t include error handling because those solutions will be unique to each user.)

About the Generated Files
You’re probably wondering why Access generates so many files. In this next section, I’ll review a snippet of each file’s contents and see how they all fit together:
  • Catalogs.xml: Access copies the data from your data source (in this case that’s the Catalogs report) to an XML file by converting the table and field names to XML elements that include the data value for each field. For instance, this line of code�� <CategoryID>1</CategoryID>�contains the data value of 1 for the CategoryID field.
  • Catalogs.xsd: This is the XML schema document and it contains descriptions of the fields, including their data types and any restrictions on the data element. I didn’t create an XSD file for Catalogs, but if I had, it would contain this definition for the CategoryID field.
  • Catalogs.xsl: This file is the stylesheet that does all the formatting. It contains a combination of XML/XSL and HTML code that controls how the displaying application handles the data in the XML file. Listing B shows the code that displays and formats the CategoryName field.
  • Catalogs.htm: This file will display our formatted XML in a browser.

Conclusion
Access 2002’s XML capabilities greatly simplify the process of creating Web-enabled reports. There’s a lot more to XML files in Access than what I was able to present in this one article. Fortunately, where Access 2002 is concerned, you should be able to adjust the concepts learned in this article to accommodate most situations. Once you have a Web-ready file (the HTM file), it’s a simple matter of retrieving it from your Web site.


Editor's Picks