Transferring Databases
Converting a Microsoft Access Database to Pocket Access

Special thanks to John Bonin for providing this sample code.  John is a Sr. Software Engineer, working at EG&G Services.   He has over 20 years programming experience, dating back when input was done with Punch cards and you waited hours to days to see that you had a syntax error in your program!  Currently, he is involved with developing internet database solutions, desktop database solutions, and applications on handheld devices to aid in the accurate and timely collection of data.

Using a simple set of API's, you can move databases from the desktop to the CE device, and the other way around.  The adofiltr.dll is a DLL which is used in a DESKTOP application written in C++ or VB.  These are NOT programs that will run on the CE device!

This brief article will lead you through the steps to convert a Microsoft Access database (MDB) on the desktop to Pocket Access (CDB) on the handheld device. There is already a great sample on vbce.com, which explains how to use ADOCE and how to transfer an Access table to the CE Object Store.

With the release of the new H/PC Pro devices and Windows CE Services 2.2, you can now transfer the database file to the handheld, not as a file in the Object Store, but as a database file that can be used in Pocket Access. You can also modify this data using ADOCE, and you can keep both copies synchronized with ActiveSync.

In order to use this new functionality you must have a H/PC pro device (tested using the Vadem Clio) and you must have Microsoft Windows CE Services 2.2 installed on you desktop. Although, you can drag and drop the MDB file to the handheld device to convert, this sample is going to show you how to do it programmatically.

The DESKTOPTODEVICE API will be used to convert our database. If you have used ADOCE before you are probably aware of this and have seen some parameters that are for future use. Well, we are now going to use those new parameters. If you are unfamiliar with this API call, look at the ADOCE sample on vbce.com or buy a copy of Larry Roof’s book "Professional Visual Basic Windows CE Programming."

Below is the Visual Basic declaration statement:

Declare Function DESKTOPTODEVICE Lib _
"D:\program files\windows ce services\adofiltr.dll" _
(ByVal desktoplocn As String, ByVal tablelist As String, _
ByVal sync As Boolean, ByVal overwrite As Integer, _
ByVal devicelocn As String) As Long

As you can see there are 5 parameters to this call. The first is the location of the desktop version of the database (this must be a fully qualified filename, i.e., "d:\temp\Bibilo.mdb". The second parameters is a list of those tables that you want to convert. Each name is separated by two periods (..), as in "Authors..Publishers". The next parameter (this is a new one), tells if you want to have these 2 copies of the database to remain in sync. If you want ActiveSync to handle the synchronization, enter, "True". The next parameter allows you to overwrite existing data. The last parameter (also a new one) lists the location of the converted database on the handheld device. Again this must be a fully qualified, CE name, as in "\Temp\Biblio.cdb". If you leave this last parameter blank then the database will be converted to the CE Object Store and will not be synchronized.

Now after you have converted you Desktop database, whenever you connect your handheld to the desktop, ActiveSync will synchronize the changes to both the desktop and handheld copies of the database. You can use both Microsoft Access and Pocket Access to modify the data and be sure that the changes will be available on both systems. Figure 1 show the Database Synchronization properties, select ActiveSync Options in the Mobile Devices folder to view the properties.

Just a note, I was transferring and synchronizing this database wirelessly using Proxim’s RangeLAN2 ethernet adapter and the RangeLAN2 7400 wireless LAN Adapter.

Download the Sample VB6 project which allows you to convert your database. This program is a desktop only program, it will not run on the handheld device. You can use the Biblio.mdb that came with Visual Basic 6 or use your own MDB file. There are comments in the code that explain what is going on.