In the previous example, the ADO code could be
rewritten to use the Recordset object's GetString method to print the data to the Debug window. This
method returns a formatted string containing data from the records in the
recordset. Using GetString, the While loop in the
previous ADO example could be replaced with the single line:
Debug.Print rst.GetString(adClipString, , ";")
This method is handy for debugging as well as
populating grids and other controls that allow you to pass in a formatted
string representing the data. GetString is also faster
than looping through the Recordset and generating the
string with Visual Basic for Applications code.
The ADO example could also have been rewritten
more concisely by using the Recordset object's Open method's ActiveConnection parameter
to specify the connection string rather than first opening a Connection
object and then passing that object in as the ActiveConnection.
The Recordset object's Open method
call would look like this:
rst.Open _
"SELECT * FROM Customers WHERE Region = 'WA'", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;", _
adOpenForwardOnly, adLockReadOnly
Internally, these two mechanisms are essentially
the same. When you pass a connection string to the Recordset object's
Open method (rather than assigning a Connection
object to the Recordset object's ActiveConnection property), ADO creates a new, internal Connection object. If you plan on opening more than one Recordset from a given data source, or opening Command or Catalog objects, create a Connection object and use that object for the ActiveConnection. This will reduce the amount of resources
consumed and increase performance.
Determining Current Position
When working with records in a Recordset
it may be useful to know what the record number of the current record is. Both
ADO and DAO have an AbsolutePosition property that can
be used to determine the record number. The following code listings demonstrate
how to use the AbsolutePosition property in both DAO
and ADO.
DAO
Sub DAOGetCurrentPosition()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("SELECT * FROM Customers", _
dbOpenDynaset)
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Move to the last record
rst.MoveLast
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOGetCurrentPosition()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM Customers", cnn, adOpenKeyset, _
adLockOptimistic, adCmdText
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Move to the last record
rst.MoveLast
' Print the absolute position
Debug.Print rst.AbsolutePosition
' Close the recordset
rst.Close
End Sub
The ADO and DAO code for determining the current
position within the Recordset looks very similar. However,
note that the results printed to the debug window are different. With DAO, the AbsolutePosition property is zero-based; the first record in
the recordset has an AbsolutePosition of 0. With ADO,
the AbsolutePosition property is one-based; the first
record in the recordset has an AbsolutePosition of 1.
Note that in the ADO code example, the CursorLocation property is set to adUseClient.
If the CursorLocation is not specified or is set to adUseServer, the AbsolutePosition property
will return adUnknown (-1) because the Microsoft Jet
Provider does not support retrieving this information. See the section,
"Using Client Cursors" for more information about using the CursorLocation property.
In addition to the AbsolutePosition
property, DAO also has a PercentPosition property that
returns a percentage representing the approximate position of the current
record within the Recordset. ADO does not have a
property or method that provides the functionality equivalent to DAO's PercentPosition property. However, when using client cursors
(adUseClient), the user can calulate an approximate
percent position from the AbsolutePosition and RecordCount properties in ADO.
Finding Records in a Recordset
Both DAO and ADO have two mechanisms for
locating a record in a Recordset: Find
and Seek. With both mechanisms you specify
criteria to use to locate a matching record. In general, for equivalent types
of searches, Seek provides better performance than Find. However, because Seek uses an
underlying index to locate the record, it is limited to Recordset
objects that have associated indexes. For Microsoft Jet databases only, Recordset objects based on a table (DAO dbOpenTable,
ADO adCmdTableDirect) with an index support Seek.
Using the Find Method
The following listings demonstrate how to locate
a record using Find.
DAO
Sub DAOFindRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
' Find the first customer whose country is USA
rst.FindFirst "Country = 'USA'"
' Print the customer id's of all customers in the USA
Do Until rst.NoMatch
Debug.Print rst.Fields("CustomerId").Value
rst.FindNext "Country = 'USA'"
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOFindRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic
' Find the first customer whose country is USA
rst.Find "Country='USA'"
' Print the customer id's of all customers in the USA
Do Until rst.EOF
Debug.Print rst.Fields("CustomerId").Value
rst.Find "Country='USA'", 1
Loop
' Close the recordset
rst.Close
End Sub
DAO includes four find methods: FindFirst, FindLast, FindNext, FindPrevious. You choose
which method to use based on the point from which you want to start searching
(beginning, end, or curent record) and in which direction you want to search
(forward or backward).
ADO has a single method: Find.
Searching always begins from the current record. The Find
method has parameters that allow you to specify the search direction as well as
an offset from the current record at which to beginning searching (SkipRows). The following table shows how to map the four DAO
methods to the equivalent functionality in ADO.
DAO method
|
ADO Find with SkipRows
|
ADO search direction
|
FindFirst
|
0
|
adSearchForward (if not currently positioned on the first record, call MoveFirst before Find)
|
FindLast
|
0
|
adSearchBackward (if not currently positioned on the last record, call MoveLast before Find)
|
FindNext
|
1
|
adSearchForward
|
FindPrevious
|
1
|
adSearchBackward
|
DAO and ADO require a different syntax for
locating records based on a Null value. In DAO if you want to find a record
that has a Null value you use the following syntax:
"ColumnName Is Null"
or, to find a record that does not have a Null
value for that column:
"ColumnName Is Not Null"
ADO, however, does not recognize the Is
operator. You must use the = or <> operators instead. So the equivalent
ADO criteria would be:
"ColumnName = Null"
or
"ColumnName <> Null"
So far, each of the criteria shown in the
examples above are based on a value for a single column. However, with DAO, the
Criteria parameter is like the WHERE clause in an SQL
statement and can contain multiple columns and compare operators within the
criteria.
This is not the case with ADO. The ADO Criteria parameter is a string containing a single column
name, comparison operator, and value to use in the search. If you need to find
a record based on multiple columns, use the Filter property
(see the section, "Filtering and Sorting Data") to create a view of
the Recordset that only contains those records matching
the criteria.
DAO and ADO behave differently if a record that
meets the specified criteria is not found. DAO sets the NoMatch
property to True and the current record is not defined. If ADO does not find a
record that meets the criteria, the current record is positioned either before
the beginning of the Recordset if searching forward (adSearchForward) or after the end of the Recordset
if searching backward (adSearchBackward). Use
the BOF or EOF properties as
appropriate to determine whether or not a match was found.
Using the Seek Method
The following listings demonstrate how to locate
a record using Seek.
DAO
Sub DAOSeekRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("Order Details", dbOpenTable)
' Select the index used to order the data in the recordset
rst.Index = "PrimaryKey"
' Find the order where OrderId = 10255 and ProductId = 16
rst.Seek "=", 10255, 16
' If a match is found print the quantity of the order
If Not rst.NoMatch Then
Debug.Print rst.Fields("Quantity").Value
End If
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOSeekRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "Order Details", cnn, adOpenKeyset, adLockReadOnly, _
adCmdTableDirect
' Select the index used to order the data in the recordset
rst.Index = "PrimaryKey"
' Find the order where OrderId = 10255 and ProductId = 16
rst.Seek Array(10255, 16), adSeekFirstEQ
' If a match is found print the quantity of the order
If Not rst.EOF Then
Debug.Print rst.Fields("Quantity").Value
End If
' Close the recordset
rst.Close
End Sub
Because Seek is based on
an index, it is important to specify an index before searching. In the previous
example, this is not strictly necessary because Microsoft Jet will use the
primary key if an index is not specified.
In the ADO example, the Visual Basic for
Applications Array function is used when specifying a value for more than one
column as part of the KeyValues parameter. If only
one value is specified, it is not necessary to use the Array function.
As with the Find method,
use the NoMatch property with DAO to determine whether
or not a matching record was found. Use the BOF and EOF properties as appropriate with ADO.
The Seek method will
work correctly only for Microsoft Jet 4.0 databases. It will fail with a
run-time error for all earlier formats, even if you use the Microsoft Jet 4.0
database engine to open the database. This will cause a problem if the
application is written to support older database formats. If so, use the Supports method of the Recordset
object to determine whether the Seek method is
available for the open Recordset. However, if all
client applications use the newer format, this check is unnecessary. Use either
Microsoft Access 2000 or the CompactDatabase method to
convert older databases to the newer format.
EmoticonEmoticon