The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
February 2012

The Need for Speed, Part One: The Uninformative Hourglass

I'm writing this on January 4, 2012, the night after the Iowa Caucuses - the first major political event of the 2012 Presidential Campaign. I am currently waiting to see an interview with Newt Gingrich at St. Anselm College in Nashua, NH. As it happens, a reporter from the New York Times decided to photograph some of the folks in the audience and, lo and behold, she took a picture of me writing this as I was waiting! That's me on the far right typing away.

For those of you who are not tuned in to presidential politics, New Hampshire has the distinction of holding the first presidential primary. Although Iowa gets a lot of attention because of the first caucuses, New Hampshire attracts the eyes of the nation for a period of several months culminating this year on January 9 when the primary is held.

Newt is due to speak at 7:00 p.m. and it's now 6:55 p.m. If this event goes like most political rallies, speeches, and town hall meetings, this event will not begin on time. As a matter of fact, it is unlikely that it will start even close to 7:00 PM. The start time, most likely, will be closer to 8:00 p.m.

These events typically end up resulting in a lot of people waiting and waiting, frustration slowly growing, until the candidate finally appears, at which point the supporters go wild, and the undecided voters get ready to be swayed (or not depending on their political leanings).

So what, you may ask, has any of this to with databases? Unfortunately, I've seen too many databases make their users wait while some cryptic process takes place in the background. This month begins a series of articles that will help you reduce waiting and avoid staring at an hourglass by providing techniques to speed things up.

(P.S. The speech actually began at 7:10 p.m. My hat is off to the Gingrich team for being so prompt. I'm not quite sure how they did it, but I was impressed.)

In this Issue
  • Tip of the Month - If Your Users Have to Wait, Give Them Something to Look At
  • A Simple Step - Disable Name Autocorrect
  • Redesign Complicated Forms - Avoid Information Overkill
  • Death by 1000 Records
  • Conclusion

  • A Simple Step - Disable Name Autocorrect

    One of the biggest contributors to overall wait time is the name autocorrect feature. Most folks aren't even aware of this little tool that does its work silently in the background. Its job is to watch what you do and any time you change the name of a table or query, this powerful little routine ripples the name change throughout the database to help you not to inadvertently break things. Although this sounds like a good idea, it pulls heavily on the processing power of the computer. By default, this feature is on. To turn it off, take the following steps:

    1. In 2010, click the File tab. In 2007, click the magic circle in the upper left-hand corner. In 2003 and earlier versions, click on the Tools menu and choose options. From this point on the directions will differ slightly from version to version. These steps apply to 2010.
    2. On the left, click Options. The Access Options dialog box opens.
    3. In the left pane of the Access Options dialog box, click Current Database.
    4. Under Name AutoCorrect Options, clear all the check boxes under the name AutoCorrect options section.

    This change will cost you if you like to change the names of objects after you've created them; however, if you use a consistent naming system it would be very unusual for you to want to change an object's name.

    Redesign Complicated Forms - Avoid Information Overkill

    If you have a form that is slow to load, check to see if it is needlessly complicated. If so, simplify. If your form contains sub-forms, which in turn contain other sub-forms, then you have set yourself up for poor performance.

    If you have this problem, rethink how you are presenting data to your users. Rather than try to show them everything they might want to possibly know about a particular record, replace all the non-critical data with buttons that will bring up more detail as they need it.

    If you have a form with loads of tabs, then you almost certainly are asking for trouble. Typically, when a user is looking at a record s/he is not interested in everything about the record. Show only the most critical information and fit it on a single form with no tabs.

    By doing this, you can gain a performance pickup. If you take the various tabs in your forms and convert them into buttons you will end up with a sleeker, faster design.

    Death by 1000 Records

    Frequently, new database designers are enthralled with the fact that if there are thousands of records in the database, they can make every single one of those records available on a single form. Although this would seem to be a good technique, it really isn't.

    Think about it. do you or your user want to see a thousand records? Most likely you and the user are interested in a single record and that's what you should retrieve from the database. If you are working across a network and your form brings all the records in a table to the form then you have dragged thousands of records across the network that will do nothing but take up bandwidth and slow things down. You're better off providing a search function that allows the user to determine which record they are interested in and then, after they make their selection, bring only that single record to the screen.


    This is the first of several articles that will help you reduce your wait time while the database does its thing.

    Although designing a speedy database, particularly one with complicated data, can be difficult, you will find that your application will be easier to use if you spend the time and effort to make things faster.

    Just as waiting for a campaign speech makes people irritable, staring at an hourglass turns users into unhappy campers. Take the time to tune your database for speed. If you do it right, your users won't even know that the database contains an hourglass.

    Tip of the Month - If Your Users Have to Wait, Give Them Something to Look At

    Even streamlined databases occasionally may have a process that is so computationally intense or manipulates so many records that a wait is inevitable.

    By all means, avoid the default hourglass. That does nothing to indicate any details about what is going on behind the scenes. It just says something is happening, and that the user should wait. This is not only discourteous, but also annoying. The user doesn't know if the computer has gone into an endless loop, or is broken. They don't know if they're being asked to wait 10 seconds, 10 minutes, or an hour. If the delay is going to be more than 10 or 15 seconds, then you can make it seem shorter by a very simple technique: Give them something to look at and let them know what is going on behind the scenes.

    The easiest thing to do is to include a progress bar that advances as your process moves forward. Provide periodic messages to the users about what's going on behind the scenes as things progress. Telling them how many records are being processed and how far along the process is will make the wait seem shorter.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886