Working with Random Access Files

When a file contains structured data, open the file in the Random mode. A file opened for random access allows you to:

■ Read/write data at the same time

■ Quickly access a particular record

In random access files, all records are of equal length, and each record has the same number of fixed-size fields. The length of a record or field must be determined prior to writing data to the file.

If the length of a string that is being written to a field is less than the specified size of the field, Visual Basic automatically enters spaces at the end of the string to fill in the entire size of the field. If the text being written is longer than the size of the field, the characters that don't fit will not be written.

To find out how to work with random access files, you will now create a small database for use in a foreign language study. This database will contain records made up of two fields to store an English term and its foreign language equivalent.

Tip 8-8: What is a Random Access File?

A random access file is one in which data is stored in records that can be accessed in a random order. This means that any record of a random access file can be read without having to read every record preceding it.

Creating a User-Defined Data Type

In addition to the built-in data types introduced in Chapter 3 (see Table 3-1), Visual Basic allows you to define a non-standard data type using a Type...End Type statement placed at the top of the module. This non-standard data type is often referred to as a user-defined data type. The user-defined data type can contain items of various data types (String, Integer, Date, and so on). When you work with files opened for random access, you often create a user-defined variable because such a variable provides you with easy access to the individual fields of a record.

1. Insert a new module into the current project and rename it RandomFiles.

2. Enter at the top of the module, just below the Option Explicit statement, the following type definition:

Option Explicit ' define a user-defined type Type Dictionary en As String * 16 ' English word up to 16 characters sp As String * 20 ' Spanish word up to 20 characters End Type

The user-defined type called Dictionary contains two items declared as String with the specified size. The en item can accept up to 16 characters. The size of the second item (sp) cannot exceed 20 characters. If you add up the lengths of both of these items, you will get the following record length—36 (16+20).

Do not enter the Option Explicit statement again if it already appears in your module.

3. Enter the EnglishToSpanish procedure shown below:

Sub EnglishToSpanish() Dim d As Dictionary Dim RecNr As Long Dim choice As String Dim totalRec As Long

'open the file for random access

Open "Translate.txt" For Random As #1 Len = Len(d)

' get the English word choice = InputBox("Enter an English word", "ENGLISH") d.en = choice

' exit the loop if cancelled If choice = "" Then Exit Do choice = InputBox("Enter the Spanish equivalent for " _

& d.en, "SPANISH EQUIVALENT " & d.en) If choice = "" Then Exit Do d.sp = choice

' write to the record Put #1, RecNr, d ' increase record counter recNr = recNr + 1

Loop Until choice = ask for words until Cancel totalRec = LOF(1) / Len(d)

MsgBox "This file contains " & totalRec & " record(s)." ' close the file Close #1

End Sub

The EnglishToSpanish procedure begins with the declaration of four variables. The variable d is declared as a user-defined type called Dictionary. This type was declared earlier with the Type statement (see step 2). After the initial value is assigned to the variable RecNr, Visual Basic opens the Translate.txt file for random access as file number 1. The Len(d) instruction tells Visual Basic that the size of each record is 36 characters. (The variable d contains two elements; sp is 20 characters, and en is 16 characters. Consequently, the total size of a record is 36.) Next Visual Basic executes the statements inside the Do...Until loop until you cancel. The first statement in the loop prompts you to enter an English word and assigns it to the variable choice. The value of this item is then passed to the first element of the user-defined variable d (d.en).

As soon as you stop entering data, Visual Basic exits the Do loop and executes the final statements in the procedure that calculates and displays the total number of records in the file. The last statement closes the file. If you enter an English word and click OK, the next dialog box will prompt you to supply a foreign language equivalent.

Of course, if you decide to quit now, Visual Basic will exit the loop and continue with the remaining statements. If everything goes fine and you enter the foreign language equivalent, Visual Basic will assign it to the variable choice and then pass it to the second element of the user-defined variable d (d.sp). Next, Visual Basic will write the entire record to the file using the following statement:

After writing the first record, Visual Basic will increase the record counter by one and repeat the statements inside the loop. The EnglishToSpanish procedure allows you to enter any number of records into your dictionary. When you quit supplying the words, the procedure uses the LOF and Len functions to calculate the total number of records in the file. After displaying the message, Visual Basic closes the text file (Translate.txt).

Creating a random access file is only the beginning. Next, the VocabularyDrill procedure illustrates how to work with records in a file opened for random access. Here you will learn statements that will allow you to quickly find the appropriate data in your file.

Tip 8-9: Understanding the Type Statement

The Type command allows you to create a custom grouping of mixed variable types, called a "user-defined data type." The Type statement is generally used with random access files to store pieces of information as fields within records of a fixed size. Instead of declaring a separate variable for each field, cluster the fields used with a random access file into a user-defined variable using the Type statement. For example, define a record containing three fields in the following way:

Type MyRecord country As String * 20

city As String * 14 rank As Integer End Type

Once the general type is defined, you must give a name to the particular variable that will be of that type:

Dim myInfo As MyRecord

Access the interior variables (country, city, rank) by using the variable name separated by a period (.) from the name of the interior variable. For example, to specify the city, enter:

MyInfo.city = "Warsaw"

4. Below the EnglishToSpanish procedure, enter the VocabularyDrill procedure shown below. The explanation of this code follows.

Sub VocabularyDrill() Dim d As Dictionary Dim totalRec As Long Dim recNr As Long Dim randomNr As Long Dim question As String Dim answer As String

' open a random access file

Open "Translate.txt" For Random As #1 Len = Len(d) ' print the total number of bytes in this file Debug.Print "There are " & LOF(1) & " bytes in this file." ' find out and print out the total number of records recNr = LOF(1) / Len(d)

Debug.Print "Total number of records: " & recNr Do

' get a random record number randomNr = Int(recNr * Rnd) + 1 Debug.Print randomNr ' find the random record Seek #1, randomNr ' read the record Get #1, randomNr, d

Debug.Print Trim(d.en); " "; Trim(d.sp) ' assign answer to a variable answer = InputBox("What's the Spanish equivalent?", d.en) ' finish if cancelled

If answer = "" Then Close #1: Exit Sub Debug.Print answer

' check if the answer is correct If answer = Trim(d.sp) Then MsgBox "Congratulations!"

Else

MsgBox "Invalid Answer!!!" End If

' keep on asking questions, until Cancel is pressed Loop While answer <> "" ' close file Close #1

End Sub

After declaring variables, the VocabularyDrill procedure opens a file for random access and tells Visual Basic the length of each record: Len = Len(d). Next, two statements print in the Immediate window the total number of bytes and records in the open file. The number of bytes is returned by the LOF(1) statement.

The number of records is computed by dividing the entire file (LOF) by the length of one record—Len(d). Next, Visual Basic executes the statements inside the loop until Esc is pressed or Cancel is clicked. The first statement in the loop assigns the result of the Rnd function to the variable randomNr. The next statement writes this number to the Immediate window. The instruction Seek #1, randomNr moves the cursor in the open file to the record number specified by the variable randomNr.

The next instruction reads the contents of the found record. To read the data in a file opened for random access, you must use the Get statement. The instruction:

Get #1, randomNr, d tells Visual Basic the record number (randomNr) to read and the variable (d) into which data is being read. The first record in a random access file is at position 1, the second record at position 2, and so on. Omitting a record number causes Visual Basic to read the next record.

The values of both elements of the user-defined type dictionary are then written to the Immediate window. The Trim(d.en) and Trim(d.sp) functions print the values of the record being read without the leading and trailing spaces that the user may have entered.

Next, Visual Basic displays an input box with a prompt to supply the foreign language equivalent of the shown word. The word is assigned to the variable answer. If you press Esc instead of clicking OK, Visual Basic closes the file and ends the procedure. Otherwise, Visual Basic prints your answer to the Immediate window and notifies you whether or not your answer is correct. You can press Esc or click the Cancel button in the dialog box whenever you want to quit the vocabulary drill.

Translate.db - Notepad

■ -jnflxO

File Edit Format Help

mother madre

father

padre

M

Figure 8-3: The contents of a random access file opened in Notepad

Figure 8-3: The contents of a random access file opened in Notepad

Text import Wizard - Step 1 of 3

Ttie Text Wizard has determined that your data is Fixed Width. ]f this is correct, chooss Next, or choose the data type that best describes your dataOriginal data tyoe

Choose the file type that best describes your data: C delimited - Characters surit as commas or tabs separate each fietd, f* [FjrtcfjijiiShj ■ Fields ate aligried in columns Mh spaces between each field.

5tart import attowr [l ^ Fila tfiçin! | 437 : OEM United Sates Preview of file C:\JK_&OOKS_AlL\Excel_EN_Z(ra2\ChaptersMew\Che(jt.. .\Translate.txt.

1

mother

madre

father

padre

3

JL

-1

4

l

Figure 8-4: The contents of a random access file on attempt to open it with Microsoft Excel. Notice that Excel correctly recognizes the original data type—the data in a random access file is fixed width.

If you decide to continue and click OK, a new random number will be generated, and the program will retrieve the English word and ask you for the Spanish equivalent.

You can modify the VocabularyDrill procedure so that every incorrectly translated word is written to a worksheet. Also, you may want to write all the records from the Translate.txt file to a worksheet so that you always know the contents of your dictionary. You will find both of these procedures on the companion CD-ROM.

Tip 8-10: Advantages and Disadvantages of Random Access Files

Unlike sequential files, data stored in random access files can be accessed very quickly. Also, these files don't need to be closed down between placing information into them and reading from them. Random access files don't need to be read or filled in order. Random access files also have some disadvantages. For example, they often store the data inefficiently. Because they have fixed-length fields and records, the same number of bytes is used regardless of the number of characters being stored. So if some fields are left blank or contain strings shorter than the declared field size, you may waste a lot of space.

+1 0

Responses

  • Aston
    How can i find out what a random access file contains uysing vba?
    7 years ago

Post a comment