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:

• Specify a Cascading Style Sheet to format the data on the page.
• Specify a browser title, and heading text to appear at the top of the page.
• Specify content (paragraphs) to appear above and below the data from your table/query. (These can be rich text in Access 2007 and later.)
• Specify metadata (author, copyright, description, and keywords) for the HTML header.
• Store all these details in the utility, and reuse them to export the data again later.

The interface and a sample output look like this:

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:

• Rich Text fields (such as Product Note above)
• Attachment fields (listing the file names)
• Multi-Valued fields (such as Applies To above)

Contents

The utility contains two files:

• the database file: AccessOutput.accdb (Access 2007 and later) or AccessOutput.mdb (older versions.)
• the sample Cascading Style Sheet (AccessOutput.css), illustrating how to format your output.

The objects in the database are:

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:

• mstrcAuthor:  Place your name inside the quotation marks. (Used in the HTML header metadata.)
• mstrcCopyright:  Place your copyright notice inside the quotes. (Used in the HTML header metadata.)
• mstrcCSS:  Place the name of your Cascading Style Sheet in quotes, optionally with a path, e.g. "/css/default.css"
• mstrcDateFormat:  The format to use when exporting date/time fields. Default is "General Date".
• mstrcCurrencyFormat:  The format to use when exporting Currency fields. Default is "Currency".
• mstrcYesNoFormat:  The format to use when exporting Yes/No fields. Default is "Yes/No".

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:

• It tests for multi-valued fields, identified as objects, and assigns them to a Recordset so we can return a concatenated string. For attachments, we concatenate the file names; for multi-valued fields, the values. (Note that we test for the literal 101 rather than the constant dbAttachment, because this code must run in Access 2000 as well.)
• If it is not Null, and not a multi-valued field, we examine the field's Type further. Text fields are returned as they are.
• For Memo fields, we examine the Attributes to see if it is a hyperlink, and if so parse the address and display value and set up the HTML Anchor tag. If the memo is not a hyperlink, we call IsRichText() to identify whether the field has its TextFormat property set to 1. (We do this in a separate procedure to recover from the error when the property does not exist, including in Access 2000.)
• For numeric fields, we return the value and set a flag for right-alignment.
• For Currency, Date, and Yes/No fields, we format using the constants you set above, and flag right-alignment for currency and date.
• For any other field types (including those in future versions that don't exist yet), we just return the value of the field.
• If nothing has come out (e.g. if the field was Null), we add a non-breaking space. (This avoids the problems TransferText has with cells that do not display correctly.)
• If we did not flag the field as already formatted, we must now substitute the special characters that cause problems in HTML: ampersand, quote, left- and right- angle brackets (used as tag delimiters), embedded carriage returns, and multiple spaces.
• Finally, we add the TD tag to the cell, setting right-alignment for those fields that should have it.

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.