compact access database

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.

access database files

simple databases

The simplest arrangement for an Access database is to have it contained in a single file.

The name of the file is the one you chose when you started creating your database. Access will have added an extension of .MDB to that name to distinguish it as an Access file. Access-minded people often refer to 'the MDB'.

multi-user databases

One reason for constructing an Access database out of more than one file is to accommodate more than one person at a time working on the data.

Under these circumstances, you will hear Access developers refer to the 'back end' and the 'front end' of a database.

The back end file contains the data. The front end file contains the user interface, through which users input, edit, view and report the data.

Both the back end and the front start off as MDBs. The front end is 'linked' to the data in the back end.

back end bloat

The back end grows in size as a consequence of the use of your database; growth, and bloat, are unavoidable.

Simply making your database 'smaller', by deleting unwanted data, usually does not reduce MDB file size.

front end bloat

Some database developers will tell you that a your front end will not bloat.

This may be the case under certain circumstances. But there are others under which it will bloat. Some front ends bloat at an alarming rate!

Check a front end for bloating by looking at the size of its MDB before you start using it and then again a month later.

reducing database bloat

You reduce the size of a database file by compacting it - the Access Compact And Repair Database feature.

Compact and repair can be achieved manually or through the use of programming.

In the most recent versions of Access there is a setting 'Compact On Close' by which Access will compact your database each time you exit the database.

We have reservations about using the Compact On Close feature.

strategy for compacting database

back end

Compact the back end MBD on a regular basis.

How frequently depends on the level of database activity. You might gauge this by weekly or monthly checks on back end file size.

We recommend you do not use the Compact On Close setting on a back end.

Always take a back-up of your back-end file before compacting it.

front end

Make sure you keep a copy of the original front-end file.

You may choose to apply the Access Compact On Close setting. Alternatively, if bloat proves to be a problem, copy the original to replace the front end you are using

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.