Record Count of Each Table 3

[Kaz888]

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

 

[JPeters]

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&quot
Set rst(2) = dbs.OpenRecordset("tblTwo&quot ' 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
------------------

 

[bdmclennan]

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

End Sub

 

[JPeters]

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
------------------

 

[bdmclennan]

Sorry,

Just noted a bug in my documentation.
tblRecordCount definition should read:
Table = text
RecordCnt = number(long)

B.

 

[MontyBurns]

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.

HTH,
Burns

 

[Kaz888]

Thankyou everyone, sorry for the delay, another project had priority.

B - Don't know where I am supposed to paste the code.

MontyBurns - I don't have a MSysObjects Table ! Could it be hidden??

Maybe I should try using Crystal Reports,
although I suppose I will face the same problem there :-(

 

[bdmclennan]

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))<>&quot;MSys&quot);

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

A fantastic example of K.I.S.S.

Cheers

Bruce

 

[Kaz888]

You are absolute Legends!!!!!!

Thankyou so much.
:-D

 

[PassingBy]

Brilliant stuff Monty - works a treat