Access why compact database

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. Compaction reduced it to , kB. Such levels of bloat exceed any expectation based on the number of records added to tables.

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. 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. Compact and repair can be achieved manually, through the use of programming or, in recent versions of Access, through the Compact On Close setting. You can choose to automatically compact and repair your database when it closes. The detailed steps to set the option are as follows:. Though the database may remain unavailable for a short time, it can still impact your workflow.

Select the Compact on Close checkbox under the Application Options section. Click OK and close and reopen your Access db. Once you have performed these steps, a database will automatically get compacted and repaired on closing. However, the process may vary depending on whether the database is open or closed. A copy of the compacted and repaired Access database will get created in the same location as the original database. Also, before performing these steps ensure that the database file is not in use by other users.

Browse and select the database that you want to compact and repair. Click the Compact button. A copy of the database that you have compacted and repaired will be created in the same location. Sometimes, when trying to open a corrupted database file, Access might prompt you to compact and repair the database. Compact and Repair feature may fail to recover a severely corrupted Access database file. Also, in some cases, the feature might help resolve database file corruption but does not guarantee recovering all the objects.

The Repair Utility will only attempt to restore corrupted tables, queries and indexes. It does not restore the other database objects: forms, reports modules and macros. These objects can only be recovered from a backup. The Compact and Repair utility, installed with Microsoft Access, has limitations and may not fully recover your databases, their objects or their data management functions.

In order to, successfully, compact a Microsoft Access database; the database must not be in use. These third party solutions offer automated methods of monitoring database access and preventing access during compaction.

In addition, the Microsoft Access installed Compact and Repair utility will open and close the database before reopening the database to compact it. If your database is corrupted, Microsoft Access may not be able to reopen the database to compact it.