Friday, May 10, 2013

Watch Out ~ Query Behaviour of Control Break keys

At some stage in the last ten years, the behaviour of what the Control Break keys did to queries changed. Before the Ctrl-Break key sequence used to stop the query when it had finished its action. Now the way it works depends on how big the transaction is.

If you press Control Break it stops the query running.  This has two different behaviours
 
Small Queries) If the query can be managed in memory, the query is NOT committed and your data does not change.
 
Big Queries) If the query does 10,000s of lines of updates and Access cannot handle the query in memory, it stops the query and all actions taken are committed. That is they are NOT ROLLED back.

For big queries, this is a possible disaster because you really do not know the state of your data. 
 
You Have A Big Query When You Get this prompt
 
There isn't Enough disk space or memory to undo the data changes this action query is about to make
 
So Is This A Problem ?
 
If this only happened when you pressed Control Break, it wouldn't matter because it is unlikely your users are going to do this. What does matter is that your user turns off their computer (in frustration because the query is taking so long) or finds some other way to interrupt the process. We have even had instances where switching to Excel triggers the Control Break behaviour. Then you have to decide, does this make my database incorrect.
 
Solution :(
 
I don't have a great solution for you apart from breaking up the query into smaller updates so it only effects smaller number of records.  That way if the process is stopped mid stream, you know that the data is in its original state.
 
Another clunky way to avoid the problem is to inform your users with a message box or popup form that this query takes a long time so that they don't become impatient. 

The vb123 story on control-break keys is here

Find
Garry Robinson
Microsoft Access MVP since 2006
 
 

 

No comments: