Visual Basic 6 Database How-To

Previous chapterNext chapterContents


- 11 -
The Windows Registry and State Information


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 SAFEGUARDS

Always 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, type

scanreq /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.

11.1 Enter and Retrieve Windows Registry Entries from Visual Basic

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.

11.2 Put Data Access-Related Information into an Application's Section of the Registry

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.

11.3 Determine Which Database and Report-Related Files Need to Be Distributed with My Applications

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.

11.Tune the Jet Database Engine Through Windows Registry Entries

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.

11.5 Tune the ODBC Engine Using Windows Registry Entries

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.

11.1 How do I...

Enter and retrieve Windows Registry entries from Visual Basic?

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.

1. Create a new project and name it RegistryEditor.vbp. Add the objects listed in Table 11.1 and edit their properties as shown. Save the form as frmRegistryEditor.frm.

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:"

2. Enter the declaration for the form-level variant variable used to store keys and settings from the Windows Registry:

Option Explicit
` form-level variable used to store keys and settings for
` desired application and section
Private m_vSettings As Variant


3. When the application begins, automatically populate the screen with the default information by calling the RepopulateKeys routine that we will enter later:

Private Sub Form_Load()
    ` initialize the application by populating the key list box
    RepopulateKeys
End Sub


4. Now enter the code for the lstKeys_Click event shown here. When the user changes the key to be displayed, the application will load the new corresponding setting and display it in the setting text box.

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


5. When the user changes the name of the application or the section
to find keys for, we want to repopulate the list box displaying
them. This is done with a call to the RepopulateKeys routine in the txtApplicationName_Change and txtSection_Change events, as shown here:

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


6. Enter the following code to end the application:

Private Sub cmdClose_Click()
    ` end the application
    Unload Me
End Sub


7. Now enter the code for the cmdSave_Click event, which uses the SaveSetting statement to save the information on the form to the Windows Registry:

Private Sub cmdSave_Click()
    ` save the selected key information from the desired
    ` information on the form
    SaveSetting txtApplicationName, _
                txtSection, _
                lstKeys.Text, _
                txtSetting
End Sub


8. Finally, enter the following code for the RepopulateKeys routine. When this code is called, the application attempts to populate the list box with all the available keys for the given application and section name. If this information does not correspond to a section in the Registry, an error occurs. The RepopulateKeys routine traps this error and gracefully exits the procedure, leaving the key list box empty and disabling any controls used to edit key settings.

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.

11.2 How do I...

Put data access-related information into an application's section of the Registry?

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.

Table 11.2. Objects and properties for the Set Options project.

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.

1. Create a new project and name it SetOptions.vbp. Add the controls and edit their properties as shown in Table 11.3 for the default form, Form1. Save the form as frmSetOptions.frm.

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:"

2. Now enter the following code in the declarations section of the project. The form-level Long variable is used to store the currently selected key from the combo box on the form. The other two declarations are constants and are used to represent the default application and section names used for this project.

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"


3. Now enter the Form_Load event code as shown next. This code calls the LoadJetRegistryInformation routine, which we will code in a separate module later, to retrieve all the keys for the specified application and section of the Windows Registry. The event then adds all the available parameters to the combo box control and selects the first one.

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


4. Now enter the code for the cboKeys_Click event, which is called every time the user selects a new key from the combo box. This event retrieves the current setting for the chosen key from the application's section of the Windows Registry. If there is no specified entry for the key in this section, the lDefaultSetting is used instead.

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


5. Enter the following code to end the application:

Private Sub cmdClose_Click()
    ` end the application
    Unload Me
End Sub


6. The following code is used to save the current key and setting combina-tion to the application's section of the Registry. This is done with the SaveSetting statement. In addition to the Registry entry, the SetOption method of the DBEngine object is called to temporarily change the setting of the desired parameter to the new value. If the user entered an incorrect data type for the key, an error is generated and the user is notified.

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


7. The following code simply deletes the key from the application's section in the Windows Registry and notifies the user of the success:

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


8. The second half of this project begins with adding a new module.
This can be done by choosing Project | Add Module from the Visual
Basic menu. Rename the module RegistryInformation and save it
as RegistryInformation.bas. The remaining code for this project
should be entered in this module.

9. In the RegistryInformation module, enter the first routine to be used, which is the LoadJetRegistryInformation as shown here. This routine loads all the settings and keys for the given application and section names. For each key specified in the corresponding section of the Registry, the SetOption method of the DBEngine object is called to temporarily change the value of the given parameter for the life of this application. If there are no settings for the given application and section names, an error is trapped and the routine exits gracefully.

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


10. Now enter the public function GetValueFromSetting, which accepts a variant as an argument and returns either a Long or String dependent upon the data type of the argument:

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


11. The following function returns the default setting for the specified key name. These defaults were obtained from the Visual Basic Books Online and can be changed to your desired settings.

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


12. Finally, enter the code for the public function GetParameterFromKey as shown here. This function returns the corresponding parameter enumeration value for a specified key.

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."

11.3 How do I...

Determine which database and report-related files need to be distributed with my applications?

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.

1. After choosing to create a package, choose a packaging script as shown in Figure 11.4.

Figure 11.4. Choosing a packaging script.

2. Click the Next button and you will see the panel shown in Figure 11.5. In this panel, you select the type of setup you wish to create.

Figure 11.5. Choosing a setup type.

3. After choosing the type of setup, choose the location to build the setup as shown in Figure 11.6.

Figure 11.6. Choosing a location to build the setup.

4. If your application uses DAO, you will choose drivers from the list shown in Figure 11.7.

5. Figure 11.8 shows a list of files that have been chosen to be included in your package. In this step, you can deselect or add files for your package.

Figure 11.7. Choosing a DAO driver.

Figure 11.8. Choosing files for the setup.

6. Next, choose whether you would like a single cab or multiple cab files for your installation as shown in Figure 11.9.

Figure 11.9. Choosing a distribution size.

7. Next, enter the name that should be shown when the setup program for your application is run. This is shown in Figure 11.10.

Figure 11.10. Choosing a name for the setup.

8. After entering a setup name for your application, click the Next button to get to the screen shown in Figure 11.11. This screen allows you to select the menu groups and items that should be created once your application is created.

Figure 11.11. Choosing menu groups and Items.

9. The next step, as shown in Figure 11.12, allows you to change the locations in which particular items in your package can be located.

Figure 11.12. Changing the location of setup components.

10. After you have set the correct location for the files in Figure 11.12, click the Next button to see Figure 11.13. This screen allows you to indicate which files in your package are considered shared files.

Figure 11.13. Choosing shared files.

11. After completing this, you will see the Finished panel as shown in Figure 11.14. Click Finish to complete the installation setup process.

12. Type a name for the installation script and click Save Script.

13. Click Finish to create your application package.

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.

11.4 How do I...

Tune the Jet database engine through Windows Registry entries?

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.

Table 11.4. The Jet\3.5\Engines\Jet 3.5 keys and default values.

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.

1. Choose a key in the section that you are now in. Choose Edit | Modify from the RegEdit menu.

2. Edit the value of the key that you selected. For a complete list of the available keys for this section, see the "How It Works" section of this
How-To.

3. Click OK to save your changes or Cancel to abort.

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.

11.5 How do I...

Tune the ODBC engine using Windows Registry entries?

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.

Table 11.5. The Jet\3.5\Engines\ODBC keys and 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.

1. Choose a key in the section that you are now in. Choose Edit | Modify from the RegEdit menu.

2. Edit the value of the key that you have selected. For a complete list of the available keys for this section, see the "How It Works" section of this How-To.

3. Click OK to save your changes or Cancel to abort.

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.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.