The database I am using has 113 tables.
I am looking for a simple way to get a record count for each and every table, and print as a Report.
eg CL_Transaction = 13550 Records
GL_Transaction = 25946 Records
In your Report's On_Load Property or whatever ... have it either call this function or just do this.
Do something like this for each of the 113 tables...
Function TableCount()
Dim i(1 to 113) As Integer
Dim dbs As Database
Dim rst(1 To 113) As Recordset
Set dbs = CurrentDb
Set rst(1) = dbs.OpenRecordset("tblOne"
Set rst(2) = dbs.OpenRecordset("tblTwo" ' and so on and so forth
With rst(1)
i(1) = .RecordCount
End With
With rst(2)
i(2) = .RecordCount
End With
End Function
Now play around with it and set your boxes control sources to the i(1) through i(113) ... or you can setup a new table and just update that recordset by placing the i values into a field by looping the .AddNew and .Update until we run out of I's. Just some ideas.
-Josh
------------------
-JPeters Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
You may find this helpful. Run it before you open your report and base your report on a table tblRecordCount.
Keep your powder dry.
B.
Public Sub subTableRecordCnt()
'############################
'NOTES
'----------------------------
'Created a table tblRecordCount and base your report on it
'Table Definition of tblRecordCount
' Name = string
' RecordCnt = number
'
'Steps
'1 - Clear tblRecordCount
'2 - Locate all internal(1) and Access Linked(6) Tables
'3 - For each table do a record Count
'4 - Store count in tblRecordCount
'############################
Dim db As DATABASE
Dim rsTables As DAO.Recordset ' LIST OF TABLES TO COUNT
Dim rsCount As DAO.Recordset ' TABLE TO COUNT
Dim rsStore As DAO.Recordset ' Area to Store
Dim strSQL As String
strSQL = "SELECT Name "
strSQL = strSQL & "FROM MSysObjects "
strSQL = strSQL & "WHERE MSysObjects.Name Not In ('tblRecordCount') "
strSQL = strSQL & "AND MSysObjects.Type In (1,6) "
strSQL = strSQL & "AND Left([Name],4) Not In ('MSys')"
'-------------------
'STEP 1
'Clear Record Source
'-------------------
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblRecordCount"
DoCmd.SetWarnings True
Set db = CurrentDb
'-------------------
'STEP 2
'Locate all Tables
'-------------------
Set rsTables = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set rsStore = db.OpenRecordset("tblRecordCount", dbOpenDynaset)
With rsTables
Do While Not .EOF
'-------------------
'STEP 3
'For each table do a record Count
'-------------------
Set rsCount = db.OpenRecordset(!Name, dbOpenSnapshot)
If rsCount.EOF = False Then rsCount.MoveLast
'-------------------
'STEP 4
'Store count in tblRecordCount
'-------------------
With rsStore
.AddNew
!Table = rsTables!Name
!RecordCnt = rsCount.RecordCount
.Update
End With
.MoveNext
Loop
End With
bdmclennan,
I like that you label your code so well. It pastes nice into Access - clearly defined labels. I need to practice this - it'll help for clearer documentation for any future developer using my applications.
Kudos,
-Josh ------------------
-JPeters Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------
i'd advise using DCount to get the recordcount from each table, rather than opening loads of recordsets. Just open a recordset of the MSysObjects table and loop through it, each time passing the name of the table into the DCount function.
Drop it into a new module. Then you can call it from anywhere.
However:
Monty has a cracking idea which I am prepared to give two thumbs up to.
Create a query which you will base your report on and paste this SQL into your query:
SELECT MSysObjects.Name,
DCount("*",[Name]) AS recordcount
FROM MSysObjects
WHERE (((MSysObjects.Type) In (1,6))
AND ((Left([name],4))<>"MSys");
This will give you a record count without having to create a new table or write any code.
Your system tables are hidden by default. to unhide in A97 go...TOOLS/OPTIONS/VIEW check SYSTEM OBJECTS
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.