Query Tables and Parameter Queries

It is often useful to base your QueryTable on a parameter query rather than a fixed SQL statement. This allows you to determine which subset of the data you display, and even allows you to provide your users with the ability to modify the parameters when the QueryTable is refreshed.

One notable quirk of parameter queries is that QueryTables will not support them if you use the OLE DB provider used in the previous two sections. Instead, you must switch to the ODBC driver. This is a simple matter of changing the first argument of the connection string from OLEDB to ODBC and providing ODBC connection information:

Sub CreateQueryTableWithParameters()

Dim qryTable As QueryTable Dim rngDestination As Range

Dim strConnection As String Dim strSQL As String

' Define the connection string and destination range. strConnection = "ODBC;DSN=MS Access Database;" & _

"DBQ=C:\Files\Northwind 2007.accdb;" Set rngDestination = Sheet3.Range("A1")

' Define the connection string and destination range. strConnection = "ODBC;DSN=MS Access Database;" & _

"DBQ=C:\Files\Northwind 2007.accdb;" Set rngDestination = Sheet3.Range("A1")

' Create

a parameter query.

strSQL =

"SELECT [Product Name], ' FROM Products" & _

[List

Price],

, [Quantity Per Unit]'

' & _

' WHERE Category = ?;"

' This

is the

parameter.

' Create the QueryTable.

Set qryTable = Sheet3.QueryTables.Add(strConnection, rngDestination)

' Create the QueryTable.

Set qryTable = Sheet3.QueryTables.Add(strConnection, rngDestination)

Populate the QueryTable.

qryTable.

.CommandText

= strSQL

qryTable.

. CommandType

= xlCmdSql

qryTable.Refresh False End Sub qryTable.Refresh False End Sub

In this example, you set the CommandText property of the QueryTable to a SQL statement that selects the Product Name, List Price, and Quantity Per Unit from the Products table of the Northwind 2007 database. The WHERE clause of the SQL statement contains a parameter. A parameter is created by placing a question mark character (?) where an actual value would normally go. A SQL statement may contain one or more parameters at any point where a value from the database would normally go. You cannot use parameters for table names, column names, or SQL keywords.

When this code is executed, VBA automatically recognizes that the SQL contains a parameter, and it will prompt you to enter a value for the parameter by displaying the dialog box shown in Figure 21-9.

Enter Parameter Value

Parameter 1

I I Use this value/reference for future refreshes

^Refresh automatically when cell value changes

Figure 21-9

For each parameter in the CommandText property of the QueryTable, VBA creates a Parameter object that it adds to the QueryTable.Parameters collection. You can gain some additional control over how parameters are handled by creating these Parameter objects and adding them to the Parameters collection yourself.

You create parameters using the QueryTable.Parameters.Add method. This method adds a Parameter object to the QueryTable and returns a reference to it. You can then use that reference to modify the behavior of the Parameter object. The next example uses this capability to prepopulate the parameter with an initial value when the QueryTable is first created, and then to have it prompt the user for a new value each time the QueryTable is refreshed after that.

If you create your own Parameter objects, they must be created in the same order that the corresponding parameters appear in the SQL statement.

Sub CreateQueryTableWithParameters()

Dim objParam As Parameter Dim qryTable As QueryTable Dim rngDestination As Range Dim strConnection As String Dim strSQL As String

' Define the connection string and destination range. strConnection = "ODBC;DSN=MS Access Database;" & _

"DBQ=C:\Files\Northwind 2007.accdb;" Set rngDestination = Sheet3.Range("A1")

' Create a parameter query.

strSQL = "SELECT [Product Name], [List Price], [Quantity Per Unit]" & " FROM Products" & _

" WHERE Category = ?;" ' This is the parameter.

' Create the QueryTable. Set qryTable = Sheet3.QueryTables.Add(strConnection, rngDestination)

' Create the parameter and give it an initial value. Set objParam = qryTable.Parameters.Add("Select Category", xlParamTypeVarChar) objParam.SetParam xlConstant, "Beverages"

' Populate the QueryTable. qryTable.CommandText = strSQL qryTable.CommandType = xlCmdSql qryTable.Refresh False

' Configure the parameter to prompt the user for a ' new value the next time the QueryTable is refreshed. objParam.SetParam xlPrompt, "Select Category"

End Sub

You can also configure a Parameter to retrieve its value from a cell on the worksheet, and to automatically refresh the QueryTable whenever the value in that cell changes. This allows you to, for example, provide the users with a data validation list of choices for the parameter, rather than forcing them to remember all the potentially valid values. This feature is also very useful when your query requires multiple parameters. Rather than having to contend with a prompt dialog for every parameter, the user can simply make the appropriate entries in the cells that specify the parameters.

If you replace the final line of code in the preceding procedure with the following two lines of code, the Parameter will retrieve its value from cell F1 and refresh the QueryTable automatically whenever the value in cell F1 changes:

' Configure the parameter to retrieve its value from cell F1 ' and refresh the QueryTable whenever that value changes. objParam.SetParam xlRange, Sheet3.Range("F1") objParam.RefreshOnChange = True

In the previous examples, you used all three options provided by the Parameter.SetParam method. The first argument of the SetParam method specifies the option to be used, and the second argument provides additional data for that option. The three SetParam options are as follows:

□ xlConstant — Tells the parameter to use the value specified by the second argument. The second argument can be anything that returns a value of the correct data type for the parameter, including a variable, a cell reference, or a hard-coded value.

□ xlPrompt — Tells the parameter to prompt the user for its value. The second argument is the prompt string that will appear in the dialog box.

□ xlRange — Tells the parameter to retrieve its value from the cell specified by the second argument. The second argument must be a valid Range object.

Was this article helpful?

+1 0
The Accidental Blogging Millionaires

The Accidental Blogging Millionaires

Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.

Get My Free Ebook


Responses

  • Severi Saraste
    How to refresh querytable?
    9 years ago

Post a comment