Developing Data Access Applications for Microsoft Windows CE 2.1 with ADOCE

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 Recordsetand Fieldobjects, 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 Recordsetand Fieldobjects. 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: Recordsetand 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 Recordsetobject supports.

  Method	Description
  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.
  Open	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 next table shows the properties that the Recordsetobject supports.

  Property	Description
  AbsolutePage	Specifies which page to move for a new current record.
  AbsolutePosition	Specifies the ordinal position of a Recordsetobject'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 Recordsetobject.
  Bookmark	Specifies a bookmark that uniquely identifies a record in a Recordsetobject.
  CacheSize	Specifies the number of records from a Recordsetobject that are cached locally in memory.
  CursorType	Indicates the type of cursor used in a Recordsetobject.
  EditMode	Indicates the editing status of the current record.
  EOF	Indicates that the current record position is after the last record in a Recordsetobject.
  LockType	Indicates the type of locks placed on records during editing.
  PageCount	Indicates how many pages of data the Recordsetobject 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 Recordsetobject.
  Source	Indicates the source for the data in a Recordsetobject—SQL statement or table name.

  Fieldobjects should not be directly created because they exist only in the context of an existing recordset. Use the Setfunction to refer to a specific Fieldobject. The Fieldobject has no methods or events. With the exception of the Valueproperty, all the properties are read-only.

  The following table shows the properties that the Fieldobject supports.

  Property	Description
  ActualSize	Indicates the actual length, in bytes, of a field's value.
  Attributes	Returns a value that indicates one or more characteristics of a Fieldobject.
  DefinedSize	Used to determine the data capacity of a Fieldobject. 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.
  Type	Indicates the data type of a Fieldobject.
  UnderlyingValue	Indicates a Fieldobject's current value in the database.
  Value (default)	Indicates a Fieldobject's current value in the recordset.

  The Fieldscollection contains a Fieldobject for each column in the recordset. You can refer to a particular field by name or by index. The Fieldscollection supports the Countproperty, 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 Tablesfrom the Toolsmenu in the Mobile Devices window. The Opendialog box appears.
    3. Select the Access .mdb file containing the tables you want to import. The Import from Database to Mobile Devicedialog 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-Onlycheck 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 datacheck box.
    7. Choose OKto 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 Tablesfrom the Toolsmenu in the Mobile Devices window. The Export from Mobile Device to Databasedialog box appears, showing the ADOCE tables on the connected device.
       3. Choose the Browsebutton to select an Access .mdb file. You also can type the path and file name in the Locationbox. 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 datacheck box.
       6. Choose OKto 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 DesktopToDeviceand DeviceToDesktopfunctions, respectively. The behavior of these functions is similar to the Import from Database to Mobile Deviceand Export from Mobile Device to Databasedialog 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 DesktopToDevicefunction, you can specify which fields are converted. However, with the DeviceToDesktopfunction, 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 Employeestable is sent as a read-only table, while the Productstable 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 Bystatement 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 ListBoxnamed 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 ListBoxnamed 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 CursorTypeand LockTypewhen 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 AddNewmethod with parameters, you do not need to use the Updatemethod 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 Updatemethod. If you use Updatewith 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 AddNewand Update, you can use the Valueproperty to get the information stored in a field. The following code example shows how to retrieve information from an ADOCE database. It requires a ListBoxnamed 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 http://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.

          
          

          ---

          © 2004 Microsoft Corporation. All rights reserved.