compact 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. Some call this phenomenon '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' are signs that bloat is having an adverse effect.

'Compacting' a database reverses the effects of bloating. Compacting can bring benefits too in maintaining your database in good working order.

more than one file to an access 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) to that name to distinguish it as an Access file.

split databases

All the databases we create start off as two main files. We refer to these files 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 computer.

back end bloat

The back-end data file grows in size as a consequence of the use of the database as records are added.

Attempts at making your database 'smaller' by deleting unwanted data usually does not reduce back-end file size.

The tendency of Access files to increase in size is sometimes referred to as 'bloat'.

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.

reducing database bloat

You reduce the size of a database file by compacting it.

Compact and repair can be achieved manually (the Access Compact And Repair Database feature), 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.We have reservations about using the Compact On Close feature and we do not engage it in any of our databases.

strategy for compacting databases

back end

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 before attempting to compact 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.

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

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.

disclaimer

DMW Consultancy Limited 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.