Compact & Repair Access Database Files
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 DMW creates start off as 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.
The front end has links to the back end so that the front end can access the back-end data store. 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. This growth, however, often exceeds that which you might expect based on the number of records added to tables — evidence of bloat.
Attempts at reducing the size of your database's back-end file by deleting unwanted data usually well may not produce significant results.
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.
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 a back-up of 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.
We 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.
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
Give DMW’s Tips Your Support
DonateIf you find Tips useful and would like to make a donation to encourage me to keep adding to them, please click Donate and let me know how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.
Thanks, in anticipation.
Automating Back–End Compaction
We provide our 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 our experiences, we do not recommend this: there are just too many hazards.
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.