用 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 FilesMicrosoft OfficeOfficeSamplesNorthwind.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 FilesMicrosoft OfficeOfficeSamplesNorthwind.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 FilesMicrosoft OfficeOfficeSamplesNorthwind.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 FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb”, _

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

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