Table size or recordcount

[John]

Hi

I am trying to find the size of each table or if not at least the number of
records to try to figure out which tables are taking most space in the db.
Is there a script I can run that will table and its size/recordcount?

Thanks

Regards

 

[Arvin Meyer [MVP]]

Here's one that I just wrote and test that will count all the records in all
your tables:

Public Sub CountRecords()
' © Arvin Meyer 12/13/2009
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim i As Integer

Set db = CurrentDb

For i = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(i)
If Mid(tdf.name, 2, 3) <> "sys" Then

Set rst = db.OpenRecordset(tdf.name, dbOpenSnapshot)
rst.MoveLast
Debug.Print tdf.name, rst.RecordCount
rst.Close
Set rst = Nothing

End If
Next i

Set tdf = Nothing
Set db = Nothing
MsgBox "Done!"

End Sub

 

[Douglas J. Steele]

Or try the following query:

SELECT Name, DCount("*", "[" & Name & "]") As RecordCount
FROM MSysObjects
WHERE Type IN (1, 4, 6)
AND Name NOT LIKE "?Sys*"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Arvin Meyer [MVP]]

Very nice.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Or try the following query:

SELECT Name, DCount("*", "[" & Name & "]") As RecordCount
FROM MSysObjects
WHERE Type IN (1, 4, 6)
AND Name NOT LIKE "?Sys*"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here's one that I just wrote and test that will count all the records in
all your tables:

Public Sub CountRecords()
' © Arvin Meyer 12/13/2009
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim i As Integer

Set db = CurrentDb

For i = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(i)
If Mid(tdf.name, 2, 3) <> "sys" Then

Set rst = db.OpenRecordset(tdf.name, dbOpenSnapshot)
rst.MoveLast
Debug.Print tdf.name, rst.RecordCount
rst.Close
Set rst = Nothing

End If
Next i

Set tdf = Nothing
Set db = Nothing
MsgBox "Done!"

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David W. Fenton]

Set rst = db.OpenRecordset(tdf.name, dbOpenSnapshot)
rst.MoveLast
Debug.Print tdf.name, rst.RecordCount

That's going to be really inefficient, and more inefficient the more
records are in the table. Instead, if you insist on using a
Recordset (instead of just checking the base table's RecordCount
property), this would be more efficient:

Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM " & tdf.name
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Debug.Print tdf.name, rst(0)

I don't know if DAO is smart enough to short-circuit this and use
the underlying TableDef's Recordcount property, but even if it does,
it will always take longer to set up the recordset than it would to
simply use the Recordcount property directly.

 

[David W. Fenton]

I am trying to find the size of each table or if not at least the
number of records to try to figure out which tables are taking
most space in the db. Is there a script I can run that will table
and its size/recordcount?

You don't need to use DCount() or a recordset to find the number of
records, as RecordCount is a property of a TableDef that is always
kept up-to-date by Jet. For instance:

?CurrentDB.TableDefs("MSysObjects").Recordcount

However, it won't be accurate for a linked table, only on the actual
table. So, for linked tables, you'd have to do this:

Dim strBackEnd As String
Dim dbBackEnd As DAO.Database
Dim tdf As DAO.TableDef

strBackEnd = Mid(CurrentDB.TableDefs("LinkedTableName").Connect,
11) Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)
For Each tdf In dbBackEnd.TableDefs
If Not (tdf.Attributes And dbSystemObject) Then
Debug.Print tdf.RecordCount
End If
Next tdf
Set tdf = Nothing
dbBackEnd.Close
Set dbBackEnd = Nothing

On the other hand, if you have both linked and local tables, you'll
need to do this:

Dim strBackEnd As String
Dim dbBackEnd As DAO.Database
Dim dbFrontEnd As DAO.Database
Dim tdf As DAO.TableDef

strBackEnd = Mid(CurrentDB.TableDefs("LinkedTableName").Connect,
11) Set dbBackEnd = DBEngine.OpenDatabase(strBackEnd)
Set dbFrontEnd = CurrentDB
For Each tdf In dbFrontEnd.TableDefs
If Len(tdf.Connect) = 0 And _
Not (tdf.Attributes And dbSystemObject) Then
Debug.Print tdf.RecordCount
Else
Debug.Print dbBackEnd.TableDefs(tdf.Name).RecordCounrt
End If
Next tdf
Set tdf = Nothing
dbBackEnd.Close
Set dbBackEnd = Nothing
Set dbFrontEnd = Nothing

Either of these will be much more efficient than the DCount() and
Recordset alternatives, and have a greater performance advantage the
larger the number of records. That is, for tables of a few thousand
records, there wouldn't be much difference, but for tables with
hundreds of thousands of records, this will be much faster than the
Recordset (especially if you're using .MoveLast instead of
COUNT(*)).

It could be that DCount() and the COUNT(*) recordset shortcircuit
and use the underlying table's RecordCount property, but still
there's more overhead involved than by simply going to the property
directly, as in the code examples above.

 

[Arvin Meyer [MVP]]

I decided to test and see just how inefficient both my and Doug's methods
were. I chose a database front-end with both local and linked tables to
multiple back-end databases. The total amount of back-end data was 103 MB
and the front-end data was under a MB. There were 77 tables, actually 78 for
Doug and 1 less for me. I rewrote my code to put the data in a table (which
I excluded from my count). It is even less efficient than before because I
also used a recordset to append the records to the table. The smallest table
had 1 record, the largest had 81 thousand + with several in the 40 to 60
thousand range.

My function took less than 1.5 seconds to run. Doug's solution was almost
instantaneous. I therefore am not worried about any perceived
inefficiencies.

 

[David W. Fenton]

I decided to test and see just how inefficient both my and Doug's
methods were. I chose a database front-end with both local and
linked tables to multiple back-end databases. The total amount of
back-end data was 103 MB and the front-end data was under a MB.
There were 77 tables, actually 78 for Doug and 1 less for me. I
rewrote my code to put the data in a table (which I excluded from
my count). It is even less efficient than before because I also
used a recordset to append the records to the table. The smallest
table had 1 record, the largest had 81 thousand + with several in
the 40 to 60 thousand range.

My function took less than 1.5 seconds to run. Doug's solution was
almost instantaneous. I therefore am not worried about any
perceived inefficiencies.

But the code is more complex, and it will become more and more
inefficient the larger the number of tables (and your .MoveLast code
will take longer proportional to the number of records in the
tables).

I'm not arguing for optimizing when it is more work but makes no
real-world difference in performance, but using the table's
RecordCount property is simpler in terms of coding. Why not use a
property that is maintained for this very purpose, instead of using
SQL to retrieve the same information?

Philosophically, it makes more sense to me to use the RecordCount
property.

On the other hand, if you are looking for portability, I'd
definitely say to use a SQL COUNT(*) statement (not DCount(),
because I'm not sure that gets passed off to a server back end or
not).