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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Vince

    counting records in query

    Vince, Nov 21, 2003, in forum: Microsoft Access
    Replies:
    3
    Views:
    472
    Van T. Dinh
    Nov 21, 2003
  2. Guest
    Replies:
    5
    Views:
    363
    Douglas J. Steele
    Feb 22, 2005
  3. Guest
    Replies:
    2
    Views:
    312
    Guest
    Jun 8, 2005
  4. Guest

    Counting Records in a Report or Query

    Guest, Apr 3, 2006, in forum: Microsoft Access
    Replies:
    11
    Views:
    494
    Al Camp
    Apr 4, 2006
  5. Kelii
    Replies:
    4
    Views:
    3,333
    Kelii
    Feb 5, 2007
  6. johan
    Replies:
    2
    Views:
    3,532
    johan
    Jun 28, 2008
  7. Counting certain records via VBA

    , May 7, 2009, in forum: Microsoft Access
    Replies:
    1
    Views:
    325
    TonyN
    May 21, 2009
  8. merry_fay

    Code for counting number of records in query

    merry_fay, Dec 3, 2009, in forum: Microsoft Access
    Replies:
    7
    Views:
    405
    David W. Fenton
    Dec 4, 2009
Loading...