Both DAO and ADO include a Recordset
object that is the primary object used for retrieving and modifying data. A Recordset object represents a set of records in a table or a
set of records that are a result of a query.
The Recordset object
contains a Fields collection that contains Field objects, each of which represent a single column of
data within the Recordset.
Opening a Recordset
Like DAO, ADO Recordset
objects can be opened from several different objects. In ADO, a Recordset can be opened with the Connection
object Execute method, the Command object
Execute method, or the Recordset
object Open method. ADO Recordset
objects cannot be opened directly from Table, Procedure, or View objects. ADO Recordset objects opened with the Execute method
are always forward-only, read-only recordsets. If you need to be able to scroll
or update data within the Recordset you must use the Recordset object Open method.
The CursorType, LockType, and Options parameters
of the Open method determine the type of Recordset that is returned. The table below shows how the
parameters to the DAO Recordset object Open method can be mapped to ADO properties.
DAO Recordset type
|
ADO Recordset properties or parameters
|
dbOpenDynaset
|
CursorType=adOpenKeyset
|
dbOpenSnapshot
|
CursorType=adOpenStatic
|
dbOpenForwardOnly
|
CursorType=adOpenForwardOnly
|
dbOpenTable
|
CursorType=adOpenKeyset,
Options=adCmdTableDirect
|
DAO Recordset Options values
|
ADO Recordset properties
|
dbAppendOnly
|
Properties("Append-Only
Rowset")
|
dbSQLPassThrough
|
Properties("Jet
OLEDB:ODBC Pass-Through Statement")
|
dbSeeChanges
|
No equivalent.
|
dbDenyWrite
|
No equivalent.
|
dbDenyRead
|
No equivalent.
|
dbInconsistent
|
Properties("Jet
OLEDB:Inconsistent") = True
|
dbConsistent
|
Properties("Jet
OLEDB:Inconsistent") = False
|
DAO Recordset LockType values
|
ADO Recordset LockType values
|
dbReadOnly
|
adLockReadOnly
|
dbPessimistic
|
adLockPessimistic
|
dbOptimistic
|
adLockOptimistic
|
The Microsoft Jet Provider does not support a
number of combinations of CursorType and LockType — for example, CursorType=adOpenDynamic
and LockType=adLockOptimistic. If you specify an
unsupported combination, ADO will pass your request to the Microsoft Jet
Provider, which will then degrade to a supported CursorType or LockType. Use
the CursorType and LockType properties of the Recordset
once it is opened to determine what type of Recordset
was created.
The following listings demonstrate how to open a
forward-only, read-only Recordset, then prints the
values of each field.
DAO
Sub DAOOpenRecordset()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers WHERE Region = 'WA'", _
dbOpenForwardOnly, dbReadOnly)
' Print the values for the fields in
' the first record in the debug window
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOOpenRecordset()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the forward-only,
' read-only recordset
rst.Open _
"SELECT * FROM Customers WHERE Region = 'WA'", _
cnn, adOpenForwardOnly, adLockReadOnly
' Print the values for the fields in
' the first record in the debug window
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
' Close the recordset
rst.Close
End Sub
In the DAO and ADO code above, the Recordset is opened and then the data in the first record of
the Recordset is printed to the Debug window by
iterating through each field in the Fields collection
and printing its Value.
Using Client Cursors
ADO Recordset objects have
an additional property, CursorLocation, not found in
DAO that affects the functionality and performance of the Recordset.
This property has two valid values: adUseServer and adUseClient. The default is adUseServer,
which indicates that the provider's or data source's cursors should be used.
When the CursorLocation
property is set to adUseClient, ADO will invoke the
Microsoft Cursor Service for OLE DB to create the Recordset.
The Cursor Service retrieves data from the underlying data provider using a forward-only,
read-only cursor and stores all of the data in its own cache on the client.
When data is requested through ADO, the Cursor Service returns the data from
its own cache rather than passing the request down to the provider. This often
results in fairly significant performance gains when the underlying data source
is on a remote server as is often the case with SQL Server. However, when the
data is stored in a local Microsoft Jet database, this can result in fairly
significant performance degradation as the data is being cached twice on the
client, once in Microsoft Jet and once in the Cursor Service.
While there may be a performance penalty for
using the Cursor Service, it does provide some functionality found in DAO that
is not currently exposed in the Microsoft Jet Provider. For example, adUseClient must be specified for CursorLocation
in order to sort an existing Recordset. (See the
section, "Filtering and Sorting Data in a Recordset" for more
information about how to use the Cursor Service to sort a Recordset.)
When developing your application, you'll
generally want to specify adUseServer as the CursorLocation to get performance and functionality similar
to DAO. However, in the few cases where the Microsoft Jet Provider does not
provide the functionality needed, consider using client cursors.
Navigating Within a Recordset
A Recordset object has a
current position. The position may be before the first record (BOF), after the last record (EOF), or
on a specific record within the Recordset. When retrieving
information with the Field object, the information
always pertains to the record at the current position.
Moving To Another Record
Both DAO and ADO contain several methods for
moving from one record to another. These methods are Move,
MoveFirst, MoveLast, MoveNext, and MovePrevious.
The following listings demonstrate how to use
the MoveNext method to iterate through all of the
records in the Recordset.
DAO
Sub DAOMoveNext()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers WHERE Region = 'WA'", _
dbOpenForwardOnly, dbReadOnly)
' Print the values for the fields in
' the first record in the debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOMoveNext()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the forward-only,
' read-only recordset
rst.Open _
"SELECT * FROM Customers WHERE Region = 'WA'", _
cnn, adOpenForwardOnly, adLockReadOnly
' Print the values for the fields in
' the first record in the debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
Notice that the code for iterating through the Recordset in DAO and ADO is identical.
EmoticonEmoticon