Query for table records

[WAVP375]

Is there a limit to the number of tables permitted to count there records in a single query.
As a control I like to take a record count in all tables before I do a significant amount of activity,


especially when I am making program changes. When I add the fourth table to the query Access
locks up. The only way to get out of it is to cancel Access in the task manager.

[Micron]

what does "locks up" mean - simply that it's not responding? Error messages? Perhaps all you need to do is wait for the query to execute.
There are limits to the number of fields that tables or queries can have; not so much limitation on records, apart from the size of the tables, db or query recordset, which is, IIRC around 2Gb.
I would give the query time to run and see what happens. The limitation may be more related to the complexity of what you're asking for in the query. Things like aggregate functions and lack of field indexes contribute to slow running queries.

Not sure I agree with the notion of polling tables for size before doing anything. This means the entire table is being mined, but for what reason? If you want to know how many records are updated or appended, look into the RecordsAffected property.

[JoeM]

As a control I like to take a record count in all tables before I do a significant amount of activity,

How exactly are you doing this?
If you are adding a bunch of tables to a single query, and not joining on them, you will be creating a Cartesian product, which could get HUGE!
(see: https://www.databasejournal.com/feat...-MS-Access.htm)
That is not really the best way to get a record count of each table.

If you really wanted to get the record count for each table in a single query, maybe look at creating a Union Query, which you will need to write in SQL View directly as opposed to Design View, through it us pretty easy.
It will look something like this:

SELECT "Table1" AS TableName, Count(ID) AS RecCount
FROM Table1
UNION
SELECT "Table2" AS TableName, Count(ID) AS RecCount
FROM Table2
UNION
SELECT "Table3" AS TableName, Count(ID) AS RecCount
FROM Table3;

Just note that the "ID" is the name of any field that can be found in that particular Table.
So you will need to change the Table names and this field for each table. And you can keep adding more tables on with more UNION statements.