Ensure a Maintainable Access Database System
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 system – and explains why problems inevitably arise.
Microsoft supply sample templates of small Access database systems 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 system 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 system
Over many years a large investment in time, resources and effort will have been put into the Access database system. The system, however unmaintainable, cannot just be thrown away.
Management is faced with creating a new system, and somehow coping with the old system for an extended period of time. Or evolving the current system by fixing the major issues.
How to improve the Access system
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 system that can be readily maintained and support the ongoing administration requirements of a growing company and its users.
Creating a Maintainable system
These are the steps that typically need to be taken:
Maintainability: A check-list of actions needed
- Introduce a cohesive error handling system
- 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 system, and may be the starting point for adding further functionality. Or it may provide a sufficient safety margin of time for an alternative administration system to be built.