Microsoft Corporation
June 1999
Summary: Provides an introduction to Microsoft® ActiveX® Data Objects (ADO) and ActiveX Data Objects for Microsoft Windows® CE (ADOCE) and discusses how to use ADOCE. (9 printed pages) Also discusses the Recordset and Field objects. Includes:
Introduction
Introduction to ADO and ADOCE
Recordset and Field Objects
Using ADOCE
Summary
For More Information
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.
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:
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.
| 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 Recordset object supports.
| Property | Description |
| 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.
| 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 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. |
| Type | Indicates the data type of a Field object. |
| UnderlyingValue | 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:
Note The ADOCE control is included in the Windows CE Toolkit for Visual Basic.
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
To copy a database from the device to the desktop computer
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"
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.
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
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
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.
This section describes additional procedures that can be performed on an H/PC running Microsoft Windows CE, Handheld PC Professional Edition, version 3.0.
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
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 the latest information about Windows CE and embedded development tools, visit the Microsoft Windows CE Web site at http://www.microsoft.com/windowsce/.
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.