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

Article translations Article translations
Article ID: 300216 - View products that this article applies to.
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 Microsoft Access 95 or Microsoft Access 97 version of this article, see 303519.
For a Microsoft Access 2002 or Microsoft Office Access 2003 version of this article, see 303528.
Expand all | Collapse all

On This Page

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.

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

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.

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

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.

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.

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 "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:
http://windowsupdate.microsoft.com
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:
http://technet.microsoft.com/en-us/sms/bb676760.aspx
For more information about the folder-redirection feature, click the following article number to view the article in the Microsoft Knowledge Base:
232692 Folder redirection feature in Windows

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:
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 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 more information about hardware and software vendor contact information, visit the following Microsoft Web site:
http://support.microsoft.com/gp/vendors
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:
299973 Using Microsoft Jet with IIS

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.

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.

REFERENCES

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:
306204 How to troubleshoot corruption in a Microsoft Access database
For detailed information about other topics covered in this article, click the article numbers below to view the articles in the Microsoft Knowledge Base:
200300 How to synchronize writes and reads with the Jet OLE DB Provider and ADO
209137 How to troubleshoot and repair a damaged Jet 4.0 database in Access 2000
299973 Using Microsoft Jet with IIS
296264 Configuring opportunistic locking in Windows
129202 Explanation of opportunistic locking on Windows NT

Properties

Article ID: 300216 - Last Review: April 11, 2008 - Revision: 7.2
APPLIES TO
  • Microsoft Access 2000 Standard Edition
  • Microsoft Open Database Connectivity Driver for Access 4.0
  • Microsoft OLE DB Provider for Jet 4.0
Keywords: 
kbdatabase kbinfo kbdownload kbfaq kbhowtomaster KB300216

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com