Counting Records in a Recordset

The RecordCount property of the Recordset object returns the number of records in the recordset. Suppose you have the following procedure:

Sub TestRecordCount()

Dim cnCh5 As ADODB.Connection Dim rsContacts As ADODB.Recordset Dim strConnection As String

'specify the connection string for connecting to the database strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.mdb;"

'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection

'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts

.CursorType = adOpenStatic

'open the recordset based on tblContacts table using the existing connection .Open "tblContacts", cnCh5 End With

'print the number of records to the Immediate Window

Debug.Print "The total number of records is: " & rsContacts.RecordCount

'close the database connection cnCh5.Close

'set the recordset and connection to nothing Set rsContacts = Nothing Set cnCh5 = Nothing

End Sub

After the recordset is created, the code determines how many records are in the recordset using the RecordCount property. The number of records is then displayed in the Immediate Window as shown in Figure 5.8.

If you remove the line specifying the CursorType, then a -1 will be returned as the record count since the recordset will default to a forward-only recordset.

Immediate

Te3tRecordCount

The total dumber of records 13: 2

0 0

Post a comment