Microsoft Access Database Design Concept – Step 7 of 7 – Automating Your Access Database

The Menu Design (Automation)

The final part of the database design concept series covers the ‘bells and whistles’ to an application and will certainly help to drive the usability of a database system.

Without this component, a database can be ‘clunky’ and un-user friendly. The automation processes can handle incomplete values for records, seamless data cleaning procedures, resetting defaults and control invisible routines that we all take for granted.

A knowledge of either Macros or Modules (VBA code) is required at the basic level to apply the features accordingly as Microsoft Access provides some help for you via its wizards.

However, from your planning task here, you need to list all procedures you will possibly require from the obvious click of a button on a Form to the more ‘under the hood’ in depth procedures which are not always so obvious.

You will not get this task right from the outset and will take various re-visits to perfect a good balance and more importantly prioritise tasks.

But what it does do for you at this early stage is to make you aware of the expectation and in some cases limitations for a database system.

Start with the obvious and work your way down to the more challenging aspect. Therefore, creating a menu interface to help users navigate to and from one process to another would be a good starting point.

You will need to know the following 5 points in order to create some automation for your Access database:

1. Decide which one to use; macro or a module. There are pros and cons but essentially it will start with your own knowledge and confidence of general programming concepts. Modules (VBA code) is more challenging but more powerful and flexible than macros. At the basic level either type of object can be used and non-programmers should lean towards macros.
2. You will need to spend some time understanding the object features of a macro (and if applicable some coding principles for a module). Get to know the interface screen and its functions including the common and general keywords. Some research and possibly investing in a book on the subject would be advisable.
3. List the type of tasks you wish to automate for each process and objects you are going to control with automation. It can be a simple button that prints and closes a report to importing and cleaning up data.
4. Identify each procedure and group them together into probably two categories; one for a specific task (a form or report) and the other for general global use across several objects. This is important as you may have to write code procedures differently based on the scope of the procedure.
5. Take each procedure and list in ‘pseudo’ code form the steps you are trying to build which will serve as the checklist and basis for your transposed procedures in either the macro or VBA language.

This process of automating an Access database is not easy and can be frustrating at times but persevere here and you will break through and succeed. In fairness, I get a lot of enquiries to help Access users and developers with this process as it’s not as intuitive as the first 6 steps in this series.