Filter 和 RecordCount 属性示例 (VB)Filter and RecordCount properties example (VB)
09/18/2015
本文内容
适用于:Access 2013、Office 2013Applies to: Access 2013, Office 2013
本示例打开 Pubs 数据库 Publishers(发行商)表上的一个 Recordset。This example open a Recordset on the Publishers table in the Pubs database. 然后使用 Filter 属性将可见的记录数限制为发行商属于特定国家/地区的记录。It then uses the Filter property to limit the number of visible records to those publishers in a particular country/region. RecordCount 属性用于显示筛选和未筛选的记录集之间的差值。The RecordCount property is used to show the difference between the filtered and unfiltered recordsets.
'BeginFilterVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
' recordset variables
Dim rstPublishers As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim SQLPublishers As String
' criteria variables
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String
' open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn
' open recordset with data from Publishers table
Set rstPublishers = New ADODB.Recordset
SQLPublishers = "publishers"
rstPublishers.Open SQLPublishers, strCnxn, adOpenStatic, , adCmdTable
intPublisherCount = rstPublishers.RecordCount
' get user input
strCountry = Trim(InputBox("Enter a country to filter on (e.g. USA):"))
If strCountry <> "" Then
' open a filtered Recordset object
rstPublishers.Filter = "Country ='" & strCountry & "'"
If rstPublishers.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
' print number of records for the original recordset
' and the filtered recordset
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishers.RecordCount
MsgBox strMessage
End If
End If
' clean up
rstPublishers.Close
Cnxn.Close
Set rstPublishers = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstPublishers Is Nothing Then
If rstPublishers.State = adStateOpen Then rstPublishers.Close
End If
Set rstPublishers = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndFilterVB
备注
[!注释] 如果知道要选择的数据,通过这种方式打开 Recordset 通过比使用 SQL 语句效率更高。本示例演示如何仅创建一个 Recordset 并获取特定国家/地区的记录。When you know the data you want to select, it's usually more efficient to open a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain records from a particular country/region.
'BeginFilter2VB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
Dim rstPublishers As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strSQLPublishers As String
Dim strCnxn As String
' open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn
' open recordset with criteria from Publishers table
Set rstPublishers = New ADODB.Recordset
strSQLPublishers = "SELECT * FROM publishers WHERE Country = 'USA'"
rstPublishers.Open strSQLPublishers, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
' print recordset
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & rstPublishers!country
rstPublishers.MoveNext
Loop
' clean up
rstPublishers.Close
Cnxn.Close
Set rstPublishers = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstPublishers Is Nothing Then
If rstPublishers.State = adStateOpen Then rstPublishers.Close
End If
Set rstPublishers = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndFilter2VB