Compact and Repair Access Database Files
Considerations on when and how to best apply compact and repair to databases as a matter of routine maintenance and fight bloat.
Last updated on 2018-09-20 by David Wallis
As you add and modify records in your database, the file, or files, that comprise the database grow in size. Access files seem to grow at rate that strikes as disproportionate to the amount of activity in the database, a phenomenon often referred to as “bloat”.
If you allow bloat to continue unhindered, the performance of your database will deteriorate. Calls from users — like the database is very slow and the database has slowed down — maybe signs that bloat is having an adverse effect.
Compacting a database reverses the effects of bloating and brings benefits too in maintaining your database in good working order.
More Than One File to the Database
The simplest arrangement for an Access database is to have it contained in a single file saved on your computer.
The name of the file is the one you chose when you start a new, blank database. Access will have added an extension of “.mdb” (Access versions prior to Access 2007), or “.accdb” (Access 2007 and later) to that name to distinguish it as an Access file.
All the databases I create are split into two main files, referred to as the “back end” and the “front end”.
The contents of the back end is made up of Access tables only. These hold the primary data stored within the database.
The front end is comprised of all the Access queries, forms, reports, macros and modules. It is the user's means of operating the database. Each user gets a copy of the front-end file.
Each user’s front end has links to the same back end so that their front end can access the back-end data and all users are working with the same data. The front end file contains the user interface, through which users input, edit, view and report on the data.
One reason for constructing an Access database out of a front end and a back end is to provide for simultaneous use of the database by more than one person. In this multi-user form of the database the back end normally resides on a network server, and each user has a front end on their workstation.
The back-end data file grows in size as a consequence of the use of the database. I worked on a client’s back-end file that was 708,452 kB in size. Compaction reduced it to 306,532 kB..
A 50% to 60% reduction in file size is not unusual in back-ends that I’ve known not to have received any TLC for some time. Such levels of bloat exceed any expectation based on the number of records added to tables.
Attempts at reducing the size of your database’s back-end file by deleting unwanted data may not produce significant results. Anyway, I’m always hesitant in agreeing with clients to delete data. Who can be absolutely certain those records may not be relevant in the future? Why go to the time and effort on inputting them in the first place?
I recommend you check the size of the back-end file periodically to determine its rate of bloat. Then you can schedule compactions accordingly.
Even though a front end file may not contain data that is changing, a front end may suffer bloat. Front ends tend to bloat at different rates. Their internal workings determine the bloat rate.
Check a front end for bloating by looking at the size of the front end file before you start using it and then again after a month’s use.
Developers do not agree about their use, but I’ve used temporary tables in a few databases. Such tables store data on the fly that are needed for a particular process. The data have no need of a life beyond the completion of the process: they are not needed as permanent records in the database.
Temporary tables will significantly inflate the rate of bloat. If I’m using them, they are always in the front-end, never in the back-end of the database.
Reducing Database Bloat
You reduce the size of a database file by compacting it.
Compact and repair can be achieved manually, through the use of programming or, in recent versions of Access, through the Compact On Close setting.
Compact On Close causes Access to compact your database each time you exit the database. DMW has reservations about using the Compact On Close feature and does not engage it in databases developed for clients.
To perform a Compact and Repair manually on an Access 2010, 2013 or 2016, open the database, choose File on its ribbon and then click Compact & Repair:
Always make certain that nobody is using your database before you compact and repair it.
Strategy for Compacting Databases
Always include the back-end file in your regular back-up routine. Furthermore, always take a back-up of your back-end file, and make certain nobody is using it, before attempting to compact and repair it.
I recommend that you compact the back end on a regular basis. How frequently depends on the level of database activity. You might gauge this by weekly or monthly monitoring of the back-end file size.
DMW provides clients with a utility that they run to compact the database as and when they choose. This utility bye-passes all the actions that you would have to take to achieve compaction manually.
Some developers suggest kicking in such a utility by scheduling it to run of its own accord at times when the database is not in use. Based on my experience, I do not recommend this: there are just too many hazards.
Make sure you keep a back-up copy of the original front-end file. If bloat proves to be a problem, simply copy the backed-up original to replace the front end that has bloated
For the databases that I supply to clients, I set front-ends to Compact on Close:
This takes care of the front-end completely, freeing you from sleepless nights worrying about bloat.
Give DMW’s Tips Your Support
ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.
Thanks, in anticipation.
DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.