Being an Access database user you all must have encountered or heard of Access Database Corruption issue at some point of your life. If yes, then you all must be well aware with the reality that how sarcastic this situation is to tackle? Well if you are also one victim of this Access database corruption then don’t get panic. As the following post will help you in examining the process that you should follow to ensure optimal functioning of your database.
This particular blog emphasizes on the complete list of strategies to repair up your corrupt Access database. In fact, you will find strategies to repair Access database, tips and tactics to avoid corruption and much more on this page.
Well for the quick precape of this complete blog you can take a look over the highlights. If you are eagerly looking for the fixes to troubleshoot corrupt/damage Access Database issue then you will really enjoy the list.
- What Makes Your Access Database To Get Stuck Into Corruption Issue?
- Types Of Access Database File Affected Due To Corruption
- Why You Need To Compact And Repair Your Database?
- Things That Compact And Repair in-built tool Performs
- Frequent Error Messages Encountered Due To Access Database Corruption
- Precaution To Take Before The Starting The Access Database Recovery Procedure:
- What Manual Fixes Should Be Tried To Repair Access Database Corruption?
- Fixes To Troubleshoot Damaged Access Database
- Ultimate Solution To Repair Corrupt/Damaged Access Database
- How To Avoid Access Database Corruption?
What Makes Your Access Database To Get Stuck Into Corruption Issue?
With the passage of time, Microsoft Access database gets enlarge in its size and unnecessarily captures a lot of disk space.
Repeated modification in the database file may also result in database corruption. This risk of corruption alleviates more with sharing of database by multiple users over a network. That’s why it’s always recommended to periodically run the compact and repair database tool to ensure the consistency of your data.
Apart from that, sometime you will also get prompted by Microsoft Access to perform database repair when the database engine encounters errors within a file.
The risk of corruption alleviates more if users frequently edit data in memo fields and this risk grows more with the time.
Often this type of corruption results from problem with Visual Basic For Applications (VBA) module and this poses the risk of database design damage. Like, sudden missing of some VBA code or unusable forms.
It’s very rarely seen that database file corruption results in data loss. Usually this loss is limited to last action performed by the user i.e. a single change in the data. When user starts modifying data and change is interrupted due to some reasons. Like, network loss then access marks that database file as corrupted one. The chances are also remains that some data may be missing after the repair is completed.
What Types Of Files Get Affected In Access Database Corruption?
Access database programs use two types of Access Database files to store data i.e MDB & ACCDB. Let’s discuss these file format briefly:
- MDB (.mdb):
It’s an old database file format which is used by older versions of Microsoft Access (Microsoft Access 2003 and earlier).
- ACCDB (.accdb) or Access Database:
Whereas, .accdb file format is for database file which is formed in Microsoft Access Version 2007 and its later versions. One can easily view or edit .accdb file in Microsoft Access 2007 and its later versions. Generally, it is much similar to MDB file format, but this offers some additional features like saving and storing file attachments.
File server database like Access .mdb or .accdb file needs to be periodically compacted and repaired for optimal performance. And to minimize the risk of such .mdb or .accdb file corruption. Compacting the database eliminates several of the small error blots that occurs meanwhile the addition or deletion of the data in the Access Database. Below here is the brief discussion on compact and repair inbuilt utility tool.
Why You Need To Compact And Repair Your Database?
It is mentioned above that periodic compact and repair access database is necessary. But do you know why it’s important to do. Well there are mainly two important reasons behind this task. So, let’s discuss them one by one.
Enormously Growth In Database Size:
First one is excessive raise in the size of the database file with the time. This growth of size is due to addition of new data to the database. Other than this, growth of database size is formed because of the temporary objects created by database and sometime unused space captured by deleted objects. By compacting them with the inbuilt compact and repair utility tool you can free-up this space.
Corruption Of The Database Files:
Secondly, when the database files (.mdb/.accdb) may get corrupted, mainly the files that are accessed by multiple users simultaneously over the shared network connection. By repairing up the database you can get rid of this corruption issues. This ultimately allows you to be continuing with the use of database and preserving the integrity of the database.
Things That Compact And Repair Performs:
After knowing about the necessity of the compact and repair it’s important to know about the list of actions that compact and repair inbuilt utility tool performs:
- This tool helps in reorganizing the page of the table so that they easily get resided on the adjacent database pages. This ultimately helps in improving the performance because the table is no more fragmented across the database.
- It also flags out all the queries so that they will recompile the next time that query is run. Well it’s important because database statistics can change and previously complied query which has an accurate query plan.
- Clears the unused space created by objects and records deletion. The occupied space is marked as captured until it gets saved by any new database data. However, size of database never gets down unless you compact the database. Overall the moral of the story is the database which frequently gets into addition, deletion or updating like procedures needs to be compacted frequently.
- Regenerates the table statistics which are useful in the query optimization process. As the statistics of the table gets out-of date with the time. Usually this happens when the transaction is rolled back or if database is not properly closed due to the unexpected power loss. Or the system gets shut down before program that is using Microsoft jet has changed to quit completely.
Microsoft Access Database Compact And Repair Inbuilt Utility Tool To Minimize Corruption And Avoid Bloat
Here are the different scenarios in which you can use compact and repair Access Database utility tool to recover corrupt Access database. Have a look, over them one by one:
— Compact and repair a database when it closes
— Compact and repair a database that you have open
— Compact and repair a database that is not open
— Compact and repair a corrupted database when Access prompts you
1#Automatically Compact And Repair A Database When It Closes
Select the compact on close database options in order to automatically Compact And Repair a database when it is close. This option only works for the databases that are already open. So set this database individually for each of the database that you want to compact and repair. In case of multi-user databases, it’s better not to avoid using this option. As, using this option can temporarily disrupt database availability.
Steps To Compact And Repair A Database When It Closes
- Firstly open the access database, go to the File> Options.
- In the Access Options dialog box, select Current Database.
- In the Application Options, choose the Compact on Close check box.
- After then press the OK option.
- At last close your database and reopen it again to see the changes.
2#Manually Compact And Repair A Database That You Have Open
This method is for the database that you can easily open or which is already open. So here are the steps to perform this task:
- Choose File> Info > Compact & Repair Database.
- Make a copy of the compacted and repaired database in the same location.
3#Manually Compact And Repair A Database That Is Not Open
This method seems helpful when the Access database denies to get opened. But before starting up the procedure make sure that no any other users are currently using the database file.
- Start Access.
- In Access 2013, Access 2016, and Access 2019:
- Go to the template page, and double tap the Blank Database.
- Select Database Tools> Compact and Repair Database.
- In the Database to Compact From dialog box, search for the database and make double tap to the database which you want to compact and repair.
- This step will create a copy of compacted and repaired database in same location.
4#Compact And Repair A Corrupted Database When Access Prompts You
This method seems helpful when you try to open the corrupted database files. And, at that time Access prompts you to compact and repair the database. Select the yes option, as this will offer you to perform two things:
- If Access has already completed the repair process of corrupted file, then it will display the message stating that repair was successful. You need to check out the database content to make sure everything is fine.
- In case the Access is partially repaired, it keeps track of database objects that won’t repair in system table named MSysCompactErrors. Access will open this MSysCompactErrors in datasheet view. If you have kept backup of your database before the occurrence of the database, then you can use the MSysCompactErrors table to make selection for the objects that needs to get imported into the repaired database.
To explore system tables, make a right click on the navigation title bar and after then in the Navigation Options dialog box, choose Show System Objects.
Things To Remember:
- Keep it in mind that compact and repair makes a new database files. So any NTFS file permission applied on the original database will not be applied over the compacted database. Therefore, it’s better to use user-level security instead of NTFS permission.
- It’s good to schedule both backups and compact/repair operations to perform on the regular basis. So, it’s an excellent idea to schedule into your database administration maintenance plans.
Frequent Error Messages Encountered Due To Access Database Corruption
If you are a newbie in getting this Access database corruption then it’s important to know what kind of error or issues give a clear indication of this corruption.
Take a look over the listed error message which encounters mainly at the time of Access database corruption.
- The database ‘filename.mdb’ needs to be repaired or isn’t a database file.
- Microsoft Access has detected corruption in this file.
- Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
- The database is in an unexpected state.
- Unrecognized database format ‘path/filename.mdb’.
- Record(s) cannot be read; no read permission on ‘xxxx’.
- Microsoft Access Error 2554 “Can’t find the database you specified”
Precaution To Take Before Starting Up The Access Database Recovery Procedure
Before approaching to try the manual fixes for the recovery of the Corrupt Access Database. You need to be careful about the following things before starting up the Access Database Repair:
- Make sure that you have enough free storage space on hard disk for both of the original and compacted version of Access database. It means you need to have enough free storage space around twice the size of Access database on that drive. It will be better if you move the Access Database to the drives which have some more space.
- Check whether you have both the permission of Open/Run and Open Exclusive for the Access database.
- Make sure that access database is not located on read-only network share or its file attribute is set to read only.
- If you have owned the database, make sure you set these permissions. But if you haven’t own the database, then in that case you need to contact the owner to find out whether you can get these permissions.
- Make sure no users is currently been using this Access Database.
What Manual Fixes Should Be Tried To Repair Access Database Corruption ?
Other than using the inbuilt utility tool compact and repair database, you have other option also to Repair Corrupt Access Database. So, let’s see what are the other solutions available to fix corrupt access database?
1#Repair Access Database using JetComp.exe
MS Access offers a freeware utility JetComp.exe, in order to repair mdb/accdb file created with Microsoft Jet database engine 3.x.
Note: before executing JetComp.exe, you need to rename the Master Client database to PROB.mdb.
Now you can easily run JetComp.exe from the MonTel Application/support directory. Here are the following steps to repair Access database using JetComp.exe:
Step 1: firstly you need to enter the full path (MonTel Datadirectory/CLIENTDB) and name PROB.mdb in Database to Compact From (Source): field.
You need to enter full path (MonTel Datadirectory/CLIENTDB) and full name ([ClientDB] section ClientDatabaseName + “.MDB”) that is likely to be CLIENT32.MDB.
Note: don’t make modification in any other fields.
Step 3: At last tap to the Compact option.
- Once you get success, allow other users also to MonTel.
- But if in case you won’t get the success, don’t forget to restore CLIENT32.MDB (or whatever it’s named) from your backup copy. After this you can delete PROB.mdb.
2#Import Corrupt Access Database into a New One
Second most opted solution of fixing corrupt Access Database is importing data from corrupt Access Database to new one. Though this step doesn’t repair up your corrupt Access Database but one can easily recover corrupt Access database data with this. So, here are the following steps that you need to follow.
Step 1: At first, open the Microsoft Access on your PC.
Step 2: on the right hand side pane, tap to the option of “create new database” files. Assign name for the file and choose the location. Tap to the Create option to create the new database.
Step 3: This will make a database file and open it up in Microsoft Access program. Click the external data at the top.
Step 4: To import Access database tap to the Access option.
Step 5: You will see a get external data Access Database window is get opened on your screen.
On this opened window tap to the Browse option to make selection for the file that you want to import.
On the opened dialog box of File Open, browse access database files and select it by clicking. Tap to the Open option to make conformation for the selected items.
In the edit box, you can easily take a look over the selected files. For further continuing of the process tap to the OK option.
On Import objects dialogue box, choose the objects that you want to import from the selected Access database files. After the selection of the objects, tap to the OK option.
Now you will successfully import all selected objects.
If you want to save the above performed import steps for future work then check the Save import steps box. At last tap to the Close option.
This import access database feature actually helps to import all the Access file objects like, Tables, Forms, Reports, Queries, Macros and Modules from corrupted Access Database to new file.
Fixes To Troubleshoot Damaged Access Database
1#Deletion Of The .ldb/.laccdb File:
.ldb/.laccdb file is mainly used to find which records is been locked in shared database and by whom. If database is open for shared usage then the .ldb/.laccdb file automatically gets created having the same name as that of corresponding database (.mdb/.accdb).
Delete the .ldb/.laccdb file is it is present but before that close the corresponding .mdb/.accdb file.
How about an example,
Suppose, that if you open the sample database Northwind.mdb for shared use in C:\Msoffice\Access folder, then it will automatically create a file named Northwind.ldb in the same folder. Well this .LDB file automatically gets deleted when the last user quit the database with these two exceptions:
- When the last user don’t have permission of folder deletion that is having .mdb file
- Or when database is corrupted
This .ldb file has the complete list of users who all have opened the Database.
2#Re-Import The ASCII File:
If your access database table is damaged and even after trying the previous step you won’t recovered the table. Then in that case just try the next steps i.e Re-importing The ASCII File.
Steps to follow:
- In your Microsoft Access, export the table to an ASCII (delimited text) file.
If you don’t know how to perform this task then check out the topic “How To Export Data Or Database Objects In Microsoft Access”.
- If any relationship associated with this table then delete it first and then delete the table from the database.
- Compact the database. And after then re-create the table or any relationship if it previously contains.
- Use the word processor to examine the ASCII file for any bad data or records and remove it.
- Now save the ASCII text file format.
- After then you need to re-import ASCII file into the newly re-created table.
- You can also re-enter any records that you were previously forced to delete.
3#Repair Damaged Form Or Reports:
If your access database form or report is got damaged then the damage can be either in the form or report itself. Or the chances are also that one or more form’s or report’s control got damaged.
To fix damaged form or reports you have option like:
- delete the form or report and import it from the backup copy of your database.
- If the damage is encountered in the form or report then create a new form or report. Then from the original form or report copy the controls to your newly created form/report.
- But if the control of the form or report gets damaged then in that case you need to create new form/ report. After then re-create the controls on your new form/report. Recreating the controls is the best option because its quiet difficult to identify which control is actually having the issue.
4#Try Importing Of Objects:
If the above step won’t help you in recovery of damaged Access Database, then also need not to worry because there are other ways still left to try.
In this step you need to try creating new database try creating a new database and import all the objects from the old database to new one. After then create the relationships. This procedure helps you to resolve issues with the damaged system tables in the database.
Things to remember:
With the import wizard you can’t import data access pages. Instead you can open the existing data access page in the new database.
Here are the steps to accomplish this task:
- In the database windows, click pages under objects.
- Tap to the New
- In the opened New Data Access Page dialog box, tap to the Existing Web page, after then tap to the Ok
- From the Locate Web Page window, search for the location of the data access page.
5#Repair Damaged Macro Or Module:
If the macro or module of the Access Database is got damaged then the chance may be that damage can be in the macro or module itself. Other than this may be the content of macro/module somehow got damaged. Options To fix damaged macros or module:
- Just delete the macro or module and import it again from your backup copy.
- If macro or module of database got damaged then create a new macro or module and copy the contents of original macro or module.
- Damage may encounter with your module as text file and delete any bad data if present. After then reload the text file into your new module.
- But if the content of the macro or module gets damaged then you need to create new macro or module. After then re-create the content of original macro or module.
If you can’t repair your damaged MDB/ACCDB database file with these aforementioned steps, then it means the database is probably damaged beyond repair. And you need reliable third party software to successfully repair the corrupt Access Database.
Worried…? from where you get such software. Well not worry as in our next section we are going to discuss about this particular thing only. So let’s dive right in….
Ultimate Solution To Repair Corrupt/Damaged Access Database
In severe access database corruption, sometime manual tricks fail to work. In such situation, you must try the Access Database Repair N Recovery Software. This ultimate software is quiet efficient tool to repair access database file and restore Access Database objects from corrupted database files.
It repairs ACCDB and MDB files created by popular version of MS Access program. This highly proficient tool can easily fix any level of Access Database Corruption. You can recover each and every file objects including BLOB, OLE and MEMO data from corrupted access database files and restores them in new database files.
How To Avoid Access Database Corruption?
Before letting you know about the fixes to repair corrupt Access database, it’s high time to share some highly useful tips to avoid access database corruption.
The steps mentioned in this section are like forever ways to prevent Access Database Corruption. So, try to keep these things in mind. Though corruption is an inevitable occurrence, but one can easily avoid such riskier situation just following the tips discussed below:
Tip #1 – Take Regular Backup
Making habit of maintaining the regular backup of Access database is really very helpful. As, it help in protecting or securing your data in a better way. Using this valid backup file you have the option to restore data during the data loss due to accidental deletion or corruption like issues.
Tip #2 – Close Access Program Properly
Cancelling or terminating the MS Access application in an improper way can result in Access database corruption issues. So, always close access program properly in the proper manner like this:
Click File tab > Exit in the option.
Tip #3 – Close all the Connections Safely
Before closing up your Access database, first of all close the entire active connection running in the Access Database. Otherwise this will take your Access Database towards the corruption glitches.
Tip #4 – Split Access Database
If two or more users are simultaneously using the same database then splitting up the access database is the best option. Well you can split up your access database into two parts: Front end and Back-end.
As, in the front end queries, data, form and reports get stored whereas on the back-end entire table along with the data is saved.
Tip #5 – Collision of Mac and Windows Users
The chances of Access database corruption get higher when the windows and mac users share same network. As compared to Windows, Mac system creates heavy traffic during the process of transfer and printing graphics. So, don’t allow the Windows and Mac users to get collided on the same network.
Some More Helpful Tips:
- Try to avoid power loss situation mainly at the time of database writing. Power loss during writing process on the database can take your database in a suspect state.
- Just like power loss, loss of network connection also harshly affects the database.
- Try to avoid sudden or abnormal termination of Microsoft Jet connections like power loss, application gets shutdown from the task manager, manual shutdown, and so on.
- At the time of programming, close all objects of DAO and ADO including, Recordset, QueryDef, TableDef, and Database objects etc. that is currently open.
- System error always causes abnormal termination. So, if your database is prone to such fatal error then try to resolve the errors before the database becomes too damaged to get opened or before the possibility of recovery fails.
For more information on this fatal system error, check out the helpful article of Microsoft Knowledge Base:
294301 ACC2002: How to troubleshoot fatal system errors in Access 2002 running on Microsoft Windows 2000
- Make a habit of compacting the database regularly.
- Avoid large number of opening or closing operations in loop. Because more 40,000 successive open or close operation causes corruption.
There you have it, the best strategies regarding Access Database Repair for any of your Access version 2002/2007/2010/2013/2016/2019.
Now, we all are eagerly waiting for your response ….which strategy from this post are you ready to try first? Are you going with manual methods or maybe you wanted to employ the third-party Access Database Repair Tool to resolve MS Access errors and blots?
Either way, let us know leaving a comment below right now. Apart from this if any other MS Access Database issue is obstructing you to work flawlessly then also you can ask your queries in our FAQ SECTION.