More Data in Office Solutions

  • Article

 

David Shank

February 3, 2000

In last month's column, I introduced you to working with ActiveX® Data Objects (ADO) in Office solutions. In that discussion, I talked about connecting to a data source and working with recordsets.

This month, I continue the discussion by focusing on using ADO to work with data. I’ll give examples of searching, sorting, and filtering data.

ADO is part of Microsoft’s Universal Data Access (https://www.microsoft.com/data/) strategy to provide both high-performance access to relational and non-relational data and an easy-to-use programming interface that is tool- and language-independent. In Microsoft Office 2000, you will mainly use ADO to work with data programmatically. ADO supports a broader array of data sources than the Data Access Object (DAO) programming model, and is the recommended data access technology.

Working with Recordsets

Obviously, before you can work with the data in a recordset, you have to create a recordset that contains some data. In ADO, you do this using a Connection object and a Recordset object. One way to create these objects is illustrated in the first example below. Additional methods were discussed in last month's column.

Seek and Ye Shall Find

There are two ways to locate a record in a Recordset object: the Find and Seek methods. With both methods, you specify criteria to be used to locate a matching record. In general, for equivalent types of searches, the Seek method provides better performance than the Find method, because the Seek method uses an underlying index to locate the record. For this reason, you can use the Seek method only on Recordset objects that are based on a table that has an associated index.

The Find method

When you use ADO's Find method, the search always starts from the current record. The Find method has a SearchDirection ** argument that allows you to specify the search direction, and a SkipRows argument that specifies an offset from the current record from which to begin searching.

The Find method's Criteria argument is a string that contains a single field name, comparison operator, and value to use in the search. If you need to find a record based on multiple fields, you use the Filter property to create a view of the Recordset object that contains only those records that match the criteria.

If the Find method does not find a record that meets the criteria, the current record is positioned after the end of the Recordset object if you are searching forward (EOF = True), or before the beginning of the Recordset object if you are searching backward (BOF = True). You can use the BOF or EOF property as appropriate to determine whether a match was found.

The following sample code shows how to locate a record in an Access database by using the Find method.

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open "c:\Program Files\Microsoft" _
            & " Office\Office\Samples\Northwind.mdb"
    End With

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

        ' Find the first record that meets the criteria.
        .Find Criteria:= "Country='USA'", _
            SearchDirection:=adSearchForward

        ' Make sure record was found (not at end of file).
        If Not .EOF Then
            ' Work with data in found record here.
        Else
            ' Record not found
        End If
        ' Close the Recordset object.
        .Close
    End With

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

The Seek method

The ADO Seek method uses an index, and so it is a good idea to specify an index before you search. If you don't specify an index and you are working with an Access database, the Jet database engine will use the primary key index.

If you need to specify a multiple field index, you use the Visual Basic® for Applications Array function to pass those values to the KeyValues argument of the Seek method. If you only need to specify one value, it is not necessary to use the Array function.

Again, you use the BOF or EOF property (depending on the search direction) to determine whether a matching record was found.

The following sample code shows how to locate a record by using the ADO Seek method. Assume that the Connection and Recordset object variables were created just as in the previous example.

    With rst
        ' Select the index
        .Index = "PrimaryKey"

        ' Open the table by using a scrolling Recordset object.
        .Open Source:= "Order Details", _
        ActiveConnection:= cnn, _
        CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, _
        Options:=adCmdTableDirect

        ' Find the order where OrderId = 10255 
        ' and ProductId = 16.
        .Seek KeyValues:= Array(10255,16),_
            SeekOption:=adSeekFirstEQ

        If Not .EOF Then
            ' Work with data in found record here.
        End If

        ' Close the Recordset object.
        .Close
    End With

Filtering and Sorting Data

Once you have a group of records to work with, it is quite common to want to further arrange those records according to specific criteria. This is where the ADO Filter and Sort properties come in handy.

Filtering records

In ADO, the Filter property applies directly to the Recordset object on which you set the filter. The 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 sample code shows how to filter a Recordset object by using the Filter property. Again, please assume that the Connection and Recordset object variables were created just as in the first example.

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

        ' Filter the Recordset object.
        .Filter = "Country='USA' And Fax<>Null"

        Do While Not .EOF
            ' Work with record here.
            .MoveNext
        Loop

        ' Close the Recordset object.
        .Close
    End With

Sorting records

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.

The following sample code shows how to sort a Recordset object by using the Sort property. The Connection and Recordset object variables in the sample were created just as in the first example.

    With rst
        ' Specify client-side cursor.
        .CursorLocation = adUseClient

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

        ' Sort the Recordset object.
        .Sort = "Country, Region"

        Do While Not .EOF
            ' Work with record here.
            .MoveNext
        Loop

        ' Close the Recordset object.
        .Close
    End With

As an alternative to using the Sort property, you may want to consider re-executing the query used to open the Recordset object and specifying an SQL WHERE or ORDER BY clause as appropriate.

Where to Get More Info

Here are some additional resources you can use to explore ADO in Office in more depth:

David Shank is a Programmer/Writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native northwesterners still living in the northwest.