Stopping a query that runs past a set length with VBA

  • Thread starter Thread starter macroapa
  • Start date Start date
M

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.
 
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
 
macroapa said:
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.
Click to expand...
 
Back
Top