Thursday, May 23, 2013

2000, 2003, 2007, 2010 Tables Are Stored Identically

I took a database with only tables and indexes in Access 2000 format that had 400 tables. Compacted it. Converted it to 2003 format. Converted it to 2007 accdb using Access 2010.  All 3 databases were exactly the same size.

Conclusion: Nothing actually changed in Access data storage in a decade through 4 versions.

Garry Robinson Access MVP 2006-2013

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

Garry Robinson
Microsoft Access MVP since 2006