How to Compact and Repair Microsoft® Access Database Files
How and why to apply compact and repair to Access databases to maintain against file bloat and reduction in performance.
Last updated on 2024-04-23
Preface
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 sometimes 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” — may be 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
Simple Databases
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.
“Split” Databases
All the databases I create are split into two main files, often referred to in the trade as the “back end” (BE) and the “front end” (FE).
If my database is known to its users as the “Contacts Database”, say, then my back end file is named Contacts_DATA.accdb and front end file Contacts_USER.accdb
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's 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 all users are working with the same data. The front end file provides the interface, through which each user inputs, edits, views and reports on the data.
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.
Back–End Bloat
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.
Front–End Bloat
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 Compact and Repair.
Compact and Repair can be achieved manually, through the use of programming or, in recent versions of Access, through the Compact On Close setting.
To perform a Compact and Repair manually on current versions of Access, open the database, choose Database Tools on its ribbon and then click Compact and Repair Database.
Compact On Close causes Access to compact a database file each time you exit it. Hence you can apply it to both front–end and back–end files.
Using Access for Microsoft 365, choose File and then Info to locate this button.
I've reservations about applying the Compact On Close to the back–end file and do not engage it in databases developed for clients.
Always make certain to back up any file before compaction. Whatsmore, if that's the back-end file, make certain nobody is using it.
Strategy for Compacting Databases
Back–End
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.
I can provide 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.
Front–End
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
Preferably, as I do for the majority of databases that I supply to clients, I set front-ends to Compact on Close.
To achieve this select File on the menu and then Options (bottom left) to display the Access Options dialog box. From that, choose Current Database and apply a tick to Compact on Close:
This takes care of the front-end completely, freeing you from sleepless nights worrying about bloat.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.
Disclaimer
David Wallis 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.