Count records of all linked tables in 2007 database

[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

[ADezii]

@samvaldez
Hello Sam, I wrote some very simple code for you that will list each Linked Table's Name along with the Number of Records contained within.

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 & " has " & DCount("*", tdf.Name) & " Records!"
  End If
Next

[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 recive anything, have I done something wrong?

[ADezii]

@samvaldez

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

No, it simply means that you have no Linked Tables. To verify this, expand the previous code to:

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 & "[Linked] has " & DCount("*", tdf.Name) & " Records!"
  Else
    Debug.Print tdf.Name & "[Non-Linked] has " & DCount("*", tdf.Name) & " Records!"
  End If
Next

P.S. - I intentionally did not filter out the System Tables to keep things simple,

[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

[ADezii]

@samvaldez
I just realized that I may have made an assumption that I should not have, and that is that you know how to access the DEBUG/Immediate Window. After executing this code, press the CTRL + G Key Combination to Open the DEBUG/Immediate Window. Another Option is to replace the Debug.Print Statement with Msgbox.

[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

[ADezii]

@samvaldez
Copy and Paste the following SQL Statement into the SQL View of the Query Design Window and everything that you request will be in the Form of a Query.

SELECT MSysObjects.Name,IIF([Type]=4,'Linked','Non-Linked') As Status,  
DCount("*",[MSysObjects].[Name]) AS[Record Count]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND 
((MSysObjects.Type) In (1,4,6)))
ORDER BY MSysObjects.Name;

Sample OUTPUT for my PC:

Name          Status                    Record Count
Bkup_Ctrl    Non-Linked                 1
Categories    Non-Linked                 8
Customers    Non-Linked               93
dbo_authors    Linked               23
dbo_titles    Linked                               18
Employee_ImportErrors    Non-Linked    225
Employees    Non-Linked    9
Employees2    Non-Linked    1
Horatio    Non-Linked    10
Order Details    Non-Linked    2158
Orders    Non-Linked    856
Products    Non-Linked    77
ServiceRec    Non-Linked    10
Shippers    Non-Linked    3
Suppliers    Non-Linked    29
Table1    Non-Linked    6
TBL_DATA    Non-Linked    4
tbl_EXTRACT_070709    Non-Linked    1
tblAccount    Non-Linked    9
tblCrop    Non-Linked    5
tblDOS    Non-Linked    4
tblEMailAddress    Non-Linked    4
tblEmployee    Non-Linked    225
tblKeywords    Non-Linked    4
tblOrders    Non-Linked    9
tblPrefixes    Non-Linked    3
tblReports    Non-Linked    2
tblSales    Non-Linked    3
tblSuffixes    Non-Linked    4
tblTest    Non-Linked    6
tShipDetails    Non-Linked    16

P.S. - Sorry, simply do not have the time to properly Format the results.