UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Parameter Query    
Parameter Query

Parameter queries are a class of queries that allows for variables to be used inside the queries and thus provide a means of dynamic execution. Sometime they make for convenient alternatives to Dynamic SQL. There are three different examples of parameter queries:

Contents

Variants

Using the default input box:

CODE
SELECT * FROM tblCustomers WHERE Last_Name = [Enter a customer last name];

Using the default input box but explicitly declare the parameters:

CODE
PARAMETERS LastName TEXT (255);
SELECT * FROM tblCustomers WHERE Last_Name = [LastName];

Using form control references:

CODE
SELECT * FROM tblCustomers WHERE Last_Name = [Forms]![frmSearch]![txtLastName];

There are three general issues with using the default input box:

1) There is no validation or control over what user may input and not input. 2) If there is no explicit declaration, Access tries to guess the data types... sometime it does it wrong and causes problems. 3) There is no means of entering "all" or "any" - leaving it blank will return a Null which will then result in zero records.

For those reasons, it is generally not recommended to rely upon the default input box though it is understood that they can be convenient in some cases since this approach does not require any coding at all whereas alternatives such as using Dynamic SQL requires not just VBA but also careful manipulations.

Performance

If performance is important, it may be necessary to prefer dynamic SQL even for where parameter queries are suitable due to how queries are optimized. Generally, Access creates a plan for a new query upon saving. When a query contains a parameter, then Access cannot know what value the parameter may contain and has to make a "good guess". Depending on which actual values are later supplied, it may be okay or poor, resulting in sub-optimal performance. In contrast, dynamic SQL sidesteps this because the "parameters" are hard-coded into the temporary string and thus a new plan is compiled with that value, guaranteeing optimal execution plan. Since compiling a new plan at runtime is very fast, it can be the case that dynamic SQL will outperform parameter queries.

Optional Parameters

In some cases there are needs to run queries either with a parameter given, or without. One possible approach is to do something like this:

CODE
WHERE (Last_Name = [LastName] OR [LastName] IS NULL);

Unfortunately, this will negate any opportunity to optimize the query using the index upon last name and thus cause it to perform less than optimally. If it is crucial that the query perform quickly, it may be desirable to use VBA to switch between paramterized query and unfiltered query. An example of this would be:

CODE
Private Sub txtMyLastNameCriteriaTextbox_AfterUpdate()

If Len(Me.txtMyLastNameCriteriaTextbox) Then
  DoCmd.OpenQuery "qrySelectCustombersByLastName"
Else
  DoCmd.OpenQuery "qrySelectAllCustomers"
End If

End Sub

Where the SQL for qrySelectCustomersByLastName is:

CODE
SELECT * FROM tblCustomers WHERE Last_Name = [Forms]![frmSearch]![txtMyLastNameCriteriaTextbox];

and the SQL for qrySelectAllCustomers is:

CODE
SELECT * FROM tblCustomers;

There are also similar approaches such as using the WhereCondition parameters for opening a form which requires even less work without sacrificing performance.

Using Parameter Query in VBA

As stated above, using the default input box does not provide much control or flexibility. One alternative was to use form control reference. However, the problem with form control reference is that for the query to run, the form must be opened. Generally that is not a problem as several times, the queries are meant to live and die by the same form but occasionally, we may have queries we want to parameterize but without tying it to a specific form and keep it working for any context. One way to supply parameters without falling to the input box is to use VBA to input the parameters. Here's an example:

CODE
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("qrySelectCustomersByLastName")

With qdf
  .Parameters("LastName") = "Smith"
  Set rs = .OpenRecordset
End With

'Use the recordset as desired...

With qrySelectCustomersByLastName SQL being:

CODE
PARAMETERS LastName TEXT(255);
SELECT * FROM tblCustomers WHERE Last_Name = [LastName];

For more information on using recordset in VBA, consult Recordsets for Beginners. When all parameters are supplied and a recordset is opened, there will be no input box, and the recordset can be then assigned to a form if so desired. One major advantage of this approach is that there is no messy concatenating that would be otherwise necessary to properly escape strings for dynamic SQL, especially if we want to search for people with last name like O'Reilly. In fact, we can do this:

CODE
With qdf
  .Parameters("LastName") = Me.txtLastName
...

and the text user enters will always be correctly escaped without any burden placed upon on our shoulders as would be the case with dynamic SQL. When using this approach, it is generally strongly encouraged that parameters be explicitly declared as to define the data type at compile time rather than at runtime and risking getting it wrong.

Parameter Limitations

One common topic that crops up is when someone want to do this:

CODE
SELECT * FROM tblCustomers WHERE Last_Name IN ("Smith", "John", "Doe");

and want to make it into parameter query:

CODE
PARAMETERS LastNames TEXT (255);
SELECT * FROM tblCustomers WHERE Last_Name IN ([LastNames]);

This will work if we input a value of say, "John" for the LastNames, but fail if we input values such as "John, Smith" or even "'John', 'Smith'". The fundamental misconception being made here is that "IN([LastNames])" does not mean "parse whatever string I give to you at runtime and get a list of names from that string." Rather, IN() is a compile-time shortcut transforming the expression into a longer ORs like this:

CODE
SELECT * FROM tblCustomers WHERE Last_Name = "Smith" OR Last_Name = "John" OR Last_Name = "Doe";

So, obviously, the IN()'s list has to be hard-coded at compile-time and thus cannot be parameterized correctly. For situations where we need dynamic lists, it may be desirable to use dynamic SQL instead.

Another limitation is that in general, parameters cannot subsitute an object reference so this will not succeed:

CODE
PARAMETERS TableName TEXT(255);
SELECT * FROM [TableName];

This will run but with erroneous results:

CODE
PARAMETERS ColumnName TEXT(255), ColumnValue TEXT(255);
SELECT * FROM tblCustomers WHERE [ColumnName] = [ColumnValue];

This is same thing as saying:

CODE
SELECT * FROM tblCustomers WHERE "LastName" = "Smith";

which will always return zero results because the string "LastName" could never be equal to "Smith" even though it was meant that LastName be a column reference rather than a string. So thus, we cannot use parameters to substitute for any columns, tables or any other object references - only as a placeholder for values.

Choosing between Parameter Queries and Dynamic SQL

In general, parameter queries are easier to create and maintain, potentially at cost to the performance. Because parameter queries abstract away all the messy concatenating, it does make for quite easy set-up and one may find the performance to be acceptable to justify continuing using it even if it may not necessarily be optimal. Another nice aspect is that because parameter queries are an object in its right, the maintenance is simplified as we only deal with an object whereas dynamic SQL may be basically anywhere in VBA code and there's a risk that there may be more than one instances of similar dynamic SQL being used in different places, complicating the maintenance of the application. But whenever we need a flexible SQL with different object references or even just most optimized performance, especially for large queries, we may find dynamic SQL to be worth its hassles.

A happy middle ground may be found in creating a set of functions to provide a central repository for dynamic SQL so we can have one place where dynamic SQL is used and be assured that the application's codebase will refer to the same place. An example would be Query Interface

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 10,257 times.  This page was last modified 01:40, 10 February 2012 by Jack Leach. Contributions by BananaRepublic  Disclaimers