Number of records in each table - query required 3

[basement131]

i have a MS access 2003 database with 120 tables.
rather than checking each one to see how many records are in each i would like to run a query that would tell me just that.
the table name and the number of records within.


any help much appreciated.


Your help & assistance today will be passed on to others tomorrow.

 

[fneily]

Here's a sub you can use:

Sub RecNum()
Dim T As TableDef, Tablelist
For Each T In CurrentDb.Tabledefs
If T.Attributes = 0 Then
Tablelist = Tablelist & T.Name _
& " - " & T.RecordCount & " records" & Chr(13)
End If
Next
MsgBox TableList
End Sub

 

[fneily]

Let me explain further: create a command button on a form. Then on the OnClick event of the command button put:
Private Sub Command0_Click()
RecNum
End Sub

Then copy the following below it:
Sub RecNum()
Dim T As TableDef, Tablelist
For Each T In CurrentDb.TableDefs
If T.Attributes = 0 Then
Tablelist = Tablelist & T.Name _
& " - " & T.RecordCount & " records" & Chr(13)
End If
Next
MsgBox Tablelist
End Sub

Then when you click on the command button in Form view, you'll get a list in a message box of the tables and record numbers.

 

[Eupher]

Hiya, basement131,

fneily's technique is on the mark. If you really require a query, you'll need to use a similar idea in a function that returns a value, which you can call as an expression in your query. So create a function in a module, like this:

Public Function MyRecCount(TName As String) As Integer
    MyRecCount = CurrentDb.TableDefs(TName).RecordCount
End Function

Then run a query against the hidden MSysObjects table, calling the function in your query's SQL, something like this:

SELECT MSysObjects.Name, MyRecCount(MSysObjects!Name) AS [# of Records]
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

HTH,

Ken S.

 

[PHV]

Ken, I'd use Long instead of Integer as return type for MyRecCount.

 

[Eupher]

PHV,

Yes, quite right, my goof - if the table contained more than 32K records, the function would either raise an error or return an incorrect result.

basement131, alter the function as indicated:

Public Function MyRecCount(TName As String) As [red][b]Long[/b][/red]
    MyRecCount = CurrentDb.TableDefs(TName).RecordCount
End Function

Thanks for the catch, PHV.

Ken S.

 

[basement131]

Problem Solved.
Thanks Fneily - I tried yours however the popup box seems limited to 57 tables that will show and i could not figure out how to print the box.

Thanks Eupher (along with PHV's comment), i was able to get exactly as i needed. I must say that given my lack of understnading of how to use modules and limited understanding of how to queryies in sql view, you were able to explain and provide just what i needed the first go round.

thank you very much.

I now know why i continue to come back to this website for assistance. i am always provided with a good answer to my problem.

Ian.

Your help & assistance today will be passed on to others tomorrow.

 

[MichaelRed]

... beware of the evils of linked tables using this ... "foregin" tables are not "Type" 1, but are 6. further troubles await the unwary evenpast this hurdle, as the tables do not (propsely) return the recordcount using the tabledef object in the "FE" db ... happy hunting through the remainder ...



MichaelRed

 

[Eupher]

MichaelRed,

Thanks for the insights. What would be a better method? Opening/populating a recordset to get the record count? Would it be better to use the Attributes property to exclude system objects?

Ken S.

 

[MichaelRed]

There are a couple of approaches. I have (just for the convenience of doing it quickly) juat opened the tables and gotten the record count. I THINK it would be a 'better' soloution to add the "BE" database to the current app and then use the tdf.recordcount like you have already done. Unfortunatly, the next little issue to rear it'e ugliness is that there may be tables in the "BE" which are not linked into the FE, so you need to start with the question as to wheather you want to count these records or not. To exclude them, you would need to use MSysObjects from the FE and check wheather they are linked.

Going down the slippery slope of "PC" (Programmiing Correctness?), it is then possible to have more than one "BE" referenced ... so the choice(s) have their own issues.

Then ... consider the why would we (anyone?) want a raw recordcount? Perhaps from the purely academic curosity perspective it is 'interesting'. BUt WHAT is the real world use?

My interest is in generating some analysis to help identify why the application performance is beginning to noticably degrade. To do that, the record count is not all that useful, as the BE causing performance degradation comes (more) from the total transfer required/accomplished for an operation. This requires not the record count, but (for the Jet dbengine) the record count * the record length. The record length is the sum of the field lengths (INCLUDING the ACTUAL size of each Memo field) as well as the fields included in the indicies.

Of course the next step is to realize (recognize) that this only reveals the 'bucket' size for the individual table-type recordset. Actual transfers (for the ubiquitous Jet db engine) is always the cartisean join of the tables referenced in a query ... this value is the MULTIPLICATION of the sizes of the individual tables plus some additional amount.

Beyond this, the prospects of actually using the results set seem to be quite limited:
[tab]Give the generic Net Cop a massive cornary.
[tab]Browbeat your bos into springing for SQL Server (and Hardware to support it)
[tab]See if there are any possible strategies to minimize the (all to obvious) problems in using tables of 25MBytes as one of the central items in a highly active multiuser (Jet db engine based) application
[tab]motivation to seek other means of support ...

Hope for the best for the rest of you.




MichaelRed

 

[fneily]

Mr. Red
From your dissertation, it sounds that you may agree that in a multiuser environment, the way to go would be MSDE (Project) for the queries are processed on the server and only moves the resulting data to the client.

 

[MichaelRed]

... duh ... but this project was started >>~ 10 Years ago, I only got involved 'yesterday'. I keep thinking about swamps and drainage ... betwen bouts of aligator infestation.

The 'real' soloution is probably to stop all but the most critical bug fix work here and go to (your favorite) .Net technology with (your choice) of industrial strength database engine.

For most readers in this arena, that would be VB.Net and SQL Server (although MSDE would certainly be O.K for development if you can get MS.MMC so that T-SQL is available to help wend your way through the query conversion(s).

At 50MBytes of FE with over 100 tables, conversion is either lifetime job security or a death sentance at the end of an infinity of solitary confinement.






MichaelRed

 

[TheAceMan1]

To All . . .

Here's what I've used in several split db's and before I go on lets be clear about the [blue]Types[/blue] field in [blue]MSysObjects[/blue]:

[blue]Type1: Local Tables
Type6: Linked Tables[/blue]

[ol][li]Place the following function in a [blue]module[/blue] in the [blue]modules[/blue] window:

[blue]Public Function TblRecCnt(tblName As String) As Long
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT [b]Count[/b](*) AS Cnt " & _
         "FROM [" & tblName & "];"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   TblRecCnt = rst!Cnt
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

[/li]
[li]Place the following [blue]SQL[/blue] in the [blue]SQL view[/blue] of a new query:

[blue]SELECT Name, TblRecCnt([Name]) AS RecCnt
FROM MSysObjects
WHERE (((Name) Not Like "MSys*") AND ((Type)=1 Or (Type)=6))
ORDER BY MSysObjects.Name;[/blue]

[/li]
[li][blue]Run the query.[/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[PHV]

Hi the Ace.
I suggest a minor improvement (for completeness):
WHERE Name Not Like 'MSys*' AND Type In (1,4,6)

And for the lazzy people, a version without VBA to write ;-)
SELECT Name, DCount('*',[Name]) AS RecCnt
FROM MSysObjects
WHERE Name Not Like 'MSys*' AND Type In (1,4,6)
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

Howdy PHV . . .

Its [blue]poetry in motion![/blue]

Type4 is?

See Ya! . . . . . .

 

[PHV]

Type4 is linked table to non Jet/ISAM database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

Ah, PH, you did it again... *sigh*
;-)

Ken S.