該范例使用 Filter 屬性打開一個(gè)新的 Recordset,它基于適用于已有 Recordset 的指定條件。它使用 RecordCount 屬性顯示兩個(gè) Recordsets 中的記錄數(shù)。該過程運(yùn)行時(shí)需要 FilterField 函數(shù)。
Public Sub FilterX()
Dim rstPublishers As ADODB.Recordset
Dim rstPublishersCountry As ADODB.Recordset
Dim strCnn As String
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String
' 使用出版商表中的數(shù)據(jù)打開記錄集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable
' 充填記錄集。
intPublisherCount = rstPublishers.RecordCount
' 獲得用戶輸入。
strCountry = Trim(InputBox( _
"Enter a country/region to filter on:"))
If strCountry <> "" Then
' 打開已篩選的記錄集對(duì)象。
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry)
If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country/region."
Else
' 打印原始記錄集和已篩選記錄集對(duì)象的記錄數(shù)。
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishersCountry.RecordCount
MsgBox strMessage
End If
rstPublishersCountry.Close
End If
End Sub
Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
' 在指定的記錄集對(duì)象上設(shè)置篩選操作并打開一個(gè)新的記錄集對(duì)象。
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function
注意 當(dāng)已知要選擇的數(shù)據(jù)時(shí),使用 SQL 語句打開 Recordset 通常更為有效。該范例說明了如何創(chuàng)建唯一的 Recordset 并從特定的國家(地區(qū))獲得記錄。
Public Sub FilterX2()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
' 使用出版商表中的數(shù)據(jù)打開記錄集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "SELECT * FROM publishers " & _
"WHERE Country = 'USA'", strCnn, , , adCmdText
' 打印記錄集中的當(dāng)前數(shù)據(jù)。
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & _
rstPublishers!country
rstPublishers.MoveNext
Loop
rstPublishers.Close
End Sub