Access (Jet) Databases

Access (Jet) Database Corruptions

finPOWER Connect 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.

Steps to avoid Database Corruption

Steps you can take to avoid data corruption include, a summary of the above:

• Avoid losing power during database writes.
• Avoid dropping network connections.
• Avoid abnormal termination of Jet connections such as power loss, manual shutdown, having Task Manager shutdown the application, and so on.
• Fatal system errors almost always cause abnormal termination. If your computer is prone to fatal errors, you should resolve the errors before the database becomes too damaged to open or recover.
• Compact the database regularly.
• Do not attempt to open the database in other applications except finPOWER Connect and Microsoft Access.

Interrupted Write Operation

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.

Opening and Saving the MDB File in Another Program

There is no way to recover an .mdb file that was opened and then saved in a program other than finPOWER or Access.  E.g. 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 does not use or expect a password for a Database.

Corruption after Restoring a Backup

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.

Use a Robust File Server and Network

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 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.

Verify That the Latest Operating System Service Pack Is Installed

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.

Faulty Networking Hardware

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.

Mismatched Versions of the Jet Database Engine

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.

Disable Opportunistic Locking (oplocks) on the Network File Server

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.

1. From the Windows Start menu click Run.
2. Type Regedit and click OK.
3. Locate the following registry key on the network file server HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameters.
4. From the Edit menu, point to New and click DWord Value.
5. Name the new key EnableOplocks and set its value to 0 (to disable opportunistic locking).
6. Reboot the network file server to ensure that the setting is accepted.

Anti-Virus Software

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 (i.e. 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.