Solved

Access Report - Record count for each table

Posted on 2013-12-20
9
2,226 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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