How to keep a Jet 4.0 database in top working condition

By default, Microsoft Jet is the database engine that is used in Microsoft Access. This article describes best practices that you can use to help keep your Jet database in top working condition. This article describes databases that are running in a single user environment and in a multi-user environment.

Best practices

This section applies to all Jet databases whether you are the only user of the database or whether the database is used by multiple users over a network.

Verify that the latest operating system service pack is installed

Install the latest operating system service pack.

To verify that you have the latest service pack installed for your operating system, visit the following Microsoft Web site: If you are in a network environment, make sure that the network file server has the latest operating system service pack. This makes sure that the network file server has the latest updates for the network redirector and for the file system.

Verify that the latest Microsoft Jet service pack is installed

For more information about how to obtain the latest version of the Jet 4.0 Database Engine, click the following article number to view the article in the Microsoft Knowledge Base: If you work in a network environment, you must install the latest Microsoft Jet service pack on all client computers. You do not have to install the Microsoft Jet service pack on the network file server unless the network file server also runs one or more applications that use Microsoft Jet.

Use efficient database design

An efficiently designed database improves performance of the database. An efficiently designed database also helps reduce the risk of file corruption. For more information about best practices for designing a database, click the following article number to view the article in the Microsoft Knowledge Base:

Use a matching Jet database file format for the version of the Jet Database Engine that is being used

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 for object names and field names

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, click the following article number to view the article in the Microsoft Knowledge Base:

Periodically compact your Microsoft Jet database

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, click the following article number to view the article in the Microsoft Knowledge Base: 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.

For more information about compacting a database, click the following article number to view the article in the Microsoft Knowledge Base: 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.

Back up your Microsoft Jet database file regularly

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.

Verify that the latest service pack for your version of Office is installed

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. To have the Office Update Web site detect the updates that you must install on your computer, visit the following Microsoft Web site: 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.

Additional best practices for network environments

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.

Do not use folder redirection on Windows Vista

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. To obtain the latest Windows service pack, visit the following Microsoft Web site:We have not seen this problem with the folder-redirection features of Windows Server 2003, of Windows XP, or of Windows 2000.

For more information about folder redirection, click the following article number to view the article in the Microsoft Knowledge Base: For more information about folder redirection, visit the following Web site:

Opportunistic locking, also known as oplocks, on the network file server

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.

To obtain the latest Windows service pack, visit the following Microsoft Web site: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 NT 4.0, click the following article number to view the article in the Microsoft Knowledge Base: For more information about disabling opportunistic locking on Windows 2000, click the following article number to view the article in the Microsoft Knowledge Base: 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 following Web 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.

Issues to consider when you share a Microsoft Jet database

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.

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

Verify network connectivity

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.

Minimize the number of connections that are made from each client

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.

Use ADO to access a Microsoft Jet database

When you access a Microsoft Jet database from ADO, we recommend that you use the Microsoft Jet OLE DB Provider instead of the Microsoft Access ODBC driver. For more information about this topic, click the following article number to view the article in the Microsoft Knowledge Base:

Move to a transactional database engine to gain additional integrity

Unlike a file-based database engine, a server-based database engine, such as Microsoft SQL Server, processes all the multiple-client requests to a database at the server. The server keeps track of these requests in a transaction log. If a request cannot be fulfilled, the server rolls back the request or does not process the request. This reduces the risk that the database is left in an incomplete state or in a corrupted state.

Before you upgrade from a file-based database engine to a server-based database engine, you must consider the advantages and the disadvantages of doing so. For more information about choosing the most appropriate database engine for your purposes, click the following article number to view the article in the Microsoft Knowledge Base: Note Although this white paper is written for Access 97, this white paper also applies to Jet 4.0 and Access 2000.

Refresh linked tables after you update the back-end database

Whenever a Jet database contains links to an ODBC back-end database server and you update that server to a later and major version, you must update all the linked tables within the Jet database. For example, you update the server from SQL Server 2000 to SQL Server 2005.

For more information about what to do if your database has already become corrupted, click the following article number to view the article in the Microsoft Knowledge Base:



For more information about other topics that are covered in the previous article, click the following article numbers to view the articles in the Microsoft Knowledge Base: