Database corruption means an occurrence of any error in the binary file format of any particular database. MS access database is one of the important application of Microsoft and it is used to store data in the file format of MDB and ACCDB. In this blog, we will discuss about the reason of corruption, symptoms, prevention of MS access database and more important the manual repairing process of MS access database files.
Common Reason of Database Corruption:
- Hardware failure such as failure of networking equipment or hard drives
- Confliction in software
- Multi user access
- Insufficient database design, etc.
Common Error Messages /Symptoms in MS Access Database:
- The database is in an unexpected state.
- Unrecognized Database Format. (Ensure you are using the correct version of Access)
- #DELETED# appears in tables when multi-user access is not used
- Invalid Bookmark
- Access crashes (Invalid Page Fault/GPF) when opening the database
- Access starts losing table records randomly
- The Microsoft Jet Database Engine could not find object Msys*/databases. Make sure the object exists and that you spell its name correctly & the path name correctly
- The database ‘filename.mdb’ needs to be repaired or isn’t a database file.
Learn how to improve MS Access performance:
- Normalize database tables
- Delete unnecessary Subdatasheets
- Compact your database on a regular basis
- Close all the applications that you are not using
- Make sure that each table in your database have a primary key
- Install Access on your local drive
- Split database
By some reason, if the access database file stop working, then what you need to perform to repair that file. I have shared here some important manual methods to repair the MS access database files.
Manual Methods to Repair the Corrupt MS Access Database:
Method 1: Use the Microsoft Jet Compact Utility
If there is minor issue in your Microsoft Access database then you can use this utility. This utility developed by Microsoft and called as JetComp.exe.
Method 2: Restore database from a previous backup
With this method, you need to try to delete the tables in the backup file and import the tables from the corrupt database. Follow any of the below method to import the tables:
1) Open the table and delete all rows from backup. Now, copy the data by using ADO (VBA code) from corrupt database.
2) Use the Access “Import Wizard” to import all data table.
Note: You cannot import data using Import Wizard. For that you have to open an existing page in new database. Follow the steps below to open the existing page:
- Click Objects -> Pages in the Database Window.
- Then, click on New. In the New Data Access Page dialog box, select Existing Web Page and click on OK Atlast, locate the data access page.
Method 3: Using inbuilt Compact and Repair Database
This method useful for large size database file and it helps to reduce the size of database file securely. Follow below steps to repair MS Access Database.
Step 1: Open the Microsoft Access program.
Step 2: Then, click on the Database Tools tab (display on the top).
Step 3: Here, click on Compact and Repair Database.
Step 4: In the Database to Compact From dialog box, browse the MS access database file. Then, select the file(s) and then click on Compact.
Step 5: Now, you need to create a new database in the Compact Database Into dialog box. Enter the name of file and click Save button.
Note: First, create a backup for database file and check there is no other user is currently using the same database file.
Method 4: Import Corrupt Database into new MS Access Database
Step 1: Open Microsoft Access and create a new database file. Click on Create to create new file in the right hand side panel.
Step 2: In a new database file, click External Data tab on the top.
Step 3: Click Access to import Access database.
Step 4: A new window opened with the name of Get External Data – Access Database. Here, click on Browse button and select the Access database file to import.
Step 5: Now, browse and select the Access database file in the File Open dialog box. Click Open for confirmation and then, click on OK to continue the process.
Step 6: Select the object in the Import Objects dialog box and then, click OK.
Step 7: All the selected objects are imported successfully. Check Save import steps box and click Close.
Method 5: Turn off AutoCorrect feature
This feature in MS Access usually create an issue in the access file so, I recommend you to turn off that feature. Follow below steps to disable this option:
For MS Access 2010:
- Go to File-> Options-> Current Database and uncheck all option (check under Name AutoCorrect Options).
For MS Access 2007:
- Go to Access logo-> Access Options and uncheck the Track Name Autocorrect Info.
For MS Access 2000, 2002, and 2003:
- Go to Tools-> Options-> General and uncheck all options.
If you have applied all the above methods to repair your MS access database files and still the files are corrupted, then you have only one option, i.e use any MS Access Recovery Tool. This tool is very easy to use and repair the any number of corrupted database files immediately.