Developing Data Access Applications for Microsoft Windows CE 2.1 with ADOCE

Microsoft Corporation

June 1999

Summary: Provides an introduction to Microsoft® ActiveX® Data Objects (ADO) and ActiveX Data Objects for Microsoft Windows® CE 2.1 (ADOCE) and discusses how to use ADOCE. Also discusses the Recordset and Field objects. (11 printed pages)

Contents

Introduction Introduction to ADO and ADOCE Recordset and Field Objects Using ADOCE Summary For More Information

Introduction

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-size PC version 1.2. This article provides an introduction to ADO and ADOCE, describes the Recordset and Field objects, and discusses how to use ADOCE.

Introduction to ADO and ADOCE

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 nonrelational, and an easy-to-use programming interface that is tool- 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 Microsoft 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 join capabilities between multiple tables.
• Familiar recordset.fieldname access.
• Desktop ADO compatibility.

Recordset and Field Objects

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.

The next table shows the properties that the Recordset object supports.

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.

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.

Using ADOCE

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.

Required Software

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

Note 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

1. Connect the device to the desktop computer and open the Mobile Devices window.
2. Choose Import Database Tables from the Tools menu in the Mobile Devices window. The Open dialog box appears.
3. Select the 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.
4. 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.
5. For the tables that you want to make read-only, select the Read-Only check box.
6. 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.
7. Choose OK to begin the conversion.

To copy a database from the device to the desktop computer

1. Connect the device to the desktop computer and open the Mobile Devices window.
2. Choose Export Database Tables from the Tools menu in the Mobile Devices window. The Export from Mobile Device to Database dialog box appears, showing the ADOCE tables on the connected device.
3. Choose the Browse button to select an 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.
4. 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.
5. 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.
6. Choose OK to begin the conversion.

For programmatic control over the conversion of .mdb files to ADOCE tables and of 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

Summary

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 More Information

For the latest information about Windows CE and embedded development tools, visit the Microsoft Windows CE Web site.

For information about ActiveX controls, visit https://www.microsoft.com/com/tech/activex.asp.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This document is for informational purposes only.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

© 1999 Microsoft Corporation. All rights reserved.

Microsoft, ActiveX, Visual Basic, Visual C++, Visual J++, Win32, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Java is a trademark of Sun Microsystems, Inc.

Other product and company names mentioned herein may be the trademarks of their respective owners.