在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 "
' use a line of code like this:
' FindRecord _
' "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
' "Customers", "Country='
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
Else
MsgBox "Record not found"
End If
' Close the Recordset object.
.Close
End With
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
例如，用用这个过程查询“罗期文商贸”示例数据库中“客户”表的“国家”等于USA的记录，可以使用下面的代码：
FindRecord “c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”, _
“Customers”, “Country=’
(译者注：如果你安装的是简体中文版要将相应的字段名改成中文)
用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)
' 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
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
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”
(译者注：如果你安装的是简体中文版要将示例中引用的相应的字段名改成中文)
(责任编辑：admin)
- ·用DAO或ADO正确访问Access 2000
- ·Access开发网络共享版技巧（多人同时操
- ·access中ADO与DAO格式的区别和写法【总
- ·access执行操作查询的几种方法对比
- ·Access中CurrentDb().Execute 和DoCmd.
- ·[源创技巧]在ACCESS中使用代码来自动创
- ·更新访问权限 (Jet) 数据库中的 40 多
- ·【实例】ADO代码计算余额法
- ·DAO实现的子窗体记录分页显示
- ·分别使用DAO和ADO连接外部数据库和Sql
- ·怎样判断一个表是否存在于数据库中? (D
- ·处理加了密码的MDB文件
- ·谈ADO访问不同数据库的差别
- ·DAO基础(4)
- ·DAO基础(3)
- ·DAO基础(2)
