Initializing the Microsoft Jet 4.0 Database Engine Driver

Microsoft Office Access 2003

Initializing the Microsoft Jet 4.0 Database Engine Driver

When you install the Microsoft® Jet version 4.0 Engine database driver, the Setup program writes a set of default values to the Microsoft Windows® Registry in the Engines and ISAM Formats subkeys. You must use the Registry Editor to add, remove, or change these settings. The following sections describe initialization and ISAM Format settings for the Microsoft Jet Engine database driver.

Microsoft Jet Engine Initialization Settings

The Jet\4.0\Engines folder includes initialization settings for the msjet40.dll database engine, used for access to Microsoft Access databases. Typical initialization settings for the entries in this folder are shown in the following example.

SystemDB = <path>\System.mdb

CompactBYPkey = 1

PrevFormatCompactWithUNICODECompression=1

The Microsoft Jet database engine uses the following entries.

Entry Description
SystemDB Specifies the full path and file name of the workgroup information file. The default is the appropriate path followed by the file name System.mdb.
CompactByPKey Specifies that when you compact tables they are copied in primary-key order, if a primary key exists on the table. If no primary key exists on a table, the tables are copied in base-table order.

A value of 0 indicates that tables should be compacted in base-table order; a non-zero value indicates that tables should be compacted in primary-key order, if a primary key exists. The default value is non-zero.

Note  This setting only applies to compacting databases created with the Microsoft Jet database engine version 3.0 or later; when you compact databases created with the Microsoft Jet database engine version 2.x, the data is always copied in the order of the base table.

PrevFormatCompactWithUNICODECompression Microsoft Jet 4.0 databases use the Unicode character set to store textual data. Compressing the Unicode data can significantly improve the performance of the database because of the reduced number of page read/write operations that are needed afterwards.

This key determines if databases created by the Microsoft Jet database engine version 3.x or earlier should be created with compressed Unicode or un-compressed Unicode.

Note  This setting does not apply to compacting Microsoft Jet 4.0 databases. Microsoft Jet 4.0 databases will default to keep the compression settings with which they were created.

The Jet\4.0\Engines\Jet 4.0 folder includes initialization settings for the msjet40.dll database engine, used for access to Microsoft Access databases. Typical initialization settings for the entries in this folder are shown in the following example.

FlushTransactionTimeout=500

LockDelay=100

LockRetry=20

MaxBufferSize= 0

MaxLocksPerFile= 9500

PageTimeout=5000

Threads=3

UserCommitSync=Yes

ImplicitCommitSync=No

ExclusiveAsyncDelay=2000

SharedAsyncDelay=0

RecycleLVs=0

PagesLockedToTableLock=0

The Microsoft Jet database engine uses the following entries.

Entry Description
PageTimeout The length of time between the time when data that is not read-locked is placed in an internal cache and the time when it is invalidated, expressed in milliseconds. The default is 5000 milliseconds or 5 seconds.
FlushTransaction
Timeout
This entry disables both the ExclusiveAsyncDelay and SharedAsyncDelay registry entries. To enable those entries, a value of zero must be entered. FlushTransactionTimeout changes the Microsoft Jet database engine's method for doing asynchronous writes to a database file. Previously, the Microsoft Jet database engine would use either the ExclusiveAsyncDelay or SharedAsyncDelay to determine how long it would wait before forcing asynchronous writes. FlushTransactionTimeout changes that behavior by having a value that will start asynchronous writes only after the specified amount of time has expired and no pages have been added to the cache. The only exception to this is if the cache exceeds the MaxBufferSize, at which point the cache will start asynchronous writing regardless of whether or not the time has expired. Microsoft Jet 3.5 database engine will wait 500 milliseconds of non-activity or until the cache size is exceeded before starting asynchronous writes.
LockDelay This setting works in conjunction with the LockRetry setting in that it causes each LockRetry to wait 100 milliseconds before issuing another lock request. The LockDelay setting was added to prevent bursting that would occur with certain networking operating systems.
MaxLocksPerFile This setting prevents transactions in Microsoft Jet from exceeding the specified value. If the locks in a transaction attempt to exceed this value, then the transaction is split into two or more parts and partially committed. This setting was added to prevent Netware 3.1 server crashes when the specified Netware lock limit was exceeded, and to improve performance with both Netware and NT.
LockRetry The number of times to repeat attempts to access a locked page before returning a lock conflict message. The default is 20.
RecycleLVs This setting, when enabled, will cause Microsoft Jet to recycle long value (LV) pages (Memo, Long Binary [OLE object], and Binary data types). Microsoft Jet 3.0 would not recycle those types of pages until the last user closed the database. If the RecyleLVs setting is enabled, Microsoft Jet 3.5 will start to recycle most LV pages when the database is expanded (that is, when groups of pages are added).

Note  By enabling this feature, users will notice a performance degradation when manipulating long value data types. Microsoft Access 97 automatically enables and disables this feature when manipulating modules, forms, and reports, thus eliminating the need to turn it on when modifying those objects. The default value is 0.

MaxBufferSize The size of the database engine internal cache, measured in kilobytes (K). MaxBufferSize must be an integer value greater than or equal to 512. The default is based on the following formula:

((TotalRAM in MB - 12 MB) / 4) + 512 KB

For example, on a system with 32 MB of RAM, the default buffer size is ((32 MB - 12 MB) / 4) + 512 KB or 5632 KB. To set the value to the default, set the registry key to

MaxBufferSize=

Threads The number of background threads available to the Microsoft Jet database engine. The default is 3.
UserCommitSync Specifies whether the system waits for a commit to finish. A value of Yes instructs the system to wait; a value of No instructs the system to perform the commit asynchronously. The default is Yes.
ImplicitCommitSync Specifies whether the system waits for a commit to finish. A value of No instructs the system to proceed without waiting for the commit to finish; a value of Yes instructs the system to wait for the commit to finish. The default is No.
ExclusiveAsyncDelay Specifies the length of time, in milliseconds, to defer an asynchronous flush of an exclusive database. The default value is 2000, or 2 seconds.
SharedAsyncDelay Specifies the length of time, in milliseconds, to defer an asynchronous flush of a shared database. The default value is 0.
PagesLockedToTableLock During bulk operations it is often more efficient to lock a whole table, instead of obtaining locks for each individual page of the table as you try to access it.

This setting specifies the number of pages that Microsoft Jet will allow to be locked in any particular transaction before Jet attempts to escalate to an exclusive table lock

The default value of 0 indicates that Jet will never automatically change from page locking to table locking.

Note  This setting should be used carefully. If a database is needed for multi-user access, then locking a whole table could cause locking conflicts for other users. This would be especially severe if a small number was used for this setting. Even when a larger number was used, such as 25 or 50, the operation for other users might become unpredictable.


Microsoft Jet Engine Replication Settings

The Jet\4.0\Transporter key includes initialization settings for Jet Replication synchronizations via the Jet Synchronizer. These settings determine the order that specific transports will be attempted by Synchronizer synchs. The valid values are 0-100. A value of 0 implies that a transport should not be attempted. All transport types with non-zero values will be attempted in ascending key value order until one successfully executes the synchronization or until all attempts fail.

Typical initialization settings for the entries in this folder are shown in the following example.

Priority_FS=1

Priority_Internet=2

Priority_direct=3

The Microsoft Jet database engine uses the following priority entries.

Entry Description
Priority_FS File System (Indirect) Synchronization relies on a series of message exchanges between replicas. The Synchronizer that manages each replica collects changes into message files (*.msg), which are then copied to a shared folder called a dropbox, used by the partner Synchronizer and accessible via the file system.
Priority_Internet Internet (Indirect) Synchronization relies on a series of message exchanges between replicas. Only the replica managed by the Synchronizer on the Internet Server has a dropbox that is accessible via a HTTP or FTP connection.
Priority_direct Direct synchronization is the process of exchanging data and design changes between two members of a replica set that are directly connected, either on the same computer or over a network. The Synchronizer opens both databases.

Note  When you change Windows Registry settings, you must exit and then restart the database engine for the new settings to take effect.

See Also

Customizing Windows Registry Settings for Microsoft Jet