Executing a Database Query | Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))

< Day Day Up >

Clicking the Run Query button on the Excel2k3 VBA Query command bar runs the query currently selected in the ComboBox on the command bar.

Getting the Information to Run the Query

In the ThisWorkbook module, the RunDatabaseQuery routine, shown here, builds a connection string using the BuildConnectionString function and gets the query string from the combo box on the command bar using the GetDBQuery routine. The Trim function is used to delete any unnecessary blanks as the beginning and the end of the strings.

For more information on using text functions to clean up your data, see Chapter 9, 'Manipulating Data with VBA'.

Sub RunDatabaseQuery()
 
Dim c As String
Dim q As String
 
c = Trim(BuildConnectionString)
q = Trim(GetDBQuery)
 
If Len(c) = 0 Then
   DBInfo.Show vbModal
   
ElseIf Len(q) = 0 Then
    DBQuery.Show vbModal
   
Else
    RunQuery c, q
      
End If
 
End Sub

The length of each temporary variable holding the connection string and the query is verified to be non-zero. If the length is zero, the appropriate form is displayed to the user to fill in the necessary information. When the form is closed, the user must hit the Run Query button to try the query again.

Assuming that there is information in the connection string and the query string, the RunQuery routine is called to execute the query with both the connection string and the query string passed as parameters.

Building a Connection String

The following BuildConnectionString routine creates a connection string from information previously stored in the Windows registry. The routine, which is located in the ThisWorkbook module, begins by declaring a number of temporary variables that will hold the information extracted from the registry, along with another temporary variable c that will hold final return value from the function.

Function BuildConnectionString() As String
 
Dim c As String
 
Dim DBName As String
Dim DBPassword As String
Dim DBPath As String
Dim DBServer As String
Dim DBType As Long
Dim DBUserId As String
Dim DBWindowsAuth As Boolean
 
c = ""
 
DBType = GetSetting("Excel2k3 VBA", "Query", "DBType", 0)
 
Select Case DBType
Case 0
   DBPath = GetRegistryValue("DBPath", "<enter path to database file>")
 
    If Len(DBPath) <> 0 Then
        c = "Provider=Microsoft.Jet.OLEDB.4.0"
        c = c & ";Data Source=" & DBPath
        
    End If
    
Case 1
    DBWindowsAuth = GetSetting("Excel2k3 VBA", "Query", _
        "DBWindowsAuth", True)
 
    DBName = GetRegistryValue("DBName", "<enter database name>")
    DBPassword = GetRegistryValue("DBPassword", "<enter password>")
    DBServer = GetRegistryValue("DBServer", "<enter database server>")
    DBUserId = GetRegistryValue("DBUserId", "<enter userid>")
 
    If Len(DBServer) = 0 Then
        DBServer = "localhost"
        
    End If
    
    If Len(DBName) <> 0 Then
        c = "Provider=SQLOLEDB.1"
        c = c & ";Data Source=" & DBServer
        c = c & ";Initial Catalog=" & DBName
    
        If DBWindowsAuth Then
            c = c & ";Integrated Security=SSPI"
        
        ElseIf Len(DBUserId) <> 0 And Len(DBPassword) <> 0 Then
            c = c & ";User line-height:1">            c = c & ";Password=" & DBPassword
        
        Else
            c = ""
 
        End If
 
    End If
    
Case 2
    c = GetRegistryValue("ConnectionString", "<enter connection string>")
 
End Select
 
BuildConnectionString = c
 
End Function

After setting c to an empty string, the routine grabs the type of database from the registry and stores it in DBType. This variable is then incorporated into a Select Case statement, which builds different connection strings depending on the type of database being used.

If an Access (Jet) database is being used, the DBPath value is extracted from the Windows registry using the GetRegistryValue helper function. This function takes two parameters, the key and the default value. If the value extracted from the registry is different than the default value, an empty string will be returned.

If GetRegistryValue returned an empty string (len = 0), c remains an empty string. Otherwise, the appropriate connection string is created for an Access database using the value extracted from the registry.

The same approach is used for SQL Server databases (DBType = 1). The value for DBWindowsAuth is extracted directly from the registry because its default value is not a string, whereas the values for DBName, DBPassword, DBServer, and DBUserId are extracted from the registry using the GetRegistryValue routine.

Unlike Access connection strings, the routine attempts to substitute intelligent values in case the user hasn't supplied a default value. For instance, if the length of DBServer is zero, the program assumes that the user is running the query against the local computer. If the length of DBName isn't zero, the program attempts to build a partial connection string using DBName and DBServer. Then, if Windows authentication is used, Integrated Security=SSPI is appended to the connection string and the information in DBUserId and DBPassword is ignored.

If DBWindowsAuth is False, the DBUserId and DBPassword are checked to see if they contain a value. If both contain information, these fields are appended to the connection string. If one of these fields doesn't have any information, c is set to the empty string, destroying the partial connection string that already exists.

If the user selected Advanced on the DBInfo form, the ConnectionString value is extracted from the registry using the GetRegistryValue helper function. No further checking is required here because GetRegistryValue ensures that an empty string is returned if the default value is retrieved from the registry.

Finally, the routine ends by returning the temporary variable c as the value of the function.

The GetRegistryValue helper function calls the GetSetting function to retrieve the desired key from the registry. If the return value is the same as the supplied default value, the return value is set to the empty string.

Function GetRegistryValue(key As String, default As String) As String
 
Dim r As String
 
r = GetSetting("Excel2k3 VBA", "Query", key, default)
 
If r = default Then
    r = ""
   
End If
 
GetRegistryValue = r
 
End Function

Getting the Query

Getting the query string from the combo box on the command bar follows the same basic approach discussed elsewhere in this chapter. After locating the appropriate command bar, the FindControl method is used to search for the specific control containing the combo box. The following function is also located in the ThisWorkbook module:

Function GetDBQuery() As String
 
Dim c As CommandBar
Dim cc As CommandBarComboBox
 
On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
 
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
    GetDBQuery = cc.Text
 
ElseIf cc.Text = "<enter a query>" Then
    GetDBQuery = ""
   
Else
    GetDBQuery = ""
 
End If
 
End Function

The main benefit of this routine is that the query string is compared to the default value that was loaded when the program began. If it is, the function will return an empty string, indicating that the user should be prompted to enter a query.

Running a Query

Once the user has supplied values for the connection string and the query string, which have passed some simple edit checks, the RunQuery routine, located in the ThisWorkbook module, is called to get the information from the database.

Sub RunQuery(c As String, q As String)
 
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
 
On Error Resume Next
   
Set cn = New ADODB.Connection
cn.ConnectionString = c
cn.Open
If Err.Number <> 0 Then
    MsgBox "Connection error: " & Err.Description
    Exit Sub
   
End If
 
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = q
cmd.CommandType = adCmdText
 
Set rs = New ADODB.Recordset
Set rs.Source = cmd
 
cn.Errors.Clear
rs.Open
 
If Err.Number = 0 Then
    CopyRows rs
   
Else
    MsgBox "Query error: " & Err.Description
   
End If
 
rs.Close
cn.Close
 
End Sub

To execute the query, this routine uses three ActiveX Data Object (ADO) database objects: a Connection object, a Command object, and a Recordset object. Each is declared at the start of this routine. Next, error checking is disabled by using the On Error Resume Next statement because the routine checks for errors after any critical statement.

The first step in retrieving rows from a database is to establish a connection to the database. A new instance of the ADODB.Connection object is created, and the connection string value that's passed to this routine is assigned to the Connection object's ConnectionString property. After instantiating the Connection object, the Open method is used to open a connection to the database.

Tip

Out with the New
Never use the New keyword when defining an object using a Dim, a Private, or a Public statement. Visual Basic for Applications (VBA) includes extra code around each object reference to determine whether the object has been instantiated. If the object hasn't been instantiated, the code will automatically create a new instance of the object for you. Although this extra code doesn't add a lot of overhead, you will be better off controlling exactly when a new object is instantiated.

If there was a problem opening the connection, an error message is displayed to the user and the Exit Sub statement is used to leave the subroutine with any further processing.

Next a new instance of the ADODB.Command object is created. The newly opened Connection object is assigned to the ActiveConnection property, and the query is assigned to the CommandText property. Finally, the CommandType property is set to adCmdText, meaning that the Command object contains an SQL statement.

A new ADODB.Recordset object is created, and the Source property is set to the Command object that was just initialized. The Connection object's Errors collection is explicitly cleared, and then the Recordset object's Open method is called.

Any errors that occur while opening the Recordset are trapped, and an error message is displayed to the user. Otherwise, the CopyRows subroutine is called to copy the rows from the Recordset object to the current worksheet.

Finally, the Recordset object and the Connection object are closed (in that order). These steps release any resources held by those objects back to the operating system.

Tip

Close Your Connections
For the best database server performance, always minimize the amount of time that a connection is open. Closing unneeded connections reduces the resources required to run the database server, which in turn lets the database server handle more connections and perform more work.

Copying Rows

Once you have a Recordset that contains rows from the database, the only step left is to copy the rows to your worksheet one row at a time. The CopyRows routine in the ThisWorkbook module relies on three local variables: i and j, which contain pointers to the current cell row and the column on the worksheet, and f, which contains a field from the Recordset object.

Sub CopyRows(rs As ADODB.Recordset)
 
Dim i As Long
Dim j As Long
Dim f As ADODB.Field
 
Cells.Select
Selection.ClearContents
 
i = 1
Do While Not rs.EOF
    j = 1
    For Each f In rs.Fields
        Cells(i, j) = f.Value
        j = j + 1
      
    Next f
   
    rs.MoveNext
    i = i + 1
 
Loop
 
End Sub

After the space for the variables has been reserved, the entire worksheet is cleared by selecting all the cells on the worksheet (Cells.Select) and then clearing the selection with the ClearContents method. Then the variable i is set to 1, which points at the first row in the worksheet.

Next a loop is set up that iterates through each row in the Recordset object. This loop continues until the EOF method returns True, meaning that the current record pointer has moved beyond the last row contained in the Recordset.

Inside the loop, j is set to 1, which points to the first column in the worksheet. A For…Each loop is used with the variable f to retrieve each Field object from the recordset's Fields collection. The value of the field is copied directly from the Value property to the appropriate cell pointed by i and j. After a field is copied, j is incremented to point to the next column.

Once all the fields for a particular row have been copied, the MoveNext method is used to reposition the current record pointer to the next record. Also i is incremented to point to the new row in the worksheet.

< Day Day Up >