What is your process for fixing database problems

Use Compact and Repair to prevent and fix database file problems

Database files can grow rapidly in size as they are used, and sometimes affect performance. They can also be corrupted or damaged from time to time. You can use the command Compress the database and use Repair to prevent or correct these problems. The compression process does not compress your data - it shrinks the database file by removing unused space. The Command database compressed and repairing it can also improve the performance of your database.

tipSharing a database can help prevent database file corruption and limit data loss by storing the data in a separate file that users do not have direct access to.

Ways to Compact and Repair a Database

There are several approaches to compacting and repairing a database. One common method is to automatically compact a database and repair it when it is closed. In addition, you can manually run the Compact and Repair Database command when a database is open and on a database that is not open.

Preparatory steps

Before you begin the compress and repair process, do the following:

  • Create a backup of the database During the repair process, Access may truncate some data from tables that are damaged. Sometimes it is possible to restore this data from a backup. In addition to your normal backup strategy, you should take a backup right before using the Database Compact command and repair it use. For more information, see Protecting Your Data With Backup and Restore Processes.

  • Get exclusive access to the database A compress and repair operation requires exclusive access to the database file because the operation can interrupt the work of other users. You should notify other users if you plan to perform a compact and repair operation so that they will not be using the database during this period. For more information, see Open an Existing Access Database.

    Let users know how long to avoid using the database. If you run compression and repair operations on a regular basis, record their duration. You can then provide more accurate estimates that will inform other users how long they should not be using the database.

  • Obtaining sufficient file permissions to the database If you do not have sufficient privileges and need to compact and repair a database, contact your system administrator for assistance. For more information, see Changes to file sharing over a network in Windows 10.

Top of page

Automatically compact and repair a database when it is closed

You can use the database option Compress when closing Select if you want a database to be automatically compacted and repaired when it is closed. Setting this setting affects only the database that is currently open. Set this option separately for each database that you want to automatically compact and repair. Do not set this option for databases with multiple users, as it can temporarily disrupt the availability of the database.

  1. Choose file > Options out.

  2. In the dialog box, choose Access options the option Current database from.

  3. Activate under Application options check box Compress when closing.

  4. Choose OK out.

  5. Close and reopen the database for the option to take effect.

Top of page

Manually compact and repair an open database

  • Choose You file > Info > Compact & Repair Database off.

    Access makes a copy of the compressed and repaired database in the same location.

Top of page

Manually compacting and repairing a database that is not open

Use this procedure if you cannot open an Access database directly.

  1. Make sure that no other users are currently using the database file.

  2. Start Access.

  3. In Access 2013, Access 2016, and Access 2019:

    1. On the Templates page, double-click Empty database.

    2. Choose You file > Close out.

  4. Choose Database Tools> Compact and Repair Database out.

  5. Navigate the dialog box Compress the database to the database that you want to compact and repair, and then double-click it.

Access makes a copy of the compressed and repaired database in the same location.

Top of page

Compact and repair a damaged database when Access prompts you to do so

If you're trying to open a damaged database file when Access prompts you to compact and repair the database, choose Yes off. Two things can happen:

  • When Access finishes repairing a damaged file, it will display a message stating that the repair was successful and that you should check the contents of the database to make sure everything is as it should be.

  • If Access is only partially successful, it keeps track of database objects in a system table named MSysCompactErrorscould not be repaired. Access opens the MSysCompactErrors table in the datasheet view. If you have a previous backup before the database was damaged, you can use the MSysCompactErrors table to decide which objects to import into the repaired database. To view system tables, right-click the navigation title bar, then select in the dialog box Navigation options the option Show system objects.

Top of page

Why should you compact and repair a database?

This overview explains how to use the Compress and repair database can help prevent and correct the following problems that can sometimes affect a database: File size growth from use and file corruption.

The size of database files grows with their use

As you add and update data and change its design, a database file grows larger. Some of this growth comes from the new data, but some other parts come from different sources:

  • Access creates temporary, hidden objects to perform various tasks. Sometimes these temporary objects remain in your database when they are no longer needed by Access.

  • When you delete a database object, the space occupied by the object is not automatically reclaimed: the database file continues to use the space even though the object has been deleted.

If your database file fills up the remnants of temporary and deleted objects, performance can be affected. Objects can take longer to open, queries can take longer than normal, and typical operations generally seem to take longer.

Top of page

Database files can be damaged

Under certain circumstances, a database file can become corrupted. If a database file is shared on a network and multiple users are working directly with the file at the same time, the file is at low risk of corruption. The risk of corruption is slightly greater if users frequently edit data in memo fields, and the risk increases over time. You can reduce this risk by using the command Compress and repair database reduce.

Often times, this type of corruption is the result of a problem with a Visual Basic for Applications (VBA) module and does not pose a risk of data loss. However, this type of corruption poses a risk of damaging the database design; B. Loss of VBA code or unusable forms.

Damage to database files rarely leads to data loss. Usually this loss is limited to the last action taken by a user. d. H. a single change to data. When a user starts changing data and the change is interrupted, e.g. Due to a loss of network service, for example, Access marks the database file as damaged. The file can be repaired, but some data may be missing after the repair is complete.

See also

Protecting Data with Backup and Restore Processes Set

general user options