I have a guest blogger today. Ben Clothier has agreed to let me post this article he recently wrote. Ben is an MS Access MVP as well as being a some-time colleague and full-time friend.
You'll find more of his excellent posts at Utter Access and at Access Word Forums.
Ben writes on the use of Parameterized Queries to achieve the results I've recently described with other techniques involving dynamic SQL. I'll leave it to you to compare the two approaches and figure out how best to adapt each to your needs. I'm aware of advantages in both approaches, as well as limitations.
============================================
Assumptions:
We have an employee table from which we want to retrieve a personnel record derived from a given Primary key. Access provides us with a very convenient way of parameterizing the query by supporting references to controls on forms such as this:
SELECT *
FROM tblEmployees
WHERE empID = Forms!frmEmployees!empID;
This works out very well when the query is intended to live and die with this specific form and this approach is very commonly used in defining a recordsource for a report.
But references are only good when there's actually something to reference. Thus, the above query will definitely fail should the form frmEmployees not be open at time the query is called. We also run into problems of reusability. Surely the need to reference a employee record based on the primary key can be used in more than one places. Should we then write each query for each possible form reference? That would be a maintenance problem. Thus we may want to consider using parameters in more general forms.
Consider this syntax, found in a Parameterized Query:
PARAMETERS lempID INT;
SELECT *
FROM tblEmployees
WHERE empID = [lempID];
Now the query is disassociated from any references to any other objects and can run on its own two feet. Of course when you run it, Access will, as usual, present a little parameter input dialog. Therefore, sometimes people like to do something like this:
PARAMETERS [Enter an Employee ID];
This will work okay for simple use, but sometime we don't want to enter the paramter via a simple dialog; perhaps we already have a combobox on a form and want to make use of it. No problem; we can use VBA to fill in this for us.
With CurrentDb.QueryDefs("qryEmployee")
.Parameters("lempID") = Me.MyComboBox
Set frm.Recordset = .OpenRecordset(dbOpenDynaset)
End With
With this code snippet we can make use of a complete pop up form or subform that is filtered by the selection made in MyCombobox.
We certainly can make use of the techniques for action queries:
PARAMETERS lempID INT;
DELETE FROM tblEmployees
WHERE empID = [lempID];
With CurrentDb.QueryDefs("qdlEmployee")
.Parameters("lempID") = Me.MyCombobox
.Execute dbFailOnError
End With
As can be seen, using parameters is much more straightforward with any action queries and provides an convenient means for reusing the same query for several different parameters that may be chosen by user at runtime.
String Sanitizing
One more benefit is that there's no need to perform any string sanitizing as is required when writing a dynamic query. Suppose, for example, an user entered this value to pass into the query as a parameter:
Mike's and Al's "Good" Cars
In a dynamic query, we would be facing lot of headache in ensuring that the quotes are correctly delimited and escaped, with one of several variants:
'Mike''s and Al''s "Good Cars'
"Mike's And Al's ""Good Cars"""
Chr(34) & "Mike's And Al's " & Chr(34) & "Good" & Chr(34) & " Cars"
But in the code based on a parameterized query:
Parameters("sParam") = Me.MyTextbox
it's always parsed correctly with no help from us!
Maintainability
One more technique. This isn't strictly related to the parameter query but pertains to maintainability. I like to put parameter queries in a function or property procedure....
Something like this:
Property GetPersonnelRecord(EmpID As Long) As DAO.Recordset
Static q As DAO.QueryDef
If q Is Nothing Then
Set q = CurrentDb.QueryDefs("qryEmployees")
End If
With q
.Parameters("EmpID") = EmpID
Set GetPersonnelRecord = .OpenRecordset (dbOpenSnapshot)
End With
End Property
The reason for doing it this way is to provide an interface for VBA into individual queries and make the code maintainable especially in regards to queries that are reused in many different places.
But here's a more important reason. Suppose that after years of doing the above, we had to change the requirement and partition the employee table between active employees and past employees due to data storage restrictions and performance reasons. With a consistent interface as given above, it's a snap to add a 2nd parameter to identify whether the employee is active or not, update the property procedure to enter the 2nd parameter. Hit the compile and the compiler will find missing arguments in all places, which is far more accurate than using Find & Replace to track down all uses of the same queries all over in the code base.
Thus, parameter queries have lot to offer in terms of code maintainability and documentation in exchange for a little investment in some extra VBA coding that otherwise wouldn't be necessary with control references or dynamic queries.
Limitations
Of course, it goes without saying that they do have their limitations. Many people working on various RDBMS have had tripped over this innocuous looking parameter (adapted into Jet SQL):
PARAMETERS MyParam TEXT;
SELECT *
FROM aTable
WHERE SomeData IN([MyParam]);
The query will compile and save OK without any errors and you can even run it. If the first parameter you put in was just '''Apple''' (of which does exist in the table), it'd work as expected. Great. Now try this
'''Apple'', ''Pear'', ''Grapes'''
and you get nothing in return. What happened? Well, IN() actually does not mean 'parse this string at the runtime', but rather a compile shortcut that translates into X = A OR X = B OR X = C and thus must be hard-coded.
In Jet SQL, a dynamic query is probably most appropriate for this type of parameter.
Optional Parameters
Another situation where it becomes an problem is when we desire optional parameters. Some developers may try to work around it like this:
WHERE SomeData = [MyParam] OR SomeData IS NULL
which unfortunately negates any index optimization Jet can perform. Here, dynamic queries makes more sense as it's cheaper to create a new execution plan and use index than to suffer the performance penalty.
