How to Count Records in VBA | Microsoft Access 2010

In a recent program that I wrote, I needed to store a count of records from a query in a variable. Since VBA and Access isn’t as intuitive as say connecting to a MySQL database with PHP, I thought I would share.

Function recordCount()
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim strSQL As String

    Set db = CurrentDb

    'Count Query - replace query with your
    'Query of Choice
    strSQL = "SELECT COUNT(*) FROM [table]"

    'Set Recordset Query
    Set rs = db.OpenRecordset(strSQL)

    'Return Record Count Variable
    recordCount = rs.Fields(0)

    'Close Connections and Reset Variables
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

I use this code in a module. To insert a module, select menu Insert>Module. Paste this code and modify.

When calling in a program you use recordCount()

Dynamically Count Records in Table

With this method, you can count records by using recordCount(“tablename”). For some reason I left this out when writing this post.

Function recordCount(selTable)  
  Dim rs As DAO.Recordset
    Dim db As Database
    Dim strSQL As String

    Set db = CurrentDb

    'Count Query - replace query with your
    'Query of Choice
    strSQL = "SELECT COUNT(*) FROM " & selTable

    'Set Recordset Query
    Set rs = db.OpenRecordset(strSQL)

    'Return Record Count Variable
    recordCount = rs.Fields(0)

    'Close Connections and Reset Variables
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Anyway, that is all I got for this post. Please feel free to comment with suggestions or improvements.