How to show the recordcount of each table in Tables view

M

MarioK

I'm using Access 2000. The Details view shows Name, Description, Modified,
Created, Type. It would be really handy to see the record count of the tables
listed without having to open them one at a time. Is there any way to do
this? Is there a Form that shows this?
 
O

Ofer

Try and use the dcount in the query
SELECT Name, Description, Modified, Created, Type, DCount("*",[Name]) AS
CountRecords
FROM TableName
 
D

Dirk Goldgar

MarioK said:
I'm using Access 2000. The Details view shows Name, Description,
Modified, Created, Type. It would be really handy to see the record
count of the tables listed without having to open them one at a time.
Is there any way to do this? Is there a Form that shows this?
Click to expand...

Ofer's suggestion of using DCount in the query is pretty good, except
that you need some source for the list of tables in the database. You
can get that from the hidden system table, MSysObjects:

SELECT
MsysObjects.Name,
DCount("*",[Name]) AS Records,
MsysObjects.DateCreate,
MsysObjects.DateUpdate
FROM MsysObjects
WHERE (
(Left$([Name],1)<>"~") AND
(Left$([Name],4)<>"Msys") AND
(MsysObjects.Type) In (1,4,6)
);
 
J

John Vinson

Ofer's suggestion of using DCount in the query is pretty good, except
that you need some source for the list of tables in the database. You
can get that from the hidden system table, MSysObjects:
Click to expand...

Just don't expect the form based on this query to open really fast, if
your tables are at all large! There's a reason this information isn't
displayed: it's volatile (if other users are in the database), and
must be calculated each time, by traversing every table's recordset.

John W. Vinson[MVP]
 
Top