![]() |
---|
This is retired content. This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |
Microsoft Corporation
June 2000
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)
Introduction to ADO and ADOCE
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
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 Visual Basic version 6.0. ADOCE provides the following features:
The ADOCE control has two objects:
Recordsetand
Field. A
recordsetis 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. The following table shows the properties that the
Recordsetobject supports.
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. The Fields collection contains a
Fieldobject for each column in the recordset. You can refer
to a particular field by name or by index. The Fields collection
supports the
Countproperty, 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:
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 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 Employees table is
sent as a read-only table, while the Products table can be
updated. 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. 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 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. The following code example shows how to create an index on a
database that is used for faster sorting. It requires a
ListBoxnamed List1. 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. 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. 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. 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. 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. 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. 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
Controlssite.
Recordset and Field Objects
Using ADOCE
Summary
For More Information
Introduction to ADO and ADOCE
Recordset and Field Objects
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.
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.
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.
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.
Typev
Indicates the data type of a
Fieldobject.
UnderlyingValuev
Indicates a
Fieldobject's current value in the database.
Value (default)
Indicates a
Fieldobject's current value in the recordset.
Using ADOCE
Required Software
Moving Databases Between the Desktop and
Device
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
Dim rs Set rs = CreateObject("adoce.recordset")
rs.Open "create table mytable _ (firstfield text, _ secondfield
integer)" Set rs = Nothing
Sorting a Table
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
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
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
rs.Addnew "firstfield", "Windows CE"
rs.Update "firstfield", "ADOCE"
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
Using an H/PC Running Pro Edition Software
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
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
For More Information