Record Count Variation

[Ray S.]

Twice a day I run a series of queries that re-make tables full of financial
transaction records for various segments of our enterprise. For various
reasons I can't query the data source directly, so I run analysis and queries
against the tables that are re-made. I'd like to use some simple code to
return the count of records in each of those tables just to confirm that I've
actually got new data after "updating" (not update queries). I've looked at
answers posted to record counting questions and can figure how to do it for
one table, but I'd like to get the record count for, let's say, five
different tables at once. Any help?

 

[Golfinray]

I usually try to do counts or sums one query at a time. It is just much
simpler for me to do it that way. Run 5 queries, count in each. Now bring the
five queries into one query to get the complete results.

 

[Ray S.]

OK, if there just is no easier way, I can write the several queries and get
the count in each, but I really wanted to do it in VBA because I also had the
idea to get the date and time at which the last "update" was done and I don't
have a clue how to do that with a query.

 

[John W. Vinson]

OK, if there just is no easier way, I can write the several queries and get
the count in each, but I really wanted to do it in VBA because I also had the
idea to get the date and time at which the last "update" was done and I don't
have a clue how to do that with a query.

Access doesn't record when the update was done in any case, so you will need
to somehow keep track of it yourself. You could use simply DCount("*",
"[tablename]") in your code to count all records in the table; or if you're
running Append or MakeTable queries, use the Execute method on a querydef
object and check the querydef's RecordsAffected property after running it.

 

[Dale Fye]

If they are always the same table names, you could create a query that does
something like:

SELECT Now() as WhenRun, T.Source, T.RecCount
FROM (
SELECT "Table1" as Source, COUNT(*) as RecCount
FROM Table1
UNION ALL
SELECT "Table2" as Source, COUNT(*) as RecCount
FROM Table2
UNION ALL
SELECT "Table3" as Source, COUNT(*) as RecCount
FROM Table3
) as T

That would give you the Date/Time the counts were taken, the table name, and
the record count. You could even make this into an insert query so that you
could keep track of consecutive counts in a single table

HTH
Dale