Counting records in a Query using VBA

Discussion in 'Microsoft Access' started by john.constantine@IFF.COM, Mar 26, 2009.

  1. Guest

    I have looked through the help files but can't find anything -what I
    want is the query equivalant to

    CurrentDb.TableDefs("Mytable").RecordCount

    Must be easy but has me stumped
     
    , Mar 26, 2009
    #1
    1. Advertisements

  2. Hi john
    Assuming the table has a primary key, you can go

    Dim lngCount as Long
    lngCount = DCount("*", "NameOfTable")

    Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


    <> wrote in message
    news:...
    >I have looked through the help files but can't find anything -what I
    > want is the query equivalant to
    >
    > CurrentDb.TableDefs("Mytable").RecordCount
    >
    > Must be easy but has me stumped
     
    Jeanette Cunningham, Mar 26, 2009
    #2
    1. Advertisements

  3. Mr B Guest

    John,

    If you are wanting a quick count of the records, look into then DCount
    function.

    Dim varTmp
    varTmp = DCount("IdFieldInTable", "TableName")

    If there is some reason you need to actually us a query, post back and we
    will try to help.
    --
    HTH

    Mr B
    askdoctoraccess dot com


    "" wrote:

    > I have looked through the help files but can't find anything -what I
    > want is the query equivalant to
    >
    > CurrentDb.TableDefs("Mytable").RecordCount
    >
    > Must be easy but has me stumped
    >
     
    Mr B, Mar 26, 2009
    #3
  4. Hans Up Guest

    wrote:
    > I have looked through the help files but can't find anything -what I
    > want is the query equivalant to
    >
    > CurrentDb.TableDefs("Mytable").RecordCount


    SELECT Count(*) AS num_records FROM Mytable;
     
    Hans Up, Mar 26, 2009
    #4
  5. You were close. The fastest way to get the record count of a table is:

    CurrentDb.OpenRecordset("Mytable").RecordCount

    The thing is, here it HAS to be a LOCAL table to return the correct record
    count. If it is a linked table or a query, it will always return 1 as the
    record count.

    The fastest way to get the record count of a linked table is this:

    Dim db As DAO.Database
    Set db = OpenDatabase("c:\My Documents\LinkedDatabase.mdb")
    db.OpenRecordset("Mytable").RecordCount

    I suppose you could put it all on one like like this:
    OpenDatabase("c:\My
    Documents\LinkedDatabase.mdb").OpenRecordset("Mytable").RecordCount


    On a table with over 7 million records, both of these returned the record
    count in 1 second or less. Every other method mentioned will be an order of
    magnitude slower. (Disclaimer: I didn't check ADO methods.)


    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com
    Want answers to your Access questions in your Email?
    Free subscription:
    http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



    <> wrote in message
    news:...
    >I have looked through the help files but can't find anything -what I
    > want is the query equivalant to
    >
    > CurrentDb.TableDefs("Mytable").RecordCount
    >
    > Must be easy but has me stumped
     
    Roger Carlson, Mar 26, 2009
    #5
  6. UpRider Guest

    John, use this function:

    Function fcnCountQueryRecs(qQuery) As Long
    fcnCountQueryRecs = DCount("*", qQuery)
    End Function

    qQuery is a string, the query name.

    HTH, UpRider

    <> wrote in message
    news:...
    >I have looked through the help files but can't find anything -what I
    > want is the query equivalant to
    >
    > CurrentDb.TableDefs("Mytable").RecordCount
    >
    > Must be easy but has me stumped
     
    UpRider, Mar 26, 2009
    #6
  7. Bookmarked for future reference

    Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


    "Roger Carlson" <> wrote in message
    news:...
    > You were close. The fastest way to get the record count of a table is:
    >
    > CurrentDb.OpenRecordset("Mytable").RecordCount
    >
    > The thing is, here it HAS to be a LOCAL table to return the correct record
    > count. If it is a linked table or a query, it will always return 1 as the
    > record count.
    >
    > The fastest way to get the record count of a linked table is this:
    >
    > Dim db As DAO.Database
    > Set db = OpenDatabase("c:\My Documents\LinkedDatabase.mdb")
    > db.OpenRecordset("Mytable").RecordCount
    >
    > I suppose you could put it all on one like like this:
    > OpenDatabase("c:\My
    > Documents\LinkedDatabase.mdb").OpenRecordset("Mytable").RecordCount
    >
    >
    > On a table with over 7 million records, both of these returned the record
    > count in 1 second or less. Every other method mentioned will be an order
    > of magnitude slower. (Disclaimer: I didn't check ADO methods.)
    >
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    >
    > <> wrote in message
    > news:...
    >>I have looked through the help files but can't find anything -what I
    >> want is the query equivalant to
    >>
    >> CurrentDb.TableDefs("Mytable").RecordCount
    >>
    >> Must be easy but has me stumped

    >
    >
     
    Jeanette Cunningham, Mar 26, 2009
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Vince

    counting records in query

    Vince, Nov 21, 2003, in forum: Microsoft Access
    Replies:
    3
    Views:
    200
    Van T. Dinh
    Nov 21, 2003
  2. Guest
    Replies:
    2
    Views:
    191
    Guest
    Jun 8, 2005
  3. Guest

    Counting Records in a Report or Query

    Guest, Apr 3, 2006, in forum: Microsoft Access
    Replies:
    11
    Views:
    327
    Al Camp
    Apr 4, 2006
  4. Kelii
    Replies:
    4
    Views:
    2,461
    Kelii
    Feb 5, 2007
  5. Counting certain records via VBA

    , May 7, 2009, in forum: Microsoft Access
    Replies:
    1
    Views:
    157
    TonyN
    May 21, 2009
Loading...

Share This Page