1. Upgrade your NT workstations

If any of your workstations are running Windows NT, be sure to upgrade them to Office SR1a and Jet4 SP 4. These are service releases and patches avaiable from Microsoft.

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

Microsoft now has two hotfixes designed to resolve this problem. Customers who do not want to disable opportunistic locking can install these two hotfixes to avoid the problem without having to disable opportunistic locking.

To avoid the problem without disabling opportunistic locking, first install the hotfix that is mentioned in the following Microsoft Knowledge Base article on the Windows 2000 file server where the Access database file resides: Q288794 Calling FlushFileBuffers() on Bidirectional File Transfer May Cause a Deadlock.

Second, install the hotfix that is mentioned in the following Microsoft Knowledge Base article on each Windows 2000 client computer that will connect to the Windows 2000 file server where the Access database resides. Q307982 Cache May Not Clean Up When the SMB File Handle Is Closed.

Note that you have to make sure that all Windows 2000 clients install the hotfix that is mentioned in Q307982 to avoid this problem. If you have Microsoft Windows 95, Microsoft Windows 98, Microsoft Windows Millennium edition (Me), or Microsoft Windows NT 4.0 clients, you do not have to install the hotfix that is mentioned in Q307982 on these clients. To disable opportunistic locking on a Windows NT 4.0 or a Windows 2000 file server, locate the following registry subkey on the network file server.

WARNING : Using Registry Editor incorrectly can cause serious problems that may require that you reinstall your operating system. Microsoft cannot guarantee that problems that result from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, see the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Microsoft recommends that you back up the registry before you edit it. If you are running Windows NT or Windows 2000, Microsoft also recommends that you update your Emergency Repair Disk (ERD). HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameters

Under this subkey, create a new DWORD value named EnableOplocks and set its value to 0 to disable opportunistic locking. Restart the network file server to make sure that the setting is accepted.

3. Issues to Consider When You Share a Microsoft Jet Database

If you can, do not share a Microsoft Jet database file that is stored on a Microsoft Windows 95, a Microsoft Windows 98, or a Microsoft Windows Millennium Edition (Me) file share with Windows NT or Windows 2000 clients. When you have a mixture of computers on a network with some computers running Windows 95, Windows 98, or Windows Me and some running Windows NT or Windows 2000, and you have to share the database with other network users, Microsoft recommends that you store and share the database file on a Windows NT or Windows 2000 server with opportunistic locking disabled. Corruption can occur if you have Windows NT or Windows 2000 client computers share a file that is stored on a Windows 95, a Windows 98, or a Windows Me file share. This can occur even if the Windows NT or Windows 2000 client have opportunistic locking disabled. This issue is still under investigation, and Microsoft will post further information to this article when it becomes available.

4. Use a Robust File Server

Make sure that you have a robust file server that can handle the number of users and the requests that are being made to the Microsoft Jet database file. Additionally, make sure that the file server is not overtaxed with handling many other processes, such as acting as a Windows Domain Controller, Exchange Server, and SQL Server. The 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 hotfix, 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 must close the database before the file server is rebooted or before software updates to the file server are applied.

A robust file server must also be placed in a secured location where it cannot be accidentally switched off. The server must have an uninterrupted power supply (UPS) to protect it from intermittent power outages or power fluctuations. The network file server must also have high performance hard drives, a good network card, and plenty of RAM to make sure that the server can handle the load that is placed on it.

Verify Network Connectivity: Make sure that you have a stable and fast network with solid network connectivity to the network file server. Avoid using Microsoft Jet over a WAN, a modem connection, FTP (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. This can increase the chance of database corruption.

Minimize the Number of Connections Made from Each Client: If you can, design each client to use one, and only one, connection to the Microsoft Jet database. Each connection to a Jet database represents an independent client to the database, even when these connections come from the same client process. To optimize performance and network I/O and to reduce the multiuser stress on the back-end database, design the client application to use a single connection to the Jet database, and then share this connection over multiple recordsets as needed. This has the added benefit of preventing read/write delays in the client application. By default, there is a five-second delay between writing a value to the database and being able to read this updated value when writing and reading on two different Jet connections, even if the two connections reside in the same client process. If you use a single connection, you avoid this issue.

5. Use ADO to Access a Microsoft Jet Database

When you access a Microsoft Jet database from ADO, Microsoft recommends that you use the Microsoft Jet OLEDB provider instead of the Microsoft Access ODBC driver.

Many thanks to Brent Tucker at NAS LeMoore for finding this Microsoft article for us. For additional assistance with tuning and configuring winSTARS, please contact support@zoa.com.