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 and obsolete, 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 obsolete 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