This article applies only to a Microsoft Access database (.mdb).
IN THIS TASK
- 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
- Do not use reserved words and reserved characters for object names and field names
- Periodically compact your Microsoft Jet database
- Back up your Microsoft Jet database file regularly
- Verify that the latest service pack for your version of Office is installed
- Additional best practices for network environments
- Opportunistic locking, also known as oplocks, on the network file server
- Issues to consider when you share a Microsoft Jet database
- Use a robust file server
- Verify network connectivity
- Minimize the number of connections that are 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
Best practicesThis section applies to all Jet databases whether you are the only user of the database or whether the database is used by multiple users over a network.
Verify that the latest operating system service pack is installedInstall the latest operating system service pack.
To verify that you have the latest service pack installed for your operating system, visit the following Microsoft Web site:
Verify that the latest Microsoft Jet service pack is installedFor more information about how to obtain the latest version of the Jet 4.0 Database Engine, click the following article number to view the article in the Microsoft Knowledge Base:
Use efficient database designAn 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, click the following article number to view the article in the Microsoft Knowledge Base:
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:
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.
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
Access 2003 Jet 4.0 none
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:
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 applicationJet Database Engine usedRecommended Jet database format
------------------------- ------------------------ --------------------------------
Access 2.0Jet 2.0Jet 2.0
Access 95Jet 3.0Jet 3.0
Access 97Jet 3.5Jet 3.0
Access 2000Jet 4.0Jet 4.0
Access 2002Jet 4.0Jet 4.0
Office Access 2003Jet 4.0Jet 4.0
DAO 3.0Jet 3.0Jet 3.0
DAO 3.5Jet 3.5Jet 3.0
DA0 3.6Jet 4.0Jet 4.0
Microsoft.JET.OLEDB.3.51Jet 3.5Jet 3.0
Microsoft.Jet.OLEDB.4.0`Jet 4.0Jet 4.0
Access ODBC DriverJet 4.0Jet 4.0
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 namesDo 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, click the following article number to view the article in the Microsoft Knowledge Base:
Periodically compact your Microsoft Jet databaseIf 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, click the following article number to view the article in the Microsoft Knowledge Base:
The following paragraphs describe the process that is used by Microsoft Jet to compact your database.
For more information about compacting a database, click the following article number to view the article in the Microsoft Knowledge Base:
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 regularlyDefine 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 installedMicrosoft 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. To have the Office Update Web site detect the updates that you must install on your computer, visit the following Microsoft Web site:
Additional best practices for network environmentsMicrosoft 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
Do not use folder redirection on Windows VistaThere 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. To obtain the latest Windows service pack, visit the following Microsoft Web site:
For more information about folder redirection, click the following article number to view the article in the Microsoft Knowledge Base:
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.
To obtain the latest Windows service pack, visit the following Microsoft Web site:
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 NT 4.0, click the following article number to view the article in the Microsoft Knowledge Base:
Issues to consider when you share a Microsoft Jet databaseWe 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 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 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 connectivityMake 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 clientWe 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 databaseWhen 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 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 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.
Before you upgrade from a file-based database engine to a server-based database engine, you must consider the advantages and the 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 update all the linked tables within the Jet database. For example, you update the server from SQL Server 2000 to SQL Server 2005.
For more information about other topics that are covered in the previous article, click the following article numbers to view the articles in the Microsoft Knowledge Base:
Raksta ID: 303528. Pēdējo reizi pārskatīts: 2017. gada 20. apr.. Pārskatījums: 2