Visual Basic Programmer: Access database Programmer

Visual Basic Programmer
Microsoft Access Database Programmer

Custom Built Software

Microsoft Access Database Solutions

Access Administration database Applications are comprised of a common Back-End Server, and multiple Client PCs.

Microsoft Access Database Development Models

There are basically three Front-End (i.e. Client) options to choose from. The Front-End can consist of:

  • An Access database with Bound Forms
  • An Access database with Unbound Forms
  • Visual Basic.Net

For all three options, the Back-End Server is an Access Database. An MS Access Database Back-End is ideal for a small business administration Application. The Access Database has a capacity limit of 2 gigabytes – more than adequate for most medium sized companies.

An Access Database Front-End using Bound Forms

Forms and Sub Forms are quickly created using the Form Wizard. For simple or one-off applications, the Bound Form is ideal.

Bound Fields of a Bound Form are linked directly to a Back-End Table. When any Field within a Bound Form is altered, the changes are immediately written to the Back-End Table. Every time the cursor moves between records, the record is saved to the underlying Back-End Table.

The positives

  • This option is ideal for Prototyping
  • A simple solution can be quickly created
  • The Bound Form Wizard provides for selecting, adding, deleting or modifying records
  • Sub Forms can easily be created to handle one-to-many Table relationships (e.g. the Invoices of a Client)

The negatives

  • There is a high drain on Server resources
  • User numbers are limited by performance issues
  • Constant tuning is required with increased functionality, high activity or as volumes grow
  • The Database connection remains open for the life of the Form, limiting usage
  • Records remain locked for the life of the Form, limiting usage
  • With large records, Bound Forms are slow to load
  • Multiple Field editing is complicated and error prone
  • Field by Field Database updating creates high Network traffic and high Server overhead
  • Response times degrade as Table volumes and user numbers increase
  • There is a chance of data corruption and an irrecoverable Back-End database
  • There are data recovery issues with linked tables from power outages or network problems
  • Security updates may invalidate ActiveX controls and procedures
  • In-depth knowledge of Access quirks (premature exiting, escape, undo, etc) is needed
  • The Access error messages are not user friendly
  • Bound forms have too many eccentricities for a reliable Application
  • Constant upgrades of Microsoft Office Professional software are required
  • Multiple Microsoft Access licences can be expensive
  • Systems can become unwieldy, difficult and expensive to modify
  • Much of the Access logic is hidden and difficult to maintain
  • Security can be easily broken
  • Upgrading the Access Back-End database to SQL Server is not advisable

The number of simultaneous Users varies from 5 to a maximum of 10. This maximum User number can be achieved only for very small database Applications. It depends upon optimal and constant tuning – minimal Record sizes, minimal Table sizes, normalised Tables, optimal indexing, regular compacting, fast Network speed, etc, etc.

The development of an Administration Application using Unbound Forms is a high risk option, and is not advisable.

An Access Database Front-End using Unbound Forms

This is the more professional alternative. Unbound Forms take longer to create than Bound Forms – but not by that much.

The positives

  • The Application is user-friendly
  • The Application is resource efficient
  • Most of the Access eccentricities are eliminated
  • There is complete control over editing, saving and deleting records
  • The Back-End and Front-End databases can be easily upgraded to SQL Server
  • There is greater control in reading and writing of records
  • Records are updated only when all fields are valid
  • Error handling is user-friendly
  • There is a reduction in concurrency issues (record in use problem)
  • Data integrity can be assured
  • The Access Unbound Form is easily converted to a Visual Basic.Net Form
  • Field and Code naming standards can be enforced

The negatives

  • This option relies heavily on the clunky and inefficient Access Forms
  • The obsolete VBA coding relies on obsolete Windows APIs
  • Security updates have invalidated ActiveX controls and API Procedures
  • Constant upgrades of Microsoft Office Professional software are required
  • Multiple Microsoft Access licences can be expensive
  • Security can be easily broken

Microsoft Access Forms are not regarded as being a suitable development environment for Business Applications.

Using a Visual Basic.Net Front-End

The Visual Basic.Net solution can take less time to create compared to the Access Unbound Form alternative.

The positives

  • There is an improvement in record retrieval efficiency.
  • The solution is more user-friendly than any Access Database Forms alternative
  • Visual Basic.Net has greater functionality than with the Access Database Forms solution
  • There is no reliance on Access versions and upgrades and security fixes
  • Users do not have to have Microsoft Office Professional installed
  • The latest technology is used
  • The Report writer of Microsoft Access can still be used
  • Microsoft Word and Microsoft Excel can be easily invoked
  • The Back-End Access Database can be easily upgraded to a SQL Server Database

Some words of caution

Microsoft Access: Backup and Disaster Recovery

It is essential to prepare for the worst scenario – as the loss of a day's work is entirely possible. Regular Backups of the Back-End database are vital.

Recovery is simply a matter of replacing the corrupted database with a previous version. All work done from the time of the Restore will need to be re-entered.

When the problems of data loss (and re-entry) becomes critical, a conversion of the Access Database Back-End to a SQL Server Database becomes a necessity. SQL Server's recovery process will ensure no loss of data. SQL Server will also allow a far greater number of users to use the Application.