Microsoft KB Archive/300216

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

Article ID: 300216

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft Open Database Connectivity Driver for Access 4.0
 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q300216



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



For a Access 95 or Microsoft Access 97 version of this article, see 303519.



For a Access 2002 version of this article, see 303528.

Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry

IN THIS TASK
SUMMARY Best practices
 * Verify that the latest operating system service pack is installed
 * Verify that the latest Microsoft Jet service pack is installed
 * Use efficient database design
 * Use a matching Jet database file format for the version of the Jet engine that is being used
 * Avoid using reserved words and characters for object and field names
 * Periodically compact your Microsoft Jet database
 * Back up your Microsoft Jet database file on a regular basis

Additional best practices for network environments
 * Opportunistic locking (oplocks) on the network file server
 * Issues to consider when sharing a Microsoft Jet database
 * Use a robust file server
 * Verify network connectivity
 * Minimize the number of connections made from each client
 * Use ADO to access a Microsoft Jet database
 * Move to a transactional database engine to gain additional integrity
 * Refresh linked tables after you update the back-end database

REFERENCES



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.

To verify that you have the latest service packs installed for your operating system, visit the following Microsoft Web site:

http://windowsupdate.microsoft.com

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.

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 Knowledge Base article:

239114 How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine

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.

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

288947 How to find information about designing a database in Access 2000

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

  Access Version   Jet Format    Translator Dll Used --  --    ---    Access 2.0      Jet 2.0       msrd2x40.dll Access 95      Jet 3.0       msrd3x40.dll Access 97      Jet 3.0       msrd3x40.dll Access 2000    Jet 4.0       None Access 2002    Jet 4.0       None 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:

  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 more information about reserved words and characters in Microsoft Access, click the following article numbers to view the articles in the Microsoft Knowledge Base:

209187 List of reserved words in Access 2000

296857 Cannot open Visual Basic Environment from design view of a database object

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.

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.

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:

273956 Jet Compact utility available in Download Center

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:

209769 Defragment and compact database to improve performance

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 &quot;defragments&quot; 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 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 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 chances of this problem on computers that are running Windows 2000, you must install Windows 2000 Service Pack 4 (SP4) 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 information about how you can obtain and how you can install Windows 2000 SP4, read the following Knowledge Base article:

260910 How to obtain the latest Windows 2000 service pack

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.

329170 MS02-070: Flaw in SMB signing may permit group policy to be modified

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:

129202 PC Ext: Explanation of opportunistic locking on Windows NT

For more information about disabling opportunistic locking on Windows 2000, click the following article number to view the article in the Microsoft Knowledge Base:

296264 Configuring opportunistic locking in Windows

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:

65416 Hardware and software vendor contact information, A-K

60781 Hardware and software vendor contact information, L-P

60782 Hardware and software vendor contact information, Q-Z

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

299973 Using Microsoft Jet with IIS

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

168549 Choosing the Appropriate Database white paper available in Download Center

NOTE: Even thought this white paper is written for Access 97, it also applies to Jet 4.0 and Access 2000.

back to the top

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.

back to the top

