Access Report - Record count for each table

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.
bhlabelleAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
bhlabelleAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sorry ... I missed the 'Report' part ... a Report would be quite different.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You could use that routine to fill a table easily enough, then build a report off the temp table.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
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
 
bhlabelleAuthor Commented:
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
 
bhlabelleAuthor Commented:
Thanks guys...figured out the rest about clearing the table, and setting the warnings to false, so as to not get the messages.
0
All Courses

From novice to tech pro — start learning today.