compiling queries
I noticed when starting from a blank database and importing in many queries from an older database the forms are terribly slow when opening. I think I found the reason but still need a little help. It seems since the queries have never been opened the have never been compiled. If I open them, show the data, then close, the forms seems to open faster.
If this is the case is there a way to open and close or compile all the queries in vb?
If this is the case is there a way to open and close or compile all the queries in vb?
the first time the query executes, the query will be compiled, then the compiled query is used when the query is executed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Maybe something like this (make a backup first). I just tried this - but I have no way to tell on this system if it makes a difference.
Dim qdf As dao.QueryDef
Dim db As dao.Database
Set db = CurrentDb
For Each qdf In db.QueryDefs
Debug.Print qdf.Name
On Error Resume Next ' in case query has systax error, like some of my EE qrys.
qdf.sql = qdf.sql
Err.Clear
qdf.Close
Next qdf
Set qdf = Nothing
Set db = Nothing
Dim qdf As dao.QueryDef
Dim db As dao.Database
Set db = CurrentDb
For Each qdf In db.QueryDefs
Debug.Print qdf.Name
On Error Resume Next ' in case query has systax error, like some of my EE qrys.
qdf.sql = qdf.sql
Err.Clear
qdf.Close
Next qdf
Set qdf = Nothing
Set db = Nothing
<<I'm thinking if in vba, you open each QueryDef and then save/close ... this may do the same thing.>>
Has to be a open of the query def, modification of the SQL, a save, and then a execute of the query without a save after that to force a re-compile.
Jim.
Has to be a open of the query def, modification of the SQL, a save, and then a execute of the query without a save after that to force a re-compile.
Jim.
"a save"
So how do you actually 'save' in vba ? Say relative to the code I posted.
So how do you actually 'save' in vba ? Say relative to the code I posted.
There's no code equivalent. Just setting the property seems to be enough, although it may be when it goes out of scope or is closed that the save actually takes place. I've never really pinned it down.
When I answered though, I was thinking of the steps I'd do manually, so I really didn't answer that correctly.
A execution is required though, which is where doing this in code becomes a problem depending on how the queries are written.
Jim.
When I answered though, I was thinking of the steps I'd do manually, so I really didn't answer that correctly.
A execution is required though, which is where doing this in code becomes a problem depending on how the queries are written.
Jim.
Execute - yeah, that's what I suspected - and yes, problematic in code most likely.
The good news: Compact & Repair.
mx
The good news: Compact & Repair.
mx
ASKER
does Compact & Repair really compile the queries though?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>so first time they are run, they are re-compiled.
even when theyare behind a form or do they still have to be run/opened directly?
even when theyare behind a form or do they still have to be run/opened directly?
<<even when theyare behind a form or do they still have to be run/opened directly? >>
That was my point earlier; many won't run outside of a certain situation, such as a form being opened.
Jim.
That was my point earlier; many won't run outside of a certain situation, such as a form being opened.
Jim.
ASKER
ok, thanks Jim. You just saved me a lot of time. :-)
Shawn
Shawn
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for helpin g me understand. So solution is we don't need vb but a C&R. C&R sure comes in handy.
That is because the JET query execution plan is recreated based on current datasets. So, effectively - you have 'recompiled' the queries.
"If this is the case is there a way to open and close or compile all the queries in vb?"
I'm thinking if in vba, you open each QueryDef and then save/close ... this may do the same thing.
mx