Mike Gunderloy untangles the problem of using parameter queries from DAO or ADO code.
I created a parameter query that gets information from a form, and it works fine when I open the query from the database container. Now I want to open this same query in code and work with the records it retrieves, but Access is giving me an error when I try to create the recordset! What’s the story?
Let’s start from the beginning here. Parameter queries are a powerful tool that some developers seem to shy away from, perhaps because they’ve never thought about them in a systematic manner. With the continued rise of distributed systems, where network traffic is an issue, parameter queries are more important than ever before. So it’s worth thinking about what they’re good for and how they work.
Consider a simple problem in data retrieval: getting a list of all the customers in France. You can do this with a simple query–call it qryCustomersFromFrance:
SELECT * FROM Customers WHERE Country = 'France'
That’s fine as long as the user always wants to work only with the customers from France. But users tend to want more flexibility than that. Sometimes they’ll want customers from France, sometimes customers from Germany, sometimes customers from Afghanistan. The initial approach that many novice developers make to this problem is to develop a whole series of queries, one for each country. That approach quickly turns into an unmanageable mess.
A better approach is to use a parameter query. The original qryCustomersFromFrance is one in which the developer set the WHERE clause at design time. A parameter query lets the user set the WHERE clause at runtime. By using a parameter query, you can provide additional flexibility to users without significantly increasing development cost. The parameterized qryCustomersFromCountry looks like this in SQL view:
SELECT * FROM Customers WHERE Country=[Enter Country]
When you open this query from the database container, you’ll get a prompt as shown in Figure 1. This prompt happens because the Jet database engine can’t resolve “[Enter Country]” as referring to any object that it knows about, so it resorts to prompting the user. If a user types in “France,” he or she will get all of the customers in France; if the user types in “Germany,” he or she will get all of the customers in Germany.
Figure 1 Figure 2
Although the flexibility of parameter queries is very useful, queries don’t represent a complete solution in most cases. That’s because in a polished database you probably don’t want to have your users retrieving data directly from the database container. It’s impossible to secure an application properly without using forms as the user interface. Forms-based interfaces also tend to be less confusing to the user.
So the obvious next step is to wrap the parameter query in a forms-based interface. Figure 2 shows what such an interface might look like. In this case, it allows the user to select a country from a predefined list, then opens a form that displays only the requested customers.
There are several components involved in making this interface work. First, there’s the combo box on the selection form, which shows the available countries in the database. This is an unbound combo box based on the query:
SELECT [Customers].[Country] FROM Customers GROUP BY [Customers].[Country]
This query simply retrieves all of the unique countries from the Customer table. By presenting a list of choices in this way, you can make it easier for users for enter reasonable values without worrying about spelling errors or choosing countries that aren’t represented in the database.
The button to show the customers simply opens up the second form listing all the customers selected on the first form:
Private Sub cmdShowCustomers_Click() DoCmd.OpenForm "frmCustomers" End Sub
So how does the other form know which customers to retrieve? The answer is that it’s based on a parameter query, qryCustomersSelect:
SELECT * FROM Customers WHERE Country = _ [Forms]![frmSelectCountry]![cboCountry]
Remember, though, that Jet prompted the user for the value of “[Enter Country]” in the earlier parameter query because it didn’t know how to evaluate that string. The key to this parameter query is to give Jet a string that it does know how to evaluate. In this case, the string “[Forms]![frmSelectCountry]![cboCountry]” refers to an object supplied by Access: the current value of the cboCountry control on the form named frmSelectCountry in the collection of all open forms. Access evaluates the string and sends Jet the value in cboCountry. Now, when you open the frmCustomers form, the form gets records based on a parameterized query that draws its parameter value from a control on the Select Country form.
This brings us finally to the original question. Suppose you want to let the user select a country and then work with the corresponding customers in code? You might write the following procedure to do so:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qryCustomersSelect")
Do Until rst.EOF
Debug.Print rst!CustomerID
rst.MoveNext
Loop
rst.Close
You might write that procedure… that is, as long as you don’t mind receiving an error message. If you try to run this, you’ll get runtime error 3061, “Too few parameters. Expected 1.” on the line that tries to open the recordset. What’s going on here?
The answer is that you’re invoking the Jet engine in a different context here, and that makes all the difference. When you get data from a parameter query that uses a form to supply the parameter via the Access user interface, as in the earlier example, Access can evalute the expression involved and supply a value to Jet. When you get data from a parameter query that uses a form to supply the parameter via VBA, instead of through a form, the bits of Access that manage user interface matters aren’t involved. Consequently, Jet is passed the string “[Forms]![frmSelectCountry]![cboCountry]” instead of the value in cboCountry. Because Jet doesn’t know how to evaluate the expression, it can’t open the recordset.
The solution is to supply the Jet engine with the parameter. You can do this by using a QueryDef object to open the query, then explicitly setting the parameter to the value before executing the query:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCustomersSelect")
qdf.Parameters(0) = _
Forms!frmSelectCountry!cboCountry
Set rst = qdf.OpenRecordset
Do Until rst.EOF
Debug.Print rst!CustomerID
rst.MoveNext
Loop
rst.Close
qdf.Close
Any QueryDef object based on a parameterized query will have a Parameters collection. Like many other Access collections, this is a zero-based collection, so if there’s only a single parameter in the query, that parameter will be Parameters(0). The previous code tells Access to open the query, fill in the parameter value from the form on the screen, and then open a recordset from the query. If you try this procedure from the Immediate Window (remembering to open frmSelectCountry and select a country first), you’ll find that it does indeed list the appropriate customers.
Of course, if you’ve moved forward to Access 2000, you might not be able to use a DAO solution to the problem. Fortunately, ADO provides a way to run parameter queries in code as well. Of course, the syntax is different, but the idea is the same:
Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset Set cmd.ActiveConnection = _ CurrentProject.Connection cmd.CommandText = "qryCustomersSelect" Set rst = cmd.Execute(, _ Array(Forms!frmSelectCountry!cboCountry.Value), _ adCmdStoredProc) Do Until rst.EOF Debug.Print rst!CustomerID rst.MoveNext Loop rst.Close End Sub
Like the DAO QueryDef object, the ADO Command object can be used to represent a query as an object. In the case of the Command object, you can supply the parameters when you execute the Command to open a recordset. The Execute method takes three arguments. The first is a variable that ADO will update with a count of the number of records affected by any update query. The second is an array of parameter values; in this example I’ve used the VBA Array() function to turn a single value into an array. The third is a constant that indicates the type of command contained in the Command object. As far as ADO is concerned, all parameterized queries are stored procedures.
That should answer your original question, and also give some insight into the general mechanics of using parameter queries in Access 95 and 97.
You’ll notice that Access 95 or 97 offers no way to use a parameterized query with a report or a form if you open the report or form from code (at least, not without bugging the user). In Access 2000, you can generate a recordset from a parameterized query, then set the form’s recordset property to the recordset to have the form display your data. The process is a little more complicated than just listing off the data in the recordset, because you have to configure the recordset so that it can be used with a form before you open the recordset.
To configure the recordset, you must create the recordset and set some of its properties instead of just creating it from the Command object. In the following code, for instance, you can see that I set the CursorLocation and CursorType properties after I create the recordset and before I open it (with Jet, I really only need to set one or the other of the two properties). Once I have the recordset object properly set up, I create a Command object to work with the parameterized query. I also use the Command object to create a Parameter object that will hold the value that I pass to the query’s parameter. Finally, with all of the objects prepared, I pass the Command object to the recordset’s Open method to fill the recordset, then associate the recordset with a form (which I’ve assumed is already open):
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter
Set rst = New Recordset
rst.CursorLocation = adUseClient
Set cmd = New Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qryCustomersSelect"
Set prm = cmd.CreateParameter
prm.Type = adChar
prm.Size = 25
cmd.Parameters.Append prm
cmd.Parameters(0).Value = _
InputBox("Enter a country name")
rst.Open cmd
Set Forms("frmCustomers").Recordset = rst
I’ll close the column with a few general thoughts about why you should familiarize yourself with this technique. As I see it, there are two main reasons to use parameter queries in many databases. The first is that parameterized queries provide convenience and flexibility to the end user. When you’re not 100% sure what the user will want to do (and what developer is ever completely sure about a user’s intentions?), parameter queries allow you to let them make the final decision about what data to see. The second reason is that parameter queries can make your database substantially more efficient as you move from a single computer to a client-server or distributed solution. Rather than retrieving all of the records in the customer table, which might be on the other side of a slow or expensive Web or modem link, you can send a value across the wire to the database engine, have it execute a query using the value as a parameter, and retrieve only the records of interest. This is particularly efficient when you’re dealing with a true client-server backend such as SQL Server. Of course, in Access 2000 you can use parameterized stored procedures in an ADP file, coupled with the ADO code I’ve shown above, to execute your parameter queries on the backend.
