The RecordCount property returns a long value that indicates the number of records in a Recordset object. RecordCount Property is not returning a correct record count?? instead it always returns a number -1? RecordCount Property returns -1 because by default the Cursor is adOpenForwardOnly. To get the exact Record Count from your RecordSet Object, you need to select the Cursor as adOpenKeyset or adOpenStatic
While establishing connection to your Database, you can set the appropriate Cursor. Syntax is same as shown in below picture.
rs.Open qry, conn, adOpenStatic
Where-
For Example: Below Code will give the Exact count of all records returned in your RecordSet Object.
Sub
Get_Count()
Dim
conn
As
New
Connection
rs
Recordset
strcon =
"Provider=Microsoft.ACE.OLEDB.12.0;"
& _
"Data Source=E:\Student.accdb;"
"User Id=admin;Password="
conn.Open (strcon)
qry =
"SELECT * FROM students"
MsgBox (rs.RecordCount)
rs.Close
conn.Close
End