LIS 558 - Thesaurus application

This database is stored as 558the00.mdb. It illustrates various Microsoft Access features by implementing a simple thesaurus construction application.

To look behind the scenes at the various Microsoft Access objects, open the database while holding down the Shift key. To see the application as a user sees it, open it without holding down the Shift key.

Basic thesaurus tables

The thesaurus is stored in two Microsoft Access tables: the terms table and the termlinks table.

The terms table contains the fields termid, term, and note: termid is an Autonumber field and is the primary key; term is a text field with an input mask that forces it to be all upper case; and note is a memo field.

The termlinks table contains the fields term1id, mnemonic, and term2id: term1id and term2id are Number fields set to look up term by termid in terms; mnemonic is a text field which identifies the type of link from the first term to the second.

The relationships for these tables appear as follows in the Microsoft Access "Relationships" window:

Note that "terms_1" is simply a second reference to terms and not another table.

Forms

The thesaurus form

The Startup setting specifies that this is the form that will be displayed when the user opens the database without holding down the Shift key. It provides buttons for the user to click on to access various functions.

Clicking on the "Edit thesaurus" button displays the editor form; clicking on the "Preview alphabetic report", "Preview preferred terms", and "Preview hierarchical report" buttons display the alpha, preferred terms, and hierarchy reports respectively. The code for these buttons was created using the wizards.

Clicking on the "Update hierarchy" button updates the hierarchy table, as explained further below.

The editor form

The form provides controls that allow for adding, modifying, and deleting terms, scope notes, and links between terms. It also allows the user to view all the information for any term.

The record source for the form is the terms table. In addition, it makes use of the termlinks table in a subform and in the Visual Basic code.

The term, termid, and note text boxes

These text boxes are bound respectively to term, termid, and note in the terms table. The termid text box is disabled because the user should not modify that field.

The term buttons

Clicking the "New term" button causes Microsoft Access to go to a new record in terms.

Clicking the "Find term" button brings up a standard dialog allowing the user to search the term field.

Clicking the "Delete term" button deletes the term. If the term that the user asks to be deleted has any links to other terms in the termlinks table, the cascading option set for joins between the tables requires that the links also be deleted. In this case, Microsoft Access first presents a warning message:
Relationships that specify cascading deletes 
are about to
cause ... record(s) in this table and in related tables to be
deleted.

Are you sure you want to delete these records?

The Visual Basic code for these three buttons was created using the wizard. For the "Find term" button, one statement was added to ensure that the dialog would default to the term field:

    term.SetFocus

The "Existing links" subform

This object uses termlinksform as its source object. It is enabled, so that the user can scroll through it, but locked, so that the user cannot add a new one-way link and so bypass the automatic reciprocation required for a well-formed thesaurus.

Control over the appearance of subforms is somewhat limited in Microsoft Access, and subforms do not carry over many of the properties of their source forms. For example, the settings of termlinksform that hide the column headings and gray the cell backgrounds are not reflected in the subform.

The "Make link" button

Clicking on this button deletes any existing links in termlinks between term and the value of term2combobox and then makes new links as specified in the linktype list box.

The Visual Basic procedure begins by opening termlinks as a Recordset, selecting its primary key as the index to be used:

Private Sub LinkButton_Click()
On Error GoTo Err_LinkButton_Click
    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("termlinks")
    rst.Index = "PrimaryKey"
It then looks for and deletes links from the first term to the second and from the second to the first:
 
    rst.Seek "=", termid, term2combobox.Value
    If Not rst.NoMatch Then
        rst.Delete
    End If
    rst.Seek "=", term2combobox.Value, termid
    If Not rst.NoMatch Then
        rst.Delete
    End If
If the user just wanted to delete the links, the procedure is finished:
    If linktype.Value = "<no link>" Then
        Exit Sub
    End If
Otherwise, the procedure first adds a record to termlinks for the link from the first term to the second:
    rst.AddNew
    With rst
        !term1id = termid
        !term2id = term2combobox.Value
        !mnemonic = linktype.Value
        .Update
    End With
Next, it determines the appropriate reciprocal link type:
    Dim recip As String
    Select Case linktype.Value
        Case "BT"
            recip = "NT"
        Case "NT"
            recip = "BT"
        Case "RT"
            recip = "RT"
        Case "USE"
            recip = "UF"
        Case "UF"
            recip = "USE"
    End Select
and then it adds the reciprocal link, from the second term to the first:
    With rst
        .AddNew
        !term2id = termid
        !term1id = term2combobox.Value
        !mnemonic = recip
        .Update
    End With
Finally, the Recordset is closed, and the form and subform are refreshed to reflect any changes:
    rst.Close
    Refresh
    Forms!termlinksform.Refresh
Exit_LinkButton_Click:
    Exit Sub

Err_LinkButton_Click:
    MsgBox Err.Description
    Resume Exit_LinkButton_Click
    
End Sub

The "Link type" list box uses as its source a value list:

"<no 
link>";"USE";" UF";"BT&quo
t;;"NT";"RT"
The space before "UF" is a device to facilitate sorting of a list of links.

The row source for term2combobox was built with the Query Builder from the terms table. It includes both termid and term from the table; termid is invisible because the first column width is zero, but it is this value that is the value of the combo box.

The termlinksform form

This form is not used by itself. It was created by the subform wizard when a subform based on the termlinks table was added to the editor form.

Queries

The termlinksquery query

This query selects mnemonic and term1id from records in the termlinks table and also, from the terms table, term for the record that matches term2id. It is used in the alphaquery query.

The alphaquery query

This query forms the basis of the alpha report. It selects term and note from all records in the terms table and also, from the termlinkquery query, mnemonic and term for all records with term1id equal to termid. The properties of the join between termid in terms and term1id in termlinksquery are set to option 2, which includes all records from terms; "orphan" terms, with no links to other terms, will thus not be excluded.

In QBE, alphaquery could be expressed as a query involving tables only, without the need for termlinksquery; but, in trying to translate such a query to SQL in order to run it, Microsoft Access becomes confused and complains of an "ambiguous join".

The nontopterms query

This query selects every term from the terms table that has either a "BT" or a "USE" link to another term in the termlinks table. It is mostly useful as an element in the topterms query.

The topterms query

This query is used in generating the hierarchy table. It selects every term from the terms table that is not in the nontopterms query; that is, it selects every term that does not have either a "BT" or a "USE" link to another term in the termlinks table.

To do this, the properties of the join between term in terms and term in nontopterms are set to option 2, which includes all records from terms. The condition "Is Null" on term from nontopterms then excludes those terms found in nontopterms.

The nonpreferredterms query

This query selects every term from the terms table that has a "USE" link to another term in the termlinks table. It is mostly useful as an element in the preferredterms query.

The preferredterms query

This query is used in the preferredterms report. It selects every term from the terms table that is not in the nonpreferredterms query; that is, it selects every term that does not have a "USE" link to another term in the termlinks table.

To do this, the properties of the join between term in terms and term in nonpreferredterms are set to option 2, which includes all records from terms. The condition "Is Null" on term from nonpreferredterms then excludes those terms found in nonpreferredterms.

Basic reports

The alpha report

This report is based on the alphaquery query. It is sorted on the first term, the mnemonic, and the second term. The first two of these have group headers, with the KeepTogether property set to WithFirstDetail. The RepeatSection property of the group headers is set to True, so that the first term and mnemonic will also appear on subsequent pages if the references extend over more than one page.

Unfortunately, scope notes are also repeated along with their terms; the reason is that note cannot have a group header, because Microsoft Access will not display a report grouped on a memo field.

The preferredterms report

This report is based on the preferredterms query. It is quite simple, being sorted on term, with no group headers.

Hierarchy features

The hierarchy table

The hierarchy table allows for 5 levels of hierarchy. Because of a bug in Microsoft Access, the fields in the table must be actual terms, rather than term ids, in order to ensure correct sorting when producing reports; this unfortunately means that the table takes up a lot of disk space, especially given that Microsoft Access does not allow sorting by variable-length fields.

Because of the bulk of the hierarchy table, it is not maintained dynamically, but is generated only on request; otherwise, it is empty.

The "Update hierarchy" button

When the user clicks on the "Update hierarchy" button on the thesaurus form, Microsoft Access executes the UpdateHierarchyButton_Click procedure.
Private Sub UpdateHierarchyButton_Click()
This procedure begins by opening various record sets: the topterms query, which lists terms that have no narrower terms; the terms table; the termlinks table; and the hierarchy table, which it also clears.
    Dim topterms As Recordset
    Set dbs = CurrentDb
    dbs.Execute "DELETE * FROM hierarchy WHERE true;"
    Set topterms = dbs.OpenRecordset("topterms")
    Set terms = dbs.OpenRecordset("terms")
    Set termlinks = dbs.OpenRecordset("termlinks")
    Set hierarchy = dbs.OpenRecordset("hierarchy")
    terms.Index = "PrimaryKey"
    termlinks.Index = "PrimaryKey"
Because the terms, termlinks, and hierarchy tables will be used by other procedures and functions called by this procedure, they have been declared in the Declarations section. The same is true of the termids array, in which the term ids of the current chain will be stored temporarily.
    Dim dbs As Database
    Dim terms As Recordset
    Dim termlinks As Recordset
    Dim hierarchy As Recordset
    Dim termids(5) As Integer

The procedure goes to the beginning of topterms and continues until it reaches the end. It puts the id for each top term in the first position in the chain and calls upon another procedure UpdateHierarchy with an argument of 1:

    topterms.MoveFirst
    While Not topterms.EOF
        termids(1) = topterms!termid
        UpdateHierarchy (1)
        topterms.MoveNext
    Wend
When finished, the procedure closes the recordsets:
    hierarchy.Close
    termlinks.Close
    terms.Close
    topterms.Close
End Sub

The procedure UpdateHierarchy can be called at different levels, as specified in its argument Level:

Private Sub UpdateHierarchy(Level As Integer)
It declares two local variables: i will be used as a pointer to elements in the chain; NTCount will keep track of how many narrower terms a term has.
    Dim i As Integer
    Dim NTCount As Integer
If Level is greater than 5, the rest of the procedure will not work; so, a little safeguard is included to prevent difficulties:
    If Level > 5 Then
        Exit Sub
    End If
The procedure now initializes NTCount to zero and looks for the first record in termlinks with the first term id the same as the term id at the specified level in the chain:
    NTCount = 0
    termlinks.Seek ">=", termids(Level), 0
If the maximum chain length of 5 has not been reached, the procedure then begins scanning termlinks, continuing as long as the end of the table has not been reached and the term ids match:
    If Level < 5 Then
        Do While (Not termlinks.EOF)
            If termlinks!term1id <> termids(Level) Then
              Exit Do
            End If
If the link is to a narrower term, the narrower term's id is placed in the next position in the chain, and UpdateHierarchy calls itself to deal with the next level:
            If termlinks!mnemonic = "NT" Then
                termids(Level + 1) = termlinks!term2id
                UpdateHierarchy (Level + 1)
The narrower term count is then incremented, and the current position in termlinks is restored, since termlinks is a global variable and its current record will likely have been reset by the intervening call to UpdateHierarchy:
                NTCount = NTCount + 1
                termlinks.Seek "=", termids(Level), 
termids(Level + 1)
            End If
The procedure then moves on to the next record:
            termlinks.MoveNext
        Loop
    End If

If no narrower terms were found or visited, a hierarchy record must be created at this level, with any remaining positions in the chain left empty:

    If NTCount = 0 Then
        For i = Level + 1 To 5
            termids(i) = 0
        Next
        hierarchy.AddNew
        hierarchy!term1 = TermOf(termids(1))
        hierarchy!term2 = TermOf(termids(2))
        hierarchy!term3 = TermOf(termids(3))
        hierarchy!term4 = TermOf(termids(4))
        hierarchy!term5 = TermOf(termids(5))
        hierarchy.Update
    End If
End Sub

The TermOf function simply gives the term for a given term id; if there is no such term, the function returns an empty string:

Private Function TermOf(Id As Integer) As String
    If Id <= 0 Then
        TermOf = ""
    Else
        terms.Seek "=", Id
        If terms.NoMatch Then
            TermOf = ""
        Else
            TermOf = terms!term
        End If
    End If
End Function

The hierarchy report

This report is based on the hierarchy table. It is sorted on term1, term2, term3, term4, and term5, and grouped on term1, term2, term3, and term4, with a group header for each. Each successive hierarchical level is shown by increased indentation and decreased font size.
Home

Last updated November 23, 2001.
This page maintained by Prof. Tim Craven
E-mail (text/plain only): craven@uwo.ca
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7