And Now For Something (Almost) Completely Different

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.

The Cheese Stands Alone

In a previous blog I talked about the usefulness of dynamically parameterized queries in generating small recordsets for forms and reports, a technique that is particularly good for client-server applications with potentially large recordsets in the back end.

One of the additional benefits I found in working with this technique is that I can construct forms that work equally well as subforms and as stand-alone forms, even though their recordsource is a parameterized query that would traditionally have had to be written like this:

Data Macros. How Hard Could It Be?

The last week has been a lot of fun. Like many of the people who have access to the Technical Preview (TP) release for Access 2010, I lost a few hours of sleep the first two or three days of the week.

However, over the last couple of days, it's begun to feel more like "business as usual". And I think that's a good thing. A very good thing.

Blue Skies Smiling at Me

...Nothing but blue skies from now on. Would that it were true.

Another blue weekend and not much Access work got done over here at GPG Manor.

It's funny how languages work (I'm thinking of English at the moment, of course, but every language has it's own quirks.)

For example, I was listening to Willie Nelson a bit ago

"Inappropriate Language" in MS Access 2007 Templates

Okay, so I've been trying to figure out how to submit additional Access 2007 templates to the MS Access Template Site. For some time now, the MS Access team has been encouraging MVP's to participate in the template program.

Well, I tried, I really tried. A little over a year ago, I submitted a dead simple template that consists of the table schema and two forms. That one was accepted.