[AccessD] Record Count for all tables

Thanks to everyone for the feedback...

I guess my confusion was from A97...where you could use the documentor...and then under FILE you had the option of 'Save as Table'...this is not available in XP?  'Save As' is grayed out?

Thanks,

Mark A. Matte


> Date: Tue, 11 Mar 2008 11:24:12 -0500
> From: DWUTKA at marlow.com
> To: accessd at databaseadvisors.com
> Subject: Re: [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.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Shed those extra pounds with MSN and The Biggest Loser!
http://biggestloser.msn.com/