Using binary file access
1999-12-17 File access 2 78
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, MyBinaryFile As String, f As Integer, i As Integer MyBinaryFile = "C:\FolderName\BinaryFile.dat" If Len(Dir(MyBinaryFile)) > 0 Then ' deletes the file if it exists Kill MyBinaryFile End If f = FreeFile ' next free filenumber ' create the new file Open MyBinaryFile 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, MyBinaryFile As String, f As Integer, i As Long MyBinaryFile = "C:\FolderName\BinaryFile.dat" f = FreeFile ' next free filenumber ' open the binary file Open MyBinaryFile 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
- Sequential access, read/write textfiles, such as error logs and reports, e.g. *.txt files, *.ini files and *.csv files.
- Random access, read/write records of fixed length.
OPE | 2012-05-15 21:48:23 (GMT)
I'm no expert on how to index information and keeping this indexing information updated, so I can't give you much advice about this.
David Powell | 2012-05-15 20:23:55 (GMT)
is it possible to Index a field using this method or even possible ?