
The Windows Registry is a remarkable component of Windows 95. It is used to store information about every application installed on your machine and replaces the original INI files of Windows 3.x. The information that is held in the Registry ranges from file paths to encrypted registration keys for ActiveX controls.
You can view the contents of the Windows Registry by locating the RegEdit application in your Windows directory. After launching this application, you can view the contents of any key stored in the Registry. You can also edit, create, or delete folders and keys within these folders; however, you should take extreme caution in doing so, because these values are of great importance. Make sure you know and realize what you are doing with the information in the Windows Registry before you decide to change anything.
REGISTRY SAFEGUARDSAlways have at least one workable version of the Registry backed up before making any type of adjustment to it.
If you have made changes to the Registry but not restarted Windows since the change, you can restore the Registry to its state at the last successful Windows startup. Shut down Windows by choosing Restart in MS-DOS mode. Then when you see the MS-DOS prompt, typescanreq /restore
If you are having a problem restarting Windows after Registry changes, you can reboot from an emergency boot floppy disk, and type the preceding command from the MS-DOS prompt.
In this chapter, we will dive into the Windows Registry and see how we can get it to work for us. We will accomplish this by using it to save state information for our application and our data access. We will also look into changing specific information that is inherent on every user's machine to tune and maximize performance in our applications.
When you exit most applications and then restart them later, you will notice that the screens appear to be just as you left them earlier. This technique of storing an application's state information is discussed in this How-To.
Sometimes it is important to use data access specifications that most other applications do not use. It is important, however, to maintain the reliability of the original specifications in the Window Registry. This How-To presents a project used to store state information for data access in the Windows Registry and to temporarily change the settings for data access.
Many times when creating a set of distribution disks for your application, you forget to include specific files relating to databases or reports. This How-To explains which files are necessary for each of these components.
If you are the down-and-dirty type when it comes to tweaking your machine for optimum performance, this How-To is for you. Here we show you how to fine-tune each of the keys relating to the Jet database engine tick.
As well as fine-tuning the Jet database engine through the Windows Registry, you can also tune the ODBC engine. In this How-To, we will explain just how to do this.
Problem
My applications need to store specific state information. For example, I would like to store the position and size of the windows used in my projects so that the next time a user loads the program, it appears to continue where he left off. How do I store state information for my applications using Visual Basic?
Technique
Back in the days of Windows 3.x, applications used INI files to store state information for their programs. Apparently, this led to numerous INI files spread throughout user's drives. With the introduction of Windows 95, we now have what is called the Windows Registry.
The Windows Registry acts as a Grand Central Station for INI files. Not only is application state information stored in the Registry, but registrations for OCXs also reside here. By using the same statements that we used to access INI files for Windows 3.x applications, we can work with the Windows Registry. There are four in all:
Visual Basic has set a special section set aside for state information of user's
applications. This section is labeled by the key HKEY_CURRENT_USER\
Software\VB and VBA Program Settings. By using the four statements previously listed,
we can create, read, and alter settings in the VB and VBA Program Settings area of
the Windows Registry to effectively record our application's state information.
Steps
Load and run RegistryEditor.vbp. You will see the form shown in Figure 11.1. The Application Name and Section text boxes are set to the default values used for this application. Change these values to see different section keys. After you select a key, you can edit it and click the Save Setting command button to save the setting. End the application by clicking the Close button.
Figure 11.1. The Registry Editor project.
Table 11.1. Objects and properties for the Registry Editor project.
OBJECT Property Setting Form Name frmRegistryEditor Caption "Registry Editor" Text box Name txtApplicationName Caption "VB6DBHT Chapter 11" Text box Name txtSection Caption "Settings" Text box Name txtSetting Caption "" List box Name lstKeys Command button Name cmdClose Caption "&Close" Cancel -1 `True Default -1 `True Command button Name cmdSave Caption "&Save Setting" Label Name lblApplicationName Caption "&Application Name:" Label Name lblSection Caption "Se&ction:" Label Name lblKeys Caption "&Key(s):" Label Name lblSetting Caption "Se&tting:"
Option Explicit ` form-level variable used to store keys and settings for ` desired application and section Private m_vSettings As Variant
Private Sub Form_Load()
` initialize the application by populating the key list box
RepopulateKeys
End Sub
Private Sub lstKeys_Click()
` error message of choice when error has occurred
` obtaining setting
Const ERRMSG_INVALID_SETTING = "<ERROR>"
` set the txtSetting text box to the value of the key in
` the Registry
txtSetting = GetSetting(txtApplicationName, _
txtSection, _
lstKeys.Text, _
ERRMSG_INVALID_SETTING)
` if there was an error in the retrieval process, disable
` editing of the key's setting
If (txtSetting <> ERRMSG_INVALID_SETTING) Then
lstKeys.Enabled = True
txtSetting.Enabled = True
cmdSave.Enabled = True
End If
End Sub
Private Sub txtApplicationName_Change()
` repopulate the key list box when the application name
` has changed
RepopulateKeys
End Sub
Private Sub txtSection_Change()
` repopulate the key list box when the section name
` has changed
RepopulateKeys
End Sub
Private Sub cmdClose_Click()
` end the application
Unload Me
End Sub
Private Sub cmdSave_Click()
` save the selected key information from the desired
` information on the form
SaveSetting txtApplicationName, _
txtSection, _
lstKeys.Text, _
txtSetting
End Sub
Private Sub RepopulateKeys()
` if there is an error, goto the code labeled by ERR_RepopulateKeys
On Error GoTo ERR_RepopulateKeys:
Dim nCount As Integer
` errors that are expected to be encountered
Const ERR_INVALID_PROC_CALL = 5
Const ERR_TYPE_MISMATCH = 13
With lstKeys
` clear the listbox and setting text box
.Clear
txtSetting = ""
` disable editing functions
lstKeys.Enabled = False
txtSetting.Enabled = False
cmdSave.Enabled = False
` retrieve available keys for given application name
` and section this will cause an ERR_INVALID_PROC_CALL
` error if one of the text box controls are empty
m_vSettings = GetAllSettings(txtApplicationName, _
txtSection)
` add each setting to the key list box
` this will cause an ERR_TYPE_MISMATCH error if there
` are no keys for the selected application and section
` names
For nCount = 0 To UBound(m_vSettings, 1)
.AddItem m_vSettings(nCount, 0)
Next nCount
` select the first item in the list box
.ListIndex = 0
End With
Exit Sub
ERR_RepopulateKeys:
With Err
Select Case .Number
` if the error is expected, do nothing but end the
` procedure
Case ERR_INVALID_PROC_CALL, ERR_TYPE_MISMATCH:
` unexpected error, display for the user
Case Else:
MsgBox "ERROR #" * .Number & ": " & _
.Description, _
vbExclamation, "ERROR"
End Select
End With
End Sub
How It Works
When this project is run, the list box control of the form is populated with the keys available for the given application and section names using the GetAllSettings statement. If the section does not exist in the Windows Registry, an error occurs, and the application is ended gracefully. If there are keys, the list box is populated and the controls that are related to editing the key's setting are enabled.
After you decide to change the setting for a given key selected from the list box, you click the Save Setting button. The code that is in the cmdSave_Click event uses the SaveSetting statement to save the current key's value.
Choosing a new key from the list box control causes the code to execute the GetSetting statement, which retrieves an individual setting for a specified application name, section, and key.
Comments
In this project, we saved all the key's settings as strings in the Windows Registry. This is because the txtSetting text box's Text property returns a string value. If you were to use this project to save the information for your application's position and size, you would have to change the string value returned to a Long value.
This problem can be avoided by using a variable with a Long data type in the SaveSetting statement. Visual Basic creates a new key with the data type of the specified setting.
Problem
My application calls for DAO settings that are not considered standard. I can manually edit the Registry key settings to the values that my application requires; however, other programs will be affected by my changes. How do I temporarily change the DAO settings every time a user runs my projects?
Technique
By using the techniques discussed in How-To 11.1, we know that we can save state information for our application. Visual Basic does not care what kind of information this is; therefore, we can just as easily store DAO setting information as we can the application's position and height.
To temporarily change the DAO settings for the Jet engine, we can use the SetOption method of the DBEngine. Using this command we can change the values of parameters that Jet uses to access data. These changes are made until we change them again or the DBEngine is actually closed.
In all, there are 11 parameters we can use to alter the Jet and DAO's behavior. These parameters and their associated key--in the Jet\3.5\Engines\Jet 3.5\ section of the Windows Registry--are listed in Table 11.2.
| KEY | Parameter Enumeration Constant |
| PageTimeout | dbPageTimeout |
| SharedAsyncDelay | dbSharedAsyncDelay |
| ExclusiveAsyncDelay | dbExclusiveAsyncDelay |
| LockRetry | dbLockRetry |
| UserCommitSync | dbUserCommitSync |
| ImplicitCommitSync | dbImplicitCommitSync |
| MaxBufferSize | dbMaxBufferSize |
| MaxLocksPerFile | dbMaxLocksPerFile |
| LockDelay | dbLockDelay |
| RecycleLVs | dbRecycleLVs |
| FlushTransactionTimeout | dbFlushTransactionTimeout |
Steps
Open and run SetOptions.vbp. You will see the form shown in Figure 11.2. By selecting the key from the combo box, you will see the associated setting for the key in the Setting text box. Changing the key's setting and clicking the Save Setting button will not only save the value of the setting, but also temporarily change the parameter for the DBEngine object. If you delete the key by clicking the Delete Key button, you will remove the key from the section for our state information in the Registry. The next time you go to view the key, you will see the default setting of the key.
Figure 11.2. The Set Options project.
Table 11.3. Objects and properties for the Set Options project.
OBJECT Property Setting Form Name frmSetOptions Caption "Set Options" Combo box Name cboKeys Style 2 `Dropdown List Text box Name txtSetting Caption "" Command button Name cmdClose Caption "&Close" Cancel -1 `True Default -1 `True Command button Name cmdSave Caption "&Save Setting" Command button Name cmdDelete Caption "&Delete Key" Label Name lblKey Caption "&Key:" Label Name lblSetting Caption "Se&tting:"
Option Explicit ` form-level variable used to store the selected parameter from ` the list in the keys combo box Private m_lSelectedParameter As Long ` form-level constant declarations used throughout the ` application to name the application and section when ` using the Get and Save settings methods Private Const APPLICATION_TITLE = "VB6DBHT Chapter 11" Private Const SECTION_NAME = "Jet 3.5"
Private Sub Form_Load()
` load all Jet Registry settings from application section
` of the Windows Registry
LoadJetRegistryInformation APPLICATION_TITLE, SECTION_NAME
With cboKeys
` add all the available parameters for the SetOption
` method
.AddItem "dbPageTimeout"
.AddItem "dbSharedAsyncDelay"
.AddItem "dbExclusiveAsyncDelay"
.AddItem "dbLockRetry"
.AddItem "dbUserCommitSync"
.AddItem "dbImplicitCommitSync"
.AddItem "dbMaxBufferSize"
.AddItem "dbMaxLocksPerFile"
.AddItem "dbLockDelay"
.AddItem "dbRecycleLVs"
.AddItem "dbFlushTransactionTimeout"
` select the first item in the combo box control
.ListIndex = 0
End With
End Sub
Private Sub cboKeys_Click()
Dim lDefaultSetting As Variant
With cboKeys
` get a long value from the text version of the key
m_lSelectedParameter = GetParameterFromKey(.Text)
` obtain the default setting for the key
lDefaultSetting = GetDefaultKeySetting(.Text)
` display the current setting from the application's
` Registry settings if there is one; otherwise,
` display the default
txtSetting = GetSetting(APPLICATION_TITLE, _
SECTION_NAME, _
.Text, _
lDefaultSetting)
End With
End Sub
Private Sub cmdClose_Click()
` end the application
Unload Me
End Sub
Private Sub cmdSave_Click() ` if there is an error, goto the code labeled by ERR_ ` cmdSave_Click On Error GoTo ERR_cmdSave_Click: ` constant declarations for expected errors Const ERR_TYPE_MISMATCH = 13 Const ERR_RESERVED_ERROR = 3000
` attempt to set the DBEngine option for the given key ` an error will occur here if an incorrect setting data ` type is entered by the user DBEngine.SetOption m_lSelectedParameter, _ GetValueFromSetting(txtSetting) ` if the SetOption method was successful, save the ` new setting value in the application Registry section SaveSetting APPLICATION_TITLE, SECTION_NAME, _ cboKeys.Text, txtSetting ` inform the user of the success MsgBox "Change has been made.", vbInformation, "Set Option" Exit Sub ERR_cmdSave_Click: Dim sMessage As String With Err Select Case .Number ` wrong data type entered for key setting Case ERR_TYPE_MISMATCH, ERR_RESERVED_ERROR: sMessage = "Value is of incorrect format." ` unexpected error, create a message from the error Case Else: sMessage = "ERROR #" & .Number & ": " & _ .Description End Select End With ` inform the user of the error MsgBox sMessage, vbExclamation, "ERROR" ` repopulate the setting text box with the current or ` default key setting and set focus to the text box cboKeys_Click txtSetting.SetFocus End Sub
Private Sub cmdDelete_Click()
` remove the setting from the application section of the
` Windows Registry
DeleteSetting APPLICATION_TITLE, SECTION_NAME, cboKeys.Text
` refresh the setting text box with the default value
cboKeys_Click
` inform the user of the success
MsgBox "Key has been deleted.", vbInformation, "Delete Key"
End Sub
Public Sub LoadJetRegistryInformation( _
sApplicationName As String, _
sSectionName As String)
` if there is an error, goto the code labeled by
` ERR_LoadJetRegistryInformation
On Error GoTo ERR_LoadJetRegistryInformation:
Dim vSettings As Variant
Dim nCount As Integer
` constant declaration for expected error
Const ERR_TYPE_MISMATCH = 13
` obtain all the settings from the Registry section for
` the given application
vSettings = GetAllSettings(sApplicationName, sSectionName)
` set all the options that were specified in the Jet 3.5
` section for the current application
For nCount = 0 To UBound(vSettings, 1)
DBEngine.SetOption GetParameterFromKey _
(vSettings(nCount, 0)), _
GetValueFromSetting( _
vSettings(nCount, 1))
Next nCount
Exit Sub
ERR_LoadJetRegistryInformation:
With Err
Select Case .Number
` there was no settings specified in the Registry
` for the given application, just continue without
` displaying an error message
Case ERR_TYPE_MISMATCH:
` unexpected error, create a message from the error
Case Else:
MsgBox "ERROR #" & .Number & ": " & _
.Description, _
vbExclamation, "ERROR"
End Select
End With
End Sub
Public Function GetValueFromSetting( _
vSetting As Variant) As Variant
` if the setting is a number, return a long; otherwise,
` return a string
If (IsNumeric(vSetting)) Then
GetValueFromSetting = CLng(vSetting)
Else
GetValueFromSetting = CStr(vSetting)
End If
End Function
Public Function GetDefaultKeySetting(sKey As String) As Variant ` return the default key setting for the key specified Select Case sKey Case "dbPageTimeout": GetDefaultKeySetting = 5000 Case "dbSharedAsyncDelay": GetDefaultKeySetting = 0 Case "dbExclusiveAsyncDelay": GetDefaultKeySetting = 2000 Case "dbLockEntry": GetDefaultKeySetting = 20 Case "dbUserCommitSync": GetDefaultKeySetting = "Yes" Case "dbImplicitCommitSync": GetDefaultKeySetting = "No" Case "dbMaxBufferSize": GetDefaultKeySetting = 0
Case "dbMaxLocksPerFile": GetDefaultKeySetting = 9500 Case "dbLockDelay": GetDefaultKeySetting = 100 Case "dbRecycleLVs": GetDefaultKeySetting = 0 Case "dbFlushTransactionTimeout": GetDefaultKeySetting = 500 End Select End Function
Public Function GetParameterFromKey(ByVal sKey As String) As Long
` return the correct constant for the given key
Select Case sKey
Case "dbPageTimeout":
GetParameterFromKey = dbPageTimeout
Case "dbSharedAsyncDelay":
GetParameterFromKey = dbSharedAsyncDelay
Case "dbExclusiveAsyncDelay":
GetParameterFromKey = dbExclusiveAsyncDelay
Case "dbLockRetry":
GetParameterFromKey = dbLockRetry
Case "dbUserCommitSync":
GetParameterFromKey = dbUserCommitSync
Case "dbImplicitCommitSync":
GetParameterFromKey = dbImplicitCommitSync
Case "dbMaxBufferSize":
GetParameterFromKey = dbMaxBufferSize
Case "dbMaxLocksPerFile":
GetParameterFromKey = dbMaxLocksPerFile
Case "dbLockDelay":
GetParameterFromKey = dbLockDelay
Case "dbRecycleLVs":
GetParameterFromKey = dbRecycleLVs
Case "dbFlushTransactionTimeout":
GetParameterFromKey = dbFlushTransactionTimeout
End Select
End Function
How It Works
This project uses two files for code. The first file, the frmSetOptions form, holds the information for displaying and altering the application's Jet engine state information.
The second file used in this project, the RegistryInformation module, is designed to be portable and to be added to your own project. By calling the LoadJetRegistryInformation and passing the application's name and section, the procedure loads all the state information stored for the Jet in the specified section of the Windows Registry. It then uses the SetOption method to temporarily change the parameter values of Jet engine access for the life of your application.
Comments
It is important not to change the values of the actual settings for the Jet in the key Jet\3.5\Engines\Jet 3.5\ because it is very likely this is where the rest of your applications are finding the parameter values for Jet DAO access. If by some chance you decide to change these values and need to set them back to their original values, you can find the default settings from the Microsoft Visual Basic Books Online, in the "Initializing the Microsoft Jet 3.5 Database Engine" section.
For more information on the meaning of the parameters used in this section, see How-To 11.4, "Tune the Jet Database Engine Through Windows Registry Entries."
PROBLEM
I am creating an application that uses the Jet database engine and Crystal Reports.
How do I determine the files I need to distribute with my application?
Technique
Before Visual Basic 5.0, life was rough. Actually, life is still rough, but we are getting there. Creating distribution disks for your application was a headache up until Microsoft began delivering its Application Setup Wizard with Visual Basic 5.0. With the introduction of Visual Basic 6.0, Microsoft offers us the Package and Deployment Wizard. The Package and Deployment Wizard does everything that the Application Wizard in Visual Basic 5.0 did, but it also adds the ability to create packages for network or Internet distribution. In this How-To, we will concentrate on how the Package and Deployment Wizard can be used to create conventional disk-based packages for our applications.
Steps
Run the Package and Deployment Wizard that comes with Visual Basic 6.0. You will see the wizard as shown in Figure 11.3.
Figure 11.3. The Package and Deployment Wizard.
Figure 11.4. Choosing a packaging script.
Figure 11.5. Choosing a setup type.
Figure 11.6. Choosing a location to build the setup.
Figure 11.7. Choosing a DAO driver.
Figure 11.8. Choosing files for the setup.
Figure 11.9. Choosing a distribution size.
Figure 11.10. Choosing a name for the setup.
Figure 11.11. Choosing menu groups and Items.
Figure 11.12. Changing the location of setup components.
Figure 11.13. Choosing shared files.
Figure 11.14. Completing the setup.
How It Works
Problems arise sometimes when files become lost or when the Package and Deployment Wizard does not find all the files necessary to be included in our distribution disks. All the files that actually have meaning to us to run our application are considered runtime files.
Runtime Files
There is a check list of standard runtime files that you should work through when creating a set of distribution disks. This checklist is as follows:
You should be able to know immediately what database, report, data, and ActiveX files you are to add to your setup disks because these are files that you explicitly added to your project. The Visual Basic runtime DLL (MSVBVM60.DLL) is necessary for every Visual Basic application. The trickiest files to find are component dependency files.
Component Dependencies
Component dependencies are files that are necessary in order to use specific ActiveX controls and particular components that are added as references from your application. There are a number of resources, however, to locate the proper dependency files that are important to you.
First, it is important to always consult the documentation on all third-party components, as well as Microsoft components, to see which DLLs are necessary in order to use its product. For instance, the Microsoft Data Reporter requires the MSDBRPT.DLL file. In most cases, REG files are also necessary. These files have registration information, entered into the Windows Registry, that is used to determine the licensing usage available to the user for the particular component.
The second source for finding the appropriate dependency files for your distribution disks is the Visual Basic documentation. All the included components are documented and explain which files are necessary in order for them to work properly. All ActiveX controls will come with DEP files, if they don't already. For example, the Microsoft Data Repeater ActiveX control comes with a file named MSDATREP.DEP, and Crystal Reports comes with CRYSTL32.DEP. These files are used by the Package and Deployment Wizard to determine which files are necessary for installation.
The third and most important source of information is the VB6DEP.INI file. This file replaces the original SWDEPEND.INI file of earlier times and describes the dependencies used by Visual Basic.
The VB6DEP.INI file can be found in the Visual Basic \Wizards\PDWizard directory. This file lists necessary dependencies for all available Visual Basic components. This file is used by the Package and Deployment Wizard to determine the appropriate files necessary to successfully run your application.
Comments
The following is a portion of the MSDATREP.DEP file. It lists the information necessary to successfully incorporate the MSDATREP.OCX ActiveX control that is for the Microsoft Data Repeater.
[MSDatRep.ocx] Dest=$(WinSysPath) Register=$(DLLSelfRegister) Version=6.0.80.52 Uses1=ComCat.dll Uses2=MSStdFmt.dll Uses3=MSBind.dll CABFileName=MSDatRep.cab CABDefaultURL=http://activex.microsoft.com/controls/vb6 CABINFFile=MSDatRep.inf
The header of this portion of code, [MSDatRep.ocx], indicates the file in question. The first key, Dest, indicates where the file should be stored on the installation machine (in this case, the Windows System path).
The second parameter, Register, indicates that the file will self-register in the Windows Registry. The Version parameter clearly holds the file's version number to compare with older files during the installation process.
After this, a list of additional dependencies is listed with the parameter form of UsesX, where X is the number of the dependency. These are files that the actual file being installed (MSDATREP.OCX) uses to reference; therefore, they in turn must also be installed. The CABFileName parameter is the name of the installation file for the particular installed file, and CABINFFile is the file that contains the installation information for the installed file.
I skipped the CABDefaultURL parameter, which indicates the default Web site that is used in reference to the installed file for upgrades or more information.
Problem
My application needs to alter the way the Jet engine is initiated in the Windows Registry in order to obtain better performance. I know I can edit the Windows Registry using RegEdit, but I do not know what to actually do. How do I tune the Jet engine through the Windows Registry?
Technique
When you install the Microsoft Jet engine for the first time, two DLL files are registered in the Windows Registry. These files are MSJET35.DLL and MSRD2X35.DLL. When these files are registered, two entries are created in the HKEY_LOCAL_MACHINES\Software\Microsoft\Jet\3.5\Engines folder. This is done automatically when you install Access 97.
The first of these keys represents the path to the system database file. The typical path for this key would be the system directory of Windows; therefore, no path is necessary because the system directory is usually part of the default path. The following is an example of this SystemDB key:
SystemDB = "C:\WINDOWS\SYSTEM\SYSTEM.MDB"
The second key that is created is called CompactBYPkey. When this key is set to anything but zero, databases will be compacted in the order of the primary key. If no primary key exists, the database is compacted in base-table order. A value of zero for the CompactBYPkey key will instruct the Jet engine to compact databases in base-table order. The default value for this key is nonzero, as in the following example:
CompactBYPkey = 1
It should be noted that this setting is good only for databases created with the Microsoft Jet database engine, version 3.0 or later. Any database created from an earlier version will compact by base-table order automatically.
The Microsoft Jet is controlled by keys set in the \HKEY_LOCAL_MACHINES\
Software\Microsoft\Jet\3.5\Engines\Jet 3.5 folder of the Windows Registry. The default
settings for these keys are shown in Table 11.4.
| KEY | Default |
| PageTimeout | 5000 |
| SharedAsyncDelay | 0 |
| ExclusiveAsyncDelay | 2000 |
| LockRetry | 20 |
| UserCommitSync | Yes |
| ImplicitCommitSync | No |
| MaxBufferSize | 0 |
| MaxLocksPerFile | 9500 |
| LockDelay | 100 |
| FlushTransactionTimeout | 500 |
| Threads | 3 |
By adjusting these settings, you can manipulate how the Jet engine operates in every program that uses these settings. The keys listed in Table 11.4 are briefly described in the text that follows.
Steps
Run the RegEdit application that is in the Windows directory on your machine. Locate the \HKEY_LOCAL_MACHINES\Software\Microsoft\Jet\3.5\Engines\Jet 3.5 section of the Registry, and you should see something similar to what's shown in Figure 11.15.
Figure 11.15. The
\HKEY_LOCAL_MACHINES\Software\
Microsoft\Jet\3.5\Engines\Jet 3.5 of the Windows Registry.
How It Works
The following is a list of the keys that make up the \HKEY_LOCAL_MACHINES\
Software\Microsoft\Jet\3.5\Engines\Jet 3.5 section of the Windows Registry and a
description of each.
PageTimeout
The PageTimeout key is used to indicate the time interval between when data that is not read-locked is placed in an internal cache and when that data is invalidated. This key is measured in milliseconds, with a default value of 5000 or 5 seconds.
FlushTransactionTimeout
The FlushTransactionTimeout key disables the ExclusiveAsyncDelay and SharedAsyncDelay keys with a value of 1. A value of 0 enables these keys. The FlushTransactionTimeout is the value that will start asynchronous writes only after the amount of time specified has expired and no pages have been added to the cache. An exception to this statement is if the cache exceeds the MaxBufferSize, the cache will start asynchronous writing even if the time has expired. For instance, the Microsoft Jet 3.51 database engine will wait 500 milliseconds during non-activity or until the cache size is exceeded before starting asynchronous writes.
LockDelay
The LockDelay key holds a value in milliseconds used to determine the time in between lock requests indicated by the LockRetry key. This key was added to prevent "bursting" (overloading of the network) that would occur with certain network operating systems.
MaxLocksPerFile
The MaxLocksPerFile key holds a value indicating the maximum number of Microsoft Jet transactions. If the locks in a transaction attempt to exceed the MaxLocksPerFile key value, the transaction is split into multiple parts and partially committed. This concept was conceived in order to prevent NetWare 3.1 server crashes when the specified NetWare lock limit was exceeded as well as to improve performance with both NetWare and NT.
LockRetry
The LockRetry key indicates the number of times to repeat attempts to access a locked page before returning a lock conflict message. The default value for the LockRetry key is 20.
RecycleLVs
The RecycleLVs key is used to indicate whether Microsoft Jet is to recycle long value pages. These include the Memo, Long Binary (OLE object), and Binary data types. With Microsoft Jet 3.0, if the RecycleLVs key was not set, these long value pages would be recycled when the last user closed the database. Microsoft Jet 3.51 will start to recycle most long value pages when the database is expanded--in other words, when groups of pages are added. When this feature is enabled, you will notice a performance drop when using long value data types. With Microsoft Access 97, this feature is automatically enabled and disabled.
MaxBufferSize
The MaxBufferSize key represents the size of the database engine internal cache. This value is represented in kilobytes. The MaxBufferSize key must be an integer greater than or equal to 512. The default value for the MaxBufferSize varies depending on the amount of RAM installed on the user's system. The formula for calculating the default is as follows:
((RAM - 12MB) / 4) + 512KB
Here, RAM is the amount of memory on the current system, measured in megabytes (MB). To set the value of the MaxBufferSize to the default, simply set the key to zero.
THREADS
The Threads key represents the number of background threads available to
the Microsoft Jet database engine. The default value for the Threads key
is 3.
UserCommitSync
The UserCommitSync key indicates whether the system should wait for a commit to finish. If the value is Yes, which is the default, the system will wait for a commit to finish. If the value is No, the system will perform the commit asynchronously.
ImplicitCommitSync
The ImplicitCommitSync key represents whether the system will wait for a commit to finish. A default value of No will instruct the system to continue without waiting for the commit to finish, whereas a value of Yes will cause the system to wait.
ExclusiveAsyncDelay
The ExclusiveAsyncDelay key specifies the amount of time (in milliseconds) an asynchronous flush of an exclusive database is to be deferred. The default value for this key is 2000 milliseconds (2 seconds).
SharedAsyncDelay
The SharedAsyncDelay key represents the amount of time (in milliseconds) to defer an asynchronous flush of a shared database. The default for the SharedAsyncDelay is 0.
Problem
My application needs to alter the way in which the ODBC engine is initiated in the Windows Registry in order to obtain better performance. I know I can edit the Windows Registry using RegEdit, but I don't know what to actually do. How do I tune the ODBC engine through the Windows Registry?
Technique
The ODBC engine keys are stored in the Windows Registry in the \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC section. Table 11.5 lists the available keys and their respective default values.
| KEY | Default |
| LoginTimeout | 20 |
| QueryTimeout | 60 |
| ConnectionTimeout | 600 |
| AsyncRetryInterval | 500 |
| AttachCaseSensitive | 0 |
| AttachableObjects | `TABLE', `VIEW', `SYSTEM TABLE', `ALIAS', `SYNONYM' |
| SnapshotOnly | 0 |
| TraceSQLMode | 0 |
| TraceODBCAPI | 0 |
| DisableAsync | 1 |
| JetTryAuth | 1 |
| PreparedInsert | 0 |
| PreparedUpdate | 0 |
| FastRequery | 0 |
By adjusting the values of any of these keys, you can affect the way any application that uses the ODBC jet engine accesses data. The following is a brief description of each key represented in Table 11.5.
Steps
Run the RegEdit application that is in the Windows directory on your machine. Locate the \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC section of the Registry, and you should see something similar to Figure 11.13.
How It Works
The following sections contain a list of the keys that make up the \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC section of the Windows Registry, and a description of each.
Figure 11.16. The
\HKEY_LOCAL_MACHINE\Software\
Microsoft\Jet\3.5\Engines\ODBC of the Windows Registry.
LoginTimeout
The LoginTimout key is used to store the maximum number of seconds a login attempt can take. After the specified time, timing out occurs with an error. The default for LoginTimout is 20 seconds.
QueryTimeout
The QueryTimeout key is used to store the maximum number of seconds the entire processing time can take to run a query before actually timing out. The default value for this key is 60 seconds. If the DisableAsync key is set to its default of 0, the QueryTimeout key is used to indicate the time in seconds waited for a response from the server between polls for the completion of a query.
ConnectionTimeout
The ConnectionTimeout key is used to store the maximum amount of seconds a cached connection may remain idle before timing out. The default value for the ConnectionTimeout key is 600 seconds (10 minutes).
AsyncRetryInterval
The AsyncRetryInverval is used to measure the time allotted between polls to determine whether the server has completed processing a query. The AsyncRetryInterval key is measured in milliseconds with a default of 500. This key is available only for asynchronous processing.
AttachCaseSensitive
The AttachCaseSensitive key is used to determine what type of matching is enabled for linking tables. A default value of 0 indicates that the linking process is not case sensitive whereas a value of 1 indicates that the tables must match according to case.
AttachableObjects
The AttachableObjects key holds a list of server object types that are allowed to be linked. The default value for this key is `TABLE', `VIEW', `SYSTEM TABLE', `ALIAS', `SYNONYM'.
SnapshotOnly
The SnapshotOnly key indicates whether Recordsets objects must be snapshots (default value of 0); they may also be dynasets (value of 1).
TraceSQLMode
The TraceSQLMode key is equivalent to the SQLTraceMode key. It indicates whether a trace of SQL statements will be recorded in SQLOUT.TXT that is sent to an ODBC data source. The default value for the TraceSQLMode key is 0, or no. A value of 1 indicates yes.
TraceODBCAPI
The TraceODBCAPI key indicates whether ODBC API calls are traced in the ODBCAPI.TXT file. A value of 0 indicates no, and 1 indicates yes. The default for this key is no.
DisableAsync
The DisableAsync key is an indicator of whether to force synchronous query execution. This key can either be set to its default of 1 for a force of synchronous query execution, or 0 for using asynchronous query execution if possible.
JetTryAuth
The JetTryAuth key indicates whether the Microsoft Access user name and password are to be used to log in to the server before prompting. The default value is yes (1). A value of 0 indicates no.
PreparedInsert
The PreparedInsert key is used to determine whether to use a prepared INSERT statement that inserts data in all columns. The default value for this key is 1, which indicates using a prepared INSERT statement. A value of 0 would indicate using a custom INSERT statement that inserts only non-null values. By using prepared INSERT statements, nulls can overwrite server defaults. In addition, triggers can execute on columns that were not inserted explicitly.
PreparedUpdate
The PreparedUpdate key is used to determine whether to use a prepared UPDATE statement that updates data in all the available columns. A value of 0 is the default and this indicates that a custom UPDATE statement is to be used and sets only columns that have changed. A value of 1 is used to use a prepared UPDATE statement.
FastRequery
The FastRequery key is used to indicate whether to use a prepared SELECT statement for parameterized queries. The default value is no, or 0. A value of 1 indicates yes.
© Copyright, Macmillan Computer Publishing. All rights reserved.