January 2000

Avoid ADO Find Frustration

by Susan Sales Harkins

Last month's article, "Learn the ABCs of ADO," touched lightly on the new ADO Find method. As previously mentioned, ADO has replaced DAO's four Find methods with a single Find method. We also noted that ADO's Find method doesn't recognize the Is operator. There's another change you'll need to be aware of. ADO's Find method doesn't allow you to combine multiple conditions using the And operator. In this article, we'll show you the new technique you'll need to use to get results with ADO.

DAO, Find, and the And operator

DAO's Find method recognizes the And operator. That means you can base a search on more than one condition. For instance, the function in Listing A searches the Products table for the next record where SupplierID equals 20 and CategoryID equals 1.

Listing A: DAO Find method

Function FindEx()
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Products", dbOpenDynaset)
rst.FindNext "SupplierID = 20 And CategoryID = 1"
Debug.Print rst!ProductID

rst.Close
End Function

ADO, Find, and the And operator

You can't convert the DAO procedure in Listing A to ADO because the Find method won't recognize the And operator. For instance, the procedure in Listing B returns an error when ADO tries to execute the statement

rst.Find "SupplierID = 20 And CategoryID = 1"

Listing B: ADO Find method

Function FindEx()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= C:\Program Files\Microsoft " & _
    "Office\Office\Samples\Northwind.mdb;"
rst.Open "Products", cnn, adOpenKeyset, adLockOptimistic
rst.Find "SupplierID = 20 And CategoryID = 1"
Debug.Print rst!ProductID
rst.Close
End Function

Filtering ADO recordsets with multiple criteria

Just because ADO doesn't recognize the And operator doesn't mean you can't use multiple criteria to search a recordset. Instead, you use a filter on an open recordset. After opening the recordset, you simply set the Filter property to create a unique view of the recordset. The procedure in Listing C will search for multiple criteria without closing and opening a new recordset.

Listing C: ADO Filter method

Function FindEx()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset   
Set cnn = CurrentProject.Connection
rst.ActiveConnection = cnn
rst.Open "Products", cnn, adOpenKeyset, adLockOptimistic
rst.Filter = "SupplierID = 20 AND CategoryID = 1"
Debug.Print rst!ProductID
rst.Close
End Function

After setting the connection to the current project, the Open method creates a searchable recordset based on the Products table. We used the Filter property to search for multiple criteria. However, if you're only matching one field, you could use the Find method.

DAO also has a Filter property, but the ADO property works a bit differently. The DAO Filter works on subsequent recordsets; while ADO's Filter works on the current recordset. A nice feature of ADO's Filter is that you can use the RecordCount property to learn the number of records in a filtered view. To reset a recordset's Filter property, use the property equal to the constant adFilterNone.

Conclusion

Although many ADO methods and properties share names with DAO's actions and attributes, they often function slightly different. We've shown you how to avoid confusion with ADO's Find method when you need to search for multiple criteria.

Copyright © 2000, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.