How to Keep a Jet 4.0 Database in Top Working Condition

For best performance and for stability, use a Microsoft Jet 4.0 format database when you use a Microsoft Jet 4.0 client. Likewise, use a Microsoft Jet 3.0 format database when you use a Microsoft Jet 3.5 client.

The following table lists the currently-available Jet formats. The following table also lists what translator dll is used to talk to the Jet format when you use a Jet 4.0 client.

The following table shows that Microsoft Access 95 and Access 97 create a Microsoft Jet 3.0 format database file. Access 2000, Access 2002 and Office Access 2003 create a Microsoft Jet 4.0 format database file. When you use a Microsoft Jet 4.0 client, use a Microsoft Jet 4.0 database format file to avoid the use of a translator dll. If you use a Microsoft Jet 3.5 client, we recommend that you use a Microsoft Jet 3.0 format database.

The following table lists the most frequently used Microsoft Jet Database Engine clients and the associated Jet Database Engine version that the Jet Database Engine client uses.

Note: The Microsoft Access ODBC driver that is included with MDAC 2.0 or earlier uses Microsoft Jet 3.5. The Microsoft Access ODBC driver that is included with MDAC 2.1 and MDAC 2.5 uses Microsoft Jet 4.0. MDAC 2.6 and later versions do not include the Microsoft Access ODBC driver at all.

In certain situations, such as when you have both older Jet applications and newer Jet applications that share the same database file, you may not be able to use the latest Jet database file format. This is because older Jet engines cannot read or cannot write to a newer Jet database file format. Therefore, must use the older format and use the translator dlls.

Do not use reserved words and reserved characters when you name objects and when you name fields in your database. Reserved words or reserved characters that are used alone or in combination with other words but enclosed by spaces may cause database corruption.

For more information about reserved words and reserved characters in Microsoft Access, visit Microsoft's List of Reserved words in Access 2002 and in later version of Access (KB286335) page.

If you make frequent changes to your database, parts of the database may become fragmented. Therefore, periodically run the Compact Database utility in Access. If you do not have a copy of Access, you can still compact the database by using the JetComp utility.

For more information about how to obtain the JetComp Utility for Microsoft Jet 4.0, visit Microsoft's Jet Compact Utility is Available in Download Center (KB295334) page.

How frequently you compact your database depends on how much the data changes. If the data does not change frequently, you do not have to compact frequently. If there are frequent changes to your database, compact your database more frequently. Although there is no formal rule for how frequently you compact your database, we recommend that you compact your database regularly.

The following paragraphs describe the process that is used by Microsoft Jet to compact your database.

The Microsoft Jet Database Engine treats an Access database file as a series of 4096-byte blocks. This is similar to the way that a typical file system treats data on your hard disk. The complete set of records in a Microsoft Jet table is stored by a series of these blocks. Each block points to the next block. Each block can hold one or more records. How many records a block holds depends on how many fields and how much data is in each record. When records are added and records are deleted from the table, the table blocks become fragmented in the database file.

When you compact a Jet database, the blocks are de-fragmented, and each table is put in a contiguous range of blocks. This improves read performance and write performance to the table.

Indexes in an Access database are also stored in 4096-byte blocks. Indexes can become fragmented just as the table records can become fragmented.

When your Jet database is compacted, Jet updates table statistics that are stored in the database. One important table statistic is the number of records in a table. If the number of records in the table statistics differs greatly from the actual number of records in the table, performance may not be as good. For example, if the table statistics indicate that there are a small number of records in the table, the Jet Database Engine optimizer does not use any indexes on the table when the Jet Database Engine performs seeks or joins. If there is a small number of records, this is actually more efficient. But if there many records, this can become very inefficient. Therefore, if your database has a large numbers of records that are added, deleted, and updated, compact the database more frequently.

Microsoft Jet also re-optimizes stored queries in the database when the database is compacted. This reflects the updated table statistics. Therefore, stored query performance can be improved when you compact your database.

Define a backup schedule that corresponds to the data that you can afford to lose. For example, if you can afford to lose one day of data, back up daily. If you can afford to lose one week of data, back up weekly. A full database file backup is the best way to make sure that you can recover your Microsoft Jet database file if corruption occurs.

Important If you are in a network environment, you must shut down all Microsoft Jet clients before you back up the Microsoft Access database file. If you do not do this, you may create a backup file with incomplete data or inconsistent data. Test your backup files regularly to make sure that your backup files are good.

Microsoft periodically provides service packs and updates to Microsoft Office. Install the latest service pack or Office update for your version of Office to enhance performance and interoperability with other programs. Use the Microsoft Office Update Web site to automatically install all the latest service packs and updates.

After the Office Update Web site detects the latest updates that you require, you receive a list of recommended updates for your approval. Click Start Installation to install the updates.

Microsoft Jet is a file-sharing database system. A file-sharing database system means that the processing of the file occurs at the client. When a file-sharing database, such as Microsoft Jet, is used in a multi-user environment, multiple client processes use file read, file write, and file locking operations on the same shared file across a network. If a process cannot be completed, the file may be left in an incomplete state or in a corrupted state. A process may not be completed for either of the following reasons:

• When a client is stopped unexpectedly
• When a network connection to a server is dropped

Microsoft Jet is not intended for use with high-stress server applications, high-concurrency server applications, or 24 hours a day, seven days a week server applications. This includes server applications, such as Web applications, commerce applications, transactional applications, and messaging server applications. For these types of applications, the best solution is to switch to a true client/server-based database system, such as Microsoft Data Engine (MSDE) or Microsoft SQL Server. When you use Microsoft Jet in high-stress applications such as Microsoft Internet Information Server (IIS), you may experience any one of the following problems:

• Database corruption
• Stability issues, such as IIS crashing or locking up
• Sudden failure or persistent failure of the driver to connect to a valid database that requires re-starting the IIS service

The following sections contain recommendations to follow when you share a Microsoft Jet or a Microsoft Access database file on a network file server.

There is a known problem in which a Jet database may become corrupted when the Jet database is saved in a folder that uses the Windows Vista folder-redirection feature in the original release version of Windows Vista.

To avoid this problem, you must update to Windows Vista Service Pack 1, or you must not save a Jet database in a folder that uses the Windows Vista folder-redirection feature.

We have not seen this problem with the folder-redirection features of Windows Server 2003, of Windows XP, or of Windows 2000.

Microsoft Internal Support Information

BUG #: 2000252 (Windows OS Bugs)
The fix for this problem is scheduled to be included with Windows Vista Service Pack 1 and Windows Server 2008 RTM.

Opportunistic locking may increase the risk of Jet database corruption when a file is shared by two or more clients on a network file server. This problem applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This problem also applies to clients that are running Microsoft Windows NT 4.0, Microsoft Windows 2000, or Microsoft Windows XP and that connect to a file server that supports opportunistic locking.

Note: Clients running Microsoft Office Access 2003 must have Windows 2000 SP3 or later or Windows XP or later operating system.

To avoid this problem, you must install Windows 2000 Service Pack 3 (SP3) or later on the Windows 2000 file server where the Access database file is located. Then install the latest Windows service pack on each Windows client computer that connects to the Windows 2000 file server.

If you are using Windows NT 4.0 or if you do not want to install Windows 2000 SP3 or later, you can disable opportunistic locking to avoid the problem.

Important If you disable opportunistic locking, this may adversely affect the performance of other applications. If you have questions about this, contact Microsoft Windows technical support.

For more information about disabling opportunistic locking on Windows 2000, visit Microsoft's Configuring Opportunistic Locking in Windows (KB296264) page.

To disable opportunistic locking on a Novell file server, we recommend that you contact Novell technical support. For information about your hardware manufacturer, visit the Microsoft Support site.

Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

We do not recommend that you share a Microsoft Jet database file that is stored on a Microsoft Windows 95 file share, a Microsoft Windows 98 file share, or a Microsoft Windows Millennium Edition (Me) file share with Windows NT clients or with Windows 2000 clients.

If you have an environment with a mixture of computers on a network with some computers that are running Windows 95, Windows 98, or Windows Millennium Edition and some computers that are running Windows NT or Windows 2000, and you share the database with other network users, we recommend that you store the database file and that you share the database file on a Windows NT server or on Windows 2000 server with opportunistic locking disabled.

Corruption may occur if you have Windows NT client computers or Windows 2000 client computers that share a file that is stored on a Windows 95 file share, a Windows 98 file share, or a Windows Millennium Edition file share. This problem may occur even if the Windows NT client or the Windows 2000 client has opportunistic locking disabled. This issue is still under investigation. We will post more information to this article when that information becomes available.

Note: Clients running Microsoft Office Access 2003 must have Windows 2000 SP3 or later or Windows XP or later operating system.

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 other processes, such as acting as a Windows domain controller, an Exchange server, or a SQL Server.

A problem also occurs if you restart the server to fix a problem with another important service, such as the mail service. Problems also occur when you restart the server after you apply new software or you apply a service pack or a hotfix, and you forget that the Microsoft Jet database is currently shared on the server. When the file server is restarted, unexpected interruption of the client connections to the database occur. This may cause database corruption. To prevent interrupted client connection, all clients must close the database before the file server is restarted or before software updates to the file server are applied.

A file server must also be put in a secured location where the file server cannot be accidentally switched off. The server must have an uninterrupted power supply (UPS) to help protect from intermittent power outages or from power fluctuations. The network file server must also have the following:

• High-performance hard drives
• A high-quality network card
• Lots of RAM to make sure that the server can handle the load

Make sure that you have a stable network and a fast network with stable network connectivity to the network file server. We recommend that you do not use Microsoft Jet over a WAN, over a modem connection, or over 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.

We recommend that you design each client to use one connection to a 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 multi-user stress on the back-end database, design the client application to use a single connection to the Jet database. Share this connection over multiple record sets as required. This has the added benefit of preventing read delays and 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 when reading on two different Jet connections. This is true even if the two connections reside in the same client process. If you use a single connection, you avoid this issue.