Count records of all linked tables in 2007 database

Originally Posted by samvaldez

Do not understand this one:
SELECT
Table_Name = Name,
Row_Count = DCount("*",[MSysObjects].[Name])
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
ORDER BY
Name
My tables are: FES02AVTALL, FES02GRDALL, and each after etc.

this one works but returns the total number on rows that never stop:
SELECT
(Select Count(Process) From FES02AVTALL) As FY02AVT,
(Select Count(Process) From FES02GRDALL) As FY02GRD,
(Select Count(Process) From FES03AVTALL) As FY03AVT,
(Select Count(Process) From FES03GRDALL) As FY03GRD
From FES02AVTALL, FES02GRDALL, FES03AVTALL, FES03GRDALL;


all help is appriciated.

Sam

Originally Posted by samvaldez

Okay, created a command button reads like:
Private Sub Command72_Click()

Dim tdf As DAO.TableDef

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then 'it is a Linked Table (this is green out)
Debug.Print tdf.Name & " has " & DCount("*", tdf.Name) & " Records!"
End If
Next
End Sub

I did not receive anything, have I done something wrong?

Originally Posted by samvaldez

I am working with 2007 Access.
the db i'm working in is the front end that users access. all tables are linked.

I'm i doing this correct with a command button?
Is their anything else I should do prior to copy and paste into the event of the button?

this is how command72 event reads:

Private Sub Command72_Click()

Dim tdf As DAO.TableDef

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then 'it is a Linked Table
Debug.Print tdf.Name & "[Linked] has " & DCount("*", tdf.Name) & " Records!"
Else
Debug.Print tdf.Name & "[Non-Linked] has " & DCount("*", tdf.Name) & " Records!"
End If
Next

End Sub
-------------------------

will the data open in a table?

Thanks

Originally Posted by samvaldez

Great!
Changed to Msgbox. However, it is only returning non-linked tables.

is their also a way to have all listed in one box or querry/table?

thanks for your help.

Sam