Table Stats

[TallMan]

Good Afternoon,

I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on.

Does anyone have a query or any code that can pull back end table stats?

Thank you in advance for your time,

:banghead:Tallman:banghead:

 

[TallMan]

Pat,

Thank you for your time. The code below pulls all the tables into the tblTableinfo table, but for the linked tables it puts a -1 in the tblRecordCount field. If I could get the tblRecordCount to update correctly, this code would be perfect.

Dim dbs As DAO.Database, rst As DAO.TableDef
Set dbs = CurrentDb
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblTableInfo;"
For intI = 0 To dbs.TableDefs.Count - 1
Set rst = dbs.TableDefs(intI)
DoCmd.RunSQL "INSERT INTO tblTableInfo (tblName,tblFieldCount,tblRecordCount,tblUpdated) SELECT '" _
& rst.Name & "', '" & rst.Fields.Count & "', '" & rst.RecordCount & "', '" & rst.LastUpdated & "';" 
Next intI
DoCmd.SetWarnings True

 

[Mihail]

The code from post #27 in this thread find all linked tables in a DB (not only in the current one).
Hope you can use it.
http://bytes.com/topic/access/answers/925508-link-tables-multiple-front-end-databases-same-time

Happy New Year !!!!!!!!
to all of you

 

[TallMan]

Mihail,

I am a little confused as to where to place this code. It looks like there is a Public Function and also a private sub. Also, after reading through the thread it looks like this code re-links tables from other databases. I am trying to loop through all of my table objects, back end and front end, and gather some stats on them.

Tallman

 

[Mihail]

When I start that thread I have had 3 FEs (lets name it Fe1, Fe2 and Fe3) and more BEs (Be1, Be2... BeN) (the BEs has the same structures because are backups for the same BE at different dates). So, my problem was:
I open one of the FE: say Fe1.
This FE pointed to one of the BE: say BeK.
OK.
Now I wish that Fe1 to point to another BE: BeP.
Done (this is easy). But, when I change the Fe1 target I like to change also the target for Fe2 and Fe3 in order to point on the same BE: BeP

So, in a form, I have a control that trigger the code (I used a command button) in order to call Public Function ReConnect(BackEndPath As String) As Boolean function.
This function is, of course, in a regular module (not in a form or a report module)

Between lines 7-25 I pointed the DB variable to each of my FEs and I apply a sub that do the job: Call RelinkThisFE(DB, BackEndPath)

This sub
Private Sub RelinkThisFE(DB As Database, BackEndPath As String)
is in the same module with the previous function. It is declared as Private because is no need to apply it from anywhere (except the ReConnect function).

The code in this sub is (in my opinion) what you are looking for: code to find all linked tables (in fact wall tables that should be linked tables, because, in my context, there are not while the FE point to a "wrong" BE)

The code will not find the native tables (tables that are, at all, in your DB). Will find ONLY the linked tables because this:
If (tbl.Attributes And dbAttachedTable) Then

Hope I explain well how it works. Not for sure, because my English
If you still not understand, feel free to ask again. This will be also a help for me because you will force me to experiment (and improve) my English