Article ID: 283849 - View products that this article applies to.
This article was previously published under Q283849
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
For a Microsoft Access 97 version of this article, see 279334
For a Microsoft Access 2000 version of this article, see 209137
There are several things that can happen, both inside and outside of Access, that may leave your database file damaged (corrupted). The symptoms of a corrupted database can range from #Deleted appearing in certain records, to you being unable to open one of the objects in the database, to you being unable to open the database file in Access altogether. 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. If you need a database system to run 24 hours a day, seven days a week in a multi-user environment, Microsoft recommends that you use a true client/server database system that supports durable transactions, such as Microsoft SQL Server.
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 databaseWhen 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.
The following is a list of actions that the compact process performs:
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 UtilityBefore you run the Compact and Repair utility on a database, make sure of the following:
Steps for trying to repair a damaged databaseThe following steps outline a general method that you can use to try to repair a damaged database:
As a final alternative, some consultants may provide a Microsoft Access database repair service. Because this is such a specialized service, the most efficient way to locate a consultant is to post a message in the Microsoft Access "Third Party and User Groups" Internet newsgroup, which has the following newsgroup address:
microsoft.public.access.3rdpartyusrgrpFor additional information about Microsoft Access Internet newsgroups, please see the following article in the Microsoft Knowledge Base: For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/287756/ )AutoNumber field is not reset after you compact a database
Why .mdb files may become corruptedThere are three main reasons why an .mdb file may become corrupted, as follows:
Interrupted write operationYou should always properly quit Access by clicking Exit or Close on the File menu. If a database is open and writing data when Access is abnormally shut down, the Jet database engine may mark the database as suspect/corrupted. This can happen if you manually turn off the computer without first quitting Windows or if you lose power. Other situations can occur that do not shut down Access but that may still interfere with Jet writing data to the disk while the database is open. This can happen, for example, when networks experience data collisions or when disk drives malfunction. If any of these interruptions occur, Jet may mark the database as potentially corrupted.
When Jet begins a write operation, it sets a flag, and it then resets the flag when the operation is complete. If a write operation is interrupted, the flag remains set. When you try to open that database again, Jet determines that the flag is set and reports that the database is corrupted. In most cases, the data in the database is not actually corrupted, but the set flag alerts Jet that corruption may have occurred. In cases such as this, compacting or repairing the database (or both) can typically restore the database. Fortunately, there are ways to determine which user and workstation was responsible for marking the file as suspect. With Microsoft Visual Basic for Applications in Access, you can output a list of users who are logged into a specific database.
208449For more information about how to do this, see the "How to determine which users/workstations are causing the file to be marked suspect" section later in this article.
(http://support.microsoft.com/kb/208449/ )Microsoft Access newsgroups available on the Internet
Faulty networking hardwareSometimes corruption can occur without the Jet database engine being involved. For example, faulty networking hardware can cause a file to become corrupted. The cause can be one or more links in the hardware chain between the computer that the database resides on and the computer that has the database open. This list includes, but is not limited to, network interface cards, network cabling, routers, and hubs.
Hardware-based corruption is typically indicated by .mdb files that cannot be restored through the use of compacting, repairing, or Jetcomp. Hardware corruption will typically recur until the responsible hardware is repaired or replaced.
Opening and saving the .mdb file in another programThere is no way to recover an .mdb file that was opened and then saved in a different program. For example, you could open and save an .mdb file in Microsoft Word, but if you were to do so, the .mdb file could never be recovered, except from a backup copy. If you accidentally open an .mdb file in another application, be sure not to save it. It really serves no purpose to open an .mdb file in another application because if you do, all you see is a seemingly random series of characters.
How to determine which users/workstations are causing the file to be marked as suspectWhen you troubleshoot to determine what is causing database corruption, you may have to see who is logged into the database. With Microsoft Visual Basic for Applications in Access 2002 or in Access 2003, you can access a list of users who are logged into a specific database.
For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/285822/ )How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2002 or in Access 2003
Steps that you can take to prevent corruptionTo prevent database corruption:
Special note on converted databasesIn versions of Access earlier than Access 2002, if there were errors while you were converting a database to the current version, there was no easy way to determine which objects were affected and possibly contained noticeable corruption.
When Microsoft Access 2002 or later encounters errors while converting an Access file, you can view a summary of these errors by opening the Conversion Errors table in the new Access file. The Conversion Errors table contains the following columns:
Object Type. The type of database object in which Access encountered an error, or "Database" if Access encountered an error that is not specific to a particular type of object.
Object Name. The name of the object in which Access encountered an error. If Access encounters a compilation error during conversion, however, the name of the module that contains the error is not specified.
Error Description: If necessary, you can press SHIFT+F2 to view the entire description of the error.
For more information about repairing databases, click Microsoft Access Help on the Help menu, type repairing in the Office Assistant or the Answer Wizard, and then click Search to view the topic. For additional information about troubleshooting databases in earlier versions of Access, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/209137/ )How to troubleshoot and repair a damaged Jet 4.0 database
(http://support.microsoft.com/kb/279334/ )How to repair a damaged Jet 3.5 database
(http://support.microsoft.com/kb/109953/ )How to troubleshoot/repair damaged Jet 3.0 and prior databases
(http://support.microsoft.com/kb/284152/ )How to troubleshoot fatal system errors in Access 2002 running on Windows Millennium
Article ID: 283849 - Last Review: May 7, 2007 - Revision: 6.4