How to keep a Jet 4.0 database in top working condition in Access 2000

Microsoft Jet is the database engine that is used by default by Microsoft Access. This article provides a list of best practices that you can use to help keep your Jet database in top working condition. This article addresses a database running both in a single and in a multiuser environment.

Best practices

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

Verify that the latest operating system service pack is installed

Install the latest operating system service pack. This makes sure that you have the latest bug fixes.

To verify that you have the latest service packs installed for your operating system, visit the following Microsoft Web site: If you are in a network environment, it is important to make sure the network file server has the latest operating system service pack to make sure that it has the latest bug fixes for the network redirector and file system.

Periodically, Microsoft provides service packs and updates to Office 2000 to make sure that the best performance and the best interoperability are maintained with other programs. These updates are available as a free download by visiting the Office Update Site at http://office.microsoft.com. Use the Check for Updates option to scan your computer for missing updates.

Verify that the latest Microsoft Jet service pack is installed

Install the latest Microsoft Jet service pack. This makes sure that you have the latest bug fixes to Microsoft Jet. To download the latest Jet 4.0 Service Pack, see the following Knowledge Base article: If you are working 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 the performance of the database. It also helps to reduce the possibility that the file will become corrupted. For more information about the 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 engine that is being used

For best performance and stability, use a Microsoft Jet 4.0 format database when you are using Microsoft Jet 4.0 clients. Likewise, use a Microsoft Jet 3.0 format database when you are using Microsoft Jet 3.5 clients, and so on.

Here is a table of the currently available Jet formats and what translator dll is used (if any) to talk to this format when you are using a Microsoft Jet 4.0 client:

This chart shows that Microsoft Access 95 and Access 97 create a Microsoft Jet 3.0 format database file. Access 2000 and Access 2002 create a Microsoft Jet 4.0 format database file. When you are using a Microsoft Jet 4.0 client, use a Microsoft Jet 4.0 database format file to avoid the use of a translator dll. Also, if you are using a Microsoft Jet 3.5 client, it is best to use a Microsoft Jet 3.0 format database.

Here is a list of commonly used Microsoft Jet database engine clients and what associated Jet engine version they use:

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 (for example, when you have both older and newer Jet applications sharing 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 write to a newer Jet database file format. In this case, must use the older format and use the translator dlls.

Avoid using reserved words and characters for object and field names

Avoid using reserved words and characters when you name objects and fields in your database. In some situations, reserved words or characters used alone or in combination with other words but surrounded by spaces can result in database corruption. For more information about reserved words and characters in Microsoft Access, click the following article numbers to view the articles in the Microsoft Knowledge Base: For more information about reserved words and characters, click Microsoft Access Help on the Help menu, type Microsoft Jet database engine SQL reserved words in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Periodically compact your Microsoft Jet database

If you make frequent changes in a database, parts of the database may become fragmented. Therefore, it is a good idea to periodically run the Compact Database utility in Microsoft Access. If you do not have a copy of Microsoft 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: Schedule compacts depending on how much the data changes. If the data does not change that often, you do not have to compact that much. If there are many and frequent updates, inserts, and deletes, compact more. Even though there is no formal rule for how often to compact, Microsoft recommends that you compact on a regular basis.

The next few paragraphs explain the compacting process that is used by Microsoft Jet in more detail. For a more general explanation of compacting a database, click the article number below to view the article in the Microsoft Knowledge Base: The Microsoft Jet engine treats a Microsoft Access database file as a series of 4096-byte blocks, much in the same way as a typical file system treats data on your hard disk. The complete set of records in a Microsoft Jet table is stored by series of these blocks, each block pointing to the next block. Each block can hold one or more records, depending on how many fields and how much data is in each record. Over time, when records are added and deleted from the table, the table blocks will become fragmented inside the database file.

Compacting a Microsoft Jet database "defragments" these blocks and tries to place each table in a contiguous range of blocks. This improves read and write performance to the table.

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

When it compacts a Microsoft Jet database, Jet updates table statistics stored inside the database. One key 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 will not use any indexes on the table when it performs seeks or joins. If there really is a small number of records, this is actually more efficient. But if there is a large number of records, this can become extremely inefficient. The key concept here is that if large numbers of records are added and deleted and updated, it is best to compact the database more frequently.

Microsoft Jet will also re-optimize stored queries in the database during compacting to reflect the updated table statistics. Therefore, stored query performance can also be improved by more frequent compacting.

Back up your Microsoft Jet database file on a regular basis

It is best to choose a backup schedule that corresponds to the amount of data you can afford to lose. For example, if you cannot afford to lose more than a day's worth of data, back up on a daily basis. If you can afford to lose a week's worth of data, back up weekly, and so on. 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, you may create a backup file with incomplete or inconsistent data. Test your backups regularly to make sure that your backups are good.

Additional best practices for network environments

Microsoft Jet 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, such as Microsoft Jet, 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.

Microsoft Jet is not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. For these type 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), customers have reported database corruption, stability issues such as IIS crashing or locking up, and also a sudden and persistent failure of the driver to connect to a valid database that requires re-starting the IIS service.

The following is a list of recommendations to follow when you share a Microsoft Jet (Microsoft Access) database file on a network file server.

Do not use folder redirection in Windows Vista

There is a known problem in the release version of Windows Vista 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.

To avoid this problem, you must update to Windows Vista Service Pack 1, or you must not save any Jet database in a folder that uses the Windows Vista folder-redirection feature. To obtain the latest Windows Vista service pack, visit the following Microsoft Web site:This problem does not occur in the folder-redirection features of Windows Server 2003, of Windows XP, or of Windows 2000.

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

Opportunistic locking (oplocks) on the network file server

Microsoft has discovered an issue where opportunistic locking can increase the risk 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 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.

Windows 2000

To reduce the chances of this problem on computers that are running Windows 2000, you must install Windows 2000 Service Pack 3 (SP3) or a later Windows 2000 service pack on the Windows 2000 file server where the Access database file is located, and then on each Windows 2000 client computer that connects to the Windows 2000 file server. For more information about how you can obtain and then install the latest Windows 2000 service pack, click the following article number to view the article in the Microsoft Knowledge Base: 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 service pack that is mentioned in the Knowledge Base article Q260910 on these clients.

Windows XP

To avoid the problem on computers that are running Windows XP, install the security update that is mentioned in the following Microsoft Knowledge Base article on each Windows XP client computer that connects to the file server where the Access database is located. 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 security update that is mentioned in the Knowledge Base article 329170 on these clients.

Disable Opportunistic Locking

If you are using Windows NT 4.0, you cannot install Windows 2000 SP4 and the Windows XP security update, or you installed Windows 2000 SP4 and the Windows XP security update but still see frequent corruption, you can disable opportunistic locking to avoid the problem.

Important Disabling opportunistic locking 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, Microsoft recommends that you contact Novell technical support. For more information about hardware and software vendor contact information, visit the following Microsoft 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

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.

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.

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. 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 of the multiple client requests to a database at the server. The server keeps track of these requests in a transaction log. If, for any reason, a request cannot be fulfilled, the server rolls back or does not process the request. This reduces the possibility that the database will be left in an incomplete or corrupted state.

Before you upgrade from a file-based database engine to a server-based database engine, however, consider the advantages and 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: Even thought this white paper is written for Access 97, it 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 refresh 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 detailed information about other topics covered in this article, click the article numbers below to view the articles in the Microsoft Knowledge Base: