By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Count records of all linked tables in 2007 database

P: 4
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
Jul 30 '09 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,679
@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.
Expand|Select|Wrap|Line Numbers
  1. Dim tdf As DAO.TableDef
  2.  
  3. For Each tdf In CurrentDb.TableDefs
  4.   If Len(tdf.Connect) > 0 Then    'it is a Linked Table
  5.     Debug.Print tdf.Name & " has " & DCount("*", tdf.Name) & " Records!"
  6.   End If
  7. Next
Aug 1 '09 #2

P: 4
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?
Aug 3 '09 #3

ADezii
Expert 5K+
P: 8,679
@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:
Expand|Select|Wrap|Line Numbers
  1. Dim tdf As DAO.TableDef
  2.  
  3. For Each tdf In CurrentDb.TableDefs
  4.   If Len(tdf.Connect) > 0 Then    'it is a Linked Table (this is green out)
  5.     Debug.Print tdf.Name & "[Linked] has " & DCount("*", tdf.Name) & " Records!"
  6.   Else
  7.     Debug.Print tdf.Name & "[Non-Linked] has " & DCount("*", tdf.Name) & " Records!"
  8.   End If
  9. Next
P.S. - I intentionally did not filter out the System Tables to keep things simple,
Aug 3 '09 #4

P: 4
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
Aug 3 '09 #5

ADezii
Expert 5K+
P: 8,679
@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.
Aug 3 '09 #6

P: 4
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
Aug 3 '09 #7

ADezii
Expert 5K+
P: 8,679
@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.
Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name,IIF([Type]=4,'Linked','Non-Linked') As Status,  
  2. DCount("*",[MSysObjects].[Name]) AS[Record Count]
  3. FROM MSysObjects
  4. WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND 
  5. ((MSysObjects.Type) In (1,4,6)))
  6. ORDER BY MSysObjects.Name;
Sample OUTPUT for my PC:
Expand|Select|Wrap|Line Numbers
  1. Name          Status                    Record Count
  2. Bkup_Ctrl    Non-Linked                 1
  3. Categories    Non-Linked                 8
  4. Customers    Non-Linked               93
  5. dbo_authors    Linked               23
  6. dbo_titles    Linked                               18
  7. Employee_ImportErrors    Non-Linked    225
  8. Employees    Non-Linked    9
  9. Employees2    Non-Linked    1
  10. Horatio    Non-Linked    10
  11. Order Details    Non-Linked    2158
  12. Orders    Non-Linked    856
  13. Products    Non-Linked    77
  14. ServiceRec    Non-Linked    10
  15. Shippers    Non-Linked    3
  16. Suppliers    Non-Linked    29
  17. Table1    Non-Linked    6
  18. TBL_DATA    Non-Linked    4
  19. tbl_EXTRACT_070709    Non-Linked    1
  20. tblAccount    Non-Linked    9
  21. tblCrop    Non-Linked    5
  22. tblDOS    Non-Linked    4
  23. tblEMailAddress    Non-Linked    4
  24. tblEmployee    Non-Linked    225
  25. tblKeywords    Non-Linked    4
  26. tblOrders    Non-Linked    9
  27. tblPrefixes    Non-Linked    3
  28. tblReports    Non-Linked    2
  29. tblSales    Non-Linked    3
  30. tblSuffixes    Non-Linked    4
  31. tblTest    Non-Linked    6
  32. tShipDetails    Non-Linked    16
P.S. - Sorry, simply do not have the time to properly Format the results.
Aug 3 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.