Slowing Things Down

  • Thread starter Thread starter LarryP
  • Start date Start date
L

LarryP

I have a procedure that performs a series of tests on a
humongous dataset. Some of these tests involve first
running a MakeTable query on the big dataset, then a
second query against the newly created working table.

My problem: sometimes it appears that Access is trying to
run Step 2 before Step 1 is completed, resulting in an "I
can't find that table" error.

How would you suggest I go about putting Step 2 on hold
until I know for certain that Step 1 is done? I
considered putting Step 2 inside a "does the source table
exist" structure, but it seems to me if the answer is No,
the code would skip it altogether and move on to the next
test without this one ever getting done. Other clunky
workarounds also come to mind, but surely there must be
some kind of built-in, elegant answer to this problem.

(The relevant part of my code is shown below if anyone
needs to look at it before responding. Nothing esoteric
about it, I think it's just a timing/multithreading issue.)

*****************************************************
DoCmd.OpenQuery "qryTest11FailedItems" 'MakeTable query
DoCmd.OpenQuery "qryTest11CodeUpdate" 'uses above table
'sometimes fails here
DoCmd.OpenQuery "qryTest12FailedItems" 'MakeTable query
DoCmd.OpenQuery "qryTest12CodeUpdate" 'uses above table
'sometimes fails here
 
How would you suggest I go about putting Step 2 on hold
until I know for certain that Step 1 is done?
Click to expand...

Try putting a line

DBEngine.Idle

after the MakeTable query; I *believe* this will make the program wait
until the query has completed.
 
It sounds to me that the Make-Table has been completed but
still in cache and therefore the next statement fails.

Try:

DoCmd.OpenQuery "qryTest11FailedItems" 'MakeTable query
DoEvents
DBEngine.Idle dbRefreshCache
DoEvents
DoCmd.OpenQuery "qryTest11CodeUpdate" 'uses above table

Personally, I would use CurrentDb.Execute instead of the
OpenQuery Method for the first statement. See Access VB
Help on the Execute statement of the Database Object.

HTH
Van T. Dinh
MVP (Access)
 
Implicit transactions are, by default, asynchronous.

Docmd.OpenQuery, is by default, a transaction. Dunno
if it is an implicit, and hence asynchronous, query,
or what.

If you use RunSQL instead of OpenQuery, you can explicitly
turn transactions on or off.

If you use Execute instead of OpenQuery, you can explicitly
turn transactions on and off.

Also, Execute will typically run about twice as fast as
RunSQL (although part of that might be because of the
default transaction processing).

You can execute an action query like this:

Application.CurrentDB.Execute "qryTest11FailedItems"

RunSQL works like this:

DoCmd.RunSQL "qryTest11FailedItems"


(david)
 
Back
Top