ActiveX Data Objects for Windows CE

ADO on a Pocket PC? You better believe it--ActiveX Data Objects Compact Edition provides the framework for data access on a Pocket PC. We show you how to use it in your own projects.

The idea of putting a database on a device that lacks a disk drive seems like a totally alien concept. After all, just how useful can a platform that fits into a shirt pocket be? But Microsoft states that ADO (ActiveX Data Objects) is a basic framework for general-purpose data access in all Microsoft Windows operating systems. So I decided to see just what ADOCE (ActiveX Data Objects Compact Edition) could and couldn't do.

Say adios to…
ADOCE is by no means a full implementation of ADO, hence the words “compact edition” in the name. A full implementation of ADO would be a waste of limited storage and contain too many unnecessary features. This means you'll occasionally have to find a way around some missing features you're used to in the full ADO, but it also means there's plenty of memory left for applications and databases.

For me, the three most notable omissions in ADOCE are the command object, the method, and the total lack of an update command when using direct SQL. While none of these issues are showstoppers, their absence does sometimesrequire a few changes in the way I usually code my applications. Take for example this snippet of SQL to update a table:
UPDATE product_tbl
SET    manufacturer = "Just Born",
       product = "Hot Tamales"
WHERE  upc = "7097040027"

With the lack of the SQL update command in ADOCE, what was a one-step process under Windows 2000 is now a multistep process under Windows CE:
Call objRS.Open("product_tbl",objConnection)
Call objRS.Find("upc = '7097040027'")
objRS("upc") = "7097040027"
objRS("manufacturer") = "Just Born"
objRS("product") = "Hot Tamales"

While not a major change in coding style, it clearly shows that ADOCE is a subset of ADO for the Windows CE platform. This isn't necessarily a bad thing; it just means that code may not be 100 percent compatible with other Windows platforms.

Data manipulation language limitations
The remaining objects, methods, and properties cover just about everything you may need to accomplish. Listing A spells out all the ADOCE-supported objects and their associated methods and properties. These work in much the same manner as they do under traditional ADO, with some limitations.

In addition to the lack of the SQL update command, the remaining commands have been scaled back somewhat. What's left is a combination of small size and functionality, because there's usually a way to accomplish the majority of tasks. Nowhere is the trade-off between size and function more apparent than with the select statement.

Under ADOCE, there are five variations of the select statement: simple, join, order by, projection, and restricted. The simple SQL select is the base upon which all queries are written:
SELECT * FROM order_tbl

Upon this foundation, you have the "restricted", "projection" and "order by" forms of the SQL select statement:

Unfortunately, there are several surprises buried in ADOCE's select syntax, as detailed in Table A.
Table A
Command Notes
SELECT simple  
SELECT restricted  
SELECT projected  
SELECT order by ASC or DESC.
SELECT join Only implicit joins are supported. The maximum number of supported joins is four. Columns must be fully qualified.
ADOCE select syntax

Under ADOCE, implicit joins are the only supported type of join. In addition, the number of tables that can be joined is limited to a maximum of four. For example, here is an ADOCE inner join:
SELECT     order_tbl.upc,
FROM       order_tbl
INNER JOIN product_tbl
ON         order_tbl.upc = product_tbl.upc
ORDER BY   order_tbl.upc ASC

Other SQL commands
With the exception of the aforementioned update command, the remainder of the data manipulation statements faired well. The SQL insert and delete commands are remarkably unchanged from their desktop counterparts:
INSERT INTO product_tbl
VALUES      ("Just Born",
             "Hot Tamales",

Creating databases and tables
You have several options available when creating databases and tables under ADOCE. The first is SQL's data definition language statements. Though not necessarily the easiest way for the non-DBA to create databases and tables, these statements (Listing B) offer the most control.

The easiest method, by far, is to create a database using Microsoft Access and then convert it to the Windows CE format. This has the advantages of not scaring the non-DBA types, such as myself.

The ADOCE application programming interface
ADOCE provides two functions that, more than anything else, serves to justify the existence of ADOCE. These functions are DesktopToDevice and DeviceToDesktop. As their names suggest, they provide a programmatic way to transfer databases both to and from a Windows CE-based device. Table B outlines the parameters for these two functions.
Table B
Parameter Number Name Required Description
The file name and path of the desktop database or the ODBC Data Source Name
The table names or individual column names with their associated fields to be copied
"True" enables; "False" disables ActiveSync.
"True" overwrites tables with the same name; "False" generates new table names. The default is "True."
The Windows CE database file
Parameters for DesktopToDevice and DeviceToDesktop functions

Editor's Picks