One very common issue reported by the MS Access user community is data corruption. As every businesses relies heavily on their database, it is frightening to imagine operating a business for any period of time without it. When data corruption strikes, the solution may take an extended time to resolve. Often, a talented and experienced database solutions expert can quickly fix the database. At other times, the solution may be much more time consuming. In the most severe cases, a business may experience permanent loss of portions or all of their data. In this event, the only solution may be to manually recreate the lost portions of the database and continue forward. At this crossroad, a tough decision must be made. Do you rebuild using MS Access? Or do you take this opportunity to upgrade to a more robust and secure database application?
What Makes MS Access Vulnerable?
The most powerful feature of any database is that it can be utilized simultaneously by multiple users in different offices. Since MS Access is a “file system” relational database, every part of the database (such as the data tables, queries, forms, reports, etc.) is stored within as little as one .MDB file. Therefore, MS Access can often be utilized in a single user environment for a long time without any sign of corruption. However, in a multi-user environment the .MDB file(s) has to be shared by many users in different locations on the network. In simple terms, this means that every record within the entire database is temporarily utilized on a single workstation while other users are accessing the same records from other workstations. This is, unfortunately, the way MS Access works and creates many more opportunities for corruption as compared to other systems. With a database helping to manage mission critical activities such as record keeping, finances, scheduling or many other functions, it is prudent to take measures to prevent data corruption. In Part 2 of this post, we will discuss the best practices for preventing corruption in MS Access databases.
Common Causes of MS Access Corruption
Software
- Any application crashing (MS Access or any other one) and bringing down others.
- Workstations on different MS Access service packs (particularly JET) causing inconsistent behavior.
Hardware / Systems
- Any time the file server is subject to a reboot, shutdown or failure.
- Power Supply Issues: inadequate power supply, spikes, dips, brown outs, outages
- Overheating, intermittent components, device conflicts.
- Intermittent cable connectors, hubs/switches, network cards
- Unstable networks such as WiFi.
- Insufficient hard disk space for the temporary folder and/or virtual memory.
Users
- Switching computer off without closing MS Access.
- Leaving the database open when not in use
When Corruption Strikes
When a file system disaster occurs, MS Access may provide a warning and prompt the user to run the Compact and Repair Database command. Before running this process, make a copy of the corrupted .MDB file, ensure that you have the latest backup of the database and have all of the users log out of the database.
If running the Compact and Repair Database command does not repair the database, you may need to use an alternate method of recovery. There are other tools available that report to fix MS Access databases. However not all tools are the same. Therefore, it is highly recommended that you utilize a reputable expert to determine the best tool to recover your .MDB database files. An inferior repair tool or an inexperienced person may, in fact, make the situation worse instead of better. Be sure to select an expert that has experience in repairing MS Access databases.
—-In Part 2 of this Post we will discuss the best practices for preventing MS Access Database corruption.