The Compact and Repair utility in Microsoft Access is a useful tool for trying to optimize or trying to repair Microsoft Access database files. This article describes this Access utility and offers additional alternatives for trying to repair damaged databases. This article also provides information about what can cause databases to become damaged. Note Microsoft Jet, the database engine that is used in Microsoft Access, is a file sharing database system.
When Microsoft Jet is used in a multi-user environment, multiple client processes are using file read, write, and locking operations on a shared database. Because multiple client processes are reading and writing to the same database and because Jet does not use a transaction log as do the more advanced database systems, such as SQL Server , it is not possible to reliably prevent any and all database corruption.
More Information. Although the steps that are outlined in this article are usually successful at recovering damaged database files, to safeguard your data, Microsoft recommends that you create a backup copy of your database file as often as you can. Description of the Compact and Repair Utility Compacting a database When you compact a database, the compact process will reclaim unused space in a database that is created by object and record deletions. It does so by creating a new destination database and by copying each object in the old database to the new database.
If you choose to compact the database into the original database name instead of to a new database, the compact process creates a temporary database, exports all the objects from the original database into the temporary database, removes the original database, and then renames the temporary database to the name of the original database.
What is Microsoft Access | What is an Access Database
The following is a list of actions that the compact process performs: It reorganizes the pages of a table so that they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database.
It reclaims unused space that is created by object and record deletions. When objects or records are deleted from the database, the space they occupied is marked as available for new additions to the database. However, the size of the database never shrinks unless you compact the database. For databases in which objects and records are frequently added, deleted, and updated, you should compact frequently. It resets incrementing AutoNumber fields so that the next value allocated will be one more than the highest value in the remaining records.
For example, if all the records in the database have been deleted, after you compact the database, the value in the AutoNumber field will be 1 when the next record is added. If the highest remaining AutoNumber value in the database is 50, after you compact the database, the value in the AutoNumber field will be 51 when the next record is added. Note that this is true even if records containing values higher than 50 were added previously but were deleted prior to compacting. It regenerates the table statistics that are used in the query optimization process.
These statistics can become out-of-date over time. This typically happens if transactions are rolled back or if the database is not properly closed because of an unexpected power loss or because the computer is turned off before a program that is using Microsoft Jet has had a change to quit completely. It flags all queries so that they will be recompiled the next time that the query is run. This is important because database statistics can change and a previously compiled query may have an inaccurate query plan. Repairing a database The repair process tries to repair only the tables, the queries, and the indexes in the database.
It does not try to repair damaged forms, reports, macros, or modules. What to Make Sure of Before You Run the Compact and Repair Utility Before you run the Compact and Repair utility on a database, make sure of the following: Make sure that you have enough free storage space on your hard disk for both the original and the compacted versions of the Access database.
This means that you must have enough free storage space for at least twice the size of your Access database on that drive.
Access Database Development Services - Blue Claw Database
If you need to free some space, delete any unneeded files from that drive, or, if possible, move the Access database to a drive that has more free space. If you own the database, make sure to set these permissions. If you do not own the database, contact its owner to find out if you can get these permissions. Make sure that no user has the Access database open. Make sure that the Access database is not located on a read-only network share or has its file attribute set to Read-only. Steps for trying to repair a damaged database The following steps outline a general method that you can use to try to repair a damaged database: Make a copy of the damaged database.
Delete the. You must close the corresponding. If a database is opened for shared use, the. For example, if you open the Northwind. Run the Compact and Repair utility. To do so, follow these steps: If the database is open, close it. In the Database to Compact From dialog box, select the file that you want to compact, and then click Compact. In the Compact Database Into dialog box, enter a new file name, and then click Save. If the compact and repair does not succeed, you will receive a message stating so.
This means the damage is so severe that it cannot be corrected. If the previous steps fail to recover your damaged database, try creating a new database and importing the objects, one-by-one, from the old database to the new one. Then re-create the relationships. This technique resolves problems with damaged system tables in the database. Note You cannot import data access pages with the Import Wizard. Instead, open an existing data access page in the new database. To do so, follow these steps: In the Database window, click Pages under Objects.
- Issues in Islamic Banking: Selected Papers (Islamic Economics Series).
- 200 Years of British Hydrogeology (Geological Society Special Publication No. 225).
- Probability and Statistics: A Didactic Introduction.
- What is programming?.
- Auguste Comte: Volume 3: An Intellectual Biography (Auguste Comte Intellectual Biography).
- Serious fun with flexagons: a compendium and guide.
- Navigation menu.
Click New. In the Locate Web Page window, browse to the location of the data access page. If the damage is in a table, and the previous steps have not recovered the table, try the following: In Microsoft Access, export the table to an ASCII delimited text file. For more information view the topic "Export data or database objects. Understanding Reference Error Messages There are several error messages that relate to a missing file or to a file that has a different version from the version that is used in the database. In most cases, you can search the Microsoft Knowledge Base for an article about the specific error message, and you can then resolve the error by following the steps in the article.
In some cases, a dependency file is not correctly matched with the primary file. The following list describes some of the reference error messages that you may receive. However, note that the list does not include all of the possible reference error messages. You can search the Microsoft Knowledge Base for articles that describe the various forms of this error message.
You may also receive this error message if a form or a report contains an ActiveX control. Often the file is flagged as Missing in the References dialog box. Sometimes the file exists on the development computer but not on the target computer. A design-time license permits you to insert licensed ActiveX controls from Office Developer into forms and reports in an Access database.
A run-time license permits you to use the ActiveX controls in an Access database on a computer that does not have Office Developer installed, but a run-time license does not permit you to insert new licensed ActiveX controls. To install a run-time license, distribute the ActiveX controls by using the Package and Deployment Wizard, which writes the license for the controls in the target computer's registry.
A Missing flag, which you may see when you open a module in Design view and then click References on the Tools menu, indicates that the reference to the Common Dialog control on the target computer does not match the source in the database file from the development computer.
Table of Contents
If you distribute a database file without installing the distributable Common Dialog control, the control's reference may be flagged as Missing , or you may receive an error message that "You don't have the license required to use this ActiveX control" if the non-distributable control is already installed on the target computer. Even when the database file is part of a run-time application, you may receive the error message that "You don't have the license required to use this ActiveX control" if the non-distributable control that is already installed on the target computer is of a later version than the control that is provided by your run-time application.
This issue can occur because the Setup program does not overwrite later versions of a file with an earlier version of the same file. Reregistering a File It is possible for a file to be in the reference list without being correctly registered in the registry. If you suspect that this might be the case, follow these steps to reregister the file: In Microsoft Windows NT 4. In the Named box, or in the Search for files and folders named box, type regsvr In the Look in box, click the root of the hard disk usually C:.
Click to select the Include Subfolders check box if it is not already selected, and then click Find Now or Search Now. After you find the file, click Start , click Run , and then delete anything that is in the Open box. Drag the Regsvr Repeat steps 2 through 6, this time searching for FileName. After the FileName.