Help prevent and correct database file problems by using Compact and Repair
Database files can grow quickly as you use them, sometimes impeding performance. They can also occasionally become corrupted or damaged. You can use the Compact and Repair Database command in Microsoft Office Access to prevent or fix these problems.
This article does not explain how to back up or restore a database. Find links to more information in the See Also section.
In this article
Why you compact and repair a database
This overview explains how using the Compact and Repair command can help prevent and correct the following problems that sometimes affect a database: files growing larger with use and files becoming corrupted.
Database files grow with use
As you add and update data and change its design, a database file becomes larger. Some of this growth comes from new data, but some comes from other sources:
-
Access creates temporary, hidden objects to accomplish various tasks. Sometimes, these temporary objects remain in your database after Access no longer needs them.
-
When you delete a database object, the disk space that the object occupied is not automatically reclaimed — the database file still uses that disk space, even though the object is deleted.
As your database file fills up with the remains of temporary and deleted objects, its performance can degrade. Objects may open more slowly, queries may take longer than normal to run, and typical operations generally seem to take longer.
Note: Compacting does not compress your data — it makes your database file smaller by eliminating unused space.
Database files can become corrupted
In particular circumstances, a database file can become corrupted. If a database file is shared over a network and multiple users work directly with the file simultaneously, that file has a small risk of becoming corrupted. The risk of corruption is somewhat greater if users frequently edit data in Memo fields, and the risk grows over time. You can mitigate this risk by using the Compact and Repair command.
Often, this type of corruption results from a problem with a Visual Basic for Applications (VBA) module and does not pose a risk of data loss. However, this type of corruption does pose a risk of database design damage, such as lost VBA code or unusable forms.
Rarely, database file corruption does result in data loss. Usually, this loss is limited to the last action of one user; that is, a single change to data. When a user starts to change data and the change is interrupted — for example, because of network service loss — Access marks the database file as corrupted. The file can be repaired, but some data may be missing after the repair is complete.
Tip: Splitting a database can help prevent the corruption of database files and limit the loss of data by keeping the data in a separate file that users do not access directly.
Access prompts you to repair a corrupted database file
When you try to open a database file that is corrupted, you are prompted to let Access automatically repair the file. You can also manually use the Compact and Repair command to repair and open a corrupted database file.
If Access completely repairs a corrupted file, it displays a message stating that the repair was successful and that you should check the database contents to be sure that everything is as it should be.
If Access is only partially successful, it keeps track of database objects that it could not repair so that you can determine what you need to recover from a backup.
Note: You can set a database option that causes the Compact and Repair feature to run automatically each time you close a particular database. If you are the only user of a database, you should set this option. In multiuser databases, you might not want to set this option, because it can momentarily disrupt database availability.
Before you begin
Consider taking the following actions before you begin a compact and repair operation:
-
Always make a backup During the repair process, Access may truncate some data from tables that are damaged. It is sometimes possible to recover this data from a backup. In addition to your regular backup strategy, you should make a backup before you use the Compact and Repair command. You can make a backup by using the Back Up Database command:
-
Click the Microsoft Office Button
, point to Manage, and then, under Manage this database, click Back Up Database.
-
-
Automatically compact and repair Unless you are sharing a single database file with other users on a network, you should set your database to automatically compact and repair.
-
Take note of the system recovery errors table When Access is unable to repair all of the objects in a corrupted database file, any objects that cannot be recovered are noted in a table named MSysCompactErrors. If there are errors, Access opens the MSysCompactErrors table in Datasheet view.
If you have a database backup that you made before the database became corrupted, you can use the MSysCompactErrors table to determine which objects you want to import from the database backup into your repaired database.
-
Obtain exclusive access to your database to use the Compact and Repair command If you are the only person who uses your database, you can skip the rest of this section and go directly to Compact and repair a database.
A compact and repair operation requires exclusive access to the database file, because the operation may disrupt other users. You should notify other users when you plan to run a compact and repair operation, so that they can avoid using the database during that time.
Tell users how long they must avoid using the database. If you run compact and repair operations on a regular basis, keep a record of how long it takes. You can then make more precise estimates that give other users guidance on how long they should avoid using the database.
-
Obtain sufficient permissions to run a compact and repair operation If you are using an earlier version database file and are part of a workgroup, you might be unable to compact and repair the database on your own. If you do not have sufficient permissions and you need to compact and repair a database, contact your workgroup administrator for assistance.
Compact and repair a database
Automatically compact and repair a database when it closes
You can select the Compact on Close database option if you want to automatically compact and repair a database when it closes.
Note: Setting this option affects only the database that is currently open. You must set this option separately for each database that you want to automatically compact and repair.
-
Click the Microsoft Office Button
, and then click Access Options. -
In the Access Options dialog box, click Current Database.
-
Under Application Options, select the Compact on Close check box.
Manually compact and repair a database
In addition to or instead of using the Compact on Close database option, you can manually run the Compact and Repair command. You can run the command when you have a database open, and you can run the command on a database that is not open. You can also create a desktop shortcut that runs the Compact and Repair command on a particular database file.
Compact and repair a database that you have open
Note: If other users are also currently using the database file, you cannot perform a compact and repair operation.
-
Click the Microsoft Office Button
, point to Manage, and then, under Manage this database, click Compact and Repair Database.
Compact and repair a database that is not open
Note: If other users are currently using the database file, you cannot perform a compact and repair operation. While you run the compact and repair operation, no one can use the database file.
-
Start Access, but do not open a database.
-
Click the Microsoft Office Button
, point to Manage, and then, under Manage this database, click Compact and Repair Database. -
In the Database to Compact From dialog box, navigate to and double-click the database that you want to compact and repair.
Create a desktop shortcut that compacts and repairs a particular database
You can create a shortcut on your desktop that you can use to compact and repair a particular database.
Before you start, verify the location of the Msaccess.exe file on your computer. The Msaccess.exe file is typically located in the following folder:
C:\Program Files\Microsoft Office\Office12
If you do not find the Msaccess.exe file at that location, search for the file and make a note of the full path.
Create the desktop shortcut
-
Right-click the desktop, point to New, and then click Shortcut on the shortcut menu.
-
On the first page of the Create Shortcut Wizard, in the Type the location of the item box, type a double quotation mark ("), enter the full path for the Msaccess.exe file (including the file name), and then type another double quotation mark. (Alternatively, click Browse to locate and select the file. In this case, the quotation marks are added automatically.)
For example, type:"C:\Program Files\Microsoft Office\Office12\msaccess.exe"
-
Following the closing quotation mark, type a space, and then type the full path to the database that you want to compact and repair. If the path includes spaces, enclose the path in quotation marks. Type another space, and then type /compact.
For example, type: "C:\My Folder\My Database.accdb" /compact
-
Click Next.
-
In the Type a name for this shortcut box, type a name for the shortcut, and then click Finish.
The wizard creates the shortcut and places it on your desktop.
-
Whenever you want to compact and repair the database, double-click the shortcut.
Tip: To add the desktop shortcut to the Start menu, right-click the shortcut, and then click Pin to Start menu on the shortcut menu.
No comments:
Post a Comment