[AccessD] Record Count for all tables

Watch for wordwrap here.  

Paste this in a module, and go to the immediate window.
Put in: ?GetAllTableRecordCount( 
And you'll get the option in the enum below. Select the option you want, close the parenthesis, and hit enter.  Whalla!

Public Enum RecCountType
    intTableOnly = 1
    intQueryOnly = 2
    intTableAndQuery = 3
End Enum

Function GetAllTableRecordCount(intCountType As RecCountType)
Dim rsTables As ADODB.Recordset
Dim rs As ADODB.Recordset
Set rsTables = CurrentProject.Connection.OpenSchema(adSchemaTables)
If rsTables.EOF = False Then rsTables.MoveFirst
Do Until rsTables.EOF = True
    If intCountType = intTableOnly Or intCountType = intTableAndQuery Then
        If rsTables.Fields("TABLE_TYPE") = "TABLE" Then
            Set rs = New ADODB.Recordset
            rs.Open "SELECT Count(*) FROM " & rsTables.Fields("TABLE_NAME").Value, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
            rs.MoveFirst
            Debug.Print rsTables.Fields("TABLE_NAME") & " - Record Count: " & rs.Fields(0).Value
            rs.Close
            Set rs = Nothing
        End If
    End If
    If intCountType = intQueryOnly Or intCountType = intTableAndQuery Then
        If rsTables.Fields("TABLE_TYPE") = "VIEW" Then
            Set rs = New ADODB.Recordset
            rs.Open "SELECT Count(*) FROM " & rsTables.Fields("TABLE_NAME").Value, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
            rs.MoveFirst
            Debug.Print rsTables.Fields("TABLE_NAME") & " - Record Count: " & rs.Fields(0).Value
            rs.Close
            Set rs = Nothing
        End If
    End If
    rsTables.MoveNext
Loop
rsTables.Close
Set rsTables = Nothing
End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Tuesday, March 11, 2008 10:33 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Record Count for all tables


Hello All,

I need to get a record count for all tables.  I'm using Access XP.  I thought there was a way to have the documentor output to a table...but can't seem to get past the report.  The report has a record count but I'd rather not read though 100's of pages and write it down.

Any suggestions?

Thanks,

Mark A. Matte
_________________________________________________________________
Helping your favorite cause is as easy as instant messaging. You IM, we give.
http://im.live.com/Messenger/IM/Home/?source=text_hotmail_join
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.