Using random access
1999-12-17 File access 0 215
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 TypeWhen 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\RandomFile.dat") <> "" Then ' deletes the file if it exists Kill "C:\FolderName\RandomFile.dat" End If FileNum = FreeFile ' next free filenumber ' create the new file Open "C:\FolderName\RandomFile.dat" For Random As #FileNum Len = Len(MyRecord) ' 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 SubThe 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 the existing file Open "C:\FolderName\RandomFile.dat" For Random As #FileNum Len = Len(MyRecord) ' 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 SubAs 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 the existing file Open "C:\FolderName\RandomFile.dat" For Random As #FileNum Len = Len(MyRecord) 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 SubIt'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 the existing file Open "C:\FolderName\RandomFile.dat" For Random As #FileNum Len = Len(MyRecord) 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 SubTo 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
- Sequential access, read/write textfiles, such as error logs and reports, e.g. *.txt files, *.ini files and *.csv files.
- Binary access, read/write records of variable length or for files with no general format, this requires that you have exact knowledge on how the data is stored in the file in order to retrieve any information.