Viewing the size of objects in a Microsoft Access database.

[Guest]

Is it possible to easily view the size (in Bytes, KB, or MB) of an object
(specifically a table) from within Microsoft Access?

From within Microsoft Access 2002 --> Objects --> Tables: When I click on
"Details" from the "view" menu the following characteristics of each table
are diplayed: Name, Description, Modified, Created, and Type. But there is
no option or any way that I can see to add file Size. Is it possible to view
Size from this view, or any other location?

 

[Douglas J. Steele]

There's no direct way to find out the amount of space a table (or any other
Access object, for that matter) is taking in the database.

The indirect approach is to create a new database, and import the objects
into it one by one, noting the change in size in the database after each
import.

 

[John Vinson]

Is it possible to easily view the size (in Bytes, KB, or MB) of an object
(specifically a table) from within Microsoft Access?

Not at all easily.

The table isn't really "a file". The contents of the table are stored
in several places in the .mdb file, in a proprietary format which
Microsoft has chosen not to document publicly. Not only are text
fields truncated to remove trailing blanks (meaning that each record
might be a different size), but even if you were to sum up the sizes
of all the records in all the fields, this would not account for
indexes or table overhead.

Why do you care? The size of the .mdb file is what's critical (it's
limited to 2 GByte in the current versions).

If you really need to know - make a copy of the .mdb file. Use
Tools... Database Utilities... Compact and Repair to compact the
database. Note the size of the database. Delete the table; compact
again, and subtract.

John W. Vinson[MVP]

 

[Guest]

First off, thank you John and Douglas for the information - I appreciate the
quick responses! I especially like Douglas' explanation of why this type of
information would be very difficult (if not impossible) for Access to
calculate and/or display.

Now as for why I asked the question in the first place: Being able to view
the size of each individual table from within the "Tables" view would allow
me to quickly delete the tables/objects that are taking up the most space.
There seems to be no easy way (short of copying each individual table to a
new database or viewing each table individually) to determine which tables
are the largest.

You see, I like to maintain a single database with all of the data I have
compiled over time readily accessible from the same location. As such,
occasionally I have the desire to do some "Spring cleaning" in order to
recovery disk space. This involves deleting some of the 'temporary' tables I
have created. Since there are quite a few of these tables after a long
period of time, I have a difficult time deciding which tables to delete. If
I am being inefficient (or just plain stupid), please feel free to inform me
at any time!

To sum-up: Not a critical issue whatsoever, just curious to know if it's
possible.

Thanks again!

 

[J. Clay]

What kind of data do you compile? It maybe that a Relational Database like
Access in not necessarily the best choice. There are several "Free Form"
data bases that are very adept at keeping random information that doesn't
really fit the standard database model. One of those that I can think of
off hand is AskSam (www.asksam.com). I more of a Relational DB guy myself,
but if you have lots of random data, these tend to work out better.

Regards,
Jim

 

[John Vinson]

You see, I like to maintain a single database with all of the data I have
compiled over time readily accessible from the same location. As such,
occasionally I have the desire to do some "Spring cleaning" in order to
recovery disk space. This involves deleting some of the 'temporary' tables I
have created. Since there are quite a few of these tables after a long
period of time, I have a difficult time deciding which tables to delete. If
I am being inefficient (or just plain stupid), please feel free to inform me
at any time!

Well... in a properly normalized database, there generally would be
very few if any temporary tables, and any that WERE there would be
*very* temporary: sometimes you just need to store data in a temp
table as an intermediate step in a particularly complicated query, or
to solve a snarky data-entry Form problem. But in those cases the need
for the data in the temp table would cease as soon as the query was
run or the data entered.

As suggested elsethread, you may be working with a different data
paradigm; but as a rule, I *know* which tables contain the data
important to the running of an application, and none of them will be
candidates for deletion; anything else can go!

John W. Vinson[MVP]