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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Startrac98Author Commented:
Divided the tables into separate databases and linked to each.  Problem is resolved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.