Microsoft Access Forms at the Crossroads
Twenty years ago, when a small company created a new administration system, the obvious choice was Microsoft Access. That is, a Microsoft Access Front-End database was installed on each user's PC and a Microsoft Access Back-End database was used to store data on a Server.
Nowadays, Microsoft Access as the Front-End is no longer a suitable choice. Much has happened in the intervening years. A Microsoft Access Database still has much in its favour. It is still an easy method for creating a simple system with the minimum of bother. The wizards for Reporting and Queries are still very powerful. Visual Basic for Applications, although 20 years old, is still effective and efficient.
A Microsoft Access Database, as a repository of a company's records, is convenient and cheap. It is still ideal for a multitude of applications – except those that have high volume, high capacity or are mission critical.
The problems with using Access as a Front-End
Microsoft Access was originally intended a single user database. This was extended to allow communication from the single user database to a Server based database. This worked, after a fashion, but was not ideal.
The technology (File Server, VBA, DAO, Jet Database Engine, ODBC, etc, etc) used by Microsoft Access harks back 20 years – which in technological terms, is equivalent to the Stone Age. This Network technology has been replaced and improved many times over.
Microsoft Access, as a Front-End Form container, is lumbering and inefficient. There are just too many problems:
- The overhead and traffic on Server and Network resources is high
- User numbers are limited by performance issues
- Constant tuning is required
- There is a high chance of data corruption at peak times
- Constant upgrades of Microsoft Office Professional software are required
- Microsoft Access licences are expensive
- The legacy VBA code is difficult to maintain
- Much of the Access logic is hidden and difficult to maintain
- Security can be easily broken
- The forms are inefficient and clunky
- Start up is slow
- Compression on exit is slow.
Visual Basic.Net is a much better and cost effective alternative
Visual Basic is now favoured as the most effective vehicle for the creation of the Front-End user interface.
Compared to using Microsoft Access Forms:
- Administration systems can be produced quicker
- Visual Basic Forms have none of the inefficiencies or problems
- Visual Basic Forms are more robust and scalable
- The cost of development is far less
- The system Networking overhead is far less
- Maintenance costs are minimal