|
The current number of records in a Recordset object.
Returns a Long value.
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records. Reading the RecordCount property on a closed recordset causes an error.
If the Recordset object supports approximate positioning or bookmarks-that is, ADO Recordset Object Supports Method (adApproxPosition) or Supports (adBookmark), respectively, returns True-this value will be the exact number of records in the recordset regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.
This Visual Basic example uses the Filter property to open a new recordset based on a specified condition applied to an existing recordset. It uses the RecordCount property to show the number of records in the two recordsets. The FilterField function is required for this procedure to run.
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
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable
` Populate the Recordset.
intPublisherCount = rstPublishers.RecordCount
` Get user input.
strCountry = Trim(InputBox( _
"Enter a country to filter on:"))
If strCountry <> "" Then
` Open a filtered Recordset object.
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry)
If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
` Print number of records for the original
` Recordset object and the filtered Recordset
` object.
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
` Set a filter on the specified Recordset object and then
` open a new Recordset object.
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function
Note
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.
Public Sub FilterX2()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "SELECT * FROM publishers " & _
"WHERE Country = 'USA'", strCnn, , , adCmdText
` Print current data in recordset.
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & _
rstPublishers!country
rstPublishers.MoveNext
Loop
rstPublishers.Close
End Sub
Copyright 2002 Sun Microsystems, Inc. All rights reserved. Legal Notice.