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
It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.