Using querydefs and parameter querys in VBA

Today I will be bitching about using parametrized query's from VBA code in a robust way.  I will also be presenting a roundup of the solutions as far as I know them.

The Problem
You want to modify a query on the fly using some dynamic option or parameter in VBA and then use that as the RecordSource for a form.  Bloody obvious need if one is building a db driven app.

Possible Solutions
The first is to user a parameter query with a field somewhere on a form/global variable that the query can just pick up and run with.  This sucks because its inflexible. I need to essentially plant a global somewhere that always exists and always contains the right value... can anyone say spagetti code? Hello 1980.

The second ( variation) is to try to plug the parameter in on-the-fly by massaging the Querydef object and then pulling the Recordset by hand and somehow pushing it into the form or whatever you are trying to use it as a source. This is just fuggly.

The third is to compose the SQL as a string and put the parameter into the string using string functions. Nice, easy and completely unmaintainable using all the nice GUI tools provided in Access.  Yes I can do it, but it means I end up with my code littered with SQL fragments that I have to then hand maintain every time something changes in the db schema. what a PITA.

The fourth possible solution I have seen floating around is to build a whole dialog that can compose the SQL on the fly using some combo boxes etc. This is just a variation on solutions 1 and 3. With the worst parts of both.  Now you have even more trash to maintain and find when you change anything. The whole point is to reduce complexity not add to it. Blahhhhh.

Fifth possible solution is to roll my own object that somehow can be instantiated, wrap a Querydef object and gracefully update it as required.  Since there are no useful hooks to build this around, it gets back to having a global object somewhere and messing with it on demand... back to option 1 but with the overhead of a whole class of code to now maintain.

Main Bitch
What I would much prefer is a nice wrapper object around a Querydef that could be used to create a temporary Querydef with parameter slots, I could then plug in the params, test that the Querydef is stable, and pass it to the form as its Recordsource.  This way I can maintain the Querydef using the Querybuilder GUI and all the other test tools, use the QueryDef gracefully from code and not littler my code with all sorts of fragile text string and maintenance headaches. Oh and I want MS to supply this wrapper so I don't need to roll my own. Bastards.

Usually when I find myself banging my head against what seems to be an obvious problem, I usually find that its just that I don't know the "Right way" to do things in that language or framework and that with a little bit of reading I can find it and get my head right (usually refactor a truck load of code) but get back on the true path and start making progress again.  However in this case it seems like its a common problem that doesn't really have any particular "right solution" its just lots of ugly hacks. (There are some truly UGLY solutions floating around the forums to this problem. But I don't feel like I have a more pleasing solution so its much of a muchness. Still they offend my eye in ways that indicate they are fragile, hard to maintain and error prone)

Ok head is a bit clearer... bitching complete. I need to go read up on the QueryDef object I think.


Edit:

The best solution I have come up with ( tip of the hat to a forum post I read but can't remember... its probably common knowledge anyway) is to maintain the query in as a normal QueryDef which gives me the ability to use the GUI and compile the SQL for testing; the Query contains a parameter which is essentially a unique key phrase that I then use the "Replace" function to replace with my desired parameter info.

For instance

    Dim qdef As QueryDef

    'contains a param "[ContID]"
    Set qdef = CurrentDb.QueryDefs("ContinuityRelationshipFormSpecificQuery")
   
    Dim theSql As String
    theSql = Replace(qdef.sql, "[ContID]", str(relationshipID))
     
   Me.RecordSource = theSql

This is a fairly simple and maintainable system. It takes about four lines to do what I would like to do in less, but its still only a line per parameter so not too horrible a trade-off.