Developing Data Access Applications for Microsoft Windows CE 3.0 with ADOCE
Summary: Using Microsoft ActiveX Data Objects (ADO), client applications can access and manipulate data from a database server through an OLE database provider. The ActiveX Data Objects for Microsoft Windows CE (ADOCE) provides a subset of ADO for Windows CE. ADOCE adds new database functionality to the Windows CE operating system by enabling access to databases stored locally on a device and provides synchronization of data to a network database. ADOCE can be used with the Handheld PC (H/PC) and the Palm PC version 1.2. This paper provides an introduction to ADO and ADOCE, describes the Recordset and Field objects, and discusses how to use ADOCE. (9 printed pages)
Microsoft® ActiveX® Data Objects (ADO) is Microsoft's strategic, high-level interface to all kinds of data. An application that uses ADO can access and manipulate data in a database server through an OLE database provider. The primary benefits of ADO are ease of use, high speed, low memory overhead, and a small disk footprint. ADO provides consistent, high-performance access to data for creating a front-end database client or middle-tier business object using an application, tool, language, or even an Internet browser.
ADO is the central component of the Microsoft Universal Data Access strategy. Universal Data Access provides high-performance access to a variety of information sources, including relational and non-relational, and an easy-to-use programming interface that is tool-independent and language-independent. These technologies enable corporations to integrate diverse data sources, create easy-to-maintain solutions, and use their choice of the best tools, applications, and platform services.
ADOCE provides a subset of ADO for the Windows CE operating system that includes implementation of the Recordset and Field objects. ADOCE adds new database functionality to Windows CE by enabling access to databases stored locally on a device and provides synchronization of data to a network database. ADOCE provides access to the Windows CE database engine from any COM-capable environment, such as the Microsoft Windows® CE Toolkit for Visual Basic® version 6.0.
ADOCE provides the following features:
- Automatic two-way synchronization to the desktop host.
- New data types: double and Boolean (compared to Windows CE database).
- SQL support. The advanced SQL support included in ADOCE is a subset of the American National Standards Institute (ANSI) SQL. It adds new database engine functionality that provides field names, multiple sort keys, complex filtering, table and index creation, deletion and editing, and joins capabilities between multiple tables.
- Familiar recordset.fieldname access.
- Desktop ADO compatibility.
The ADOCE control has two objects: Recordset and Field. A recordset is a virtual database table whose fields and rows correspond to a subset of the fields and rows in an actual database table on the Windows CE-based device. When you make additions, deletions, or changes to the information in a recordset row, you can pass those changes to the corresponding parts of the table. When you change data in the recordset, the recordset stores the changes in memory, enabling you to cancel them before the underlying database is updated. ADOCE does not support batch updates. Only one row at a time can have data that is changed but not committed to the underlying database.
The following table shows the methods that the Recordset object supports.
|AddNew||Inserts a new row into the recordset.|
|CancelUpdate||Cancels changes held in memory.|
|Clone||Duplicates a recordset.|
|Close||Closes a recordset.|
|Delete||Deletes a row from the recordset.|
|GetRows||Returns data stored in the recordset.|
|Move||Changes the pointer to the active row in the recordset.|
|MoveFirst||Makes the first row active.|
|MoveLast||Makes the last row active.|
|MoveNext||Moves the active row pointer to the next row.|
|MovePrevious||Moves the active row pointer to the previous row.|
|Openv||Defines and opens recordsets; runs SQL commands.|
|Supports||Determines if the recordset supports certain features.|
|Update||Commits changes held in memory and updates the actual table.|
The following table shows the properties that the Recordset object supports.
|AbsolutePage||Specifies which page to move for a new current record.|
|AbsolutePosition||Specifies the ordinal position of a Recordset object's current record.|
|ActiveConnection||Sets the current database connection. Always a zero-length string (""). For H/PC running Pro Edition software, the name of the cdb file.|
|BOF||Indicates whether the current record position is before the first record in a Recordset object.|
|Bookmark||Specifies a bookmark that uniquely identifies a record in a Recordset object.|
|CacheSize||Specifies the number of records from a Recordset object that are cached locally in memory.|
|CursorType||Indicates the type of cursor used in a Recordset object.|
|EditMode||Indicates the editing status of the current record.|
|EOF||Indicates that the current record position is after the last record in a Recordset object.|
|LockType||Indicates the type of locks placed on records during editing.|
|PageCount||Indicates how many pages of data the Recordset object contains.|
|PageSize||Indicates how many records constitute one page in the Recordset.|
|RecordCount||Returns a Long value that indicates the current number of records in a Recordset object.|
|Source||Indicates the source for the data in a Recordset object-SQL statement or table name.|
Field objects should not be directly created because they exist only in the context of an existing recordset. Use the Set function to refer to a specific Field object. The Field object has no methods or events. With the exception of the Value property, all the properties are read-only. The following table shows the properties that the Field object supports.
|ActualSize||Indicates the actual length, in bytes, of a field's value.|
|Attributes||Returns a value that indicates one or more characteristics of a Field object.|
|DefinedSize||Used to determine the data capacity of a Field object It returns the defined size, in characters, of the field. Compare with ActualSize, which returns the size in bytes.|
|Name||Returns the name of a field.|
|Typev||Indicates the data type of a Field object.|
|UnderlyingValuev||Indicates a Field object's current value in the database.|
|Value (default)||Indicates a Field object's current value in the recordset.|
The Fields collection contains a Field object for each column in the recordset. You can refer to a particular field by name or by index. The Fields collection supports the Count property, which indicates the number of fields in a recordset.
With ADOCE, you can move databases to and from your Windows CE-based device. You can also create and access databases on the device and on an emulator on a desktop computer. The following sections describe how to use ADOCE and show examples. Note that ControlConsts.bas is installed in \Program Files\Microsoft Visual Studio\VB98\VBCE\Samples. This file contains the definitions for the enumerations in ADOCE and other controls.
To use the ADOCE control, you need the following software:
- Microsoft Windows CE Services version 2.1 or later
- Microsoft Windows NT® Workstation version 4.0 or Windows 95 or 98
- Microsoft Windows CE Platform SDK for the device you are using
- Microsoft Visual Basic® Professional Edition version 6.0, development system or Microsoft Visual Basic Enterprise Edition version 6.0
- Microsoft Windows CE Toolkit for Visual Basic version 6.0
The ADOCE control is included in the Windows CE Toolkit for Visual Basic.
Moving Databases Between the Desktop and Device
You can move database tables back and forth between a desktop computer and a Windows CE-based device. The databases stored on the desktop computer are in Microsoft Access database format. The databases stored on the device are stored in standard Windows CE-based tables with additional information added to the ADOCE system tables.
To copy a database from the desktop computer to the device
- Connect the device to the desktop computer and open the Mobile Devices window.
- On the Tools menu, click Import Database Tables. The Open dialog box appears.
- Select the Microsoft Access .mdb file containing the tables you want to import. The Import from Database to Mobile Device dialog box appears with a list of the tables and fields in the database.
- Select the check boxes for the tables and fields you want to import, and clear the check boxes for the tables and fields you do not want to import.
- For the tables that you want to make read-only, select the Read-Only check box.
- If you want to replace tables on the device that have the same name as the tables in the .mdb file, select the Overwrite existing tables and/or data check box.
- Click OK to begin the conversion.
To copy a database from the device to the desktop computer
- Connect the device to the desktop computer and open the Mobile Devices window.
- On the Tools menu, click Export Database Tables. The Export from Mobile Device to Database dialog box appears, showing the ADOCE tables on the connected device.
- Click Browse to select a Microsoft Access .mdb file. You also can type the path and file name in the Location box. If the file does not exist, ADOCE creates it for you.
- Select the check boxes for the tables you want to export, and then clear the check boxes for the tables you do not want to export. ADOCE puts all of the selected tables in the same .mdb file.
- If you want to replace tables in the .mdb that have the same name as a selected ADOCE table, select the Overwrite existing tables and/or data check box.
- Click OK to begin the conversion.
For programmatic control over the conversion of .mdb files to ADOCE tables, and ADOCE tables to .mdb files, you can use the DesktopToDevice and DeviceToDesktop functions, respectively. The behavior of these functions is similar to the Import from Database to Mobile Device and Export from Mobile Device to Database dialog boxes, except that the user-choice dialog boxes do not appear. All progress bars do appear, showing the transfer status. An error return does not cause the log file to automatically display. Instead, an error value, HRESULT, is returned by the function, and the calling application can take the appropriate action.
With the DesktopToDevice function, you can specify which fields are converted. However, with the DeviceToDesktop function, all fields in a table are converted by default.
The following code example shows a simple Visual Basic application that copies a subset of the Northwind database included with Visual Basic to the device with no synchronization, but overwriting any existing Northwind tables. The Employees table is sent as a read-only table, while the Products table can be updated.
Declare Function DesktopToDevice Lib _ "c:\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 result = DesktopToDevice _ ("c:\mydbs\nwind.mdb", _ "!Employees..Products.ID.Name.Quantity..", _ False, True, "") If result <> 0 Then MsgBox _ "An error occurred transferring the data"
Creating a Table
Before you can access data with the ADOCE control, you must have a table available in which to store it. The following code example shows how to create a new ADOCE table in the Databases directory of an H/PC running the Pro Edition software.
Dim rs Set rs = CreateObject("adoce.recordset") rs.Open "create table mytable _ (firstfield text, _ secondfield integer)" Set rs = Nothing
Because SQL statements that change the structure of a table leave the recordset closed, you cannot read or write data until the recordset is reopened.
Sorting a Table
You can sort a database by using the Order By statement with or without indexes. However, sorting a database without an index is generally slower than sorting a database with an index. The following code example shows how to sort a database without an index. It requires a ListBox named List1.
Dim rs Set rs = CreateObject("ADOCE.RecordSet") rs.open "select * from mytable order by _ firstfield desc", "", _ adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF List1.Additem _ rs.Fields("firstfield").Value rs.MoveNext Loop End If rs.Close set rs = Nothing
The following code example shows how to create an index on a database that is used for faster sorting. It requires a ListBox named List1.
Dim rs Set rs = CreateObject("ADOCE.RecordSet") rs.open "create index i1 on mytable _ (firstfield desc)" rs.open "select * from mytable order by _ firstfield desc", "", _ adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF List1.Additem _ rs.Fields("firstfield").Value rs.MoveNext Loop End If rs.Close set rs = Nothing
Storing and Retrieving Information
To add, remove, or change information in a database, you must change the default CursorType and LockType when opening the recordset. The following code example shows how to store information in a database.
Dim rs Set rs = CreateObject("ADOCE.RecordSet") rs.open "mytable", "", _ adOpenKeyset, adLockOptimistic rs. Addnew rs.fields("firstfield") = _ "ActiveX Data Objects" rs.fields("secondfield") = 1.8 rs.Update rs.Close Set rs = Nothing
If you use the AddNew method with parameters, you do not need to use the Update method because the changes are automatically made to the database. The following code example shows how to use AddNew.
rs.Addnew "firstfield", "Windows CE"
To change a record, rather than add one, make the required changes to the fields and then use the Update method. If you use Update with parameters, the changes are automatically made to the database. The following code example shows how to use Update.
rs.Update "firstfield", "ADOCE"
After you store data in the database using AddNew and Update, you can use the Value property to get the information stored in a field. The following code example shows how to retrieve information from an ADOCE database. It requires a ListBox named List1.
Dim rs Set rs = CreateObject("ADOCE.RecordSet") rs.open "mytable", "", _ adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF List1.Additem _ rs.Fields("firstfield").Value rs.MoveNext Loop End If rs.Close set rs = Nothing
Distributing ADOCE Applications
The components of an ADOCE application must be installed on both the desktop computer and the device. One way to distribute an ADOCE application is to make modifications to the sample setup application included with the Microsoft Windows CE Toolkit for Visual Basic 6.0. All ADOCE setup applications should verify that Windows CE Services 2.1 or later is installed and add references to the ADOCE .cab files to the AppMgr .ini files for your application.
Using an H/PC Running Pro Edition Software
This section describes additional procedures that can be performed on an H/PC running Microsoft Windows CE, Handheld PC Professional Edition, version 3.0.
Creating a Database
The following code example shows how to create an external database on an H/PC running Pro Edition software. Dim rs Set rs = CreateObject("adoce.recordset") rs.Open "create database '\mydatabase.cdb'" Set rs = Nothing
Retrieving Information from a .cdb File
If you created a database on an H/PC running Pro Edition software, you can access this data by passing the path to the database in the connection parameter. All operations that can be performed with an empty connection string ("") can be performed on a .cdb file by changing the connection string. The following code shows how to retrieve information form a .cdb file.
Dim rs Set rs = CreateObject("ADOCE.RecordSet") rs.open "mytable", "\mydatabase.cdb", _ adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF List1.AddItem _ rs.Fields("firstfield").Value rs.MoveNext Loop End If rs.Close set rs = Nothing
ADOCE is an application-level data access object model that can be used with the Windows CE Toolkits for Visual Basic. ADOCE adds new database functionality to Windows CE by enabling access to databases stored locally on a device and provides replication and synchronization of data to a network database. ADOCE's speed and ease of use, along with its low memory overhead and small disk footprint makes it ideal for use with the compact, powerful, and fast Windows CE operating system.
For information about ActiveX controls, go to the Microsoft COM Technologies ActiveX Controls site.