Filtering and Sorting Data in a Recordset

In general, when using ADO with the Jet Provider, you will have better performance if you filter and sort data by specifying an SQL WHERE or ORDER BY clause in the SQL statement or stored query used to open the Recordset.

Using the Filter Property

The following listings demonstrate how to use the Filter property.

DAO

Sub DAOFilterRecordset()

   Dim db As DAO.Database

   Dim rst As DAO.Recordset

   Dim rstFlt As DAO.Recordset

   ' Open the database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")

   ' Open the Recordset

   Set rst = db.OpenRecordset("Customers", dbOpenDynaset)

   ' Set the Filter to be used for subsequent recordsets

   rst.Filter = "Country='USA' And Fax Is Not Null"

   ' Open the filtered recordset

   Set rstFlt = rst.OpenRecordset()

   Debug.Print rstFlt.Fields("CustomerId").Value

   ' Close the recordsets

   rst.Close

   rstFlt.Close

End Sub

ADO

Sub ADOFilterRecordset()

   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

   ' Filter the recordset to include only those customers in

   ' the USA that have a fax number

   rst.Filter = "Country='USA' And Fax <> Null"

   Debug.Print rst.Fields("CustomerId").Value

   ' Close the recordset

   rst.Close

End Sub

The DAO and ADO Filter properties are used slightly differently. With DAO, the Filter property specifies a filter to be applied to any subsequently opened Recordset objects based on the Recordset for which you have applied the filter. With ADO, the Filter property applies to the Recordset to which you applied the filter. The ADO Filter property allows you to create a temporary view that can be used to locate a particular record or set of records within the Recordset. When a filter is applied to the Recordset, the RecordCount property reflects just the number of records within the view. The filter can be removed by setting the Filter property to adFilterNone.

Using the Sort Method

The following listings demonstrate how to sort records with the Sort method.

DAO

Sub DAOSortRecordset()

   Dim db As DAO.Database

   Dim rst As DAO.Recordset

   Dim rstSort As DAO.Recordset

   ' Open the database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")

   ' Open the Recordset

   Set rst = db.OpenRecordset("Customers", dbOpenDynaset)

   ' Sort the recordset based on Country and Region both in

   ' ascending order

   rst.Sort = "Country, Region"

   ' Open the sorted recordset

   Set rstSort = rst.OpenRecordset()

   Debug.Print rstSort.Fields("CustomerId").Value

   ' Close the recordsets

   rst.Close

   rstSort.Close

End Sub

ADO

Sub ADOSortRecordset()

   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 "Customers", cnn, adOpenKeyset, adLockOptimistic

   ' Sort the recordset based on Country and Region both in

   ' ascending order

   rst.Sort = "Country, Region"

   Debug.Print rst.Fields("CustomerId").Value

   ' Close the recordset

   rst.Close

End Sub

Like the Filter property, the DAO and ADO Sort properties differ in that the DAO Sort applies to subsequently opened Recordset objects, and for ADO it applies to the current Recordset.

Note that the Microsoft Jet Provider does not support the OLE DB interfaces that ADO could use to filter and sort the Recordset (IViewFilter and IViewSort). In the case of Filter, ADO will perform the filter itself. However, for Sort, you must use the Cursor Service by specifying adUseClient for the CursorLocation property prior to opening the Recordset. The Cursor Service will copy all of the records in the Recordset to a cache on your local machine and will build temporary indexes in order to perform the sorting. In many cases, you may achieve better performance by re-executing the query used to open the Recordset and specifying an SQL WHERE or ORDER BY clause as appropriate.

Also, you may not get identical results with DAO and ADO when sorting Recordset objects. Different sort algorithms can create different sequences for records that have equal values in the sorted fields. In the example above, the DAO code gives 'RANCH' as the CustomerId for the first record, while the ADO code gives 'CACTU' as the CustomerId. Both results are valid.