Query for record count - all tables

  • Thread starter Thread starter GD
  • Start date Start date
G

GD

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

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

THANKS!!!!!!!!
Click to expand...

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.
 
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.
 
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?

--
GD


Jerry Whittle said:
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


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

THANKS!!!!!!!!
Click to expand...
Click to expand...
 
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


GD said:
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?

--
GD


Jerry Whittle said:
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


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

THANKS!!!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top