April 1999

Create a DB App for Windows CE

by Jose Mojica

Reprinted with permission from Visual Basic Programmer's Journal, Apr 1999, Volume 9, Issue 4, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com.

Windows CE is Microsoft's operating system for handheld devices (H/PCs), palm-sized devices (P/PCs), automobile PCs (AutoPCs), and Internet terminals (WebTV). Windows CE comes with Pocket Office, which comprises smaller versions of the office programs, and Windows CE Services, which detects when the H/PC is connected to a desktop computer and automatically notifies components with the appropriate interfaces so they can synchronize correctly. What you need: Visual Basic 5.0 VBCE 1.0 or later ADOCE Access Microsoft offers three toolkits for developing applications for Windows CE: a Visual C++ toolkit, a J++ toolkit, and a Visual Basic toolkit (VBCE). Microsoft has also released ADOCE, a version of ActiveX Data Objects (ADO) for Windows CE. Using the VBCE toolkit with ADOCE, you can build Windows CE applications that enable remote users to collect data and synchronize it with their Access or ODBC databases. The goal of VBCE application development is not to convert an existing application to run on Windows CE, but to create a companion application to a larger desktop app. Think of a VBCE app as a helper app, rather than a replacement for a traditional Windows program. In this article, you'll get an overview of Windows CE development using VBCE and ADOCE. Then you'll create a small inventory-tracking companion program and write a desktop program that imports the data into an Access database. To try this example, you need VBCE 1.0 or later. You don't need a handheld device; the toolkit provides an emulation program where you can try the example. The VBCE Toolkit The VBCE toolkit, a VB add-in, disables or adds menu options to render the IDE specific to Windows CE development. The Windows CE Platform SDK, which includes a Windows CE emulator, comes with the toolkit. The emulator requires Windows NT because Windows CE uses Unicode strings not supported by Windows 98. The toolkit can only produce programs that run on H/PCs—as opposed to the Visual C++ toolkit, which can produce programs that run on H/PCs, P/PCs, and AutoPCs. The VBCE toolkit does not produce executables; instead, it creates Pocket Visual Basic (PVB) files, a compacted Unicode representation of the source. The toolkit includes an executable called PVB.exe that, along with supporting DLLs, interprets the PVB file at run time. The executable and supporting DLLs are available for each of the different processors in H/PCs such as the Hitachi Sb processor and the MIPS 3900 and 4100 series (at press time). There is also a PVB.exe file that runs on the emulator. You can let the user think the PVB file is an executable by associating the PVB extension of the pseudo- compiled source files with the PVB.exe program. Although the PVB files are not fully compiled, this architecture has advantages. The first: PVB files are extremely small—about 15K for a large VBCE program. This is crucial in Windows CE programming because of the H/PC's limited memory. The newer H/PCs only have about 16 MB of memory, and no storage devices such as hard drives. Instead, the device uses half of its RAM for storage (remember RAM drives?) and half of its memory for running applications. The PVB architecture's second advantage is that PVB files are processor independent. Windows CE programs need to be compiled for each of the available processors, but only the PVB.exe file needs to be processor dependent because of the VBCE architecture. The same PVB file will run in each processor once PVB.exe is installed on the different machines. The third advantage: A number of the vendors are putting the PVB.exe file and accompanying DLLs into their ROM chips. This means VBCE developers can run their applications simply by copying the PVB file to the device and executing it. The VBCE toolkit also includes tools that ease Windows CE development, such as Remote Heap Walker, Remote Process Viewer, Remote Registry Editor, Remote Spy, and Remote Zoom. These tools are called Remote because they run on the desktop machine but perform their task on the handheld device through a serial connection. For example, Remote Spy reports the Windows messages taking place on a window running on the handheld device. In addition to the remote development tools, you can also take advantage of the Application Install Wizard (a Setup Wizard that produces setup programs for Windows CE), Control Manager (which helps the developer transfer and register ActiveX controls in the devices), and a new set of help files. Differences Between VBCE and VB Because the goal of VBCE development differs from the goal of traditional VB development, you should expect differences between the two environments. I've already explained how the VBCE compiler produces PVB files instead of EXE files; you'll see other differences in the compilers as well. For example, the VBCE compiler doesn't syntax-check your code as thoroughly. In my tests, I entered "blah, blah, blah" for a statement and the compiler compiled my program without complaints. But when the interpreter hits that line of code, it issues a runtime error. The compiler does not warn you if a VB command you are using is not supported in VBCE. Another difference is that you can't use the standard VB debugger when creating VBCE apps; you use the toolkit's external debugger instead. When you run a VBCE program from the VB IDE (with debug information), VBCE runs the external debugger and loads your source code. It also launches the program on the device or emulator, depending on a project setting (more on that later). You can't place breakpoints while in the standard IDE, but you can while in the external debugger. The program runs on the remote device until it comes to the line with a breakpoint. Then execution halts, and the external debugger shows the line of code with the breakpoint. You can't modify code while in the external debugger and you can't enter commands in the Immediate window, but you can examine variable values and single-step through commands. With VBCE development, you'll also face limitations that arise from one of three sources: VB components, ActiveX controls, and language syntax (see Table 1). You cannot use classes, UserControls, or UserDocuments, and you can't have more than one module per application. Instead of making API calls through Declares, you must wrap them into ActiveX controls. Current third-party ActiveX controls don't work with Windows CE. Vendors need to provide a special version of each control compiled for Windows CE, a version for each processor that supports Windows CE, a version that runs on the emulator, and a desktop version. The desktop version does not need to do any real work; it is available so you can drop it onto a form at design time. Windows CE supports ATL and MFC controls. Just as some devices include the VBCE runtimes in ROM, some also include the MFC runtimes. I have not seen a machine that includes atlce.dll, however. Because VBCE's language is VBScript, as opposed to VB or VBA, all variables must be Variants. Also, only late binding is supported—this means ActiveX components for Windows CE need to support the IDispatch interface. Aside from the standard VBCE components, your program needs database support. Because VB's data control doesn't work in VBCE (see Table 1), Microsoft has provided a version of ADO that runs on Windows CE. Just like VBCE, ADOCE is a scaled-down version of its counterpart. Let's examine some of the differences between ADOCE and ADO. Differences Between ADOCE and ADO I'll explain database support in Windows CE before I explain ADO. Windows CE has a built-in database mechanism. You can create tables; add, remove, and edit records; sort records; and perform seeks through API calls. Each table is part of the system's object store and is assigned a unique ID number. You can reference the tables either by ID or by name. The tables support up to four indexes. You can't run queries or join tables. One peculiar difference between VBCE tables and other database tables: Each record in a VBCE table can have a different number of fields. Fields can be integer, unsigned integer, long, unsigned long, string, filetime, and BLOB types. Fields, like tables, have a unique ID number based on a programmer-assigned number and the field type, but they do not have names. You can search fields from the beginning or the end, and you can search for an exact value or a value less than or greater than the value. A great advantage of having built-in support for database files is that most applications use the same format for storing their data. This means you can read the contacts database generated by Pocket Outlook, for example. Even though Windows CE offers this database support natively, you can't use it through VBCE directly. I mentioned earlier that VBCE doesn't let you make API calls. (This feature will be available in the next release of the toolkit.) Making API calls involves the use of structures, and structures are not supported in VBCE. The only way to reach these APIs is through an ActiveX control. ADOCE is a wrapper for these database APIs. You can create recordset objects, run queries, and reference fields by name with ADOCE, which adds this functionality by creating system tables that store field names for each field ID. The only limitation with this approach: You can't create recordsets from databases that were not created with ADOCE. ADOCE has a lot of limitations over standard ADO (see Table 2). (With the code for this article, I have included an ATL control for Windows CE that enables you to access any database in the system. Download the control, called ByteSized DB, here.) ByteInventory Tracker Let's build an application—we'll call it ByteInventory—for Windows CE using VBCE and ADOCE. This companion app will allow remote maintenance workers to keep track of inventory on their H/PCs and import that data into the company's main desktop database. (We'll create a sample "main" database when we write the code to transfer the data.) This inventory tracking system—more effective than relying on workers to update the main database every day—will help the company track the exact count of materials used for a given job and purchase parts as necessary.   Figure 1 The Windows CE Development Cycle. Click here One last thing you must understand before jumping into the code: the software cycle for VBCE applications (see Figure 1). The first step is to use the toolkit to write the code in the desktop machine. Then try the program in the emulator, and finally run the program in the remote device. A word of caution: A program is not done until it runs on the handheld device—you cannot rely on the emulator 100 percent. Because the emulator runs in the desktop machine, it has access to all of the desktop machine's resources such as memory, speed, colors, and fonts. Start Visual Basic 5.0. You have a new project type if you installed the VBCE toolkit: Windows CE Application. Double-clicking on this icon brings up the Project Properties dialog (see Figure 2).   Figure 2 Project Properties Dialog. Click here Note a few differences between this dialog and the standard Project Properties dialog. The second and third fields from the top left ask for the Form Width and Form Height. VBCE uses these dimensions to resize forms each time you request a new one. Most H/PCs feature a resolution of 480x240 pixels; some have half VGA resolutions of 640x240, such as the LG Electronics Phenom Ultra. A new class of devices known as H/PC Pro devices look like laptops and have a full VGA screen. Palm-sized devices have 320x240 screen resolutions. You can change a form's width and height through code at any time—I'll show you how to resize each control inside the form proportionally. However, the user will not be able to resize the form himself or herself; Windows CE forms are not resizable because of an operating system limitation. The Local Path field asks for the location where VBCE will store the output file when it compiles the program for debugging purposes. The Remote Path is the path in the emulator or H/PC where the program will be copied after it is compiled. For example, you can specify the Storage Card directory created when you inserted the Flash Memory card, a device used by most H/PCs for storing files. The only other field of interest is the Run On Target field. This option frame lets you select whether you want to run the program on the emulator or on the H/PC. Enter ByteInventory for the project name, leave the size fields at their default values, change the local and remote path fields to match the new project name, and make sure the emulator is selected. Then click on OK.   Figure 3 Track Your Inventory. Click here VBCE creates a new project with a blank form sized to the dimensions specified in the Project Properties dialog. Rename the form frmInv, and change the caption to ByteSized Inventory (see Figure 3). You'll use Microsoft's CE grid control to display the inventory items. The grid control is not shipped with the SDK, but it's available through Microsoft's ActiveX Control for Windows CE Pack, which you can download for free from its Web site at www.microsoft.com/windowsce/developer. After installing the controls, you can use the Windows CE Control Manager (under the Windows CE menu) to transfer and register the control in the emulator and in the handheld device. Add the grid control to your form, then two command buttons: one to subtract inventory, "Use" (cmdUse); and one to add inventory, "Add" (cmdAdd). Take care of aesthetics before you get into database programming. Because there are two possible screen sizes for H/PCs, make sure the form is sized to fit the screen and the controls inside it are resized proportionally. To do that, create a generic function called ResizeForm and put it in a module. Add a module to your project, name it modGeneral, and add code (see Listing 1). The code calculates what the form's rate of increase will be after resizing, then resizes the form and changes the size of each control in the form's control collection by the same ratio. It might seem strange that the code loops through the control array until it gets an error, but this is necessary because VBCE does not support the Count property in the control's collection. So, you can't loop for a predefined number of times. Also note that the code's Dim statements don't have a type declaration. This isn't poor programming practice—VBCE doesn't let you specify a data type for variables. All variables must be Variants. Keep in mind that Variants are Variants until they are assigned a value, after which they take on the data type of the value. If a function in an ActiveX control expects a number, your Variant needs to contain a number. The best way to keep track of this in a VBCE program is to name the variables according to the value they are going to hold. You can modify the form's Form_Load event to call the ResizeForm routine once the code is in place: Private Sub Form_Load() Call ResizeForm(frmInv) End Sub Notice that the function call sends frmInv as the parameter instead of Me. As you might guess, Me is also not supported. The inventory form, frmInv, opens the program's database table, truckinv, and populates a grid with the table's records. The trackinv table has three fields: ItemID (String - 15), Description (String - 50), and Quantity (Numeric - Integer). Under our example, the truckinv table can only be created by the desktop application when the driver uses the program for the first time. Add this declaration in the general section of the form: Dim rs rs will hold the ADOCE recordset (again, this variable is of type Variant). Add code to frmInv to create a recordset with all inventory records (see Listing 2). The LoadRecords routine first creates the recordset object by calling the CreateObject method, then checks for errors after each call. VBCE doesn't support On Error GoTo, but instead supports On Error Resume Next and On Error GoTo 0. Next, open the recordset. The second parameter is left blank because there are no connections in ADOCE. The third and fourth parameters indicate you are requesting a read-only Keyset cursor. Populate the grid after the recordset is built (see Listing 3). Place this code in the module modGeneral. The CE grid control operates like the MSFlexGrid control. You can set the number of rows and columns, then go through each cell and set the text. The grid control functions like an array in that it retains its values even while the grid scrolls. Navigating through the recordset in ADOCE is similar to navigating through the recordset in the desktop version. The code loops through the records until EOF is true, and puts the value of each field in the grid's cells. With this code in place, you can now complete your Form_Load event: Private Sub Form_Load() Call ResizeForm(frmInv) Call LoadRecords Call FillGrid(grdInv, rs) End Sub All the program needs to do now is subtract or add inventory items. I have created an UpdateQuantity function whose job is to update a single record in the truckinv table. It creates a recordset object like the code in LoadRecords, except that it opens it in optimistic mode (in ADOCE, this means the object is updatable). This code shows a segment of this routine: sql = "select * From truckinv where ItemID='" & sID & "'" rsUpdate.open sql, ", 1, 3 The complete routine is available here. The routine updates the recordset after it is created: 'Increase/Decrease quantity rsUpdate.Edit rsUpdate.Fields("Quantity") = _ rsUpdate.Fields("Quantity") _ + iVal rsUpdate.Update You can call this routine accordingly in the Use and Add buttons: Private Sub cmdUse_Click() Call UpdateQuantity(-1) End Sub Private Sub cmdAdd_Click() Call UpdateQuantity(1) End Sub Inventory Transfer Program The next step is to write a program that will transfer data to and from the device to the main database. For your "main" database, create a new database in Access and call it inventory.mdb. This database must contain a table with the same name and structure as the table the ByteInventory program will open (truckinv). When you install ADOCE, the setup program installs a DLL called adofiltr.dll with two API functions to do transfers: DEVICETODESKTOP and DESKTOPTODEVICE (see Listing 4). Both calls have the same parameters. The first parameter is the location of the desktop database file, which can be an Access database or an ODBC Connection string. The second parameter is a list of tables that will be updated. The list is period separated—one period to separate table names and two periods to indicate the end of the list. The third parameter, not yet supported, could be used in a later release to specify whether you wish to do synchronization. In the current version, the APIs transfer the entire tables. The fourth parameter indicates whether you wish to overwrite the table if it is already present, or append records to the table. The last parameter is also for a future release; leave it blank. Create a new Standard EXE project. Rename the project's form (Form1) frmTransfer. Add two buttons to the form: one to transfer data to the device, "Start" (cmdStart); and one to transfer data back to the desktop, "Finish" (cmdFinish). Then, declare the API functions in the form and enter the code from Listing 5. If the table is not present in the device the API creates it first. The cmdFinish event transfers the data back to the desktop. You now have a small inventory tracking program for Windows CE and a desktop program to transfer data to and from it. To turn it into a full-fledged program, you might want to transfer data to a temporary table, then use your own program to synchronize from this temporary table to your own table. Jose Mojica is a consultant working at IBM in West Palm Beach, Fla., where he develops controls in ATL for the ViaVoice (speech recognition) SDK team. He is the author of ActiveX Controls with Visual Basic 5.0 (IDG Books Worldwide), coauthor of Programming Internet Controls (Prima Publishing), and the soon to be published Windows CE (IDG Books Worldwide). Reach Jose at Jose_Mojica@yahoo.com.