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

[1] [2]  下一页