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
Startrac98Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
http://www.experts-exchange.com/Database/MS_Access/Q_27953896.html

In the EE question above, IrogSinta has posted the VBA needed to move each object to a separate database, calculate the size, and store those in a table. Note there is a "corrected version" in the last comment on that question.

That said, if you're concerned about bumping into the 2 GB limit you should consider either (a) moving some of your large tables out to external databases, and linking them back to the main database or (b) moving to a different db platform that supports larger sizes, like MS SQL Server, MySQL, etc.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, there are some 3rd party utilities that do this, like Total Access Analyzer:

http://www.fmsinc.com/microsoftaccess/bestpractices.html
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Saurabh Singh TeotiaCommented:
You can use a freeware tool like this to do what you are looking for..

http://www.atroplan.com/AccMemReporter.htm

Or

http://www.fmsinc.com/microsoftaccess/documentation/reports/Table_SizeBySize.html

Saurabh...
0
 
PatHartmanCommented:
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.
0
 
Startrac98Author Commented:
http://www.atroplan.com/AccMemReporter.htm works for .mdb formats.

Anybody aware of vba code that I can copy?
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Startrac98Author Commented:
The table size cannot exceed 2GB, so I need to know that size not the record count.
0
 
Startrac98Author Commented:
Divided the tables into separate databases and linked to each.  Problem is resolved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.