Get a list of all tables with a count of all rows 1

[synapsevampire]

Using Access 2007, but need it to work with 2003 as well.

This will get the list of tables into another table:

SELECT msysobjects.name INTO [CCP Tables]
FROM msysobjects
WHERE (((msysobjects.name) Not Like 'MSys*') AND ((msysobjects.type)=1))
ORDER BY msysobjects.name;

How do I then use this to get a list of all tables with the count of all rows?

I'm used to using Oracle and SQL Server, Access queries seem more proprietary and less SQL like.

All input appreciated.

-k

 

[synapsevampire]

Nevermind, I figured it out:

SELECT
Name,
DCount("*",[MSysObjects].[Name])
FROM
MSysObjects
WHERE
(((msysobjects.name) Not Like 'MSys*') AND ((msysobjects.type)=1))
ORDER BY
Name;

-k

 

[PHV]

A VBA way:

Dim td As TableDef
For Each td In CurrentDb.TableDefs
  If Not td.Name Like "msys*" And Not td.Name Like "~*" Then
    Debug.Print td.Name, td.RecordCount
  End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[synapsevampire]

Thanks, *PHV*

I'm more the SQL sort, but I appreciatre the effort!

-k