Microsoft Access Database: The 5 Most Common Tasks in Understanding an Access Database

I’m often hired to carry out and audit an Access database application that has been built and re-developed over the years due to:

1. The developer (perhaps a former employee) has since left the organisation and left no documentation.
2. An individual has accidentally locked a database and can not gain access which requires an official ‘hack’.
3. Some of the components to the Access database has been lost and users need some new functions added.
4. New administrators have been given a database and do not have enough knowledge to extend the system reports.
5. The client wants an honest opinion on how well (or poorly) designed an Access database has been built and implemented.

But with a little bit of training and know how, there are some basic but simple tasks that users can carry out for themselves to breakdown and understand an Access database before calling in the repairman/woman.

I have 5 common tips to help you on your way:

1. With most well designed Access database systems, you normally start with a welcoming screen (an Access form) which will need to be disabled when starting the application and instead get either the database window (Access 2003 or earlier) or the navigation pane (Access 2007 and later). You need to hold down the SHIFT key from the keyboard before starting the application which will ‘ByPass’ the startup form and take you into the background environment.
2. With the database window or navigation pane, you need to make sure that all objects are visible (as some could well be hidden). This will require that you switch on the ‘Show Hidden Objects’ switch which is found in either the ‘Tools‘, ‘Options‘ menu (Access 2003 or earlier) or the ‘Office/Backstage‘ buttons (Access 2007 or later).
3. With the visible tables, ascertain if they are local (physical) tables or linked tables to other databases (including Microsoft Access). This is easily identified by the icon presented. A simple table icon (which looks like a table grid) is a real physical table and a link table has a blue pointing arrow (to the right) with the same icon symbol. If you database is using linked tables, you will need to source the database file and audit that system too (probably first).
4. A useful utility called the ‘Database Documenter‘ can be run to produce a long and detailed report of all selected objects which covers the design elements, indexes and security (where applicable). This a very detailed report but it shows the technical information required to understand the structures in your database. Search the Access help to know how to run his tool – it’s simple to use.
5. Another useful utility is to run the ‘Object Dependency‘ tool for a selected object which displays a window pane show what other objects that have a relationship which depend on the selected object as its source. Again, use the Access help system to locate how to run it.

You will need to put some time aside to carry out the above tasks especially if it is quite a large database (say over 100 objects combined) but with patience and determination you will soon have documented your own Access database and take control over it.