One of my tasks as an Access database consultant is to carry out and audit Access database applications that have been built and re-developed over the years due to:
- An individual has accidentally been locked out of a database and can not gain access requiring an official ‘hack’.
- The developer (a former employee) has since left the organisation and there are no technical and end-user documents.
- Some of the components to the Access database appears to be lost and users need some new functions added.
- New administrators have been given a database and do not have enough knowledge to extend the system reports.
- 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:
- With most well designed Access database systems, you normally start with a welcoming screen (which is an Access Form) which will need to be switched off and disabled when starting the application and instead have either the navigation pane (Access 2007 and later) or database window (Access 2003 or earlier) popup. You do this by holding down the SHIFT key from the keyboard before starting the application which will ‘ByPass‘ the startup procedure and load you default form taking you into the background environment.
- With the navigation pane or database window, you need to make sure that all the objects are visible (as some may well be hidden via the options settings). This will require that you switch on the ‘Show Hidden Objects‘ switch which is found in either the ‘Office/Backstage‘ buttons (Access 2007 or later) or ‘Tools‘, ‘Options‘ menu (Access 2003 or earlier).
- With the visible tables, ascertain if they are local (physical) tables or linked tables to other databases (including other Microsoft Access databases). 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).
- Another useful tool is to run the ‘Object Dependency‘ utility for a selected number object which displays a window pane view showing you what other objects that have relationships to it. Search the Access help system to locate how to run it.
- A useful utility called the ‘Database Documenter‘ can be used run and produce a long and detailed report of all selected objects which covers the design elements, indexes and security (where applicable). I usually start with just the tables as this report can get quite large and may take a while to compile. This a very detailed report but it shows the technical information required to understand the structures in your database. Again, search the Access help to know how to run his tool – it’s simple to use. You can then run this report (as many times as you like) for other objects including queries, forms and reports.
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.
One more tip for you! Most Microsoft Access database systems are deemed relational (RDBMS) and you can go and check out the ‘Relationship Window‘ view to see and create a map of your database architecture which will help in determining how your tables (and in some cases, queries) are joined together. Once again, using the Access help system will guide you through on how to use this tool or if you prefer, just browse the menu bars or ribbon bar tabs – it’s pretty easy to find!
From Ben Beitler – “Your Access Database Expert”