When it comes to Microsoft Access databases, even with intense lengthy training only a few people, become capable of creating a usable database. But even these databases are not error free.

Most office workers computers have Microsoft Office installed on them. That’s Word, Excel, Outlook, PowerPoint and in many cases Access database program. Most people can start using Word, PowerPoint and Outlook with no training whatsoever. With a little training, or a good book many can create some basic spreadsheets. Even so many people still get themselves into trouble by creating error generators.

Access is different. Access is actually a powerful programming language that hides behind a very user friendly facade. It has the potential to lure the unwary user into some very deep dark waters.

Here are 5 common traps that can befall the unexperienced.

1.    Bad database design.

Bad database design is not about choosing lime for a background colour with burnt orange text.

Bad database design is about not completing the analysis process called normalisation. Briefly, normalisation splits the data into meaningful tables which may be linked together with other tables that relate to each other. It takes a very experienced analyst to be able to understand a business process and to convert that to a data structure. Bad data structure will lead to big problems further down the track.

Bad is an understatement. I have seen some creations so terrible that they cannot be imported and reshuffled into something that would be satisfactory.

Bad design can lead to:

• The same data having to be re-entered for each transaction. For example, every time a customer buys something, their entire name address, phone numbers have to be re-entered every single time.
• Not knowing which is the right record to change when that customer changes their address or phone number
• Not being able to tell if that customer is the same customer or a different customer from another with a similar name or address.

These are just very basic examples.

Slightly more complex, or dare I say real situations, lead to much larger disasters.

I always recommend that participants of my courses send me their data structure diagrams, before beginning to program, for free inspection and guidance.

2.    Database design copied from paper forms

A particuarly common error for new designers is when the database creator literally copies the fields from a paper source document such as a new client information form to make the record structure. This usually means that absolutely no database design work, such as normalisation has been attempted. The result is usually a single table database. They might just as well have used Excel.

3.    Not splitting the database

This might be forgivable in a single user situation, but never in a multi-user environment. An Access database can be split into a front end (forms, queries and reports) and a back end which contains the data tables. The front end is installed on the users computer. The backend should be installed on a shared or network drive.

This improves performance over a network. It also makes it very easy to work on, or modify and test out the changes without interfering with the current live version.

4.    Bad GUI

GUI isn’t some kind of new age Feng Shui. GUI stands for graphical user interface.

GUI is involved with how users interact with the program. Is it intuitive? Does it have the same look and feel throughout the whole application? Are the controls used appropriately – Dropdowns vs Checkboxes vs radio buttons?

 

A novice creator might ignore this, which is all very well if they are the only user, but at some point in time most databases will be used by someone else. Maybe it is when the creator goes on holiday, has a sick day, or much worse leaves the organisation.

For one of the worst databases, in my experience, I was called in by co-workers only after the creator was safely on leave for months. This database had 72 different forms for entering data which agonised their co-workers to wits end.

After my analysis of the business processes I managed to create an application to do the same tasks with only five screens. Imagine the co-workers delight. They no longer had to search through the database list of forms, but could now click and zoom right to the data or the screen that they required.

5.    Not Documenting

There is more than one kind of documentation. There is the documentation that is given to the end users that explains how to use the database and what each field and screen is for. This is called the user manual. The user manual is important because the database creator will not always be there to train new users.

Then, there is the documentation that programmers write for other programmers who come after them. This can be made up of comments in the code, which is very hard to place in these days of drag and drop user interfaces. Lastly there is the system documentation detailing data structure, business processes and reasons behind the decisions that were made in the creation of the database. This can be very useful in later recalling why a decision was made to program something one way rather than another.

6.    Leaving it all up to one person

Allowing a single person to build, maintain and often also be the one who enters the data for the database is an all too common mistake. It sounds great until that person leaves the organisation. The usual response is to try to hire another office admin with experience in Microsoft Access. The big problem is that it takes a very experienced programmer to modify work that was done by a novice.

Any new office admin that tries to alter the database could end up doing more harm than good. Changing one thing could well break somethng else.

The other issue is that a solo database creator does not have the mentoring of an expereinced software developer who can help them avoid the errors.

Picking up the pieces after the solo developer has left the organisation is the most common task I get called in to do.

7.    Not getting training in Microsoft Access

A beginners Microsoft Access training course will teach the basics of how to create a single table, form, basic queries and reports. At the conclusion of the course participants could be expected to use a database that has been created by someone else.

The advanced Microsoft Access training course will teach normalisation, multi-table databases, advanced queries and touch on some introductory programming. Successful participants should be capable of creating a simple database under the supervision of an experienced developer.

Each course should take 2 days in a group training environment.

Training should be conducted by an accreditated workplace trainer and assessor.

8.    Not hiring a professional software developer

The easiest way to avoid all of these traps is to hire a professional software developer. They should have industry or tertiary qualifications and significant experience. They should have numerous reference sites that you can contact. Some will even offer a full money back satisfaction guarantee. The money spent on a software developer could end up being less than the waste of time, effort and stress, of attempting to do it yourself.

 

Do you know of any other traps in Microsoft Access?