Microsoft Access Database: The Secret to Designing An Access Database Using The 5 Commandments
And it came to pass, thou shall plan, build and implement an Access database application to serve and please a higher authority; well the boss anyway!
I’ve been fortunate in the sense I’ve always worked for myself and have really never had to answer to any of my superiors and perform to impress and please others – though my wife may have something to say about that!
In reality, when working with students and developers who are given the responsibility of producing a solution using Microsoft Access as the key tool to supply and manage business data, the biggest obstacle is often trying to meet the demand and expectations of their managers who constantly change their minds, do U-turns and have no clue or appreciation of the real effort they have applied.
However, if you follow my 5 commandments when designing an Access database you will be able to adapt and adjust design changes easily and meet the demanding deadlines and expectations of others.
When designing your tables (which is where your data is archived) in Microsoft Access you need to adopt the process called ‘Normalisation’ which is the process of storing logical data sets into smaller units of information that are related to other tables, keeping data organised and avoiding duplication and memory waste. This is an important step and can be the difference between scrapping a database and adapting an existing Access database.
Learn to take advantage of Microsoft Access auto indexing and maintain data integrity by using the ‘AutoNumber’ data type for your unique identifier in a table. This will avoid duplication and help release further functionality within Microsoft Access’s other objects namely queries, forms and reports.
Garbage In, Garbage Out! (GIGO) Make sure your data is entered in a clean and easy to follow state. Do not mix and merge values that can be confusing and restrict how end-users will report on your data. Decide on a structure of looking for the smallest unit of information. For example, a contact name called be made up from three fields; Title, Firstname and Surname instead of one field called Fullname.
Performance is always going to be a challenge for any software application that is shared over a network and Microsoft Access is no different. Therefore, learn to split your database into two by creating a ‘Back-End’ and ‘Front-End’ database setup. Your data is then better protected and everyone can utilise the same data avoiding replication and different versions. New ‘Front-End’ applications can be deployed if a re-design is required with no concerns about data migration.
Microsoft Access has a rich set of objects – so use them. In other words, do not work directly in tables, that to me is like working in the backroom of an office and in the dark too. Giving access directly to the tables will expose potential misuse and leave your database vulnerable to inexperienced users. Use forms to input data in a controlled environment, use reports to format the output of your data and queries to control how data is calculated and filtered.
You may think this is pretty obvious when you sit back and think about them. I know you are itching to get developing and build, build, build but you must plan ahead and tick off the above list which to be fair are not the only rules but they are essential ones!