Solved

Access Report - Record count for each table

Posted on 2013-12-20
9
2,743 Views
Last Modified: 2013-12-26
I'm trying to come up with a simple access report that will show just two things:

1) name of each table in the database
2) count of each table in the database (number of records)

I know there is the database documenter, but I'm looking for something much simpler.

Seems easier said than done.

Also, I'm not interested in downloading a third party tool...I am hoping to just have a nice simple solution for my db.
0
Comment
Question by:bhlabelle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75
ID: 39732675
How about this:

Function mTableNameAndRecordCount()

    Dim tdef As DAO.TableDef
    For Each tdef In CurrentDb.TableDefs
        If InStr(1, tdef.Name, "MSys") = 0 Then
            Debug.Print tdef.Name & "  " & tdef.RecordCount
        End If
    Next tdef
End Function
0
 

Author Comment

by:bhlabelle
ID: 39732715
where would I put the function, or what should call the function...I assume somewhere in the report (on print?)

Sorry I'm kind of a hack at this...so this stuff has to be spelled out like I'm a child.
0
 
LVL 75
ID: 39732733
Sorry ... I missed the 'Report' part ... a Report would be quite different.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 58
ID: 39732756
You could use that routine to fill a table easily enough, then build a report off the temp table.

Jim.
0
 
LVL 58
ID: 39732760
<<I am hoping to just have a nice simple solution for my db.>>

 FYI, it's going to be fairly straight forward, but probably not as simple as your going to like.

Jim.
0
 
LVL 30

Accepted Solution

by:
hnasr earned 350 total points
ID: 39732768
Try this:
Create a table Tables_List" and use this table as a record  source for the report. Here is the code to create the Tables_list.

Private Sub cmdCheck_Click()
    DoCmd.RunSQL "Select 'tname' as TableName, 1 As TableCount into Tables_List from a  where false"
    Dim tdef As TableDef
    Dim tcount As Integer
    For Each tdef In CurrentDb.TableDefs
        tcount = DCount("*", tdef.Name)
        DoCmd.RunSQL "Insert into Tables_List Values ('" & tdef.Name & "'," & tcount & ")"
    Next
End Sub

Open in new window

0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 150 total points
ID: 39732787
Actually, not as bad as I thought!   base your report on the following query:

SELECT MSysObjects.Name,  DCount("*",[Name]) AS RecCount
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like 'MSYS*') AND ((MSysObjects.Type)=1));


Jim.
1
 

Author Comment

by:bhlabelle
ID: 39740148
hnasr - thanks for the code.  Looks like it works fine....although I had to take out the first line because it didn't know what "from a" was.  Code seems to work without it anyway.

However, is there a way to get rid of having to click OK to append to the table after each Next statement.  I have like 100 tables and I would like to not have to keep clicking each time my new table is updated.

Also, how would I clear the table at the beginning of the code?
0
 

Author Closing Comment

by:bhlabelle
ID: 39740210
Thanks guys...figured out the rest about clearing the table, and setting the warnings to false, so as to not get the messages.
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question