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

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