Navigation:  Bronze Collection > Jan-1998 > Access the PalmPilot Using Pendragon Forms

Ivan Phillips          Bronze Collection   

 
One of the premier development tools for the hot new Personal Digital Assistant is Pendragon's Access-based development system. Here's a look at what you can do with this tool from the system's developer.

 

In August 2011, HP decided that the latest version of the Palm operating system, web os, would be phased out

 
Although the PalmPilot won't run Microsoft Access, you needn't feel left out. Users do want to collect their data on a pocket-sized device, but they want to manage and analyze it with desktop tools like Access and Excel. In June of 1996, Pendragon was inspired to create a mobile data collection tool for the PalmPilot that would interface with desktop databases. Pendragon chose Microsoft Access as the desktop component for our product, which was later named Pendragon Forms. Pendragon Forms was designed with two goals in mind: to be used by non-programmers, and to be extended by developers and power users.
 
In this article, I'll demonstrate a few of the capabilities of Pendragon Forms for PalmPilot by creating a helpdesk workorder entry system. In this simple example, trouble tickets are entered on the PC and dispatched to handheld units according to the PalmPilot user name. The tickets will remain on the PalmPilot until the mobile user checks a completion box on the form (see the sidebar, "Keeping It Hot: The PalmPilot, Your PC, and Access," for a description of how a PC interacts with a PalmPilot).
 
First, I'll create the data collection form and set the synchronization rules with the built-in functionality of Pendragon Forms. After the basic foundation is in place, I'll show you how to extend these capabilities with a little VBA code. This example illustrates just one approach to customizing the basic system provided by Pendragon Forms.
 
Pendragon Forms
Pendragon Forms consists of three components: a runtime program for the PalmPilot, a conduit DLL, and a desktop database and designer called the Pendragon Forms Manager. The Pendragon Forms Manager is an Access database that stores both the form design and the user form data.
 
The Pendragon Forms conduit reads and writes ASCII files in a data transfer directory on your PC. The Pendragon Forms Manager is really a front end to these ASCII files.

Figure 1
 
The first step in creating a form is to design the interface of the form. This can be done very easily, even by end-users, because of the form designer's wizard-like interface. Figure 1 shows the form designer in action. After entering the form name, the user simply enters the field names and selects the field types (there are 18 different field types to choose from). The field types are listed in Table 1, along with the corresponding Access field types that are used to hold the data.
 
Table 1. Pendragon Forms field types.

 
 
The form designer displays a preview of the form as it will appear in Field View on the PalmPilot runtime. Forms can also be displayed in Record View, which shows 11 fields on a screen.
 
The design of the work order form is shown in Table 2. Here, I created fields for the user name, location, and description of the problem. I also created fields to be entered by the mobile user, including fields to record the resolution of the problem, the start and completion times, and a completion check box.
 
Table 2. The design of the Help Desk Work Order form.

Each form design has data persistence properties that determine what happens to records on the PalmPilot after a HotSync. These properties are set via the Form Properties dialog box, shown in Figure 2. The default behavior is to remove records from the PalmPilot. This is ideal for applications such as expense reports, surveys, and inspections. In this case, records must be kept on the PalmPilot until the completion check box is marked by the mobile user. This is achieved by checking the "Keep incomplete records on PalmPilot" box in the form properties.

Figure2
 
The next step is to freeze the form design. Freezing a form design serves two purposes. The first is to create a table in the database that will hold collected data. As you can see in Table 3, Pendragon Forms adds four control fields to the table layout. These fields are used to identify records across multiple PalmPilots. The TimeStamp field is set to the date and time the record was created on the handheld. The UnitId field can be configured to identify a particular PalmPilot but is usually left as its default value of zero. The TimeStamp and UnitId form the primary key for the table in the PalmPilot's datastore. Each PalmPilot has a user name, and this is recorded in the UserName field.
 
Table 3. The resulting table created when the form is frozen.

 
Choosing to distribute the form design schedules the transfer of the design to the PalmPilot during the next HotSync. On the PalmPilot, the form will look like the ones in Figure 3, Figure 4, and Figure 5.

Figure 3 Figure 4 Figure 5
 
Out of the box, Pendragon Forms provides some basic data management facilities. It's possible to edit data in the Access table and then transfer it to the PalmPilot via the Send To PalmPilot dialog box pictured in Figure 6. This simple dialog box allows data to be sent to the PalmPilot either by user name or by specifying a SQL WHERE clause. In the case of this Work Orders system, some of this built-in functionality will need to be enhanced and automated.

Figure 6
 
Enhancing the built-in functionality
An effective dispatching system must have an easy way for a dispatcher to create a new work order. The system also needs a way to automatically distribute work orders to the appropriate user, and track the status of work orders.
 
An Access form for entering new records into the table can be easily created by Pendragon Forms. This form will be the creation point for new work orders. There are two special considerations here. The first is that the uniqueness of the UnitId-TimeStamp combination be maintained. One approach is to use the UnitID field as a Work Order ID number. It's straightforward to create a unique index on the UnitId field and write a function to provide the next unique Work Order ID number. This function, and its use in the BeforeInsert event, can be seen in Listing 1.
 

 
 
The Work Order Entry form also includes a combo box to select a Technician name. The names in the combo box are generated from the UserName field values in the table itself. This input will determine which user will receive the work order on his or her PalmPilot.
 
Now, it's time to tackle the more challenging part. How do the right work orders actually get onto the appropriate PalmPilot? The nicest solution would be to generate each user's list of work orders at the time they HotSync. This can be done by using the Pendragon Forms custom control to intercept the HotSync. The Pendragon Forms Manager uses this same custom control to trap the end of the HotSync process and read data from temporary ASCII files into the appropriate tables. In this case, the HotSyncStart event of the control can be used to identify the user and write an ASCII file containing the relevant work orders for the user's PalmPilot. I hosted the ActiveX control in my Access form.
 
Included with the source code for the Pendragon Forms Manager is a procedure called WriteFormData
that passes selected data in a string, formatted so the conduit can transform it into PalmPilot data records. With carefully chosen parameters, this function will serve to create the ASCII file that will be used during the HotSync. The procedure accepts the form ID number, a file number, a user name, and a SQL WHERE clause:
 

 
 
The HotSync manager retrieves the user name associated with the PalmPilot currently being HotSynced, and this information is placed in the conduit in the file RECENT.DAT in the data transfer directory. RECENT.DAT is updated immediately, before the HotSyncStart event is fired. Here's a sample RECENT.DAT file:
 

 
 
My code for the HotSyncStart event reads the user name from the file and then calls WriteFormData to prepare outbound data for that user (see Listing 2).
 

 
 
This work order example is, as it stands, rather simplistic. A full-fledged application would track which records had been sent to the PalmPilot, and provide for contingencies such as restoring data to a PalmPilot that lost persistent storage. However, as you can see, very simple data collection applications can be built in minutes, while multi-user applications can be built in a day.
 
The tip of the iceberg
Of course, there are hundreds of possible applications for this technology. We've seen customers use Pendragon Forms for everything from tracking lobster populations to inventory control. The software's support for an external bar code reader and for digital ink signatures extends the power of the PalmPilot/Access combination even further.
 
Given the ease of development, the ease of use, and the low cost of the PalmPilot, I think that we'll see many PalmPilot-enabled Access applications in the next few months.
 
Download PDRAGON.ZIP
 
Ivan Phillips is the president of Pendragon Software Corporation.
 
Sidebar: Keeping It Hot: The PalmPilot, Your PC, and Access
 
The PalmPilot communicates with the PC via a HotSync. A HotSync is a serial conversation between the PC and the handheld, during which each application on the PalmPilot has the opportunity to share its data with the PC. The HotSync Manager on the PC controls the HotSync process by listening for the HotSync request from the PalmPilot, and then executing conduit DLLs to perform data transfers to and from the PalmPilot. The system is extensible so that custom applications on the PalmPilot can have their own custom conduits to transfer their data to the appropriate PC or server data format. For example, a spreadsheet application on the PalmPilot would use a conduit to read and write Excel or 1-2-3 files on the PC, and read and write corresponding data records on the PalmPilot.
 
The HotSync architecture results in a one-button synchronization process. The user drops the PalmPilot in its cradle and presses a button to synchronize every application. This ease of use, coupled with the PalmPilot organizer's speed and small size, make it very appealing as a data collection tool. Developing a PalmPilot application from scratch is a daunting task if you're using C/C++. With Pendragon Forms, the task is rather easy.
 
Pendragon Forms allows you to design a data collection form for the PalmPilot without any programming. When you freeze the form design, Pendragon Forms creates an Access database table corresponding to the fields on the form. The form design can then be sent, via HotSync, to the PalmPilot, and data collection can begin. Subsequently, each HotSync will transfer collected data to the PC and provide events for an Access application to use to participate in the process.