• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

How to Count Records in VBA | Microsoft Access 2010

August 22, 2013 by Ryan McCormick 3 Comments

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.

Related

Filed Under: Microsoft Access Tagged With: count records, ms access 2010, vba

Reader Interactions

Comments

  1. tawarith says

    January 15, 2015 at 6:47 am

    Good, For the beginer VBA Code , Thank you.

    Reply
  2. Thomas Osten says

    April 17, 2018 at 10:37 pm

    Hello from Hamburg, Germany.
    Thank you for this tiny code.
    In an business DB with tasks and actions (many per task) I needed to build an myID for (new) actions like autoincrement but always starting from 1 per task. With your code I can count the actions records with same fk to tasks and use the result+1 as default value for myID when creating new actions via form.

    Reply
  3. THU DV says

    May 1, 2019 at 5:24 am

    Dear Sir

    Can you make a exemple for function “recordCount”?

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in