This article applies only to a Microsoft Access database (.mdb).
Best practicesThis 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 installedInstall 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:
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 installedInstall 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:
Use efficient database designAn 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 usedFor 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.
Access VersionJet 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
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.
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
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 namesAvoid 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:
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 databaseIf 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:
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:
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 basisIt 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 environmentsMicrosoft 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 VistaThere 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:
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 serverMicrosoft 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.
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:
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.
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:
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 databaseIf 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 serverMake 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 connectivityMake 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 clientIf 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 databaseWhen 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 integrityUnlike 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:
Refresh linked tables after you update the back-end databaseWhenever 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.
Article ID: 300216 - Last Review: Jun 22, 2014 - Revision: 1