Random Access Files

Random access files allow the programmer to access specific values within the file without having to load the entire file into memory. This is accomplished by ensuring that the individual data elements are of the same length before writing to the file. Again, consider the example of a phone book. Instead of storing the information as variable-length strings, the name and phone number can be stored with fixed length strings. The combination of the two fixed length strings that follow require the same amount of memory for every line written to the file. This will make it easy to locate a particular line in the file when the data is input.

Dim theName As String*20 Dim theNumber As String*8

If the name to be stored is less than 20 characters, then spaces are added to match the defined length. If the string exceeds 20 characters, only the first 20 characters of the string are stored; therefore, it is important to define the length of the string so that it will be long enough to contain any possible value, yet not so long that too much memory is wasted by saving lots of spaces. The resulting data file might then look something like this:

"John Smith ", "111-2222" "Joe James ", "123-4567"

"Jane Johnson ", "456-7890"

Each line in the file requires the same amount of memory to store and is referred to as a record. Records can be represented by one or more values of the same or different data type (string, integer, and so on). Because the length of each record is identical, finding a specific record in the file without loading the entire file into memory is relatively easy (as you will see shortly).

Rather than declare the individual elements of a record as separate variables, it is useful to define a custom data type that can be used in a variable declaration. The variable of the newly defined type can include all the desired elements of the record. To define a phone record for the previous example, a custom data type that includes both string elements must be declared in the general declarations section of a module.

With the new data type definition, any variable can now be declared in a procedure as type Phone as shown in the CreateRanAccessFile() sub procedure. Individual elements of the phoneRec variable are accessed using the dot operator. To take full advantage of the custom data type, I write the phoneRec variable to a file using random access.

Private Type Phone theName As String*20 theNumber As String*8 End Type

Public Sub CreateRanAccessFile() Dim phoneRec As Phone Dim filePath As String Dim I As Integer, recNum As Integer recNum = 1

filePath = ActiveWorkbook.Path & "\randomPhone.dat" Open filePath For Random As #1 Len = Len(phoneRec) For I = 1 To 3

phoneRec.theName = Cells(I, "A").Value phoneRec.theNumber = Cells(I, "B").Value Put #1, recNum, phoneRec recNum = recNum + 1 Next I Close #1 End Sub

The length of the record is specified by passing the variable phoneRec to the Len() function. The data is written to the file using the Put statement. (You should read a random access file with the Get statement.) An integer variable indicating the record number (recNum) must also be included with the custom variable in the Put statement so VBA knows where to insert the value within the file. The record number (indicated by the variable recNum in the CreateRanAccessFile() procedure) must begin with the value 1.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment