The Front-End: Written in Visual Basic or in Microsoft Access?
It has been the tradition when developing a new company administration Database System, to create the Front-End as a Microsoft Access database. With the popularity and productivity of Visual Basic.Net, the option of using Visual Basic as the Front-End should be considered.
Microsoft Access is geared for the small company – and the novice programmer who wants results quickly. Microsoft Access was designed for this – a meaningful and a useful application can be created within hours.
On the other hand, a Visual Basic Front-End is now a viable option, using a Microsoft Access Database as the Back-End. The system can be efficient, cost-effective and has few of the drawbacks of a purely Microsoft Access solution.
Here are the arguments for and against using each alternative:
Develop the Front-End in Microsoft Access
The good features:
- An administration system can be created quickly using the Microsoft Access Wizard to create bound forms.
- The Query and the Report designers of Microsoft Access are very friendly
- The Rapid application development (RAD) features of Microsoft Access allow it to quickly prototype any proposed system
- There are templates of various applications (like Asset tracking, Contacts, Inventory) that make initial Database design easy
- Bound forms (created by the Wizard) have too many quirks for a reliable system
- Constant upgrades of Microsoft Office Professional software are required
- Multiple Microsoft Access licences can be expensive
- There are performance problems, even with minimal users
- Constant tuning is required with increased functionality, high activity or as volumes grow
- Network traffic can be high and is difficult to optimise
- The Networking protocols have long been superseded
- There are data recovery issues with linked tables
- Transactions are not logged – crashes can cause data inconsistency and/or record loss
- Power outages or network problems can cause an unusable database due to corruption
- Microsoft Access Systems quickly become unwieldy – they are difficult and expensive to modify
- Much of the Microsoft Access logic is hidden and difficult to maintain
- The form controls and third-party software are now hard to support
- There are problems in upgrading from old Access Database formats to the new ACCDB version
- Visual Basic for Applications (VBA) has not been updated for decades, and although still powerful, is obsolete
- Security can be easily broken
There are enough negatives to make Business Systems development using a Microsoft Access Front-End an extremely high risk option.
Develop the Front-End in Visual Basic
Visual Basic.Net has matured into a highly productive programming environment. It makes programming faster and easier. Development and maintenance costs are low.
The advantages of using Visual Basic as the Front-End
- The Visual Basic Front-End will be more robust and scalable than a Microsoft Access Front-End
- Visual Basic has a huge array of features that can produce any desired effect in the Front-End Forms
- The development environment is stable and highly efficient
- Emailing can be added without the need for Microsoft Outlook or third-party software
- The Charting graphics are superb
- Reports can still utilise the Microsoft Access Database report writer
- The Back-End Microsoft Access Database can be used without modification
- The cost of development is less than that for a Microsoft Access Front-End system
- The cost of maintenance is far less than that for a Microsoft Access Front-End system
- Visual Basic is far easier to modify and enhance compared to a Microsoft Access Front-End system
- The Networking overhead is considerably less than that of a Microsoft Access Front-End system
- The Visual Basic development software – Visual Studio Community is fully-featured and free
- Minimal changes are needed on converting Microsoft Access to SQL Server
The decision to use to Visual Basic for the Front-End is easy to make.
Extending the lifespan of an Access Database
For a large and expanding company administration system, with the capacity of a Microsoft Access database limited to 2 Gigabytes, space problems are soon met.
How to reduce the size of a Microsoft Access database
- Move Temporary or infrequently changed Tables to a second Database
- Split the Back-End Database into multiple Databases
- Regularly delete historic records
- Move historic records to a separate Database
- Store BLOBs (Word Documents, Images, etc) in Windows Folders
- Store Memo fields as Windows Text files
- Compress Unicode Text fields
- Ensure that the smallest data type is chosen
- Normalise Columns and Tables to minimise redundancy
For a small to medium sized company, these measures are usually sufficient to extend the usable life of a Microsoft Access Database indefinitely.
Upgrade Microsoft Access Database to SQL Server
With large data volumes and a large number of users, the preferred option is Visual Basic as the Front-End and SQL Server for the Back-End database. This combination will use SQL Server Reporting Services for report writing.
The Visual Basic.Net program code will need minimal changes to handle SQL Server. I just change an ADO Class to an SQL Server Class. The Microsoft Access Database may need some changes, depending upon how well the Access Database has been designed.
For further details of the Access Database conversion to SQL Server, see Microsoft Access To Microsoft SQL Server Migration.
Microsoft Access will no longer be needed in the application.
Microsoft Access and simple Software Systems
An all Microsoft Access solution may still be a cost-effective solution for one-off prototypes applications.
Management must resist the temptation of adding "just one more feature" to the Microsoft Access Database application. Small systems have the habit of becoming the administrative mainstay of the company, with all the support problems inherent in an Microsoft Access Database Front-End solution.
The changing role of Microsoft Access
- Microsoft Access is no longer regarded as a suitable environment for company application development
- Microsoft Access has still an important role to play as the BackEnd Database for small companies, where two gigabyte of data is more than adequate