在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\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将用主键。
如果你需要从多个字段中指定值做为搜索条件,可以用A中的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
'10255and ProductID is16,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=10255and
' 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”
(译者注:如果你安装的是简体中文版要将示例中引用的相应的字段名改成中文)