Ensure a Maintainable Access Database Application
An Access database can be created without the complexity of Visual Basic programming, or any knowledge of how a relational database works – this is the start of many a company's administration Application – and explains why problems inevitably arise.
Microsoft supply sample templates of small Access database Applications that can be readily used – the software is easily set up and works well, although with limited functionality.
Microsoft Access: From simplicity to complexity
Macros, Tables, Forms, Queries, Modules, Reports and odd bits of coding are introduced willy-nilly. The limitations of the ad hoc design, the limitations of inexperienced programmers, complexity and higher volumes will finally take their toll. The database Application becomes error ridden, unfriendly to the users and costly to maintain or enhance.
And the final blow occurs when a new release of Microsoft Access requires revision of the underlying logic.
Managing the transition to a new Application
Over many years a large investment in time, resources and effort will have been put into the Access database Application. The Application, however unmaintainable, cannot just be thrown away.
Management is faced with creating a new Application, and somehow coping with the old Application for an extended period of time. Or evolving the current Application by fixing the major issues.
How to improve the Access Application
A strategic plan needs to be agreed and put into effect, to gradually evolve to a more professional structure. What is required is a database Application that can be readily maintained and support the ongoing administration requirements of a growing company and its users.
Creating a Maintainable Application
These are the steps that typically need to be taken:
Maintainability: A check-list of actions needed
- Introduce a cohesive error handling Application
- Restructure the database for efficiency and with the correct indexes
- Identify all objects, routines and properties with inefficiencies
- Eliminate the thousands (no exaggeration) of problems that can arise
- Ensure that database record handling is efficient
- Delete all unused procedures and variables
- Ensure that all objects are eliminated after use
- Delete all redundant Forms, Queries, Reports, etc – Access databases are often used as a rubbish dump
- Ensure that all variables are Type Cast for efficiency and maintainability
- Enforce coding standards where possible
- Ensure that the record locking strategy is efficient and sufficient
And of course, all the changes must be thoroughly tested.
A stable Access Database platform
All of this will stabilise the Access database Application, and may be the starting point for adding further functionality. Or it may provide a sufficient safety margin of time for an alternative administration Application to be built.