3

We have an Access 2000 format database running in Office Access 2003.

We have a problem with the size when I merge the database; the size is about 250 MB. Two days later, the size has ballooned to about 750 MB.

How can I see where this increase in size comes from? Can I look behind the scenes in Access?

The database is running for a tennis club and our employees are working all day long with the database. Another programmer made the database and says it is common to see Access databases increase in size.

What can I do to alleviate this issue?

  • 1
    As I recall, the Jet engine (or whatever runs MSAccess in 2000 format) commonly uses the MDB file to store its scratch space, so it doesn't have issues with temptables. This is most likely what's happening. – jcolebrand Jul 30 '12 at 19:36
  • There are things that can help, for example, make sure you do not create and drop tables in the usual running of the database. – Remou Jul 30 '12 at 20:54
4

MS Access grows just from using it - running queries, etc. That is just the nature of it.

I would recommend including a compact and repair process in your daily processing. Access removes the existing file and replaces it with the compacted version, thus shrinking the database.

If you are worried about the size, the consider creating a daily process (nightly) to run the compact and repair. I had an MS Access database that we had to run a nightly compact/repair to shrink it from ~2GB to 300MB every night.

| improve this answer | |
4

All you have to do is the following:

  • Across the Top of the Access Window, Click the File Tab
  • Click the Compact and Repair Button

I would not worry about growth because data insertion into MDB usually appends changes to back of the file. Garbage collection and space reusage is not a strong suit for MSAccess. To make matters worse, there is filesize limit (I think it is 2GB) on MDB files. This is why some migrate to SQL Server Express where SQL Server can be configured to perform automatic Compacting and Repairing.

| improve this answer | |
  • 4
    ;-) Now tell him why. :p – jcolebrand Jul 30 '12 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.