Working with Random Access Files in VB

Level:
Level2

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.

If you enjoyed this post, subscribe for updates (it's free)

HOW TO GENERATE RANDOM FORMS?

uhmm pls help for example if i click a command button i want to show form 1 2 or 3 randomly. is it possible?

Oh Really?

Why dont they show you have to do a direct read on a record for any key the user inputs? Hello!
theyve already trapped the out of bounds keys but thats pretty childish. What if theres 100 records in the file and i want record 1001?
What will happen? How do I trap the resulting error?
This example is incomplete.

Random Acces

How to make a random access file that can store a 26 people,
in the program you can update the record or add some file in the same record that stores in the data
and in the program you can search a file in the form.

please help me

thanks...,

Great tutorial ! :D

Thank you !!! Exact what I needed

Tutorial - Random access

Superb tutorial! Many thanks.

Delete record in random file

HOw can i delete record in Random file plz hlep now?

Help wanted

How can i Delete and Edit in Random files plz Help

i want to create an putput

i want to create an putput file like ;
------- file structure -------
DERIV RAD
DIM M
$FLTCON NMACH=1.0, MACH(1)=0.166,
NALPHA=14.0, ALSCHD(1)=-4.0,-3.0,-2.0,-1.0,0.0,
1.0,2.0,3.0,4.0,5.0,5.73,6.0,7.0,8.0,
RNNUB=3.78E6$
$OPTINS SREF=0.209, CBARR=0.233, BLREF=0.9144$
$SYNTHS XCG=0.071, ZCG=0.0, XW=0.0, ZW=0.0, ALIW=0.0$

first two raws doesnt matter. Third fourth fifht and sixh raws includes couple of arrays which are also
doesnt matter but the commant FLTCON must begin with$ and end with $. so which way is better to
create such a file? (i also want to limit the lenght of raws)

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>. The supported tag styles are: <foo>, [foo].

More information about formatting options

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.