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. 

 

If you want a copy of the database that I used to test this, post a note to me on this form. http://www.gr-fx.com.au/contact-us.html It has 1.6 million records in a table and 3 different ways to run queries.

 

Garry Robinson
Microsoft Access MVP since 2006

 

 

 

My Windows 8 Phone

When it comes to mobile phones, I am not up with the times at all. For the last year I have fiddled around with a couple of cheap Google phones and they were ok. When I added the Swype keyboard entry system, those phones became pretty good. But it lacked one thing, my Calender and Contacts were hard to sync. I missed a meeting or two and enough was enough.

So when my mobile plan came up, I decided I had to stop being a Windows 8 doubter and hopefully get proper Outlook Exchange Synchronisation. I also didn't have pockets big enough for the new Samsung Android.4G phones (that is trouser pockets, not $$$ pockets). So I purchased a HTC 4G phone.

Summary:

I totally love the very customisable main screen of the Windows 8 phone as shown in pictures 1 and 2. It makes the icons on Google and IOS look like a boring Desktop. In figure 1, I see all my emails, messages, and things I need to know at a glance. When I slide the screen up, I can see HTML pages and other apps.

My Outlook Exchange account now syncs beautifully. I now can sort out my email mess in a browser, in Outlook on a desktop and on my phone and it instantly changes in all the others. This is what a phone is meant to do.

What is missing is some apps and the totally slick Swype keyboard interface. Please Microsoft, pay Nuance to add it to Win 8 mobiles.

I now can see what Microsoft are trying to achieve with the UI of Windows 8, before I was only excited with the way it looks. And the phone fits very comfortably in my pocket.  All in all, the upgrade has been a success and here are the screen dumps

Screen 1: What I see on the home screen. 5 in Messaging means I have 5 unread SMS messages. At the top of the Date tile is the next Calendars task. Screen 2 is still the main screen which you get to by sliding the screen up. The white tiles are links to HTML pages in IE.

And the bottom of the main page is here. The stocks app updates at regular intervals.

 

 

Garry Robinson:
Access MVP and a not so clever mobile ph user.

A Couple Of Debug Tricks

Click here for a couple of VBA Debug tricks that you may find handy. If you know them already, well done. 

Access 2013 is one of the Pay as You Go carrots

From this article at ZdNet, the following quote says a lot about the future of Access in the Office suite.  "With an Office 365 Home Premium subscription, you get those programs (Word, Excel, Powerpoint and OneNote) and Outlook, Publisher, and Access.

My thoughts on this "Though I haven't read the fine print, this means that Microsoft need to keep including Access in the Premium line-up because the licence entitles users to free upgrades as long as they keep paying. So there you go dear readers, Microsoft have to keep Access going... As Excel and Word and Outlook work well in the cloud, its a good bet that Access will get the resources to fly to the cloud in the not to distant future. 

Garry Robinson - Access MVP from 2007 till now.

Access ADP Not In 2013

Showing how important it is to get millions of eyeballs if a feature is going to be supported by Microsoft these days, Access ADP is not included in Access 2013 as explained by Microsoft here.

Access 2013 - Close to A Revolution according to the Scottsman

In this MS article that I mentioned in a recent post, Access aims to be the "easy to use database tool for the web". For anyone who has ever headed out into this world, you will realise this was always going to be hard. As this is early days in a new tool, the best place to look is the book writers as they can be a little impartial and certainly have to test the product well. So when I saw I post by Andy Couch, author of a few books and way smarter than me, I thought I would share his early insights.

Book Links:  Access 2013 Inside and Out  by Jeff Conrad (Andy is an editor)

Microsoft® Access® 2013 Plain & Simple by Andy Couch http://shop.oreilly.com/product/0790145365408.do

Microsoft® Office Professional 2013 Step by Step by a bunch of people http://shop.oreilly.com/product/0790145365163.do

From Andy...

Hi All,

Like most of you skilled Access users, what I really want to see in Office 2013 is a cracking offering from MS for Access. For too long we have been wandering in the wilderness. All of you have wanted a browser based experience, and it has not come to pass. Until now.
I have said this so many times is we needed a solid platform for the internet before we added the WOW factors. After working with the beta and now the live version of 2013, I think it is difficult to dispute that we now have the platform.

I have just spent all of today going though one of Jeff Conrads Chapters on constructing views (which is basically a form in the browser). What Jeff has show me is that these views go much further than you would imagine in an online programming world. This excitement is something that I haven't experienced for a 15 years. When I was a Paradox Developer and got my hands on Access 2.0, and I saw just how great that product was. Then with Access 97 when OLE Automation came of age, I thought wow. When the Ribbon came along I thought well it is nice, but not a revolution. Other than that, Access has just been good old Access.

Now I have pleasantly had my third Access Revelation. The work that has gone into the View Design Tool is just awesome. Not only have the Team managed to preserve so much of the standard productivity of good old Access, but they have added features that actually make the UI surpass Desktop Access!

For a simple example, when you drag and drop a field onto a form, the design surface moves all the other controls out of the way to make space for when you drop down or move an existing control. But that is just one of a number of very sexy new features. Now we wait while Microsoft posts some worthwhile examples, great help guides and videos and irons out the kinks in Office 365 to see if these revelations actually become a revolution. In the meantime, why not pre-order one of these books to whet your appetite"

Andy Couch

Surface Pro = MacBook Air

 FYI: A Surface Pro has very similar usable disk space to the MacBook Air according to ZDnet.  Especially if you move the recovery space to a USB stick.

For more pesky questions to the MS team about Surface pro, read this page or just read an early ZDnet review

FYI 2: People who use Macs, Ipads and Google devices don't seem to develop custom software and just like suffering with what they have been given. Good businesses should continually improve their business practises, standing still or downloading the odd app is unlikely to liberate the information that individuals collect.

Access 2013 - Wots in the Box

Access 2013 looks like it can be summed up by the following words  - SharePoint, SQL Server, Macros, Azure, Office 365 (this may not be ready) in an environment called MS Access Web Apps

So if you are looking for the upside, my guess is it may be an environment where Access programmers can generate solutions that run in more worldly places than our conventional files server solutions. I believe we had better hope that all those big companies with SharePoint find this is a great idea and then they start looking for people with Access skills to build these apps. Only grandfather time will reveal if this is going to come about.

Remember when installing a new version of MS Access to test, always install with the additional options and do not remove your old version of Office. Generally I like to only add Access and leave Excel and Word at the lower levels.

Note:  Access was included in the Office 365 Home Premium pack, something people can rent for 15$ a month or thereabouts. That's a positive sign.

 

Bit of a laugh on a rainy day

If you have the time, here is a show I would have loved to have been at. The writer sees the funny side of the fancy dress.  Link here

Kick up the heels - outsource your daily grind

An employee of the month outsourced his own programming work.  Read more

Learn how to switch primary keys in Access

When I showed a programming friend about indexes in Access the other day, I realised that this may be a topic that many programmers know very little about. Then after changing about 30 indexes in a database becuase of the Autonumber bug, I thought I had better write up some notes

See all the steps to change indexes in Access here

The AutoNumber "goes crazy" fix

There are posts everywhere about Autonumbers misbehaving in Access 2007 and Access 2010. Most post end up pointing to a solution on Allen Brownes great website

Unfortunately there are problems with that solution (and if I am wrong about that sorry).

1) It doesn't work in Access 2010 but seems to work.
2) It fixes the random number seed but eventually the problem returns.

There is only one solution and its a manual fix.

Open the table in design view. Find the auto-number field.  Make it the primary key. Look at the sort on the primary key. It must be Ascending. 

Do not remove the Primary key from the autonumber key, no matter how you want that table to behave.

What I think is going on

At some stage, someone in Microsoft decided that when a database is compacted, they could look at the very last record in the table and then look at the autonumber field and save that number as the seed.  Then when the next record is added, it looks at the seed, adds one and then updates the seed.  All fine if the table is sorted correctly.  I think the bug crept in because Microsoft templates all have autonumbers as the primary key in each table. Therefore they wouldn't have noticed the bug.  For people who are getting an error like "Autonumber already exists", its because the seed is lower than the highest autonumber in the database and eventually it hits a record that already exists.

I hope I am wrong, this is totally unnecessary bug that should be fixed in the next service pack. It has caused problems in at least 3 of the databases we are maintaining.

We have this bug in Access 2000 databases but we are running them with Access 2010 now.

I do not want anyone to lecture me about primary key design or autonumbers but if you know anything about house paint colours and kitchen design, happy to chat.

Here is an article that shows you how to change (primary key) indexes

Garry Robinson
Access MVP 2006-2013

Happy New Year

Hello vb123.com followers

This year promises us a great comet display, and probably for many of us, our first taste of Windows 8 and Office 15. 

Should be interesting

Have a great year

Garry Robinson

Building software, is it the same as renovating a house?

Over the last year, we have been renovating our house. We have a good builder, we have had to make lots of decisions and we know absolutely nothing about building. Looking back at the process, sure I have lived in houses for 50 years, I actually never paid the slightest bit of attention to what makes up a house. But when the builder rings you says have you got the tiles ready, the tiler arrives on Thursday, you sure have to pay attention. When you look at the newly stripped bathroom and measure up that dream vanity and realise it finishes inches from the toilet, you realise you need to pay attention to what it going on and do some planning. But until that moment, architects plans drift by, bathroom drawings sit on the plans and you really don't pay attention.

Surely this is what our software customers must feel when we write software for them. We have our little meetings, they drift in, sit down and we all chat, we walk out with visions of great user interfaces and they go back to what they were doing yesterday. Then our software arrives and they start to use it and ....

So ask yourself, did they actually tell you what they needed at the first meeting or were they only 80% focussed like I was with our house. You will never know until the software is installed and they start using it. Given that software is rarely as important as fixing a home, its a good bet they will not have focused. Is that their fault, no, its human nature.

With Microsoft Access you have always got to strive for good design and getting the software (read data model) into the hands of the user as quickly as possible. Don't be worried that they will not be excited about an interface with basic buttons and datasheet views of data and quick exports to excel for reports. These are the tools that you have that gets users to focus on your software. Just like me and my house, once the bathroom walls were in place, I could make cardboard cut-outs of vanities and toilets and make sure that things fitted before the carpenter fixed them to the wall.

Have a great festive season.

Garry Robinson
Access MVP since 2006
www.vb123.com

2003 verses 2000 format

I asked this question of Wayne Phillips from EverythingAccess.com
In your website you stated  "When using Access objects (e.g. forms, reports, modules etc.) consider upgrading Access 2000 databases to Access 2002/2003. This is due to a much improved file-system for storing these objects in Access 2002/2003."
I am working on a huge system in a2000 format.  Is it really worth the upgrade to 2003 ?
and Wayne answered

I generally go with 'if it ain't broke don't fix it' :)  But if it is broke, or you're experiencing regular corruption, I definitely would recommend it.  In Access 2000 the forms/reports/vba etc are all stored together in one big compound file called a DocFile, which is then itself split into 4000 byte or so chunks and each stored as records in the MSysAccessObjects table.  This DocFile adds an extra layer of abstraction (and complexity) to the storage of Access-objects which is not needed and degrades performance.  The 2002+ format removes the DocFile complexity and instead stores all the Access-objects data directly as separate records in the MSysAccessStorage table.  Furthermore, in my experience, the 2000 format is more prone to corruption issues.

Kind regards   Wayne