Benifit of Compressing my Access DB

[bzeyger]

I currently have a VBA database in Access 2003. It is 63,000 KB.

This takes up a great deal of space. I recently added a few new forms,
tables, and macros and the DB droped to 27,000 KB. All of my data is still
there. I do not know why this had happened. I imported a small table it it
jumped back up.

I also noticed that there is a compress tool in Access. Is there a benifit
to using this tool?

 

[Douglas J. Steele]

Yes. You should compact your database on a regular basis, especially if
you're still developing in it.

Access doesn't actually delete entries: it just marks them as deleted.
Compacting actually frees up the space.

 

[Jerry Whittle]

Your database is still rather small as an A03 mdb file can be up to 2GB in
size. Access databases are more like paper bags than balloons. You need to
refold a paper bag once it's empty to get it smaller. A balloon automatically
deflates. The compact and repair tool 'refolds' the database getting rid of
empty space. There is even a setting to automatically compact every time that
you close a database, but I don't recommend it.

Actually there is no good reason to compact a database daily or even weekly.
Maybe once a month. If you compact the database and run a few of the normal
queries, forms, and reports, the database just expands to about the same
size. This is what it needs to work.

If you are getting near the 2GB file size limit, then maybe you need to
split some tables off to another BE; archive some data (bad idea); or upgrade
to SQL Server.

 

[Armen Stein]

I currently have a VBA database in Access 2003. It is 63,000 KB.

This takes up a great deal of space. I recently added a few new forms,
tables, and macros and the DB droped to 27,000 KB. All of my data is still
there. I do not know why this had happened. I imported a small table it it
jumped back up.

I also noticed that there is a compress tool in Access. Is there a benifit
to using this tool?

Access does not recover internal disk space when objects are deleted
or shrink in size. Using the Compact and Repair feature recovers this
space, and can also correct some corruption issues.

For more info on this feature, try a web search for: Access compact
repair

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

I currently have a VBA database in Access 2003. It is 63,000 KB.

This takes up a great deal of space. I recently added a few new forms,
tables, and macros and the DB droped to 27,000 KB. All of my data is still
there. I do not know why this had happened. I imported a small table it it
jumped back up.

I also noticed that there is a compress tool in Access. Is there a benifit
to using this tool?

In addition to the advantages mentioned by the others, Compacting also resets
the statistics on queries so that they will be recompiled next time they're
opened. If the contents of your tables are changing substantially, this can
lead to markedly faster query execution.

Compacting will also get rid of (much, often not all) obsolete code and
form/report objects. As a rule of thumb I'd compact when the database doubles
in size.