Making Finding Aids for the WWW
..or Adventures in extracting HTML from Microsoft Access
4.1 Export to rtf method
As I mentioned, this is mainly included for historical purposes and I wouldn't recommend that you actually use it anymore (use the "module" method instead). However, I do remember with particular fondness the moment I realised I could get HTML out of Access (gee, I must have a sad life...). The year was 1994, and I was trying to get Bright Sparcs up and going. I had been exploring various possibilities without much success. As bizarre as it might seem (well at least it does to me), the first version of Access did not have the facility to output a report directly to a file - all you could do was print. So someone programmed a little add-on called "Output As" which gave this functionality. That was the first breakthrough. The next step was to create a report that inserted HTML tags. This wasn't too hard, although you had to be aware of special characters such as quotation marks. By exporting this report to a text file using "Output As" I could create a legal HTML file.This is pretty easy to do. Just open a new report and insert a text box. Now kill the annoying "label" (does anyone know how to insert a text box without a label?). All you have to do is insert an expression into the text box that combines HTML markup with your data. Here's an examples from the Directory of Archives in Australia:
="<TR><TD><H1>|[RepName]|</H1> <H2>|[Sub-name]|</H2></TD> <TD ALIGN=" & Chr$(34) & "CENTER" & Chr$(34) & " VALIGN=" & Chr$(34) & "MIDDLE" & Chr$(34) & ">" & IIf([Logo]<>"","<IMG SRC=|Chr$(34)|logos/ |[Logo]||Chr$(34)|") & "</TD></TR>"This expression results in the following HTML:
<TR><TD><H1>Australian Academy of Science</H1> <H2>Adolph Basser Library</H2></TD> <TD ALIGN="CENTER" VALIGN="MIDDLE"> <IMG SRC="logos/2.gif"</TD></TR>And this is how the entry looks (click on screen capture to view real entry):
You'll notice that you have to use Chr$(34) to insert double quotes in the expression. Also, that the expression is a combination of data fields (in square brackets) and text strings (in quotes) joined by ampersands. There are a few alternative syntaxes for these expressions. Unfortunately there is no real error checking, Access won't let you leave a text box where there's a syntax problem, but it won't tell you what the problem is! So it can take a while to find out what you're doing wrong.
There were other problems as well. First of all the text output was very "dirty" - lots of unwanted blank space, carriage returns etc. Also, there were problems with the "Can shrink" and "Can grow" properties of text fields in reports. Exporting to RTF instead of text helped a bit, but the results were still pretty dirty. Also, I wanted to create a separate file for each of Bright Sparcs's 2,500+ entries - exporting each of these separately would have been very slow. So the system I settled on involved exporting the Bright Sparcs entries all in one go to a large RTF file, then opening this RTF file in Word and running a series of macros to clean up the RTF, and split the large file into lots of little files, saving each as a plain text (HTML) file with a unique file name. This is basically the process that is still used by ASAP to generate Bright Sparcs and the Directory of Archives in Australia.
This method enable us to create some important resources, but it still has its problems. First of all, it is SLOOOOOOW. To create a new version of Bright Sparcs takes several hours. Secondly, Access has an extremely nasty habit of truncating data in reports for no apparent reason. I have tried all sorts of combinations of text box size, report size, font size, object property settings etc etc, all to no avail. You think you've fixed everything up and then Access suddenly decides to truncate some totally different field. After much tearing of hair and gnashing of teeth I have decided that there are two possible causes of this behaviour:
- it's the effect of cosmic rays
- it's the work of the devil
2. Why use databases?
3. Exporting your files to databases
4. Producing HTML from databases
4.1 Export to rtf method
4.2 The module method
4.2.1 Contents page
4.2.2 Item listings
4.2.3 The results
Some Word macros
you may find useful:
Created by Tim Sherratt (Tim.Sherratt@asap.unimelb.edu.au)
Last modified: 16 March 1998