在Recordset对象中查询记录的方法

    无论是DAO还是ADO都有两种从Recordset对象中查询记录的方法：Find方法和Seek方法。在这两种方法中可以让你指定条件进行查询与其相应的记录, 一般而言，在相同条件下，Seek方法提供了比Find方法更好的性能，因为Seek方法是基于索引的。因为这个原因基本提供者必须支持 Recordset 对象上的索引，可以用Supports(adSeek) 方法确定基本提供者是否支持Seek，用Supports(adIndex) 方法确定提供者是否支持索引。（例如，OLE DB Provider for Microsoft Jet支持Seek和Index。），请将Seek方法和Index属性结合使用。如果Seek没有找到所需的行，将不会产生错误，该行将被放在Recordset 的结尾处。执行此方法前，请先将Index 属性设置为所需的索引。此方法只受服务器端游标支持。如果 Recordset对象的CursorLocation属性值为adUseClient，将不支持 Seek。只有当 CommandTypeEnum值为adCmdTableDirect 时打开Recordset对象，才可以使用此方法。 

用ADO Find方法 

DAO包含了四个“Find”方法：FindFirst,FindLast,FindNext和FindPrevious. 

DAO方法   ADO Find 方法 

下面的一个例子示范了如何用ADO Find方法查询记录：

Sub FindRecord(strDBPath As String, _

               strTable As String, _

               strCriteria As String, _

               strDisplayField As String)

    ' This procedure finds a record in the specified table by

    ' using the specified criteria.

    ' For example, to use this procedure to find records

    ' in the Customers table in the Northwind database

    ' that have "USA" in the Country field, you can

    ' use a line of code like this:

    ' FindRecord _

    '    "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _

    '    "Customers", "Country='USA'", "CustomerID" 

    Dim cnn As ADODB.Connection

    Dim rst As ADODB.Recordset   

    ' Open the Connection object.

    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 

        ' Find the first record that meets the criteria.

        .Find Criteria:=strCriteria, SearchDirection:=adSearchForward       

        ' Make sure record was found (not at end of file).

        If Not .EOF Then

            ' Print the first record and all remaining

            ' records that meet the criteria.

            Do While Not .EOF

                Debug.Print .Fields(strDisplayField).Value

                ' Skip the current record and find next match.

                .Find Criteria:=strCriteria, SkipRecords:=1

            Loop

        Else

            MsgBox "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   

End Sub 

例如，用用这个过程查询“罗期文商贸”示例数据库中“客户”表的“国家”等于USA的记录，可以使用下面的代码：

FindRecord “c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”, _

   “Customers”, “Country=’USA’”, ”CustomerID”

 (译者注：如果你安装的是简体中文版要将相应的字段名改成中文) 

用ADO Seek方法 

因为ADO Seek方法使用Index，最好是在用这个方法之前指定一个索引，可是，如果你没有指定索引，Jet database engine将用主键。

如果你需要从多个字段中指定值做为搜索条件，可以用VBA中的Array函数传递这些值到参数KeyValues中去。如果你只需要从一个字段中指定值做为搜索条件，则不需要用Array函数传递。

象Find方法一样，你可以用BOF或者EOF属性测试是否查询到记录。 

下面的一个例子显示了如何用ADO Seek方法查询记录： 

Sub SeekRecord(strDBPath As String, _

               strIndex As String, _

               strTable As String, _

               varKeyValues As Variant, _

               strDisplayField As String)

     ' This procedure finds a record by using

    ' the specified index and key values.

    ' For example, to use the PrimaryKey index to

    ' find records in the Order Details table in the

    ' Northwind database where the OrderID field is

    ' 10255 and ProductID is 16, and then display the

    ' value in the Quantity field, you can use a line

    ' of code like this:

    ' SeekRecord _

    '   "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _

    '   "PrimaryKey", "Order Details", Array(10255, 16), "Quantity"

    Dim cnn As ADODB.Connection

    Dim rst As ADODB.Recordset  

    ' Open the Connection object.

    Set cnn = New ADODB.Connection

    With cnn

        .Provider = "Microsoft.Jet.OLEDB.4.0"       

        .Open strDBPath

    End With

    Set rst = New ADODB.Recordset

    With rst

        ' Select the index used to order the

        ' data in the recordset.

        .Index = strIndex   

        ' Open the table by using a scrolling

        ' Recordset object.

        .Open Source:=strTable, _

              ActiveConnection:=cnn, _

              CursorType:=adOpenKeyset, _

              LockType:=adLockOptimistic, _

              Options:=adCmdTableDirect   

        ' Find the order where OrderId = 10255 and

        ' ProductId = 16.

        .Seek KeyValues:=varKeyValues, SeekOption:=adSeekFirstEQ   

        ' If a match is found, print the value of

        ' the specified field.

        If Not .EOF Then

            Debug.Print .Fields(strDisplayField).Value

        End If 

        ' Close the Recordset object.

        .Close

    End With

   ' Close connection and destroy object variables.

    cnn.Close   

    Set rst = Nothing

    Set cnn = Nothing   

End Sub 

例如，用主键索引查询“罗期文商贸”示例数据库中“订单明细”表的“订单编号”等于10255并且产品编号等于16的”数量”的值，可以使用下面的代码：

SeekRecord “c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”, _

   “PrimaryKey”, “Order Details”, Array(10255,16), ”Quantity”

 (译者注：如果你安装的是简体中文版要将示例中引用的相应的字段名改成中文)