Best way to execute querydefs in a loop using DoEvents? (1 Viewer)

[burma]

I'm running a bunch of saved append queries in a loop. The queries add records to linked SQL tables from local Access tables. Using DoEvents makes it take forever. Is there a better way? Is using StillExecuting the preferred way? Here's what I have so far. Thx

On Error GoTo ErrHandler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = DBEngine(0)(0)
Screen.MousePointer = 11
For Each qdf In db.QueryDefs
    db.Execute qdf.Name, dbFailOnError
    DoEvents
Next
    
ExitHere:
  Screen.MousePointer = 0
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere

 

[The_Doc_Man]

The first question I have is whether the Append queries are written as "PassThru" queries. If not, you are not running asynchronously anyway, so would not need to allow for delays. You wouldn't be able to execute anything else until the query was complete.

NOTE, however, that your "For Each qdf in db.QueryDefs" is not selective. It will attempt to execute EVERY query - including any UPDATE, DELETE, and SELECT queries, as well as the INSERT INTO queries. Unless that is a customized front end that has no other queries than your Appends AND unless they are defined in the exact order you wanted executed, the order of insertions might be a little confusing.

Note that syntax-wise, I am NOT criticizing your code. My questions are about the semantics of what you are doing.

 

[burma]

No pass-throughs. There's just append queries only, and they all have to run. So I'm not worried about running anything that shouldn't be run.

So you're saying leave out DoEvents? That would probably speed up the process but I don't want any query to not run. I'm also wondering if using ADO might be a better way to do this. Thanks

 

[gemma-the-husky]

I don't see why doevents has any bearing on this.

The db.execute statement runs and then the doevents allows any waiting tasks to process.

Doevents doesn't really achieve anything much here, surely.

Normally you would put a doevents within a long repeating loop that would otherwise prevent users interacting until the loop finished. This isn't the same as executing a bunch of queries, as each query is atomic, if you will.

Why would you want to execute every query in your database, sequentially, though? I can't see that makes any sense. What are you appending? Can you time each append query, and see where the bottlenecks are occurring?

Indeed I imagine if you really want to execute a long process then you specifically would not want a doevents which might allow the user to interfere with the process (accidentally, just by doing something). That's more of a design issue though. I still don't see why simply including the doevents would extend your process time, if you just watch and don't interact with the database.

It's like polling for interrupts when there aren't any. It surely can't use more than a handful of clock ticks.

I would display on screen the process flow with a timer so you can see when each query starts. That would let you follow the process.

 

[NauticalGent]

Adding to what GTH has said, I usually use DBEngine.Idle dbRefreshCache after a db.execute statement instead of DoEvents. In your case, I suspect it is unnecessary, but there is no "Kill" like an "Overkill"!

 

[isladogs]

Another way of building in a delay is to use the Sleep API
or a comparison between DoEvents, DBEngine.Idle RefreshCache and Sleep, see Speed Comparison Tests 3 (isladogs.co.uk)

 

[ebs17]

but I don't want any query to not run

The code will do what it says. One query after another. There is no stopping it unless there are errors. DoEvents is unnecessary.

makes it take forever

This is more likely to be due to the queries themselves. Have you carried out these individually and measured the required times?
How many records are moved?

 

[ebs17]

The queries add records to linked SQL tables

What are these tables?
For tables with relationships among each other, a consistent order is not possible; primary tables must first be served before dependent records can be created in linked secondary tables.