Solved

Access Report - Record count for each table

Posted on 2013-12-20
9
2,521 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Jim.
0
 
LVL 57
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 57

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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