How To Use the Jet OLEDB:Registry Path Dynamic Property of the OLE DB Provider for Jet (252444)


The information in this article applies to:

  • Microsoft OLE DB Provider for Jet 4.0
  • Microsoft OLE DB Provider for Jet 3.51
This article was previously published under Q252444
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SUMMARY

You can edit Microsoft Windows registry settings for the Microsoft Jet engine to alter the default behavior of the engine. However, doing so affects all applications that use Jet and may have unintended negative consequences in some circumstances. You can also create a new registry key to tune Jet settings for each application. These settings are sometimes referred to as a custom profile.

MORE INFORMATION

The 32-bit versions of Microsoft Jet read their settings from the registry. By default, the registry locations are as follows:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5

-and-

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0

To use custom settings for a particular Jet application, you can tell Jet to read certain settings from a different branch of the registry. These alternate branches are sometimes called profiles.

Note: Jet uses the default location to read any keys or values that are missing from the custom profile.

There are many settings that you can specify in the connect string that override registry values. If you need to alter these values, you do not need a custom profile.

ValueConnect String Argument
Engines\SystemDBJet OLEDB:System Database
Engines\SandBoxModeJet OLEDB:Sandbox Mode
Engines\Jet 4.0\ExclusiveAsyncDelayJet OLEDB:Exclusive Async Delay
Engines\Jet 4.0\FlushTransactionTimeoutJet OLEDB:Flush Transaction Timeout
Engines\Jet 4.0\ImplicitCommitSyncJet OLEDB:Implicit Commit Sync
Engines\Jet 4.0\LockDelayJet OLEDB:Lock Delay
Engines\Jet 4.0\LockRetryJet OLEDB:Lock Retry
Engines\Jet 4.0\MaxBufferSizeJet OLEDB:Max Buffer Size
Engines\Jet 4.0\MaxLocksPerFileJet OLEDB:Max Locks Per File
Engines\Jet 4.0\PagesLockedToTableLockJet OLEDB:Page Locks to Table Lock
Engines\Jet 4.0\PageTimeoutJet OLEDB:Page Timeout
Engines\Jet 4.0\RecycleLVsJet OLEDB:Recycle Long-Valued Pages
Engines\Jet 4.0\SharedAsyncDelayJet OLEDB:Shared Async Delay
Engines\Jet 4.0\UserCommitSyncJet OLEDB:User Commit Sync
Engines\ODBC\QueryTimeoutJet OLEDB:ODBC Command Time Out

You can use the hierarchy and naming scheme of your choice to create the new registry subkey, as long as the subkey is located under HKEY_LOCAL_MACHINE.

Create the Custom Registry Settings

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

This example illustrates how to use a custom profile to increase the Jet MaxLocksPerFile setting from the default 9500 to 15000.
  1. In Registry Editor, click to expand HKEY_LOCAL_MACHINE\Software, and add key values for your company name, program name, and version number. For example:

    HKEY_LOCAL_MACHINE\Software\<MyCompany>\<MyApp>\1.0

  2. Add the subkeys Engine and Jet 4.0 beneath the preceding values. For example:

    HKEY_LOCAL_MACHINE\Software\<MyCompany>\<MyApp>\1.0\Engines\Jet 4.0

  3. Add keys and values for the Jet engine settings that you want to change for your application. For example, add a new DWORD value named MaxLocksPerFile, and specify its value as 15000.

Use the Custom Settings with ActiveX Data Objects

In order for your Microsoft Visual Basic application to use the custom registry settings, you must use the Jet OLEDB:Registry Path dynamic property of the ActiveX Data Objects (ADO) Connection object. This property must be specified before opening the Connection. The most important fact to keep in mind is that the Jet provider adds HKEY_LOCAL_MACHINE\ to the beginning of the value that you specify and \Engines\Jet 4.0 to the end; thus, you must omit these portions of the key from the value that you specify.

For example, to access the settings at the registry key

HKEY_LOCAL_MACHINE\Software\<MyCompany>\<MyApp>\1.0\Engines\Jet

you must specify the Jet OLEDB:Registry Path property as follows:

Software\<MyCompany>\<MyApp>\1.0

Then, use the following code to tell your ADO application to use these custom settings: 
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
  .Provider="Microsoft.Jet.OLEDB.4.0"
  .ConnectionString = "C:\Program Files\Microsoft Visual Studio\Vb98\Nwind.mdb"
  .Properties("Jet OLEDB:Registry Path") = "Software\MyCompany\MyApp\1.0"
  .Open
End With
Alternately, you can supply all the information on the Open method of the Connection object: 
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb;" & _
        "Jet OLEDB:Registry Path=Software\MyCompany\MyApp\1.0"
As a result, Jet applies your custom setting of 15,000 MaxLocksPerFile to this database connection only. Other running applications that use Jet are not affected, nor are other connections to the Jet database engine within the same application.

Note: For the Microsoft.Jet.OLEDB.3.51 provider registry key, the subkey is Engines\Jet 3.5.

REFERENCES

For additional information%1, click the article number%2 below to view the article%2 in the Microsoft Knowledge Base:

%3 %4

Modification Type:MinorLast Reviewed:7/13/2004
Keywords:kbhowto kbMDACNoSweep KB252444
©2004 Microsoft Corporation. All rights reserved.