Connecting to a Database

---

To execute a database query, you need to create a connection to the database. However, because you need different connection information for each database, it makes sense to use a user form to collect this information. (See Figure 24-3.)

Figure 24-3: The user supplies the database connection information through the Database Properties dialog box.

The program stores the connection information in the Microsoft Windows registry so the program will remember the last value that the user selected.

Because each database system needs different connection information, a hidden MultiPage control contains a unique Page object for each supported database. A ComboBox control at the top of the user form selects which page is visible. This arrangement is more clearly illustrated in the Visual Basic Editor, as shown in Figure 24-4.

Figure 24-4: Each page in the MultiPage control contains connection information unique to each particular database.

Tip 

Keep an Eye on Your Tabs
When you are developing applications with the MultiPage control, you might want to leave the tabs visible in design mode even though you want to hide them from the user. Doing so lets you quickly switch to the tab you want to modify while in development mode. Then, when you load the form, just set the Style property to: MultiPage1.Style = fmTabStyleNone.

Initializing the DBInfo UserForm

When the DBInfo form, which generates the Database Properties dialog box, is loaded, the UserForm_Initialize event shown in the following code is fired. The first step is to populate the Provider ComboBox control with the list of supported databases. For this example, only the Microsoft Access and SQL Server databases have their own pages; however, an Advanced page was added that allows the user to input any connection string value.

Private Sub UserForm_Initialize()
 
Provider.AddItem "Access (Jet)"
Provider.AddItem "SQL Server"
Provider.AddItem "Advanced"
 
DBProperties.Style = fmTabStyleNone
 
DBName.Text = GetSetting("Excel2k3 VBA", "Query", "DBName", _
    "<enter database name>")
DBPassword.Text = GetSetting("Excel2k3 VBA", "Query", "DBPassword", _
    "<enter password>")
DBPath.Text = GetSetting("Excel2k3 VBA", "Query", "DBPath", _
    "<enter path to database file>")
DBServer.Text = GetSetting("Excel2k3 VBA", "Query", "DBServer", _
    "<enter database server>")
DBWindowsAuth.Value = GetSetting("Excel2k3 VBA", "Query", _
    "DBWindowsAuth", True)
DBUserId.Text = GetSetting("Excel2k3 VBA", "Query", "DBUserId", _
    "<enter userid>")
ConnectionString.Text = GetSetting("Excel2k3 VBA", "Query", _
    "ConnectionString", "<enter connection string>")
 
Provider.ListIndex = GetSetting("Excel2k3 VBA", "Query", "DBType", 0)
 
End Sub

The tabs on the MultiPage control are hidden by setting the Style property to fmTabStyleNone.

The GetSetting function is used to extract the values for each field on the form from the registry. The GetSetting function takes four parameters. The first three parameters represent a key that is used to identify the value, while the fourth parameter provides a default value in case the value isn't currently stored in the registry.

At the bottom of the listing, the last GetSetting function is used to choose the selected database and set the ComboBox control accordingly. Note that setting the ListIndex property will also fire the Change event associated with the control.

Changing Database Providers

The Provider_Change event in the DBInfo user form is fired any time the user selects a new database from the drop-down box or the ListIndex property changes. All this routine does is to select the appropriate page to display in the MultiPage control by setting its Value property like this:

Private Sub Provider_Change
 
DBProperties.Value = Provider.ListIndex
 
End Sub 

Selecting Windows Authentication

A check box on the SQL Server page allows the user to select Windows authentication. When the Windows authentication information is used, there is no need to display the userid and passwords fields to the user.

The following code handles the Click associated with the check box and hides the userid and password fields when the value of the control is True and displays them when the value is False.

Private Sub DBWindowsAuth_Click()
 
If DBWindowsAuth.Value Then
    DBUserId.Visible = False
    DBPassword.Visible = False
    DBUserIdLabel.Visible = False
    DBPasswordLabel.Visible = False
    
Else
    DBUserId.Visible = True
    DBPassword.Visible = True
    DBUserIdLabel.Visible = True
    DBPasswordLabel.Visible = True
    
End If
 
End Sub

Saving the Database Info

When the user clicks the OK button, the code that follows is executed. The procedure calls the SaveSetting routine to save the values input by the user into the Windows registry.

Private Sub CommandButton1_Click()
 
SaveSetting "Excel2k3 VBA", "Query", "DBName", DBName.Text
SaveSetting "Excel2k3 VBA", "Query", "DBPassword", DBPassword.Text
SaveSetting "Excel2k3 VBA", "Query", "DBPath", DBPath.Text
SaveSetting "Excel2k3 VBA", "Query", "DBServer", DBServer.Text
SaveSetting "Excel2k3 VBA", "Query", "DBWindowsAuth", DBWindowsAuth.Value
SaveSetting "Excel2k3 VBA", "Query", "DBUserId", DBUserId.Text
SaveSetting "Excel2k3 VBA", "Query", "ConnectionString", _
    ConnectionString.Text
SaveSetting "Excel2k3 VBA", "Query", "DBType", Provider.ListIndex
 
Unload Me
 
End Sub

Once the values have been saved to the Windows registry, the Unload routine is called to close the current form. Notice that the Cancel button's Click event merely calls the Unload routine to unload the form. Any changes made to the fields on the form are discarded.

Tip 

I Am Me
UserForm modules are like regular class modules, so you can refer to the current instance of the object by using the keyword Me. This can be extremely useful with some statements, such as the Unload statement, where you have to supply an object reference to the UserForm object you wish to unload.

When the user clicks the Cancel button, the CommandButton2 event is called. Because the user has chosen not to change the connection information stored in the registry, the only step necessary is to unload the current form.

Private Sub CommandButton2_Click()
 
Unload Me
 
End Sub

---