Link to home
Start Free TrialLog in
Avatar of ANTHONY CHRISTI
ANTHONY CHRISTIFlag for United States of America

asked on

How can I find out the size of each table in my database

How can I find out the size of each table in my database
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

The only way to accurately determine the size is to export each table to a separate database, and compare before and after sizes.

You can estimate the size by determining the maximum size of each Field in each table, and then summing those to find the size of a "row", and then multiplying that by the number of rows in the table. This would give you a very rough estimate of the table size - but if you have Memo, Attachment or any other multivalued fields then you really can't use this method.
Also, there are some 3rd party utilities that do this, like Total Access Analyzer:

http://www.fmsinc.com/microsoftaccess/bestpractices.html
Table size is also impacted by the number of indexes attached.  So, you would need to include a calculation for indexes in your estimate as well.
Avatar of ANTHONY CHRISTI

ASKER

http://www.atroplan.com/AccMemReporter.htm works for .mdb formats.

Anybody aware of vba code that I can copy?
Not sure how/where you need this data displayed, ...
But try code like this as a start:

Dim tdf As TableDef
Dim strRecCount As String

    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "msys" Then
          strRecCount = strRecCount & vbCrLf & "The table " & tdf.Name & " has " & DCount("*", tdf.Name) & " records"
        End If
    Next tdf

Note that code like this will take a while to run if you have a lot of tables with a lot of records

Let me know

JeffCoachman
The table size cannot exceed 2GB, so I need to know that size not the record count.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Divided the tables into separate databases and linked to each.  Problem is resolved.