Using binary file access

With binary access it's possible to store information in any way you want, you are not limitied to a fixed record length. This means that you have to know how the information is stored in a binary file to retrieve it again. Binary access files uses less diskspace by allowing variable sized records. You can also read and write information at the same time like a random access file.

In a binary file each record can be of variable size. This is achieved by using an user-defined data type. User defined data types are defined in a the declaration section of a module (at the beginning of a module) like this:

Type MyBinaryRecordInfo
    ID As String ' variable length
    LastName As String ' variable length
    FirstName As String ' variable length
    BirthDate As Date ' fixed length
End Type

To open a file for binary access from VBA you use the Open statement:
Open filename For Binary As filenumber
It's not necessary to specify the buffersize/recordlength, it's ignored with binary files.
You must Close all files you Open before reopening the files for another type of operation.

Since records with binary access can be of variable length, it is necessary to store information about the size of each field in the record so that the information can be read later. A good way to do this is to store an integer with each string to indicate the length of the string. The macros below shows how to create a new binary file using records of the userdefined type MyBinaryRecordInfo defined above:

Sub WriteBinaryFile()
Dim MyRecord As MyBinaryRecordInfo, f As Integer, i As Integer
    If Len(Dir("C:\FOLDERNAME\BINFILE.DAT")) > 0 Then
        ' deletes the file if it exists
        Kill "C:\FOLDERNAME\BINFILE.DAT"
    End If
    f = FreeFile ' next free filenumber
    ' create the new file
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #f
    ' write records to the binary file
    For i = 1 To 100
        With MyRecord
            .ID = RandomLengthString(10, 3) ' just a dummy
            .LastName = "LastName" & i
            .FirstName = "FirstName" & i
            .BirthDate = Date - i
        End With
        WriteBinaryRecord MyRecord, f ' save the record
    Next i
    Close #f ' close the file
End Sub

Sub WriteBinaryRecord(MyRecord As MyBinaryRecordInfo, f As Integer)
' writes the content of MyRecord to the binary file
Dim intSize As Integer
    With MyRecord
        intSize = Len(.ID) ' determine the length of the variable
        Put f, , intSize ' write the length of the variable
        Put f, , .ID ' write the variable
        
        intSize = Len(.LastName) ' determine the length of the variable
        Put f, , intSize ' write the length of the variable
        Put f, , .LastName ' write the variable
        
        intSize = Len(.FirstName) ' determine the length of the variable
        Put f, , intSize ' write the length of the variable
        Put f, , .FirstName ' write the variable
        
        Put f, , .BirthDate ' write the variable (fixed length variable)
    End With
End Sub

Private Function RandomLengthString(Optional lngMax As Long = 100, Optional lngMin As Long = 1) As String
Dim i As Long, j As Long, c As Integer, strResult As String
' returns a random string of random length, the function has no practical use outside this example
    Randomize
    j = Int(Rnd * (lngMax - lngMin + 1) + lngMin)
    strResult = vbNullString
    For i = 1 To j
        c = Int(Rnd * (122 - 97 + 1) + 97)
        strResult = strResult & Chr(c)
    Next i
    RandomLengthString = strResult
End Function

From the example macros above you can see that a binary access file requires more code to handle the read and write operations than a random access file that can read and write an entire record using a single function call.

To read information from a binary file you use the Get statement.
When you use Get with a variable-length string, the number of bytes read from the file equals the current length of the string variable.
To set the length of a variable-length string you can use the String$ function.
The String$ function can set the variable equal to a specific number of spaces (or other characters).
The macros below shows how you can read information from a binary file:

Sub ReadBinaryFile()
Dim MyRecord As MyBinaryRecordInfo, f As Integer, i As Long
    f = FreeFile ' next free filenumber
    ' open the binary file
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #f
    ' read records from the binary file
    i = 0
    Do While Loc(f) < LOF(f)
        i = i + 1
        ReadBinaryRecord MyRecord, f
        ' do something with the input
        With MyRecord
            Debug.Print "Record #" & i, .ID, .LastName, .FirstName, .BirthDate
        End With
    Loop
    Close #f ' close the file
End Sub

Sub ReadBinaryRecord(MyRecord As MyBinaryRecordInfo, f As Integer)
' reads the next record from an open binary file
Dim intSize As Integer
    With MyRecord
        Get f, , intSize ' read the size of the ID field
        .ID = String(intSize, " ") ' set the variable length
        Get f, , .ID ' read the variable string field
        
        Get f, , intSize ' read the size of the LastName field
        .LastName = String(intSize, " ") ' set the variable length
        Get f, , .LastName ' read the variable string field
        
        Get f, , intSize ' read the size of the FirstName field
        .FirstName = String(intSize, " ") ' set the variable length
        Get f, , .FirstName ' read the variable string field
        
        Get f, , .BirthDate ' read the BirtDate field (fixed length variable)
    End With
End Sub

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