Microsoft Office Tutorials and References
In Depth Information
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 = ""
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, indiĀ­
cating 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
If Err.Number <> 0 Then
Search JabSto ::

Custom Search