DEVelopers HUT

Once one delves into the wonderful world of MS Access & VBA programming, you quickly gets faced with the problem of executing queries and SQL statements from within VBA. No in general, this does not pose much of an issue and there are any number of great tutorials that cover the various way to handle this. For instance, Database Journal has a great article by Danny Lesandrini entitled Executing SQL Statements in VBA Code which covers all the methods available to a developer and explains the pros and cons of each.

That said, all of these articles cover but the basic idea of a simple SQL Statement. What happens if you want to execute a parameter query? How do you approach that. In fact, this isn’t to big an issue either, once you are shown how to handle it.

Firstly, let us very briefly cover the subject of a Parameter. What is a parameter? In a query, we can specify a criteria. In most instances, the criteria will be a static value. That said, it become necessary in many instances to make these criteria dynamic, and to do this there are different methods, but one common approach is to refer to a control on a form, thus allowing the end-user the ability to specify the criteria to apply to the query. This is what I am referring to in this post.

The problem arises that if you merely try to execute a query with a criteria to a form control, it will result in an error “Too Few Parameters”. The work around is that we much first make the database evaluates each parameter before running the query, to do so, we use the following code

    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim prm             As DAO.Parameter
    Dim rs              As DAO.Recordset
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("YourQueryName")
 
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
 
    Set rs = qdf.OpenRecordset
 
    'Work with the recordset

    rs.Close 'Close the recordset
    'Cleanup after ourselves
    Set db = Nothing
    Set qdf = Nothing

So how does it work exactly? Well, we start off by specifying which query we are working with by setting the value of the qdf variable. Next, we llop through the parameters and evaluate each one. Finally, we execute the query. So simple, once you are shown the proper tachnique!

You may wish to look over the sample database found at Roger’s Access Library entitled Too few Parameters.