These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Using random access

A random access file is assumed to contain a series of records of equal length. This makes it easy and quick to locate stored information. Random access files can use less diskspace compared to sequential files. They can also waste space if fields in the records are left blank or if most of the record strings are shorter than the dimensioned string length. It's also possible to read and write information at the same time. Each record must correspond to a single data type like integers, strings, or user-defined types. User defined data types are defined in a the declaration section of a module (at the beginning of a module) like this:

Type MyRecordInfo
    LastName As String * 30
    FirstName As String * 30
    BirthDate As Date
End Type

When you define a user defined variable that you are going to use with a random access file, its important that you use variables with a fixed length. The variable type String can contain strings of different lengths, make sure you tell how many characters you want the string to store, e.g. LastName as String * 30 means that the variable LastName can contain 30 characters.

To open a file for random access from VBA you use the Open statement:
Open filename For Random As filenumber Len=buffersize
Buffersize must be set equal to the record length, Len = Len(MyRecord).
You must Close all files you Open before reopening the files for another type of operation.

Here is a example macro that creates a new file for random access using records of the userdefined type defined above:

Sub WriteRandomFile()
Dim MyRecord As MyRecordInfo, FileNum As Integer, i As Integer
    If Dir("C:\FOLDERNAME\RANDFILE.DAT") <> "" Then
        ' deletes the file if it exists
        Kill "C:\FOLDERNAME\RANDFILE.DAT"
    End If
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\RANDFILE.DAT" For Random As #FileNum Len = _
        Len(MyRecord) ' creates the new file
    ' write to the random file
    For i = 1 To 100
        With MyRecord
            .LastName = "Lastname" & i
            .FirstName = "Firstname" & i
            .BirthDate = Date
        End With
        Put #FileNum, , MyRecord ' adds a new record at the end of the file
    Next i
    Close #FileNum ' close the file
End Sub

The information in the resulting random access file can be read like this:

Sub ReadRandomFile()
Dim MyRecord As MyRecordInfo, FileNum As Integer, i As Integer
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\RANDFILE.DAT" For Random As #FileNum Len = _
        Len(MyRecord) ' open the existing file
    ' read from the random file
    For i = 1 To 100
        Get #FileNum, , MyRecord ' reads the next record
        ' do something with the input
        With MyRecord
            'Debug.Print .LastName & ", " & _
                .FirstName & " " & .BirthDate
            Debug.Print Trim(.LastName) & ", " & _
                Trim(.FirstName) & " " & .BirthDate
        End With
    Next i
    Close #FileNum ' close the file
End Sub

As mentioned earlier, it's easy to retrieve spesific records with information in a random access file. The example macro below shows how this can be done:

Sub ReadRandomFileRecord()
Dim MyRecord As MyRecordInfo, FileNum As Integer, i As Integer, RecordsCount As Long
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\RANDFILE.DAT" For Random As #FileNum Len = _
        Len(MyRecord) ' open the existing file
    RecordsCount = LOF(FileNum) / Len(MyRecord) ' total number of records
    i = InputBox("Enter a record number (1-" & _
        RecordsCount & ")", "Display a Record", 1)
    If i > 0 And i <= RecordsCount Then
        Get #FileNum, i, MyRecord ' reads the record
        ' do something with the input
        With MyRecord
            Debug.Print Trim(.LastName) & ", " & _
                Trim(.FirstName) & " " & .BirthDate
        End With
    End If
    Close #FileNum ' close the file
End Sub

It's equally easy to edit and update spesific records with information in a random access file. The example macro below shows how this can be done:

Sub WriteRandomFileRecord()
Dim MyRecord As MyRecordInfo, FileNum As Integer, i As Integer, RecordsCount As Long
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\RANDFILE.DAT" For Random As #FileNum Len = _
        Len(MyRecord) ' open the existing file
    RecordsCount = LOF(FileNum) / Len(MyRecord) ' total number of records
    i = InputBox("Enter a record number (1-" & _
        RecordsCount & ")", "Update a Record", 1)
    If i > 0 And i <= RecordsCount Then
        ' edit the chosen record
        With MyRecord
            .LastName = "NewLastname"
            .FirstName = "NewFirstname"
            .BirthDate = Date + 1
        End With
        Put #FileNum, i, MyRecord ' writes the record
    End If
    Close #FileNum ' close the file
End Sub

To add records to the end of a random access file you have to determine the record number for the new record. This number equals the count of records in the file plus one. When you have determined the new recordnumber you can write the new record at this position in the file. The number of records in the file is calculated like this: RecordsCount = LOF(FileNum) / Len(MyRecord)
A new record can be written like this: Put #FileNum, LOF(FileNum) / Len(MyRecord) + 1, MyRecord

Document last updated 1999-12-17 12:48:50