| ||||||||
|
What are random access files? When I first heard the term random I was perplexed. I thought, "Do they really mean that we have to somehow deal with random data in our programs - or has this file system been developed only for games of chance where just certain programs need to get special data randomly?"
No - that turned out not to be the case. What was actually developed was a way to place and retrieve chunks of data called records rapidly any place in a file. There is considerable overhead performed by Visual Basic positioning and buffering the actual physical disk on which these records are stored to make this all happen with just a few simple commands. Still it can be confusing for those who have become familiar with the commands for sequential file I/O (Input and Output). | ||||||||
| * An indexing system of file positions for data in a sequential file could be devised, and then using the Seek Statement, one could access data in a rapid manner sequentially. However, such a system is relegated to read only except for appending to the end as stated above. | ||||||||
1. Dim recData& 2. Dim fHnd% 3. Dim numRecs& Private Sub Form_Load() 4. fHnd = Freefile 5. numRecs = Len(recData) 6. Open "c:\myDir\myFile.ext" For Random As fHnd Len = numRecs 7. numRecs = Lof(fHnd) \ numRecs End Sub | ||||||||
|
In this simplest of examples just a Long intrinsic variable type is used as the record. It is only four bytes long. In fact, had we specified the Byte Type, each record would be only one byte long.
A fairly standard programming practice is to use the file handle in steps 1 and 4, although in the open statement you could use "As #1". It is wise to adopt this practice for your projects since you will frequently have several random files open at once, and the best way to keep track of them is with file handle variables. Probably the one thing added in the above code that is new to you is the Len = clause in the open statement. This is important for the Visual Basic compiler to know exactly how to partition data in the file buffer so that adjacent records are not overwritten with garbage as you put single records to various file record locations. Since we know that a long is 4 bytes, you could use Len = 4 here. But it is advisable to get used to the Len() function in step 5 to get the length of your variable, because as you will soon see, the I/O variable can become very complex. Note how my practice is to allow numRecs to do double duty initially as the record length for the Open statement, since I rarely use that value again. You may prefer to use a separate variable. In this example I am showing the execution code in the Form_Load event, but of course, you could have it in any procedure. Also, I will almost always declare the three variables at the Form or Module level since they usually need to be visible in several Functions and Subs. | ||||||||
|
If it were possible to see the first three records of data bits on disk of the random access file opened with the above code, they might look something like this. I have stacked the records one on top of another which is how I usually think of them when I write programs. But actually they are one continuous long trail of bits that the disk controller forms into bytes and then the operating system and Visual Basic group them into records as commanded with the open statement.
| ||||||||
|
Note that when you use the Open For Random statement, if the file that you have named does not exist, it will be created automatically. In contrast, if you were to attempt to Open For Input a sequential file that does not exist, a trappable error would be generated.
This is one of the reasons why it is advisable to determine the number of records in the file immediately after you open it. It may also be advisable under circumstances where you do not wish to create a file, to first check for its existance. The Dir$ function is one way of doing so. | ||||||||
In each instance the first parameter is the same, fHnd, the integer file handle that was used for the open statement. Put and Get each have two additional parameters. The first is a Long Type number indicating which record you want. The second is the name of the data variable to accept the Get data (for a Put operation the second is the data you want to place in the file). Visual Basic knows how long that second variable is, so it knows exactly how many bytes to place in or retrieve from the file.
| ||||||||
|
It is important to know that as you perform your Put operations, Visual Basic is keeping it in a file buffer in memory. The actual physical writes to the disk are unpredictable.
As an experiment open a new random access file, and Put records in it. Then without ending the program or closing the file, switch to Windows Explorer and locate that file. What you will probably see is that the file is there but the file size is zero. This is because the record is still in memory in the file buffer. But the instant you close the file (ending the program will automatically close the file), the record length will change in Windows Explorer indicating that the buffer has been flushed to the disk. Closing the file is the one predictable flush you have at your disposal. If record security is crucial (e.g. if you worry about a power outage) or if you have multiusers accessing this file, then it might be a good idea to close the file after each Put, and then immediately reopen it. | ||||||||
|
Now for the slightly complicated business of the UDT - user defined type. In the seven step example above only a simple long variable has been used. In fact, this is used more often than you might expect as an index file of another random access file (see the reference at the bottom of the page).
However, for any data file to be of use to us it must be able to store considerably larger records, and these records need to contain lots of information of different types. This is accomplished by first defining your own user defined type, and secondly declaring a record variable as that type. A simple example is:
One thing that you always have to carefully consider at the design stage is how long you want these lengths to be. They must be long enough to hold the longest value (within reason), but then you have to balance that with how much space is wasted on the disk. In the above example if in one record the CustFirstName is "Rick", then you can see you will have 12 wasted bytes of disk space on your hard drive. Also if records are very large it will effect how fast VB will be able to Get and Put them because the file buffer is a fixed size and more actual physical reads and writes are required as your record size increases. | ||||||||
|
UDT's are interesting to deal with after you have declared them. Since they are made up of several different variables there must be a way to specify which variable in your UDT you want. This is done with the period "." Suppose you just got (with Get) recData from the random access file, then the customer's first name is:
recData.CustFirstName Note: When you declare them, fixed length strings are initialized to the null character, Chr$(0). If you want them to be spaces (so that you do not have null chars in your file) then you would have to do it yourself. An example would be:recData.CustLastName = Space$(18) Tip: When you declare your recData, declare another called recBlank and in your initialization code go through the above process of making all the fixed length strings spaces. Then whenever you need to blank out your recData I/O variable you need only use the line: | ||||||||
Now to combine what has been covered so far using my seven steps:
1. Private Type recType
DeleteCode As Byte
SortFlag As Byte
CustCode As Long
CustFirstName As String * 16
CustLastName As String * 18
End Type
Dim recData As recType
2. Dim fHnd%
3. Dim numRecs&
Private Sub Form_Load()
4. fHnd = Freefile
5. numRecs = Len(recData)
6. Open "c:\myDir\myFile.ext" For Random As fHnd Len = numRecs
7. numRecs = Lof(fHnd) \ numRecs
End Sub
As you see the only difference is that recData is declared as recType instead of as a Long. In step 5, Visual Basic knows the length of the declared type so Len(recData) will return 40, and then in 6 the Open statement will set all internal buffering up to expect records 40 bytes long.
Note that only a single byte is required as a flag to indicate that this record is set for deletion, and that a future sort function should sort on the first name. These are just my examples of flags. You might consider others when you plan a record type. (See how the flags are set with Checkboxes below.) | ||||||||
Now the only thing left is to show examples of a Put and Get:
numRecs = numRecs + 1 recData.DeleteCode = Check1.Value recData.SortFlag = Check2.Value recData.CustCode = 10000 + numRecs recData.CustFirstName = Text1.Text recData.CustLastName = Text2.Text Put fHnd, numRecs, recDataThis shows how to add a new last record to a random access file. Note that we are just generating the Customer Codes based on the record number. Our first customer will be customer number 10001. Assumed are two checkboxes and two textboxes on a Form where the data could be input. Normally there is validation code before you arrive at the Put statement to ensure that the record you are about to put has good data in it. One interesting thing is that you may actually Put a record in position 203 even if there are only 15 records in a file. Visual Basic will expand the file size such that there are 202 records before number 203. I have never found a reason to do this. | ||||||||
Get is the reverse process:
If x < 1 Or x > numRecs Then Exit Sub Get fHnd, x, recData Check1.Value = recData.DeleteCode Check2.Value = recData.SortFlag Label1.Caption = recData.CustCode Text1.Text= recData.CustFirstName Text2.Text = recData.CustLastNameYou must supply the x (a Long), of course, as the record number you want. Another reason to have numRecs declared at the form or module level is to check that x is in the range between 1 and numRecs before you attempt a Get. | ||||||||
|
One peculiarity is that you can not shorten random access files. If you try to delete a record by moving all the records one position lower (with a Get of the one above and then a Put), and then blanking out the data in the top record, you will be annoyed when you find out there is no way to tell VB or the system to change the file size so that top record does not show up anywhere.
The answer to the problem is to Open For Random a temp file of the same record length, Get the good records from your good file consecutively, Put them in the temp file consecutively, Close both files, Kill the good file, and finally Name tempfile as goodfile. (A new file has to be created.) Here is an example:
| ||||||||
|
The Mini-Database demo project could be described as a "poor man's database" since it simply writes and retrieves your input data as records to and from a random access file and then establishes other random access files to index the records. If you wish, you may download the Mini-Database Project (12K) to see the complete commented source code and random access in operation.
Another tutorial is: Indexing Random Access Files in Visual Basic. | ||||||||