Neville
Silverman

Visual Basic Programmer
Microsoft Access Database Programmer

Custom Built Software 
Sydney, Australia

Microsoft Access Database: Front-End Options

Administration database systems 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

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 system. 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 be easily created to handle one-to-many Table relationships
  • Optimistic record Locking is used

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
  • With large records, Bound Forms are slow to load
  • Records remain locked for the life of the Form
  • Multiple Field editing is complicated and error prone
  • Field by Field updating creates high Network traffic and high Server overhead
  • Response times degrade as Table volumes 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
  • User knowledge of Access quirks (premature exiting, escape, undo, etc) is needed
  • The Access error messages are not user friendly
  • Bound forms have too many quirks for a reliable system
  • 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 depends upon optimal tuning – minimal Record sizes, minimal Table sizes, normalised Tables, optimal indexing, regular compacting, fast Network speed, etc, etc.

The development of an Administration system using Unbound Forms is a high risk option.

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 system is user-friendly
  • The system is resource efficient
  • Most of the Access quirks 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 Form
  • Field and Code naming standards can be enforced

The negatives

  • This option relies heavily on the clunky Access Forms
  • VBA coding relies on obsolete Windows APIs
  • Security updates may invalidate 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 with an Unbound Front-End is still a viable option for the small company. But Microsoft Access is no longer regarded as an ideal development environment for Business systems.

Using a Visual Basic Front-End

This Visual Basic 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 alternative
  • Visual Basic.Net has greater functionality than the Access Database 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 Front-End and the Back-End database can be easily upgraded to handle SQL Server requirements

The negatives

  • I can't think of any!

And some advice on adopting Visual Basic.Net

  • Avoid Object Oriented Programming like the plague – Visual Basic projects have no requirement for OOP
  • Maintain strict Visual Basic program coding standards

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.

The chance of an irrecoverable Database occurring with Bound Forms is much greater than with Unbound Forms. Even worse, is the chance of data corruption going unnoticed with Unbound Forms.

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 SQL Server becomes a necessity. SQL Server's recovery process will ensure no loss of data.

Microsoft Access: Database Communication

There has been a gradual evolution in Microsoft's Database access technology over the years.

DAO (Data Access Objects)

  • Data Access Objects (DAO) was used to create and manipulate Access .mdb databases in code.

ADO (ActiveX Data Objects)

  • Microsoft introduced ADO in October 1996 as a replacement for DAO. ADO can access any data source (i.e. an Excel spreadsheet). ADO eases the transition to an SQL database. ADO is mainly used with the Unbound Form option.

ADO.Net (ActiveX Data Objects)

  • ADO.NET accesses data using disconnected Datasets – where the connection is not maintained. The disconnected Dataset can be modified, and later updated when the connection is re-established. ADO.Net is only available with the Visual Basic Front-End option.

Using the latest ADO.Net Database communication technology will greatly improve the efficiency of a database system.