QueryDef

[dsmaj]

I'm having a hard time wrapping my head around QueryDef's--hopefully one of you enlightened individuals can help me out here.

I understand that QueryDef's are just a way to dynamically alter existing saved queries, however I have a few questions...

1) The database application I am developing makes very heavy and frequent use of queries to populate listboxes/comboboxes/textboxes on many of my forms. Is using a QueryDef significantly faster than building and running SQL code from within my code modules?

2) If the answer to question #1 is Yes (and from what I've read, I think it is), then why is it that most of the QueryDef examples I've looked at simply refer to the saved Query, but delete the SQL code from it and replace it with the dynamic code. I.e. If I completely replace the contents of the qdf.SQL property with my new, dynamically created, SQL code, does that defeat the effectiveness of using a QueryDef?

3) From playing around with QueryDef's a little bit, I've figured out that when you make a change to the qdf.SQL property, it is permanent, and affects subsequent use of that query within the code. Is it common practice (and/or advisable) to save a copy of qdf.SQL in order to restore its value when you're done with the current query. Also, does restoring the value to qdf.SQL affect performance? (i.e. does the action of storing a value into qdf.SQL actually cause the query to run in the background, or is it just like assigning any other string to a regular variable or object property?)

I think those are all the questions I have...much appreciated if someone could shed some light for me.

Thanks in advance,
Sam

 

[dsmaj]

Thanks for the reply, Pat. That clears things up a lot for me. Could you point me in the direction of a good (comprehensive if possible) example so that I can see this in action?

Thanks,
Sam

Edit: I suppose I should elaborate somewhat. I want to use QueryDef's to implement a fairly intensive search function in my database. I would like to be able to dynamically modify the WHERE criteria, whether it be a specific value, or a wildcard, or a combination of the two. Does using a QueryDef in a situation like this make sense?

Furthermore, do I just create a regular query within Access, and then access it from the code using QueryDef techniques, or is there a special way of creating QueryDef queries?

Thanks again,
Sam

 

[dsmaj]

I must be deficient or something. I'm searching for QueryDef stuff on this forum, but all I'm finding are posts that talk about "how much info is on this forum about QueryDef." I am getting very frustrated

If anyone can help me out here, I would be very appreciative.

Sam

 

[Jon K]

A Microsoft KB article about Dynamic QueryDef

http://support.microsoft.com/default.aspx?scid=kb;en-us;210242&Product=acc2000

 

[dsmaj]

What you CANNOT do is to change fld1 and fld2 to fld3 and fld4 or change the = to > or Like to =. These are structural changes and cannot be done as parameters. If you need to modify structural elements, you won't be able to use stored querydefs, you'll need to create dynamic SQL at runtime to have control over structural elements.

Ahhh! That's exactly what I needed to know--thanks. So then, would it be most efficient to save SEVERAL queries, and use the apropriate querydef in the various search situations, or just create my queries dynamically at run time and suffer the performance loss there?

Thanks,
Sam

 

[dsmaj]

The MSKB example

By the way, I don't see how the MS KB article's example takes advantage of pre-compiled queries, as it appears that the SQL code is being built dynamically within the code in that example. Can someone explain differently?

Sam

 

[dsmaj]

One final question...(I think)

Sorry to bring this thread back from the dead, but I just have one last question with regards to QueryDef's. Lets say I have the framework of a query (qry_Test) saved as follows within access:

SELECT testID, testName
FROM tbl_Test
WHERE testID = 1;

Now I want to use this query within my VBA code a bunch of times, but searching for a different testID each time. How do I change the testID that is being searched for at runtime? (i.e. what code elements should I be using?)

Also, if I simple save the query as

....
WHERE testID = Forms!frm_TestForm!txtTestID

...will it still work as a QueryDef, and allow me to search for different testID's by simply modifying the txtTestID object?

Thanks in advance,
Sam

 

[dsmaj]

Thanks for the help pat!

For anyone else who is reading this and might be interested, I think I may have stumbled accross something more along the lines of what I'm looking for...The 'parameters' property of the QueryDef object allows you to assign values to a QueryDef's parameters at run time (in code), and most importantly it seems to work A) When the data-source form is not loaded, and *B*) When you're calling the query from code in order to populate a recordsource object or whatever else (in which case the jet-engine is being called "from a different context,"(MS) and does not reference the form that has your input value(s) on it, resulting in a "Too few parameters" error). This last point has proved to be extremely useful for me as it turns out...

Sam