General discussion

Locked

Compacting Access Database (It grows!)

By TechSupportSpecialist ·
I have been working extensively with Access for a few years now, and I've learned that .mdb files will typically grow in size and need to be compacted periodically. Unfortunately, I cannot figure out why this happens nor does there seem to be any documentation that explains why. For awhile, it didn't really matter, but my colleagues and I need to know exactly what is happening. We need to know the following:

1) Why does the .mdb file grow? What is actually getting bigger?
2) What is getting lost or 'compacted' when the database is compacted?
3) Is there any benefit to leaving the database UNCOMPACTED? If not, then why doesn't it automatically compact itself when it closes? I know the option is there, but why is it even an option if there's no benefit?

If anyone can answer these questions for me, I will be really grateful (and so will my colleagues). Thank you.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Cactus Pete In reply to Compacting Access Databas ...

Let's pretend you just deleted some data from a table in Access. You would think that the database file would be smaller by the amount of space the item took up, right? Well, that's not how it works... The blank space stays there even though you have removed the data from the space. This is what you're compacting when you choose that option, the blank space.

Why would someone ever NOT want to compact? Well, if you've done a LOT of work in the database, you might not wish to wait for this huge file to be processed before logging off and going home for the day. Then there's always a risk of corrupting the tables, etc. [You should always have a backup before making serious changes, and backups every day, anyway...]

As for the growth, I think that's explained when you know what's being compacted...

Collapse -

by timfox In reply to Compacting Access Databas ...

As dpetrak has indicated:

1) Why does the .mdb file grow?
- Lots of reasons, eg. New tables/queries/code/uncompiled code. Access also creates and deletes a lot of objects in order to facilitate the pretty GUI screen that let's us develop Access applications.
What is actually getting bigger?
- nothing that's perceptible to us
2) What is getting lost or 'compacted' when the database is compacted?
- Similar to defragging a hard disk, compacting removes the deleted data and temporary objects within the mdb.
3) Is there any benefit to leaving the database UNCOMPACTED?
- No benefit other than not realising performance improvements often gained by compacting etc
If not, then why doesn't it automatically compact itself when it closes?
- Compacting large databases takes time and can cause errors. Why put every user through this process, when the administrator should look after it?
I know the option is there, but why is it even an option if there's no benefit?
Reduced size of your mdb and possible performance improvements are the benefits - but as always, there is the risk of corruption, if the compacting fails.

Hope this helps
TimFox

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums