QueryDef slow down query

[Ian]

I used this code to modify the criteria for a query:

dim qdf as dao.querydef
dim db as dao.database
set qdf=db.querydefs("MyQuery")
with qdf
.sql="Select * From tblMyTable Where MyID>3"
.close
end with
set qdf=nothing
set db=nothing

The problem is once the query is modified by VB code it
runs a lot slower than if I modified the query in design
mode without VB with the same ctiteria.

Any Ideas

 

[Albert D. Kallal]

It is very possible that in design mode, it flags the sql to be re-compiled,
and when you do it in code..the sql is not optimized correctly.

however, my real question is why modify the query?

Why not base your form, or report on the table, or a query WITHOUT any
criteria.

Then, just open the report, or form with the "where" clause feature. I would
not even both with bunch of code that modifies the queries.

To open the report with just all MyId > 3, you can use:

docmd.OpenReport "myreprot",acViewPreview,,"MyID > 3"

Or, the same goes for a form:


docmd.OpenForm "MyForm",,,"MyId > 3"

Using the above means you don't have to modify, or fool around with
queues...

 

[Ted Allen]

Hi Ian,

My understanding is that MS Access optimizes saved
queries when a database is compacted, but cannot do the
same for queries designed or modified at run time. I
would guess that this what is causing some or all of your
performance difference. Perhaps others will know of
other reasons.

HTH, Ted Allen