Counting Records

[John C.]

I need to count the number of records in a table, can
anyone help or point me in the right direction?

 

[Michel Walsh]

Hi,


SELECT COUNT(*) FROM TableName


would do.


Hoping it may help,
Vanderghast, Access MVP

 

[John C.]

It's amazing what you can find if you look long enough:

'For Access 2000, ensure Microsoft DAO 3.6 Object Library
is enabled in VBA Editor (Tools, References)

Function MyRightRecordCount()
'For Access 97 use:
' Dim MyDB As Database
' Dim MyRS As Recordset

'For Access 2000 use:
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblname", dbOpenDynaset)
MyRS.MoveLast
MyRightRecordCount = MyRS.RecordCount
MyRS.Close
End Function

 

[Tim Ferguson]

Set MyRS = MyDB.OpenRecordset("tblname", dbOpenDynaset)

Opening a dynaset to count the records is incredibly wasteful of system
resources, as well as inconsiderate to other users who will find all the
records locked... If you have to use this method, at least use a Forward
Only Snapshot.

Then again, you just might not think it is sensible to cart an entire 350MB
table across the network when all you want to end up with is a single
integer (4 bytes). The COUNT(*) function, or its DCount() function
shortcut, allows the work to take place on the server and reduces
drastically the network traffic and resources in the workstation.

B Wishes


Tim F

 

[TC]

Opening a dynaset to count the records is incredibly wasteful of system
resources, as well as inconsiderate to other users who will find all the
records locked... If you have to use this method, at least use a Forward
Only Snapshot.

Then again, you just might not think it is sensible to cart an entire 350MB
table across the network when all you want to end up with is a single
integer (4 bytes). The COUNT(*) function, or its DCount() function
shortcut, allows the work to take place on the server and reduces
drastically the network traffic and resources in the workstation.

What's more, msoft make a few coy references to how SELECT COUNT(*) is
Rushmore optimized. Compared to SELECT COUNT(something_else), which isn't. I
believe they are referring to the following fact. The # of records in each
table, is stored & maintained in a data item in the table header (in the MDB
file). I believe that SELECT COUNT(*) will simply retrieve the data page
containing the table header, & get the count directly from there. No table
scan required, at all. Other methods (such as traversing a recordset of any
kind) will perform a full table scan.

Cheers,
TC

 

[Tim Ferguson]

What's more, msoft make a few coy references to how SELECT COUNT(*) is
Rushmore optimized. Compared to SELECT COUNT(something_else), which
isn't.

and they give different answers. COUNT(*) returns the record count, while
COUNT(Elephant) only counts the rows WHERE Elephant IS NOT NULL (have to
confess I haven't checked this so feel free to abuse me for it if I am
wrong).

All the best


Tim F

 

[TC]

and they give different answers. COUNT(*) returns the record count, while
COUNT(Elephant) only counts the rows WHERE Elephant IS NOT NULL (have to
confess I haven't checked this so feel free to abuse me for it if I am
wrong).

Sure - as intended!

But my point was that COUNT(*) is far more efficient than any other method.
COUNT(*) will not, IMO, do a full table scan. It will pull at most a few
pages over the network, regardless of the table size. Other methods
might/will pull the whole table.

Cheers,
TC