Solved

Access Report - Record count for each table

Posted on 2013-12-20
9
2,104 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now