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

    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

    Print

    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.