HOW TO: Keep a Jet 3.x Database in Top Working Condition (303519)

SUMMARY

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.

back to the top

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.

back to the top

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.

Periodically, Microsoft provides service packs and updates to Office to make sure that the best performance and the best interoperability are maintained with other programs. For additional information about how to obtain the latest Service Pack for Office 97, click the following article number to view the article in the Microsoft Knowledge Base: To verify that you have the latest service packs installed for your operating system, visit the following Microsoft Web site: If you are working 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.

back to the top

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 article in the Microsoft Knowledge Base: If you are in a network environment, you must install the latest Microsoft Jet service pack on all client computers. Note that 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.

back to the top

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 additional information about the best practices for designing a database, click the article number below to view the article in the Microsoft Knowledge Base: back to the top

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 3.5 client:

   Access Version	Jet Format    Translator Dll Used
   --------------	----------    -------------------
    Access 2.0    	Jet 2.0       msrd2x40.dll
    Access 95     	Jet 3.0       none
    Access 97     	Jet 3.0       none
				

This chart shows that Microsoft Access 95 and Access 97 create a Microsoft Jet 3.0 format database file. 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:

   Client Application          Jet Engine Used    Recommended Jet DB Format
   -------------------------   ---------------    -------------------------
    Access 2.0                       Jet 2.0             Jet 2.0
    Access 95                        Jet 3.0             Jet 3.0
    Access 97                        Jet 3.5             Jet 3.0
    Access 2000                      Jet 4.0             Jet 4.0
    Access 2002                      Jet 4.0             Jet 4.0
    DAO 3.0                          Jet 3.0             Jet 3.0
    DAO 3.5                          Jet 3.5             Jet 3.0
    DA0 3.6                          Jet 4.0             Jet 4.0
    Microsoft.JET.OLEDB.3.51         Jet 3.5             Jet 3.0
    Microsoft.Jet.OLEDB.4.0          Jet 4.0             Jet 4.0
    Access ODBC Driver*              Jet 4.0             Jet 4.0
				

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.

back to the top

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 additional information about reserved words and characters in Microsoft Access, click the article numbers below to view the articles in the Microsoft Knowledge Base: For more information about reserved words and characters, click Contents and Index on the Help menu, click the Index tab in Access Help, type the following text
and then double-click the selected text to go to the "Microsoft Jet Database Engine SQL Reserved Words" topic. If you are unable to find the information you need, ask the Office Assistant.


back to the top

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. Jetcomp.exe comes with the latest service pack for Microsoft Jet 3.5.

For additional information about how to obtain the latest service pack for Microsoft Jet 3.5, click the article number below to view the article in the Microsoft Knowledge Base: If you want to use Jetcomp.exe with a Microsoft Access run-time database, click the article number below 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 frequently 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 2048-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 upon 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 2048-byte blocks, and can become fragmented just like the table records.

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 to the top

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

back to the top

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.

back to the top

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 chance that this problem can occur on computers that run Windows 2000, you must install Windows 2000 Service Pack 3 (SP3) on the Windows 2000 file server where the Access database file is located. Then, you must install Windows 2000 Service Pack 3 on each Windows 2000 client computer that connects to the Windows 2000 file server. For additional information about how you can obtain and how you can install Windows 2000 Service Pack3, 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 260910 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 Knowledge Base article. You must do this for each Windows XP client computer that connects to the file server where the Access database is located. If you have Windows 95, Windows 98, Windows Millennium Edition, or 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 use Windows NT 4.0, and the following problems occur:

• You cannot install Windows 2000 SP3 and the Windows XP security update.
• You installed Windows 2000 SP3 and the Windows XP security update, but you 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 additional information about disabling opportunistic locking on Windows NT 4.0, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about disabling opportunistic locking on Windows 2000, click the article number below 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 information about how to contact Novell, click the appropriate article number in the following list to view the article in the Microsoft Knowledge Base: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.

back to the top

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. back to the top

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.

back to the top

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.

back to the top

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.

back to the top

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 additional information about this topic, click the article number below to view the article in the Microsoft Knowledge Base: back to the top

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 additional information about choosing the most appropriate database engine for your purposes, click the article number below to view the article in the Microsoft Knowledge Base: back to the top