Stopping a query that runs past a set length with VBA

[macroapa]

Hi,

I have a make table query that will be scheduled to run via VBA during
the night.

However, I want to have some code (or set the query some how) to stop
from running if it takes longer than a certain amount of time.

Is this possible, if any pointers would be greatly appreciated.

Thanks for any help.

 

[Tom van Stiphout]

On Fri, 26 Jun 2009 13:04:17 -0700 (PDT), macroapa

No, that is not possible, other than deliberately crashing Access
(which I would NOT recommend).
Sometimes query runs for a long time because it is done inefficiently.
Have some other experts review the code to see if they can improve it.
Another option that can sometimes be used is to break a query up in
many smaller parts with a where-clause. For example rather than
processing all customers process them by zipcode:
set rs = db.OpenRecordset("qryZipcodes", dbOpenSnapshot)
while not rs.eof
update Reallycomplicatedstuff
where zipcode = rs!ZipCode
rs.movenext
wend
Then you have a process that can be gracefully interrupted; in the
While loop you can check the status of a Cancel button.

-Tom.
Microsoft Access MVP

 

[o;;]

Hi,

I have a make table query that will be scheduled to run via VBA during
the night.

However, I want to have some code (or set the query some how) to stop
from running if it takes longer than a certain amount of time.

Is this possible, if any pointers would be greatly appreciated.

Thanks for any help.