Automatic Way To Compact and Repair Microsoft Access Database On Close

Are you at the risk of losing up your important access database data due to corruption? If yes, then compact and repair the MS access application inbuilt utility tool may help you in resolving this.

Access allows you to compact and repair a database automatically whenever the database is closed. This can prevent performance or file from corruption problems.

You can compact or repair database whether manually or automatically whenever the database is closed. To know how this is to be done let’s have a look over the complete manual solution.

About Repairing and Compacting An Access Database

Microsoft Access or Jet Database requires a routine maintenance, so that it ensures integrity, efficiency and reliability. Repairing and Compacting an access or jet database will do the following:

  1. Organizes and Consolidates data pages, recover space by using deleting and renaming objects.
  2. Updates table & index statistics, so that query optimizer could choose the most efficient query plan.
  3. To choose the clustered index from the table, it physically rearranges the row of the table.
  4. It resets the AutoNumber fields by the next available number, that is it is one higher than the current highest value.
  5. If structural inconsistency is found, it will repair the database.

It can be manually repair and compact a database by opening in Microsoft Access and from the menu select Compact and Repair command. This saves the time and effort by configuring the database to configure and repair itself, each and every time one closes the database.

What’s The Need To Compact And Repair An Access Database?

Before approaching for compact and repair inbuilt option, it’s important to know what it does or in what case compact and repair seems helpful. So, let’s have a look

  • Database files grow with use

When you add, update data or make change in its Access Database design then it raises the size of the Access Database. Some of this growth comes either from new data or other sources.

  • Database files can become corrupted

When the file is shared over a network or when the multiple users work directly with the file simultaneously which has very small chances of being corrupt. If user edit data in memo field then also the risk of corruption get higher.

How To Automatically Compact and Repair Microsoft Access Database with Compact on Close?

  1. Select the Access Option form
    • For Microsoft Access 2010 and 2013, Click Option from File Menu.
    • For Microsoft Access 2007, Click Access Option from Office Button.
  2. From the left border, click the current database.
  3. Check the box “Compact on Close”.

compact-on-close-2007

How To Open The Database For Access 2003 or Earlier?

  1. Click Tools then click Options menu option to open Access Option
  2. On General Tab, check the box “Compact on Close”.

compact-on-close-2003

Limitations And Issues Of Compact On Close

On selecting “Compact on close”, it ensures that the important maintenance tasks are regularly performed, but it has some limitations:

  1. Compact on Close increases the time significantly that it takes to close the database. It uses considerable system resources which delays in getting the other work done.
  2. Databases are repaired and compacted when one opens and closes them interactively. Linked or back-end database which are not opened interactively should be repaired and compacted in other way.
  3. Repairing and compacting would require exclusive access to database. The database for multi user, one must be fully convinced that no one has opened the database prior to repairing or compacting.

YOU MAY ALSO LIKE:

3 Instant Fixes To Resolve MS Access Error 2302: “Can’t Save The Output Data To The File You’ve Selected”

Top 5 Fixes To Resolve MS Access “The Database Is Required To Be Repaired Or It’s Not A Database File” Error

How To Minimize Access Database Corruption And Bloating 

Microsoft Access or Jet Database needs to be compacted periodically. File server database like ACCDB and Access or JET MDB need to be repaired and compacted periodically for maximize the performance  and to reduce Access Database Corruption. Database bloats are eliminated by compacting as data are deleted and added in the database. The database of Microsoft Access can be compacted manually from the Access menu. The versions and process of compacting currently open database or other database are as follows:

  1. MS Access 2013: If the database is open it will compact the database on selecting Compact and Repair database. If the database is not open, it will give suggest to select a database from the disk and to name the compact database. This option can be selected from Database Tool Ribbon. OR  from the File menu Select Info  then click Compact and Repair Database button at the right end.

compact-access2013

2. Microsoft Access  2010: Select the Compare and Repair options from Database Tool ribbon OR From the file menu select Info and then select Compact and Repair Database button from right.

Compact ribbon 2010

3. MS Access 2007: Select Office button then select Manage and then select Compact and Repair database.

Compact access 2007

4. Microsoft Access 2003 and earlier: Select Tools then select Database utilities and choose Compact and Repair Database.

compact access 2003

 How To Recover Data From Corrupt Access Database When The Compact And Repair Won’t Work?

If the above mentioned steps cannot repair the Microsoft access database then one can use the Third Party Software Tool that is MS Access Repair N Recovery Tool to repair the Microsoft Access Database. With the help of MS Access Repair tool one can repair and recover the damaged, inaccessible or damaged database. It easily recovers the database from any sort of corruptions; it restores the corrupt index, table, relations and query. This software also recovers password protected forms or modules.

Step By Step Guide On How To Repair And Recover Corrupt Access file

Step 1 – Install the software and launch it. Now select the corrupt Access file for Repairing.

1

Step 2 – The software will show you the list of searched files in a drive. Select the file that you want to repair and click on Repair button.

2

Step 3 – The repairing process will get started and you see the log report for analysis.

3

Step 4Once the repairing process completes click on “OK” button to see the preview of the file database objects.

4

Step 5 – You can now preview the repaired file.

5

Step 6 – By default the software will save the repaired file to the location where the corrupt database is saved but with a new name. You can also select different location.

6

Step 7Your saving is in process.

7

Step 8 Saving completed.

8

Automatic Way To Compact and Repair Microsoft Access Database On Close…..!