Making Finding Aids for the WWW

..or Adventures in extracting HTML from Microsoft Access

4.2.1 Contents page

First you need to tell the module what database to use, if it's the current database, the code is just:

	Set dbs = CurrentDb
(dbs should have been previously declared as a variable of type "Database").

Now we want to get the data from the series table so we can create the "Contents" page. The easiest way to create an SQL query is to just create a normal query in Access and then switch to SQL view. You can then just copy the SQL statement and paste it into your module.

Create SQL statement

	SQLSeries = "SELECT * FROM Series ORDER BY [series number]"
(which just means get all the fields from the table called "Series" and order the records by "series number")

Use this SQL statement to create a recordset

	Set rstSeries = dbs.OpenRecordset(SQLSeries)
(the SQL string is used to open a recordset within this database. "rstSeries" should have been previously declared as a variable of type "Recordset")

Now we can start defining some variables (which I'll assume have been previously declared).

	headOpen = "<HTML><HEAD>"
	headTitle = "<TITLE>Contents of this \
		finding aid</TITLE>"
	headClose = "</HEAD>"
(These variables contain the HTML code for the HEAD section of a WWW document.)

	bodyOpen = "<BODY>"
	docTitle = "<H1>Contents of this \
		finding aid</H1>"
	bodyClose = "</BODY></HTML>"
(Code to open and close the body section and create a heading for the page)

Time to open a file and start writing some HTML to it

	fileName = "c:/finding_aid/contents.htm"
	fileNumber = FreeFile
	Open fileName For Output As #fileNumber
(The file name can obviously be whatever you want, provide the full path to where you want it written. The rest of the code just assigns this file name to a number which is referred to by the Print function.)

	Print #fileNumber, headOpen
	Print #fileNumber, headTitle
	Print #fileNumber, headClose
	Print #fileNumber, bodyOpen
	Print #fileNumber, docTitle
(This simply just writes the variables to the named file. Each variable is written to a new line.)

Now we want to list each series number and title, with links to individual pages for each series. To do this we need to loop through the rstSeries recordset:

	rstSeries.MoveFirst
	Do Until rstSeries.EOF
		seriesLink = "<A HREF=" & Chr$(34) \
			& "series_" &
		rstSeries![series number] & ".htm" & \
			Chr$(34) & ">"
		seriesList = rstSeries![series number] & \
			" - " & seriesLink & \
			rstSeries![series title] & \
			"</A><P>"
		Print #fileNumber, seriesList
		rstSeries.MoveNext
	Loop
(This just steps through each record in the recordset, pulling out the necessary data and embedding it in HTML. The "seriesLink" variable creates the hyperlink that will link the series entry to a file containing the item listing (which we'll create below) - note that the file name for the item listing is created by combining "series_" + the series number + ".htm". The "seriesList" variable creates the entry as it will appear on the WWW page, using "seriesLink" to create a hyperlink on the series title.)

Just have to finish things off:

	Print #fileNumber, bodyClose
	Close #fileNumber
All done! Wasn't that easy?

Next Section - 4.2.2 Item listings

1. Introduction
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

View code:

Created by Tim Sherratt (Tim.Sherratt@asap.unimelb.edu.au)
Last modified: 16 March 1998