Compacting Sample Database Tables
Compact & Repair Sample Database Tables
As an Access Database processes the data stored within it, vacant space can be left behind as records are deleted, or moved due to internal housekeeping. This space is referred to as bloating. Bloating in an Access Database can be most pronounced after deleting records from multiple tables at the same time, which is what occurs when you delete our sample data from the database.
Compact & Repair Tool
To manage bloating, Microsoft has included a ‘Compact & Repair’ tool in Access Database. The Compact & Repair tool can be used to correct minor internal data issues which can arise as a result of unexpected PC crashes or user’s switching off their PC without first closing the database, as well as for removing the bloat from an Access Database.
Generally, it is a good idea to run a compact and repair if any of those events occur. Otherwise, run a compact and repair every 6 months or so to remove bloating, and to keep the Access database running at peak performance.
Our Sample Database Installation format
To ensure maximum performance and to provide optimum multi-user functionality, all of our sample access database software is installed in a Front-end/front-end configuration. The database front-end is the user interface containing the forms and reports with which the user interacts interacts with. The access database back-end is the data tables file where the data that you input into the database is stored.
The back-end tables is that file that you were required to manually placed in a shared folder, when you installed the database. The database Data Tables file is named that same as the Access Database front-end, but has a suffix of ‘_data’ at the end of its name. For instance, the Assets Database back-end data file is named ‘Assets_data.mdb’ or ‘Assets_data.accdb’. In most cases, you will not see the file extension name.
In our Access Database samples, the database front-end is set to automatically run a compact and repair each time you close the database front-end. There is no need for you to take any further action regarding the front-end.
In the Access database back-end (data tables file) we have incorporated the 'Compact and Repair' functionality so it will run with the press of a single button. The button is available on the main screen when you open the back-end database file.
How to Compact & Repair the Database tables.
To run the Compact and Repair tool in the database tables file (back-end) perform the following steps.
- Locate the back-end tables file in the tables folder. The file is named ‘Databasename_data.mdb’ where the database name is the name of the database.
- Backup the back-end database tables file, before taking any further action. Simply saving a copy to another location will suffice to ensure you have a safe copy in the event of any issues.
- Double click the data file to start the tables database. The following screen will be displayed when the database starts.
- Read the information on the page and if ready to proceed, click the ‘Compact Database’ button. The following message will be displayed.
- Read the message and only if you have made a backup copy of the database, click the ‘OK’ button. Once you are certain that no other users are currently accessing the database, click the ‘Compact Database’ to run the compact and repair function. The database will close once the process has completed.
- IMPORTANT It is important that once you have run the Compact & Repair that you re-open the Database tables file and then close the Database Tables file using the Quit button. Doing so resets the database tables for normal operation.