Symptoms
You can edit the Jet Registry keys to alter the data access behavior of theMicrosoft Jet database engine. However, doing so affects all Jet-basedapplications, and may have unintended negative consequences for some. Byusing custom profiles, you can tune Jet on a per-application basis.
Resolution
In 16-bit versions of Visual Basic and Microsoft Access, Jet settings arestored in an .ini file (for example, Msaccess.ini or Msacc20.ini). You canprovide a custom .ini file with your application that contains settingsspecific to your application.
The 32-bit versions of Jet read their settings from the registry.
In Visual Basic 4.0, 5.0, 6.0, and Jet versions 3.0 and 3.5 use the defaultregistry location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5
If using Microsoft Access, settings are read from the following branchfirst, and then the default location (above) is used for any additionalvalues:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5
To prevent multiple applications from making custom changes to thesesettings and negatively impacting other Jet-based programs, the applicationcan tell Jet to read its settings from a different branch of the registry.These alternate branches are known as Profiles.
You don’t have to reproduce the entire registry branch in your profile.Values you specify in your custom profile override values specified in thedefault registry location, which in turn override internal Jet defaultvalues.
Example ProfileWARNING: Using Registry Editor incorrectly can cause serious problems thatmay require you to reinstall your operating system. Microsoft cannotguarantee that problems resulting from the incorrect use of Registry Editorcan be solved. Use Registry Editor at your own risk.
For information about how to edit the registry, view the “Changing Keys AndValues” Help topic in Registry Editor (Regedit.exe) or the “Add and DeleteInformation in the Registry” and “Edit Registry Data” Help topics inRegedt32.exe. Note that you should back up the registry before you edit it.If you are running Windows NT, you should also update your Emergency RepairDisk (ERD).
This example illustrates registry changes for a custom profile that lowersthe LockRetry setting from the default value of 20 to 5.
In the Registry Editor, expand HKEY_LOCAL_MACHINE\SOFTWARE and add keyvalues for your company name (MyCorp), application name (MyApp), andversion (1.0):
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0The above is sufficient for Visual Basic, but to maintain compatibilitywith Microsoft Access applications, add keys for “Jet” and “3.0″ or”3.5″:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5This is an empty Profile. From here, add keys and values that affectJet’s behavior. To make the LockRetry changes, add the keys “Engines”and “Jet”:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0\Engines\Jet
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5\Engines\Jet
Then, add a REG_DWORD Value, called LockRetry, and a value of 5.
The profile is complete.
Using the Profile in Visual BasicTo use the profile in Visual Basic, set DBEngine.IniPath to the profilename prior to using any database functionality:
In Visual Basic 4.0:
DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0″
In Visual Basic 5.0:
DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5″
Using the Profile in Microsoft AccessUnlike visual Basic, where you can set the profile name programmatically,Jet is already initialized by the time your Microsoft Access code isrunning. To specify a profile name, you need to add another registry key tospecify a “friendly” profile name and use a command-line switch whenstarting Microsoft Access:
Expand the following registry branch:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\AccessAdd a key “Profiles”.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Profiles
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\ProfilesAdd a REG_SZ value with the name being your “friendly” profile name(for example, MyProfile) and the value being the registry branch wherethe profile settings are located. For example:
Name:MyProfileType:REG_SZValue: HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0Start Microsoft Access with the command-line option:
MSACCESS.EXE /PROFILE “MyProfile” MYDATABASE.MDBMicrosoft Access 95 will automatically choose the …\Jet\3.0 branch,Microsoft Access 97 will automatically choose the …\Jet\3.5 branch, inVisual Basic you must specify the complete profile path.
Because you can’t guarantee that Microsoft Access was started using thecorrect profile, you can check your startup code to see if the userstarted the application properly or just double-clicked the icon for the.mdb file. For example:
If SysCmd(acSysCmdProfile) <> “MyProfile” ThenMsgBox “To use this database, run MyApp from the Start Menu”DoCmd.Quit acExitEnd If
Compatibility with Earlier Jet DatabasesIf the database you’re opening is used by earlier versions of Jet (1.0,1.1, 2.0, or 2.5), Microsoft Jet 3.x doesn’t read these files directly. Ituses an Installable ISAM engine in the same way it would when reading adBase or Paradox file.
For example, to set the LockRetry value for accessing a Jet 2.x database,add the Value to the following location:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0\Engines\Jet 2.x\ISAM
You still only reference the profile location as indicated in the MicrosoftAccess and Visual Basic usage sections above. For example:
In Visual Basic:
DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0″
-or-
DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5″
In Microsoft Access:
Value: HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0
Table of Engine KeysThe following table lists registry branches for the Jet 3.x engine and thevarious other engines:
EngineRegistry Branch—————————————————-Jet 3.x…\Engines\JetJet 2.x…\Engines\Jet 2.x\ISAMODBC…\Engines\ODBCXbase…\Engines\XbaseParadox…\Engines\ParadoxExcel…\Engines\ExcelLotus…\Engines\LotusText…\Engines\Text
Jet 3.5 NotesWith Jet 3.5, you can tune some values dynamically using the new SetOptionmethod of the DBEngine object. This means you can use some values for onepart of your program, that is, to favor a bulk update, while using othervalues in another part of your program, that is, to favor interactive dataentry. Using DBEngine.SetOption affects only the current instance of yourprogram. It can be used in Microsoft Access 97 and Visual Basic 5.0.
The following table lists the settings you can change:
SettingConstant—————————————————PageTimeoutdbPageTimeoutSharedAsyncDelaydbSharedAsyncDelayExclusiveAsyncDelaydbExclusiveAsyncDelayLockRetrydbLockRetryUserCommitSyncdbUserCommitSyncImplicitCommitSyncdbImplicitCommitSyncMaxBufferSizedbMaxBufferSizeMaxLocksPerFiledbMaxLocksPerFileLockDelaydbLockDelayRecycleLVsdbRecycleLVsFlushTransactionTimeout dbFlushTransactionTimeout
Usage:
DBEngine.SetOption dbMaxBufferSize, 128
