Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Filtering and Sorting Data in a Recordset Object

Office 2000

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The following sections describe how to filter and sort data in an ADO Recordset object.

Filtering Records

The DAO and ADO Filter properties are used slightly differently:

  • In DAO, the Filter property is used to specify a filter to be applied to any subsequently opened Recordset objects that are based on the Recordset object to which you have applied the filter. You must essentially create a clone of the original Recordset object in order to filter the data.

  • In ADO, the Filter property applies directly to the Recordset object on which you set 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 current Recordset object. When a filter is applied to the current Recordset object, the RecordCount property reflects just the number of records available after you apply the filter. The filter can be removed by setting the Filter property to adFilterNone.

The following example shows how to filter a Recordset object by using the ADO Filter property.

Sub FilterRecordset(strDBPath As String, _
                    strTable As String, _
                    strFilter As String, _
                    strDisplayField As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset

   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   Set rst = New ADODB.Recordset
   With rst
      ' Open the table by using a scrolling Recordset object.
      .Open Source:= strTable, _
            ActiveConnection:= cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

      ' Filter the Recordset object.
      .Filter = strFilter

      ' Print the records.
      Do While Not .EOF
         Debug.Print .Fields(strDisplayField).Value
         .MoveNext
      Loop

      ' Close the Recordset object.
      .Close
End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

To use this procedure to filter a Recordset object opened on the Customers table in the Northwind database to include only those customers in the U.S. that have a fax number and display the values in the CustomerID field, you can use a line of code like this:

FilterRecordset "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "Customers", "Country='USA' And Fax<>Null", "CustomerID"

The FilterRecordset procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

Sorting Records

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

The Microsoft Jet 4.0 OLE DB Provider doesn't support the IViewFilter or IViewSort OLE DB interfaces that ADO uses to filter and sort Recordset objects. For the Filter property, ADO will automatically call the client cursor engine to perform the filtering. However, for the Sort method, you must explicitly specify that you want to use the client cursor engine by setting the CursorLocation property to the adUseClient constant before you open the Recordset object. The client cursor engine will copy all of the records in the Recordset object to a cache on your local machine and will build temporary indexes in order to perform the sorting. In many cases, you will get better performance by re-executing the query used to open the Recordset object and by specifying an SQL WHERE or ORDER BY clause as appropriate.

The following example shows how to sort a Recordset object by using the ADO Sort property.

Sub SortRecordset(strDBPath As String, _
                  strTable As String, _
                  strSort As String, _
                  strDisplayField As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   
   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With
   
   Set rst = New ADODB.Recordset
   With rst
      ' Specify client-side cursor.
      .CursorLocation = adUseClient

      ' Open the table by using a scrolling Recordset object.
      .Open Source:= strTable, _
            ActiveConnection:= cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

      ' Sort the Recordset object.
      .Sort = strSort

      ' Print the records.
      Do While Not .EOF
         Debug.Print .Fields(strDisplayField).Value
         .MoveNext
      Loop

      ' Close the Recordset object.
      .Close
End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

For example, to use this procedure to sort a Recordset object opened on the Customers table in the Northwind database by the Country and Region fields and display the values in the CustomerID field, you can use a line of code like this:

SortRecordset "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "Customers","Country, Region", "CustomerID"

The SortRecordset procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

Show:
© 2014 Microsoft