用ADO Find方法 

DAO包含了四個(gè)“Find”方法：FindFirst,FindLast,FindNext和FindPrevious. 

DAO方法   ADO Find 方法 

下面的一個(gè)例子示范了如何用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 

例如，用用這個(gè)過程查詢“羅期文商貿(mào)”示例數(shù)據(jù)庫中“客戶”表的“國(guó)家”等于USA的記錄，可以使用下面的代碼：

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

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

 (譯者注：如果你安裝的是簡(jiǎn)體中文版要將相應(yīng)的字段名改成中文) 

用ADO Seek方法 

因?yàn)?/SPAN>ADO Seek方法使用Index，最好是在用這個(gè)方法之前指定一個(gè)索引，可是，如果你沒有指定索引，Jet database engine將用主鍵。

如果你需要從多個(gè)字段中指定值做為搜索條件，可以用VBA中的Array函數(shù)傳遞這些值到參數(shù)KeyValues中去。如果你只需要從一個(gè)字段中指定值做為搜索條件，則不需要用Array函數(shù)傳遞。

象Find方法一樣，你可以用BOF或者EOF屬性測(cè)試是否查詢到記錄。 

下面的一個(gè)例子顯示了如何用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 

例如，用主鍵索引查詢“羅期文商貿(mào)”示例數(shù)據(jù)庫中“訂單明細(xì)”表的“訂單編號(hào)”等于10255并且產(chǎn)品編號(hào)等于16的”數(shù)量”的值，可以使用下面的代碼：

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

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

 (譯者注：如果你安裝的是簡(jiǎn)體中文版要將示例中引用的相應(yīng)的字段名改成中文)

(責(zé)任編輯：admin)