The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

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:
  • 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.
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.

Some Niceties

 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.
 

Conclusion

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.


 

Tip of the Month: 
Understanding the ldb File

When you or someone else opens your database, Access creates a companion file with the same base name and with the extension “ldb”. This ldb file is a way that Access keeps track of active users in your database. This file continues to exist until the last person closes the database, at which point it is deleted.
 
As part of the opening process, it will also put that person’s identifier in the ldb file. Double clicking the ldb file will not open it directly, but you can see the contents with any text reader such as note pad. In the file you'll find a list of people currently in the database. This may give you some idea of who is in the file; however, in many cases, people will show up as “admin” or as their computer identity, so it might be a bit difficult to figure out who is really in there.
 
In a future Wizard, I will show you a method to keep track of who is in a database so it is easier to identify active users.
 
One final note on the locking file: If a user's machine crashes or s/he exits the database in a nonstandard fashion (control alt delete method for example), the ldb file will not be deleted.  To check for this case you can simply try to delete the file from Windows Explorer.  If you take this approach, make sure of course that you are deleting in the ldb file not the accdb or mdb file.

 
Quick Links:

Custom Software Home

Access Wizard Archives

Our Services


© Copyright Custom Software
All Rights Reserved | Westford, MA 01886