在Recordset对象中查询记录的办法[Access防范]
本文“在Recordset对象中查询记录的办法[Access防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
无论是 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\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"
( 译者注:假如你安装的是简体中文版要将示例中引用的呼应的字段名改成中文 )
以上是“在Recordset对象中查询记录的办法[Access防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“在Recordset对象中查询记录的办法”更多相关内容
中查找“在Recordset对象中查询记录的办法”更多相关内容