Visual Basic Programmer: Access database Programmer
Neville
Silverman

Visual Basic Programmer
Microsoft Access Database Programmer

Custom Built Software
Australia

Ensure a Maintainable Access Database Application

Ensuring a Maintainable Access DatabaseAn 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.