LIS 558 - Reading a MARC record in Microsoft Access

The Microsoft Access table marctabl in the database Marcde00.mdb is designed to hold variable-length fields from MARC records. Each record in the table corresponds to one field occurrence from one MARC record. The fields in the table are: ControlNo is the unique control number (not necessarily a pure number) assigned to the MARC record. The value of TagInstance begins again at 1 for each new MARC record and is incremented for each additional MARC field in the MARC record. The combination of ControlNo and TagInstance thus uniquely identifies a row in the table. The table field MARCTag contains the 3-character MARC tag for the MARC field. The table field Data contains the variable-length data for the field; Data is a memo field to allow for various lengths of data.

The form marcform displays the contents of marctabl. When you click on the button labeled "Get record", the MARC record in marcdemo.mrc is read and its variable-length fields transferred into marctabl.

Look at the GetRecordButton_Click method, which responds to the onClick event of the button. The following variables are defined:

    Dim b As Byte
    Dim base As Integer
    Dim c, No, RecordLength  As String
    Dim fieldsCollection As New Collection
    Dim fileLength, i, l, n, p As Integer
    Dim s As String
The method begins by trying to open Marcdemo.mrc for reading one byte at a time:
    Open "s:\people\faculty\craven\558\marcdemo\marcdemo.mrc" 
For Random As #1 Len = 1
(You could modify this line to open any single MARC record file downloaded, say, from the Library of Congress.) Since Marcdemo.mrc contains only one MARC record, we do not need to worry about the offset of the record from the beginning of the file, as we probably would in a practical application.

If the file can be opened, the next step is to read the value of recordLength from the first 5 characters of the record:

    RecordLength = ""
    For i = 1 To 5
        Get #1, , b
        RecordLength = RecordLength + Chr(b)
If this value can be read, it is displayed in the form label length:
    Length.Caption = RecordLength

The base address of data in a MARC record starts at position 12; but, because Microsoft Access starts counting file characters at 1 instead of at 0, the method goes to position 1+12:

    For i = 6 To 12
        Get #1, , b
If the base address can be read temporarily into variable s, it is displayed in the form's baseAddress label and its numeric value is stored in the variable base:
    s = ""
    For i = 13 To 17
        Get #1, , b
        s = s + Chr(b)
    baseAddress.Caption = s
    base = Val(s)

To begin reading the record directory, the method moves on to position 1+24 and begins a loop that will continue until the end of the directory (one position before the base address of data) is reached:

    For i = 18 To 24
        Get #1, , b
    i = 25
    While i < base
Each directory entry is read temporarily into variable s and then appended to the list fieldsCollection:
        s = ""
        For l = 1 To 12
            Get #1, , b
            s = s + Chr(b)
        fieldsCollection.Add (s)
        i = i + 12
(Alternatively, the number of directory entries could have been established in advance with the formula (base-25)/12.)

The method skips over the field terminator character at the end of the record directory and reads the control number:

    Get #1, , b
    No = ""
    For i = 1 To 12
        Get #1, , b
        No = No + Chr(b)

Now updating the Microsoft Access table can begin. First, for demonstration purposes, any existing records are cleared out and the data source for the form is requeried:

    Dim dbs As Database
    Set dbs = CurrentDb
    dbs.Execute "DELETE * FROM marctabl WHERE true;"
(You could remove these lines if you want to be able to load multiple MARC records from different files.)

The method uses a loop to traverse the array of MARC fields, inserting a new record for each and setting the values of ControlNo, TagInstance, and MARCTag:

    For i = 1 To fieldsCollection.Count
        DoCmd.GoToRecord acDataForm, "marcform", acNewRec
        ControlNo = No
        s = fieldsCollection.Item(i)
        TagInstance = i
        MARCTag = Mid(s, 1, 3)
The MARC field length and starting-character positions are extracted from the directory entry and converted to numbers, the position of the file is set to the starting position, and the MARC field contents are read temporarily into variable s; because characters with ASCII values below 32, including the MARC subfield delimiter character, may not display correctly, they are converted to ASCII character #127; the final string value is then used as the value of Data:
    l = Val(Mid(s, 4, 4))
        n = Val(Mid(s, 8, 5))
        Get #1, base + n, b
        s = Chr(b)
        For p = 2 To l
            Get #1, , b
            If b < 32 Then
                b = 127
            End If
            s = s + Chr(b)
        Data = s

When the entire file has been read, it is closed, and the form is refreshed to ensure that the last record is updated.

    Close #1
End Sub


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