While working with MS Access, you may need to bind the MS Access Form with recordset. For that you have to set the valid Data access object (DAO) or ADO recordset object. In this article we are interested in count the number of records in ADO recordset. For that we are using Recordset count property. Recordset count property contains the count of number of records in database table. Basically it returns the long value that indicates the number of records in the recordset.

When we create ADO connection, we have to set the curser. Curser is for control the record navigation, data updateability. Curser is of two types, client side and server side curser. To get the valid count, firstly we have to understand ADO curser position client side curser and server side curser. If you using JET, the curser position always we on Client machine, because JET always runs on Client machine. Client side curser with an Access database adds overhead to data access, because data has been cached twice on same machine, once in JET database engine and once in curser service. But client side curser type has more advanced functionalities. Client side curser is slower than server side curser because on server side record contained in recordset are cached on server. So network traffic is significantly low but side wise for every active client server resource are consumed.

In this article we obtained the valid count, by setting curser position. To implement this firstly we have to create table with. If table has no record then record count is zero else it will count the number of records of table. When the curser position is specified by the client machine, we always get valid count as shown in Fig 1.1. It means the table has three records.

When recordset is opened, by default forward only curser mode is enable in server side. It will not return the valid count. As similar if we use dynamic curser then also, valid count is not come, because we didn't specify the curser position, it defaults to the server side. Static and keyset curser returns the actual count as shown in Fig 1.2.

VBA code for Client side records count:

Option Compare Database
Private Sub Command0_Click()
Dim rstobj As ADODB.Recordset
Dim strDataSource As String
Dim conn As ADODB.Connection
Set rstobj = New ADODB.Recordset
Set conn = CurrentProject.Connection
strDataSource = "SELECT u_id FROM Table1 ORDER BY u_name"
rstobj.CursorLocation = adUseClient
rstobj.Open strDataSource, conn
MsgBox "recordset count is " & rstobj.RecordCount, , "Client Side"
Set rstobj = Nothing
Set conn = Nothing
End Sub

VBA code for Server side records count:

Private Sub Command1_Click()
'For adOpenDynamic curser
Dim rstobj As ADODB.Recordset
Dim strDataSource As String
Dim conn As ADODB.Connection
Set rstobj = New ADODB.Recordset
Set conn = CurrentProject.Connection
strDataSource = "SELECT u_id FROM Table1 ORDER BY u_name"
rstobj.CursorLocation = adUseServer
rstobj.Open strDataSource, conn, adOpenDynamic, adLockOptimistic
MsgBox "recordset count using Dynamic Curser " & rstobj.RecordCount, , "Server Side"

'For adOpenForwardOnly curser
Dim rstobj1 As ADODB.Recordset
Dim strDataSource1 As String
Dim conn1 As ADODB.Connection
Set rstobj1 = New ADODB.Recordset
Set conn1 = CurrentProject.Connection
strDataSource1 = "SELECT u_id FROM Table1 ORDER BY u_name"
rstobj1.CursorLocation = adUseServer
rstobj1.Open strDataSource, conn, adOpenForwardOnly, adLockOptimistic
MsgBox "recordset count using Forward Curser " & rstobj1.RecordCount, , "Server Side"

'For adOpenKeyset curser
Dim rstobj2 As ADODB.Recordset
Dim strDataSource2 As String
Dim conn2 As ADODB.Connection
Set rstobj2 = New ADODB.Recordset
Set conn2 = CurrentProject.Connection
strDataSource2 = "SELECT u_id FROM Table1 ORDER BY u_name"
rstobj2.CursorLocation = adUseServer
rstobj2.Open strDataSource, conn, adOpenKeyset, adLockOptimistic
MsgBox "recordset count using Keyset Curser " & rstobj2.RecordCount, , "Server Side"

'For adOpenStatic curser
Dim rstobj3 As ADODB.Recordset
Dim strDataSource3 As String
Dim conn3 As ADODB.Connection
Set rstobj3 = New ADODB.Recordset
Set conn3 = CurrentProject.Connection
strDataSource3 = "SELECT u_id FROM Table1 ORDER BY u_name"
rstobj3.CursorLocation = adUseServer
rstobj3.Open strDataSource, conn, adOpenStatic, adLockOptimistic
MsgBox "recordset count using Static Curser " & rstobj3.RecordCount, , "Server Side"
End Sub