Compile Queries

[krymat]

Is there a way I can programmatically compile all of my queries.

 

[tlbroadbent]

Pleasem explain what you mean. Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[krymat]

I've read that until you actually run a query it is in an uncompiled state and slower. So every change you make to a query, you need to actually run it to compile it. I have maybe a couple of hundred queries and I really don't want to spend the time to run them individually..

 

[DabblingDan]

I am facing the same problem and question at this time. Did you ever find a solution which allowed you compile the queries? From what I have read, I think the queries need to be recompiled after every COMPACT. I having some write conflicts resulting in the need to run REPAIR. It happens most after a COMPACT. I am wondering if recompiling the queries before users get to the database on the network might help.

Thanks

 

[krymat]

I never found a solution, I just ran all my queries manually

 

[DabblingDan]

Do you re-run each time you COMPACT the database also?

 

[krymat]

I've never had a problem with queries "uncompliling" with a compact. I have had problems with importing queries and not working correctly until being manually ran.

 

[CVigil]

Here's your code, folks -- created in and for Access 97. I just came across your old thread today. But the last post is just some months old, and it only took me a few minutes to put the code together.

Note that the Sub I give you here only runs *SELECT* queries, and that this is very much on purpose! Other queries, action queries, can make changes to your data, and I don't want to hand you a loaded pistol . If you want to load it yourself, though, it's easy. If you want to have action (or other specific types of) queries run automatically, in this or other contexts, you can adapt the code below using the info from the help files on the Type property of the QueryDef object. (I've included the relevant info from Access 97's help file on this topic, below the code.)

Also note that I decided to save each query as I close it. That might not be necessary; I don't know, so I played it more cautiously. If it isn't necessary, though, you could save time by using acSaveNo instead of acSaveYes below.

Also, strictly speaking, the Application.Echo lines and the MsgBox aren't necessary. They don't affect the query execution at all; but they are nice additions for the user (you). The first turns off display while the queries are running, so you don't have to watch the screen change a lot and so that the computer doesn't have to waste time changing the screen display. The second lets you know when the sub has completed, so that if it takes a while, you won't have to wonder.

And note that you will still be prompted for query parameters, and will still be able to see the prompts. Also be aware of whether any of the queries use values from forms or not. If so, Access will prompt you for those values if a given form in question is not open.

============

Sub RunAllSELECTQueries()
'Runs all Select queries in the database
'Note: You will still be prompted for query parameters,
and will still be able to see the prompts.
'MS-Access version: 97

    Dim qry As QueryDef
    
    'turn off display; speeds execution
    Application.Echo False, "Running Queries..."

    For Each qry In CurrentDb.QueryDefs
        If (qry.Type = dbQSelect) Then
            'open the query
            DoCmd.OpenQuery qry.Name, acViewNormal, acEdit
            'close the query, saving it
            DoCmd.Close acQuery, qry.Name, acSaveYes
        End If
    Next qry

    Application.Echo True, "Done Running Queries"
    MsgBox "All Done Running Queries!"

End Sub

============


The following is copied directly from the Access 97 help file for the Type property; this section relates to the QueryDef object:
(It may be more legible if copied to a word processor.)
--------------
For a QueryDef object, the possible settings and return values are shown in the following table.

Constant          Query type
dbQAction         Action
dbQAppend         Append
dbQCompound       Compound
dbQCrosstab       Crosstab
dbQDDL            Data-definition
dbQDelete         Delete
dbQMakeTable      Make-table
dbQProcedure      Procedure (ODBCDirect workspaces only)
dbQSelect         Select
dbQSetOperation   Union
dbQSPTBulk        Used with dbQSQLPassThrough to specify a query that doesn't return records (Microsoft Jet workspaces only).
dbQSQLPassThrough Pass-through (Microsoft Jet workspaces only)
dbQUpdate         Update

Note To create an SQL pass-through query in a Microsoft Jet workspace, you don't need to explicitly set the Type property to dbQSQLPassThrough. The Microsoft Jet database engine automatically sets this when you create a QueryDef object and set the Connect property.
--------------


And, lastly, another idea has just occurred to me. You *could* put this code, with some additional automation code and a bit of tweaking, into another database, to open each of your databases in turn, running all of the Select queries in multiple databases automatically. "Each of your databases" could be all of the databases in a directory or directory structure, or all those named in a list, or ... Well, many possibilities exist. (By the same token, the list of queries you run in the loop in my code could also be modified.)

Have a good one! -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot