How to Keep a Jet 4.0 Database in Top Working Condition

Provided by Microsoft in KnowledgeBase Article 303528 that is no longer available online

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.


Verify that the Latest Operating System Service Pack is Installed

Install the latest operating system service pack.

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

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, visit Database Design Basics (KB289533).

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.

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
Access 2003 Jet 4.0 None

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.

Client Application Jet Database Engine
Used
Recommended Jet Database
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
Access 2003 Jet 4.0 Jet 4.0
DAO 3.0 Jet 3.0 Jet 3.0
DAO 3.5 Jet 3.5 Jet 3.0
DAO 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 DriverJet 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, 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, visit Microsoft's List of Reserved words in Access 2002 and in later version of Access (KB286335) page.

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

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.

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.

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

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.

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, visit Microsoft's OLE DB Provider for Microsoft Jet Overview page.

Note from FMS:
This is no longer recommended as DAO is used to support Access Database Engine (ACE) and the new ACCDB formats. ADO does not support all the features in ACE.

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.

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.

Additional Microsoft References

For more information about what to do if your database has already become corrupted, visit the following Microsoft pages:

Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS