Random Access Files on the Pocket PC
Don’t forget Random Access files!
You may think that ADOCE is the only way to create
useful data tables, but not so. Before databases even existed for me, I was
creating data tables in Basic using Random Access files. They are generally
faster and fairly easy to create, but a little harder to work with because
more work is needed to sort and search for things. Yet, I have found them a
lot faster to work with on small Palm Size PC’s and Pocket-PC's than the
ADOCE tables that can be created.
For instance, I have a Medical Equipment file that
contains about 9820 records of 115 characters in a 1.2 meg file, yet I can
find any device in this table in less than a second. The longest time taken
is loading a grid with the information, and that takes less than a second.
And with random access files, you can add records, sort in different ways,
delete records, and do all the other database things you need to do. Not
always easily, but working with ADOCE can be a pain as well.
The other reason I'm using random access files is that
the original data tables don't have to be converted by the device from
Access type files required on the device. A text file going to the device
does not have to be converted so the transfer is fairly fast. Since I have
to have updated files on the device on a weekly or sometimes daily basis,
the conversion of a table the size I use can take a really long time. Look
at these statistics:
FoxPro .dbf --> Access .mdb -->
device .cdb, 75 min for a 1 meg file, et vice versa, definitely too long a
BioMstr.txt for device (~30 sec to create), 1,176,565
WOFile.txt for Device (~3 min to
create), 2273 Work Order records (just the latest work orders), 488,695
bytes with 100 character notes. For a complete history, this represents
20,972 records, 7,843,902 bytes on the device with 255 character notes. This
easily fits on a 32 meg Pocket-PC. We use Compaq iPaqs.
If all files have been updated,
ActiveSync will finish loading these files in less than two minutes. If just
Work Orders Due are updated, ActiveSync will finish in about 1/2 minute.
Need I say more?
So let’s start out with what a random access file is
by looking at how one can be created from a FoxPro data file.
A delimited text file is usually a character file with
each line in the file containing fields separated by some delimiter like a
comma, or a tab character, with a CRLF (Carriage Return/Line Feed) at the
end. A typical set of records might look like this:
As you can see, each field can be a different length,
or even empty as the case may be.
An SDF file is similar, but the fields are of fixed
length. Each record, however, still consists of a single line with a CRLF at
I can create SDF files very easily using FoxPro.
FoxPro data files are where all our equipment data are kept. I first create
a temporary equipment table (BIOTEMP.DBF)
that is filtered to remove non-active devices, is sorted on the
equipment number, which is unique, and set up to only create fields that are
going to be needed on the device. Then I create the SDF file from the temp
table using this program line:
COPY TO BIOMSTR.TXT TYPE SDF FIELDS BIOTEMP.MFG,
BIOTEMP.ID, BIOTEMP.MODEL, BIOTEMP.SN, BIOTEMP.DESCR, BIOTEMP.DATEPM,
I imagine something similar can be done with Access or
other database managers. The result is a file that contains all the records
in a format similar to this:
I put the manufacturer first, then the five digit ID
number, the model number, description, serial number, date tested, date
serviced, and the PM test number. I put the manufacturer first because when
the ID number is put first, the conversion has a tendency to remove the
“0’s” from the beginning of the record. Putting the manufacturer first is
normally not going to be a problem, however, since we are going to pick out
the fields that we need from each record when it’s converted to a random
access file anyway. We’ll show how later.
The major difference between an SDF file and a Random
Access file is a four byte header at the beginning of each record, and there
is no CRLF at the end of each line, it’s just one long string of characters:
When we convert from an SDF file to a random access
file all we have to know is the exact length of each record.
The program for the Work Station
This is my Visual Basic 6 form that creates the file:
Following is the Visual Basic 6 code to create the
master text file from the SDF file, but before we get into the code, let me
explain a couple things I do for my programs: all, or most of, my global
variables begin with ‘g’, local variables begin with an ‘l’, character
variables have a ‘c’, numbers have an ‘n’, arrays have an ‘a’, and I try to
use upper and lower case for the names. I always make two constants in
Basic, ‘t’ for ‘True’ and ‘f’ for ‘False’, this keeps the typing down and
saves some space when programming on the device itself.
At the start of the VB code we explain what we are
'This program creates the BIOMSTR.TXT random access
'file from the E:\BIOMSTRC.TXT SDF file.
Now we need a few variables set up:
Then we start the Form up:
Private Sub Form_Load()
Because I work at home and at work, or if I just want
to test the application, I have some option controls,
OptWork, and OptEmul that
I can set to have the output file put in a number of places.
Private Sub OptTest_Click()
Private Sub OptHome_Click()
Private Sub OptWork_Click()
Private Sub OptEmul_Click()
The default is to the root directory of one of my hard
drives. But it can also go to the emulation directory which is available on
my work NT workstation, or one of
the sync directories. When placed in a sync directory, it
automatically gets put onto the device when synced up.
This next bit of code sets the Path for our output
Private Sub SetOutPutPath()
OptTest.Value = True Then
If OptHome.Value = True Then
OptWork.Value = True Then
OptEmul.Value = True Then
lblMsg.Caption = gcOutFilePath
This is the code behind the 'Create
Master File' button:
Private Sub cmdCreateFile_Click()
Which calls this following routine.
Private Sub CreateMasterFile()
lblMsg.Caption = "Creating Master file..."
lcIDRec, lcThisLen, lcIDRec2, lcIDRec3
We open the input SDF file using the standard Basic
Open FileInName For Input As #1, so we have to have an
actual file name, and a file number that we can get from the
FreeFile function which gets the next available file handle.
If Err <> 0 Then
Next we open the output file, which will become the
random access file using the standard Basic random access syntax:
For Random Access Write Lock Write As #2 Len =
<> 0 Then
If we didn’t get any errors opening the files, we get
to this point. Here we have a loop that pulls in each line of the input file
using this statement:
Line Input #filenumber,
and displays the number on the form so we know that
something is going on:
Do While Not EOF(lnFileNum1)
Then we save each random access record using this
'Save the record as random record
lnCnt = lnCnt + 1
We save the number of records we’ve created and close
MstrCount = lnCnt
lblMsg.Caption = "Done!"
If you need to put in a little two second timeout,
this is the little routine I use
Private Sub Wait2Secs()
You can examine the output file using Notepad or Wordpad, but you should note that the header will display as some unknown ASCII characters, there won’t be any CRLF at the end because each record is fixed in length, and the file will be just one long string.
My form also has a few extra controls on it so that I
can create an index, reindex, and view the file, but we won't go into that
in this article. Creating an index is handy, and adding a binary search
makes finding random access records real fast. Perhaps we can go into that
in another article.
Now that we have created the file, and it resides on
the device, what’s next? We will now actually read in the records with a
program that resides on the device. Next section.
The program for the Pocket-PC
When the Random Access file was created above, it would have normally been put into the sync directory, and if so, will eventually end up on the device. If you have Windows NT or Windows 2000, you can also put it into the emulation directory for much faster testing. However, we really want the files on the Palm Size PC or Pocket-PC device when we are doing the real work of entering work orders.
When the Random Access file is on the device we have
to open it, and then store the information from the random access records,
into a grid.
The startup form for my application on the emulator
looks like this:
The list of devices that comes from the Random Access file are placed in a
grid and looks like this:
These are views using the emulator, the forms are very
similar when actually on the device.
And the eMbedded Visual Basic 3.0 code for the device
We start with some constants and global variables,
then set the directory where the file ended up on the device.
Const t = True
When the form loads we open
the files, and if there was an error we exit, else we display the
application title and version number, and put our cursor into the field we
want it to be, in this case the equipment ID field.
Private Sub Form_Load()
gcFormCap = "Opening files, standby..."
form to see messages
glError = t Then
This routine normally opens a lot of files, one after
the other, but for this article we are only looking at the one we created
gcFormCap = "Opening Master file, standby..."
glError = t Then
The above routine calls the following routine.
Private Sub OpenMaster()
lcAppPath, lcFilePath, lcFileName
path for files
the equipment table
The syntax to open a file on the device is as follows:
File.Open pathname, mode, [access], [lock],
fsModeRandom, fsAccessRead, fsLockRead variables are normally
initialized at the beginning of the program, but we put in their values here
for you to see. So we open the file and check for an error.
If Err.Number <> 0 Then
This next code determines how many records we have in
the file with a little math, and then get the record length the same way.
This I can use for checking the length of the record when testing.
gnNumIDRecs = Round((ceIDFile.LOF / RecLen))
Then we put the first few records into a grid on the
grid with data
This following bit of code does two things: if the
Find field has any data it, we are in a search mode where we look for the ID
number. That code is not in this article. What we do set up, however, is
what records we start and end at. Rather than load every single record into
the grid, which slows the whole process down (we can have thousands of
records), we only load enough to show on the screen in the grid. Then, if
you notice on the form, we can use navigate buttons to move through these
records simply by incrementing and decrementing
Private Sub LoadIDGrid()
grid with ID data
This following code first sets the grid up. I won’t
cover every line since they are all in the help files on grids. Then we set
a For/Next loop that gets each record (GetIDRec),
and puts each field into an array, and then into the type of line the grid
Private Sub GetIDs()
the grid with the contents of the Table
'Set # of
row for column headers
OK, get all the records
'Read the record
Notice below that the ID number,
gaIDRec(1), which is in the
second field position, is now displayed in the first position. And also note
that arrays will start with 0.
'Create the row
The following code is really where we reverse the
process: retrieving the data
from the random access record. We get one record from the random access
file, separate each field out, and put them into an array. You may ask why
not put the fields directly into the grid. Well, you could, but arrays are
good to work with, they are fast, and they're even better when debugging.
These next few lines basically show how my device
record is set up. If you remember from above, what we have is the
manufacture’s name, the device’s ID number, the model, description, serial
number, and some dates and a bit of other information we need. If you
display the record this way, you can easily tell how large each field is and
where it starts; it's a big help during development to look at the record
this way. By the way, you want to use a fixed length font such as
Courier New when doing this.
Don't even bother trying it with something else. Because the data is longer
than this page, some information at the end is truncated off.
This next bit of code gets
the individual record from the file. It will take the single line, like the
last line above, and puts the record into the variable
'Read the record
Err.Number <> 0 Then
We now put that line into a global variable so the
next routine can see it. This code also does some converting of the data.
And finally puts the field data into a global array with the
array with data from string
This code will pull each
field into the array by using the Mid() function. As you can see, this is
why the formatted lines above help with this bit of code; we know where the
field starts and how long it is.
Private Sub LoadIDArray()
array with data from string
gaIDRec(0) = Mid(gcIDRec, 1, 20)
Since we are always working with text files and
character strings, the code is very fast. This is very important in a hand
held device with a slow or slower processor. So if you need this kind of
speed, forget database files in Access and go with Random Access.