Random Files
Random files are record-based files with an internal structure that supports "direct access" by record number. This means that your program can read from or write to a specific record in a random access file, say the 50th record, without reading through the previous 49 records. Compare that to reading or writing a sequential file, where to get to a specific record, you must read through all preceding records.
The difference between random access and sequential access can be likened to accessing music on a CD versus a cassette tape. To get to song number 6, you can tell your CD player to go directly to track 6, whereas on a cassette tape, you must fast-forward through the first 5 songs to get to song number 6.
In the earlier days of BASIC, before the "client-server era" where RAD systems such as VB, Delphi, and PowerBuilder interacted with desktop and ODBC databases such as MS-Access, SQL Server, and Oracle, random access files were used as building blocks to put together data access systems that could be considered early forms of desktop databases.
The Open Statement for Random Access Files
The "full blown" syntax for the Open statement was given in the previous topic on binary files. The syntax for the Open statement, as it pertains to random files, is as follows:
If you only want to read from the random access file, use:
Open filename For Random Access Read As #filenumber Len = reclength
and if you only want to write to the random access file, use:
Open filename For Random Access Write As #filenumber Len = reclength
and if you want to both read from and write to the random access file (for example, you want to access a particular record and then update one or more of its fields), use:
Open filename For Random Access Read Write As #filenumber Len = reclength
In the syntax formats above, reclength refers to the total length in bytes of all of the fields (variables) you define as part of a user-defined Type (UDT) structure. The variable that you base on the UDT serves as the storage facility, or record variable, into which a record from the random file is read, or from which a record to the random file is written.
For example, the sample program for this topic will use a random access version of the employee file we used in the topics on sequential files. The Type structure for the employee record will be defined as follows:
Private Type EmployeeRecord
EmpName As String * 20
DeptNbr As Integer
JobTitle As String * 25
HireDate As Date
HrlyRate As Single
End Type
The record variable based on this EmployeeRecord Type will be defined as:
Private Type mudtEmpRecord As EmployeeRecord
Note that the String variables that make up this structure are defined as fixed-length strings. This is important for efficient access of the random file. Given that (in VB6 and lower), the size of an Integer is 2, the size of a Date is 8, and the size of a Single is 4, the total length of the structure above is 59 (20 + 2 + 25 + 8 + 4)
Thus, an Open statement for the random employee file could be written as:
Open strRndEmpFileName For Random Access Read Write _
As #intRndEmpFileNbr Len = 59
or, even easier and more flexible:
Open strRndEmpFileName For Random Access Read Write _
As #intRndEmpFileNbr Len = Len(mudtEmpRecord)
In the syntax above, the Len function is used on the record variable, thus "letting the computer do the work" of figuring out the total length of the structure. And, using this method, if we add fields to or remove fields from the structure, we don't have to worry about recalculating the total length.
The Get Statement
The Get statement is used read data from a file opened in random mode. The syntax, as it applies to random files is:
Get [#]filenumber, [recnumber], varname
The filenumber is any valid filenumber.
Recnumber is the record position within the file that is read. The record position is "one-based", meaning the first record position in the file is 1, the second record position is 2, and so on. You can omit this entry, in which case the next record following the last Get or Put statement is read. If you omit the record number entry, you must still include the delimiting commas in the Get statement, for example:
Get #intRndEmpFileNbr, , mudtEmpRecord
Varname is the record variable into which the data will be read. The record variable is a variable defined on the UDT record structure as described above. Once the Get statement is executed, the data from the file record can be referenced using the following syntax: recordname.fieldname
For example, a reference the EmpName field would be coded as:
mudtEmpRecord.EmpName
The Put Statement
The Put statement is used write data to a file opened in random mode. The syntax, as it applies to binary files is:
Put [#]filenumber, [recnumber], varname
The filenumber is any valid filenumber.
Recnumber is the record position within the file which is written. The record position is "one-based", meaning the first record position in the file is 1, the second record position is 2, and so on. You can omit this entry, in which case the next record following the last Get or Put statement is written. If you omit the record number entry, you must still include the delimiting commas in the Put statement, for example:
Put #intRndEmpFileNbr, , mudtEmpRecord
Varname is the record variable from which the data will be written. The record variable is a variable defined on the UDT record structure as described above.
Sample Program
The "Try It" sample program performs three main functions that demonstrate the features of random files: (1) creates a random file based on input from a sequential file; (2) reads back the random file just created and displays its contents; and (3) retrieves a record from the random file given the record number, and updates a field in the retrieved record based on user input.
The code listed below is heavily commented to aid in the understanding of how the program works.
"Try It" Program Code:
The Type declaration for EmployeeRecord must be defined in the General Declarations section of the from (the portion of the code prior to where any Subs or Functions are defined). The record variable "mudtEmpRecord" is also declared in this area (although strictly speaking, that is not an absolute requirement, as it could have been declared at the local level with the Dim statement in the cmdTryIt_Click event procedure).
Option Explicit
Private Type EmployeeRecord
EmpName As String * 20
DeptNbr As Integer
JobTitle As String * 25
HireDate As Date
HrlyRate As Single
End Type
Private mudtEmpRecord As EmployeeRecord
. . .
Code for the cmdTryIt_Click event procedure:
Private Sub cmdTryIt_Click()
Dim strSeqEmpFileName As String
Dim strRndEmpFileName As String
Dim strBackSlash As String
Dim intSeqEmpFileNbr As Integer
Dim intRndEmpFileNbr As Integer
Dim strPrompt As String
Dim strNewJob As String
Dim intRecordCount As Integer
Dim strRecordNumber As String
Dim intRecordNumber As Integer
Dim intX As Integer
Dim strEmpName As String
Dim intDeptNbr As Integer
Dim strJobTitle As String
Dim dtmHireDate As Date
Dim sngHrlyRate As Single
' Prepare the file names ...
strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")
strSeqEmpFileName = App.Path & strBackSlash & "EMPLOYEE.DAT"
strRndEmpFileName = App.Path & strBackSlash & "EMPLOYEE.RND"
'-----------------------------------------------------------------------
' In the first part of this sample program, we will create, or load,
' a random access version of the comma-delimited sequential employee
' file that was used in one of the sample programs for sequential access
' files.
'-----------------------------------------------------------------------
' Open the sequential employee file for input ...
intSeqEmpFileNbr = FreeFile
Open strSeqEmpFileName For Input As #intSeqEmpFileNbr
' If the random employee file we want to write already exists,
' delete it ...
If Dir$(strRndEmpFileName) <> "" Then
Kill strRndEmpFileName
End If
' Open the random employee for writing ...
intRndEmpFileNbr = FreeFile
Open strRndEmpFileName For Random Access Write _
As #intRndEmpFileNbr Len = Len(mudtEmpRecord)
' Initialize record count variable to keep track of how many records will
' be written to the random file ...
intRecordCount = 0
' This loop will read a record from the comma-delimited sequential employee file
' and write a corresponding record to its random access counterpart ...
Do Until EOF(intSeqEmpFileNbr)
' Read a record's worth of fields from the comma-delimited employee file,
' storing the fields into their corresponding variables ...
Input #intSeqEmpFileNbr, strEmpName, intDeptNbr, strJobTitle, dtmHireDate, sngHrlyRate
' Assign each variable read in from the comma-delimited file to its corresponding
' field in the mudtEmpRecord record variable (based on the EmployeeRecord UDT).
' Note that a With/End With block is used. If With/End With was not used, this set
' of assignment statements would have to be written as follows:
' mudtEmpRecord.EmpName = strEmpName
' mudtEmpRecord.DeptNbr = intDeptNbr
' mudtEmpRecord.JobTitle = strJobTitle
' mudtEmpRecord.HireDate = dtmHireDate
' mudtEmpRecord.HrlyRate = sngHrlyRate
With mudtEmpRecord
.EmpName = strEmpName
.DeptNbr = intDeptNbr
.JobTitle = strJobTitle
.HireDate = dtmHireDate
.HrlyRate = sngHrlyRate
End With
' Now that the record variable has been populated with the proper data,
' write the record out to the random file using the Put statement ...
Put #intRndEmpFileNbr, , mudtEmpRecord
' Increment the record count variable ...
intRecordCount = intRecordCount + 1
Loop
' Close the sequential file and the random file ...
Close #intSeqEmpFileNbr
Close #intRndEmpFileNbr
'-----------------------------------------------------------------------
' In the next part of this sample program, we will display the records
' written to the random file by reading them back and printing their
' contents on the form, one by one.
'-----------------------------------------------------------------------
' Print headings on the form ...
Cls
Print intRecordCount & " employee records were written to the random file."
Print "Contents as follows:"
Print "EMP NAME"; _
Tab(25); "DEPT"; _
Tab(35); "JOB TITLE"; _
Tab(60); "HIRE DATE"; _
Tab(70); "HRLY RATE"
Print "--------"; _
Tab(25); "----"; _
Tab(35); "---------"; _
Tab(60); "---------"; _
Tab(70); "---------"
' Open the random file for reading ...
intRndEmpFileNbr = FreeFile
Open strRndEmpFileName For Random Access Read _
As #intRndEmpFileNbr Len = Len(mudtEmpRecord)
' Since we know how many records are in the file, we can use a For/Next loop
' to control the reading and printing of the records ...
For intX = 1 To intRecordCount
' With the Get statement, read the next (or first) record from the
' random file, storing its contents in the mudtEmpRecord structure ...
Get intRndEmpFileNbr, , mudtEmpRecord
' Print the data from the record onto the form. Once again, a With/End With
' block is used to "factor out" the record variable. If With/End With was
' not used, the Print statement would have to be written as follows:
' Print mudtEmpRecord.EmpName; _
' Tab(25); Format$(mudtEmpRecord.DeptNbr, "@@@@"); _
' Tab(35); mudtEmpRecord.JobTitle; _
' Tab(60); Format$(mudtEmpRecord.HireDate, "mm/dd/yyyy"); _
' Tab(70); Format$(Format$(mudtEmpRecord.HrlyRate, "Standard"), "@@@@@@@")
With mudtEmpRecord
Print .EmpName; _
Tab(25); Format$(.DeptNbr, "@@@@"); _
Tab(35); .JobTitle; _
Tab(60); Format$(.HireDate, "mm/dd/yyyy"); _
Tab(70); Format$(Format$(.HrlyRate, "Standard"), "@@@@@@@")
End With
Next
' Close the random file ...
Close #intRndEmpFileNbr
'-----------------------------------------------------------------------
' In the last part of this sample program, we will request an employee
' record and then update the job title for that employee.
'-----------------------------------------------------------------------
' Prompt the user to enter a valid record number (the record number must be
' between 1 and the number of records in the file). The loop below validates
' the entry, and re-prompts the user if necessary, before moving on ...
Do
strRecordNumber _
= InputBox("Enter a record number between 1 and " & intRecordCount & ":", _
"Random Record Test")
If strRecordNumber = "" Then Exit Sub ' user clicked Cancel
intRecordNumber = Val(strRecordNumber)
If intRecordNumber < 1 Or intRecordNumber > intRecordCount Then
MsgBox "Invalid Record Number.", vbExclamation, "Random Record Test"
End If
Loop Until intRecordNumber >= 1 And intRecordNumber <= intRecordCount
' Open the random employee file for read/write access ...
intRndEmpFileNbr = FreeFile
Open strRndEmpFileName For Random Access Read Write _
As #intRndEmpFileNbr Len = Len(mudtEmpRecord)
' Get the employee record corresponding to the record number entered above ...
Get #intRndEmpFileNbr, intRecordNumber, mudtEmpRecord
' Prompt the user to enter a new job title for the employee ...
strPrompt = "The employee in record # " & intRecordNumber & " is " _
& Trim$(mudtEmpRecord.EmpName) _
& ". Enter the new job title for this employee:"
strNewJob = InputBox(strPrompt, "Update Employee", Trim$(mudtEmpRecord.JobTitle))
' Display the results
If strNewJob = "" Then ' user clicked Cancel
Print "Record was not updated."
Else
mudtEmpRecord.JobTitle = strNewJob
Put #intRndEmpFileNbr, intRecordNumber, mudtEmpRecord
Print "Job title for "; Trim$(mudtEmpRecord.EmpName); _
" was updated to "; Trim$(mudtEmpRecord.JobTitle) & "."
End If
' Close the random file ...
Close #intRndEmpFileNbr
End Sub
Upon clicking Try It button, the random file will be written out and then read back and its contents will be displayed. The user will then be prompted to enter a record number, as shown below:
The program will then prompt you to enter a new job title for the retrieved employee, showing the exsiting job title as a default:
You can then enter the new job title:
A message confirming the update is then displayed below the previously displayed data:
Download the VB project code for the example above here.