Query for record count

Query for record count - all tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GD - 30 Oct 2009 18:35 GMT

Can someone help me with a query that would give me the name and number of
records for all tables in a database?

THANKS!!!!!!!!

Signature

Reply to this Message

John W. Vinson - 30 Oct 2009 19:00 GMT

>Can someone help me with a query that would give me the name and number of
>records for all tables in a database?
>
>THANKS!!!!!!!!

You could loop through the Tabledefs collection in VBA code:

Public Sub countrec()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then ' skip builtin system tables
Debug.Print tdf.Name, DCount("*", tdf.Name)
End If
Next tdf
End Sub

If they're local tables (not linked) you can use tdf.RecordCount instead of
calling DCount.

Signature

John W. Vinson [MVP]

Reply to this Message

Jerry Whittle - 30 Oct 2009 19:03 GMT

SELECT MSysObjects.Name,
CLng(DCount("*",[name])) AS NumRecords
FROM MSysObjects
WHERE (((MSysObjects.Type)=1))
ORDER BY CLng(DCount("*",[name])) DESC;

I can't take credit for the above. Notice where it says =1. That means
records within the .mdb or .accdb file. It doesn't do linked tables. I think
that 6 will do that.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Can someone help me with a query that would give me the name and number of
> records for all tables in a database?
>
> THANKS!!!!!!!!

Reply to this Message

GD - 30 Oct 2009 19:39 GMT

Thanks for the quick reply, Jerry!

When I try to use this SQL on a small db, I get the following error message:
Record(s) cannot be read; no read permission on 'MySysACEs'.

And when I click OK, I get:
Invalid use of Null.

What do these mean?

Signature

> SELECT MSysObjects.Name,
> CLng(DCount("*",[name])) AS NumRecords
[quoted text clipped - 10 lines]
> >
> > THANKS!!!!!!!!

Reply to this Message

Jerry Whittle - 30 Oct 2009 20:26 GMT

Is there user level security on that database where you need to log in with a
user name and password? If so you don't have the permissions to query those
tables. Get someone with Administrator privileges to run it.

The null error just happen due to the read error problem.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Thanks for the quick reply, Jerry!
>
[quoted text clipped - 20 lines]
> > >
> > > THANKS!!!!!!!!

Reply to this Message