Microsoft Access: Applications and Utilities

provided by Allen Browne. Created January 2010.  Last updated: April 2010.


Export data to web pages

This article demonstrates how to output web pages that display the data from an Access table or query.

The approach outlined here has several advantages over the approaches built into Access (such as TransferText.) You can:

The interface and a sample output look like this:

Interface screenshot Output screenshot

The utility comes in two versions: for Access 2007 and later (55kb zipped) and Access 2000/2002/2003 (35kb zipped.) Both versions handle hyperlink, text, memo, and number fields, and custom formatting for dates, currency, and yes/no fields. The code in both versions is identical, but the Access newer version also handles:

Contents

The utility contains two files:

The objects in the database are:

Tables MyTable Sample products to export. (Additional fields to demonstrate features.)
tblExportSource Stores how you want each web page set up.
Queries
qryTableQuery List of the tables and queries (Row Source for the combo on the form.)
Form frmExport Interface for the export (screenshot above.)
Module ajbExportHTML The code that performs the export. Call OutputHTML() from your own interface if you prefer.

To use this in your database, import the blue objects.

Configuration

To configure the utility, open the module in design view, and set these public constants:

To configure how the output looks on the web page, edit AccessOutput.css (e.g. in Notepad.) Alter the TH tag to change how the header row of the data table is displayed, and the TD row to change the properties of the data in the table cells.

How it works

You can use the utility without understanding it, so this explanation is for programmers. The code is identical for both versions.

OutputHTML() is the main function. Other than the name of the table or query to export, all arguments are optional. The three test functions illustrate calling it with different levels of detail.

The function opens a recordset based on the input table or query, and calls FixupFilename() to prepare the output file name. If no file name is supplied, we use the table/query name in the current directory. If the file name is a folder, we use the table/query name in that folder. If the file name lacks an extension, ".htm" is added.

Now the file is open, we use Print # to print the lines for the HTML header section, slotting in the metadata from the constants and the values passed in.

Then we start the body, printing the heading (if there is one) and the head paragraph. If these values begin with a left-angle bracket, we assume they have the HTML tag built in; otherwise we add H1 for the heading, and P for the paragraphs.

Next we begin a table to show the data, and print the field names in the header row. There are certain fields we cannot display (binary, or OLE Objects), so we call IgnoreField() to weed them out by examining the Type of the Field. If the field has a Caption property, we use that as its name. Otherwise we use the field name, calling ConvertMixedCase() to insert spaces. For example, if your field name is LastName or Last_Name, it will display as Last Name.

Now we are ready to loop through the records, printing a row for each one. We call the same IgnoreField() function we used when setting up the columns, to skip OLE Objects, etc. Each field becomes a cell of the HTML table, so we call FormatCell() to format the contents of the field. This function must handle different field types differently, so it performs a series of steps:

Back to the main procedure again, the loop does this for each field in the record, and for all records in the source table/query. Once that is complete, we close the HTML table, add the footer paragraph (if supplied), and close out the HTML page.

The file we have been writing must be closed in the error recovery section so it not left open after an error. Then close the recordset, and set objects to Nothing, before displaying the new file (if the user asked for that.)

Here again are the links to view the code, or download the utility for Access 2007 and later or the Access 2000 - 2003.


Home Index of tips Top