Access (Jet) Database Corruptions
TSB1019 | Tuesday, 13 November 2001
finPOWER, emPOWER and fdPOWER can use either a Microsoft Access or Microsoft SQL Server database. This article applies to Microsoft Access (Jet) databases.
Access databases may become corrupt due to a number of different factors. The symptoms of a corrupt database can range from run-time errors whilst processing data to the complete inability to open the file in the POWER Series or even in Access.
Microsoft Jet (the database "engine" behind Access) is a file-sharing database system. A file-sharing database is one in which all the processing of the file takes place at the client. When a file-sharing database is used in a multiuser environment, multiple client processes are using file read, write, and locking operations on the same shared file across a network. If, for any reason, a process cannot be completed, the file can be left in an incomplete or a corrupted state. Two examples of when a process may not be completed is when a client is terminated unexpectedly or when a network connection to a server is dropped.
Please note that backups form the most important defense against database corruption. If your database is corrupt and unrepairable the only option is to restore to a backup. The only 100% safe thing to do after an Access database corruption occurs is go back to a good backup.
This article highlights some of the possible causes for database corruption. For more information on repairing an Access database see technical support article TSB1023.
For more information regarding Access database corruptions see Microsoft Technet article Q209137.
Steps you can take to avoid data corruption include, a summary of the above:
You should always exit finPOWER properly by clicking Exit on the File menu.
If a database is open and writing data when the POWER Series is abnormally shut down, the database may be marked as corrupted. This can happen if the computer is turned off or a power failure occurs.
When Jet begins a write operation, it sets a flag, and resets the flag when the operation is completed. 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, repairing the database can typically restore the database.
There is no way to recover an .mdb file that was opened and then saved in a program other than finPOWER, emPOWER, fdPOWER, or Access. For example, Microsoft Word allows you to open an Access database and then save it (although it serves no good purpose to do so because if you open an MDB file in another program, all you can see are extended characters).
Saving the file in this manner will cause the .mdb file to prompt you for a database password when you try to open it in Access even though the file may have never been password-protected in Access. The password prompt occurs in such cases because the first byte range that Access checks when it opens a file is where the database password would be. If that byte contains corrupted data, Access treats the file as being password protected. Even if there were a way to get around the password prompt in this case, the database would still be unrecoverable because the binary structure is scrambled and therefore unreadable to Access. Recovering a backup copy of the file is the only solution in this case.
finPOWER, emPOWER, fdPOWER do not use or expect a password for a Database.
If you restore back to a backup there is always the possibility that the backup is actually corrupt. Either the database was already corrupt when it was originally backed up or it did not restore correctly or the backup media is faulty.
When restoring to a backup, if that restore fails and overwrites the "corrupt" database you could be left with a bigger mess. Always rename the existing corrupt database rather than restoring over the top of it.
See knowledgebase article TSB1015 for important information regarding backing up and copying databases.
Ensure that you have a robust file server that is capable of handling the number of users and the requests being made to the Microsoft Jet database file and that is also not overtaxed with handling many other processes, such as acting as a Windows Domain Controller, Exchange Server, SQL Server, and so on.
DO NOT STORE Access databases on a non-Microsoft Windows operating system.
One reason for this recommendation is that a network administrator, or another owner of the server, may reboot the computer to fix a problem with another important service (such as the mail server), or may reboot after applying new software, a service pack, or hot fix, and may forget that the Microsoft Jet database is currently shared on the server. Rebooting the file server will cause unexpected interruption of the client connections to the database and may cause database corruption. To prevent this interrupted client connection, all clients should close the database before the file server is rebooted or software updates to the file server are applied.
A robust file server should also be placed in a secured location where it cannot be accidentally switched off. The server should have an uninterrupted power supply (UPS) to protect it from intermittent power outages or power fluctuations. The network file server should also have high performance hard drives, a good network card, and plenty of RAM to ensure that the server is not overly taxed by the load placed on it.
Ensure that you have a stable and fast network with good solid network connectivity to the network file server.
Avoid using finPOWER, emPOWER, fdPOWER over a modem connection or any other less-than-reliable network transport. Because Microsoft Jet is a file-sharing database system, any less-than-reliable network transport increases the chances of a dropped client, which in turn increases the chance of database corruption.
Installing the latest operating system service pack on the network file server ensures that you have the latest bug fixes for the network redirector and file system.
To verify that you have the latest service packs installed for the operating system, visit the following Microsoft Web site here.
In this case, the file corruption does not involve the Jet database engine; rather the file is literally corrupted by some outside cause. 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 (see later). Hardware corruption will typically recur until the responsible hardware is repaired or replaced. It is a matter of isolating the problem hardware by removing sections of the network in a logical manner.
If you run different versions of the Jet Database Engine in your environment, you can also cause corruption of an Access database. Different versions of Jet write to the database differently, and therefore can be the cause of corruption in a database.
For example, avoid storing an Access database on a Microsoft Windows 98 computer and accessing using a Windows Vista computer.
Microsoft has discovered an issue where opportunistic locking can increase the possibility of Jet database corruption when the file is shared by two or more clients on a network file server. This issue applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This issue is still under investigation.
Opportunistic locking is enabled by default on Windows NT and 2000 file servers. To disable opportunistic locking on a Windows NT or 2000 file server, you have to use regedit to edit the Windows Registry.
WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.
Most modern anti-virus software can perform background scans of files as they are opened. This usually includes Microsoft Access database files with the extension MDB because they can contain VBA code.
There is some thought, in the computer community, that disabling background/ system scans of non-local (ie files not located on the user's own hard drive) MDB files reduces the possibility of database corruption.
WARNING: You should be careful to only disable background scanning of non-local and/ or MDB and not all files.
Whilst we have not been able to confirm this is a cause of database corruption, it makes sense to be proactive in removing the possibility of this scenario.