Visual Basic Programmer

Microsoft Access Database Programmer

Access Database to VB.Net and SQL Server – Conversion Strategy

Microsoft Access is showing its age. Most of the techniques (File Server, VBA, DAO, Jet Database Engine, etc) hark back 20 or more years. Microsoft will continue supporting VBA for many years to come, but the emphasis is rapidly changing in favour of Visual Basic.Net.

The reasons for an upgrade from Microsoft Access to Visual Basic.Net are:

• A strategic corporate decision
• A worry about the future of Visual Basic for Applications (VBA)
• The difficulty and cost in supporting obsolete and error ridden code
• The need to replace clunky Access Forms with a more efficient alternative
• The need for Transaction logging and disaster recovery to prevent data loss
• The many quirks of Access bound forms
• The performance problems and constant tuning
• The high Network traffic
• The limited number of simultaneous users
• The limited Table record storage capacity
• Greater security requirements

For these reasons, the conversion of existing Access administrative systems to VB.Net and SQL Server may become an imperative.

Most new business applications will be designed by the professional developer using a VB.Net Front-End and a SQL Server Back-End database.

Suggested Conversion Strategy

With an awful lot of hand coding and hard work, it is just about possible to convert existing VBA code, Access Forms and Reports to VB.NET. It is however quicker and more cost effective to extract only the existing business rules logic, and start from scratch creating new Forms.

This note details the strategy to minimise some of the hurdles that will be met with the conversion, and reduce the programming effort.

The Conversion Process

Most of the VBA code logic is used for the support of the User Interface – it has no meaning in the VB.Net environment. There will be little or nothing to be gained from any attempt to convert the Form related code. The VBA to VB.Net conversion effort should concentrate on the identification and conversion of business rules contained in the VBA code modules.

• Project - There is the option of using an MDI Form or a Multi-Tabbed Form.
• Forms - New VB.Net forms should be designed from scratch. There is the option to use Bound or Unbound forms.
• Reports - Crystal Reports, SQL Server Reporting Services or Business Intelligence Development Studio (BIDS) will produce reports similar to the old Access reports. However it is still possible to call an Access Database and the Access reports from VB.Net. This can make the transition process easier. See SQL Server Reporting
• Tables - These may have to be redesigned due to lack of normalisation or incorrect indexing. The 'dbo_' default schema prefix that SQL Server uses may have to be added to each Table name.

Implementing VBA Standards

The conversion effort very much depends upon the standards used by the programmers – and usually, depending upon the experience of the programmers and the age of inception of the system, the standards will leave a lot to be desired.

Standards that will ease the conversion should be implemented first in the Access system. There are several modifications that can be done to make the later code conversion easier:

• Consistent indentation
• Declared all Variables with a Type
• Use a prefix of say "m_" or "g_" for Module or Global variables
• Ensure that Global variables are needed globally
• Set Option Explicit in each code Module
• Add Variables used but not declared
• Add a data Type to all Variables, whether dimensioned or a parameter
• Add a return data type to all Functions
• Use functions like DateAdd for date arithmetic, instead of '+' or '-'
• Eliminate all Eval functions
• Avoid, where possible, the 'bang' construct – i.e. Forms!Customers!CustomerID

It would be helpful if Control fields could be renamed with descriptive names, instead of Text1, Text2 or Command1, Command2. Unfortunately changing a Field name can cause problems, as the Field may be referred to in other Forms, Macros, Reports, Modules or Queries. After any such changes, the Access system usually becomes a nightmare to debug.

Converting DAO to ADO or ADO.Net

DAO functions like Recordsets and Querydefs will need to be converted to either ADO or ADO.Net. ADO.Net is newer than ADO, but ADO is still supported. If you are familiar with ADO, stick with it – it is simpler than and just as efficient as ADO.Net.

After the Initial Conversion Attempt

• Constants will need a data type.
• All variables in Functions or Subroutines will, by default, be declared with a ByVal keyword. Wherever a value needs to be returned, the keyword should be changed to ByRef.
• Optional parameters in Function and Sub routines must have a default value.
• Constants will need explicit data types.
• Use Option Strict to ensure efficiency and avoid data Type errors.

Welcome to the joys of Object Oriented Visual Basic!