How to Output MS Access Data to HTML

One Page for Each Record with File Name from Database Field

I recently found myself needing to create a website from an inventory list which was in an Access database. We needed a separate web page for each item/record, and because we would be linking to the pages from others we'd create in a similar way, we needed the files named based on a field in the database.

I spent a lot of time looking online for an answer, but eventually had to (mostly) develop my own solution. There is some time involved in setting this up, but once you have your template files, updating or adding to your site is quick and easy. I'm sure that a programmer could automate this process even more with Visual Basic, but that's not my area of expertise. This method requires no pragramming ability, though you do need some experience with HTML and MS Word.

Overview

  1. Preparation
  2. Create main merge document from HTML template file
  3. Merge data and save pages as separate files
  4. Rename files with field from database

1. Preparation

  • Create your HTML template using whatever method you prefer. At each point where you want information from your database, type the name of the field in quotes - like "PartNum". For the images, insert a sample image so you have a visual representation of the design you're creating.

  • Set Autoformat options to make sure that Word won't mangle your HTML code. Open Word and click Format --> Autoformat then click on Options. Select the 'Auto Format as you Type' tab. Make sure you clear the boxes indicated - for quotes, symbol characters, & hyperlinks.

  • Set View options so mergefield codes will be shaded. Click Tools --> Options and select the View tab. Make sure "Field Codes" is selected.

2. Create main merge document from HTML template

  • Copy your HTML source code and paste it into a blank Word document, then close the file, saving it as a Word Document.

  • Open your database. If you're using a query, make sure you're exporting all the fields you'll need; Access has an option in preferences to output all fields for queries, but I don't recommend it - it slows things down.

  • Open your query or table, then click Tools --> Office Tools --> Merge with MS-Word. Then select "Use an existing Word document." Navigate to your main merge document and click OK. MS Word will open, then Access should return to the foreground. If Access doesn't regain focus, exit and re-open your query, then send the data again.

  • In your Word document, replace the "PartNum" placeholders by selecting one (including the quotes) and using the Insert Merge Field button to select the appropriate field from the drop-down list.

  • Before you complete the merge, select all or part of the first line or paragraph of your document. Click Format -> Paragraph and change Outline level from Body text to Level 1. This is required by the macro.

For some tips and ideas on using Word codes to perform more complex tasks, see Tips & Troubleshooting for Outputting Access to HTML.

3. Merge data and save pages as separate files

When you merge the data with your document, Word puts everything in one file. We'll use a macro to save the output from each record as a separate file. Create the macro by clicking Tools -> Macro ->Macros and, in the dialog that opens, click Create. The Microsoft Visual Basic editor will open. Copy the macro and paste it into the editor, then save and close the Visual Basic editor.

Merge the data with your HTML file, selecting Merge to new document. When it has finished, run the macro you just created by selecting Tools -> Macro -> Macros. Make sure that SaveRecsAsFiles is selected, and click Run. How long this will take depends on the number of records and on your computer. If you have a lot of records and/or a slow computer, you may want to go get some coffee.

When the macro is complete, close the original results document (no need to save it.)

4. Rename files with fields from database

  • Find the individual files which the macro just saved to your hard drive. They may be in your Documents folder, or in the same folder as the original Word main merge document. Look for MergeResult1.txt, MergeResult2.txt etc.

  • To create the DOS batch file, go back to your query and choose Tools --> Office Tools --> Merge with MS-Word. Select 'Create a new document and merge my data with it.' Paste the following text into the new document:

Ren MergeResult[MergeSequence#].txt [MergeFieldWebPage].htm

  • Replace the text [MergeSequence#] with the actual command from Word's Insert Word Field menu, and replace [MergeFieldWebPage] with the appropriate mergefield(s). If the field you're using to name the pages already contains the .htm then you don't need to include it here.

  • Save the new document in the same folder as your other main merge document.

  • Complete the merge. Save the resulting file in the folder with all the MergeResultx.txt files. Change the Save As type to MS-DOS text with a name like "rename.bat" making sure to type the quotation marks into the Save As dialog box; otherwise Word will add .txt to the end.

  • Open Windows Explorer and double-click rename.bat to run it. All of the web pages should now have names corresponding to a field in your database. Open one or two of the pages to confirm, then upload them to your server.

When the data changes and it's time to do it again, it will be much easier. You'll already have your HTML template file (with all the merge codes), your macro, and your batch file, so it's just a matter of sending the data to Word, clicking merge, running the macro, and then merging and running the batch file.