Winter is Here!
Time to Compact and Get Small
|It's winter in New England. If you have ever been here in the heart of winter and you are not a native, you'll probably notice two things. First, if there happens to be snow on the ground, it's really pretty. Snow frosts the trees and covers the ground with a blanket of white.
You'll also notice that it is cold, all the way down to where the thermometer declares it can’t find any temperature at all - 0°. Sometimes it’s even less than nothing! Negative degrees! :-)
It is a time when New Englanders either hit the slopes, man the snowplows, breakout the snow-blowers, or keep in their houses to try to stay warm. It's a time to compact and get small. The bitter cold is one of the reasons that the houses are smaller the further north you go - less space to heat.
An Access database also starts small. Overt time, it will always grow larger and larger and larger. This effect is called bloat. In this month’s Access Wizard, we'll take a look at bloat and how to deal with it using the compact and repair tool.
Why Does Bloat Happen?
When Access manipulates records, it needs space to work. When it deletes records, it doesn't automatically recover that space. When you do an update query, Access needs space to manipulate the data. When you use a make table query, Access uses space. As a matter of fact, any time that data is created or destroyed, space is used and not typically recovered.
Over time, that empty space will more than double the database size. With today's hard drives, disk space is cheap, so why worry about something like database bloat? One very good reason is that, if you have to move or copy your database, it will require more space and take more time (especially if you are moving it over the internet). All that extra space can make transferring databases cumbersome.
An even more important reason is that all that space slows the database down. Luckily, the solution in most cases is simple.
Compact and Repair
Compacting a database in most cases is an easy exercise. On the ribbon, click on the database tools tab (for Access 2003 and earlier, select the tools menu) then choose compact and repair database. Depending upon your computer speed, you might see the spinning blue circle, after which your database will close and reopen.
Behind the scenes this is what’s happening:
The last step is the reason Access needs to close the database. You actually end up with a new database, minus all the useless space.
Access checks to see whether or not there is anybody else in the database. If there is someone else in your application, the process will stop and let you know that it can’t continue. For more on this problem, see this month’s Tip and Trap.
It creates a temporary database.
It copies your objects from the existing database to the newly created database.
It closes the database.
It replaces the existing database with the newly created database.
If you would like to compact your database every single time use it, you can automate the process. Click on the file tab in the ribbon (or the Office button if you are a 2007 user), choose the current database, and then under the application options, click on the compact on close check box. Now every time you close your database, it will do a compact and repair automatically.
This process will delay closing your database by a little bit, or perhaps more if you have a large database.
By compacting your database periodically, you will end up with greater speed and a smaller file size. It is an easy process and you want to make a habit of doing it periodically.
Trap of the Month: Anyone in the Database?
Sometimes you will try to compact your database and find that you can't. This typically happens when there are other people in the database. Because part of the compact process includes replacing the database, it won’t be able to accomplish this if someone is in the file. So you will need to make sure that everybody is out of the database before you can compact.
If you want to check to see if there's somebody in the file before compact look for a file with the same name as the database but with an extension of “ldb”. If there is a file with that extension, it means that somebody is in the database. Of course you have to make sure that you are out of the database before you check for the ldb file, otherwise you will end up chasing your tail.