Convert Access Database to Visual Basic.Net
Microsoft Access is showing its age – the underlying techniques (File Server, VBA, DAO, Jet Database Engine, ODBC) hark back 20 or more years.
Microsoft will continue supporting the obsolete VBA for many years to come, but the programming emphasis has changed irrevocably in favour of Visual Basic.Net.
For company administration systems, the combination of a Visual Basic Front-End with an Access database Back-End is the ideal. Visual Basic is business rules oriented – and an Access database is cheap, efficient and easy to maintain.
Visual Basic.Net Upgrade Conversion Reasons
The reasons for an upgrade from a Microsoft Access Front-End database to a Visual Basic.Net Front-End are:
- The difficulty in supporting Visual Basic for Applications (VBA)
- The difficulty and cost in maintaining obsolete and error ridden code – and most Access code contain a multitude of problems
- The difficulty and cost in supporting routines written by amateurs without regard to coding standards
- The need to replace inefficient administration systems that just "grew like Topsy"
- The need to replace clunky Access Forms with a more efficient alternative
- The many idiosyncrasies of Access Database bound forms
- The performance problems and constant tuning required
- The high Network traffic
- The limited number of simultaneous users
For these reasons, the conversion of the existing Microsoft Access Front-End and Forms to Visual Basic is usually an imperative.
Most new administration applications will nowadays be designed by the professional developer using a VB.Net Front-End. A quick'n'dirty Access Front-End may still be used for one-off testing or prototyping.
Microsoft Access to VB.Net: Suggested Conversion Strategy
It is almost impossible to automatically convert a Microsoft Access Form with its attached VBA code to a modern Visual Basic.Net Form. It is much more practical to attempt to extract only the existing business rules logic, and start from scratch creating new VB.Net Front-End Forms.
This note details the strategies needed to minimise some of the hurdles that will be met with the conversion process, and reduce the programming effort.
Identify the Business rules in the VBA code
Most of the VBA code logic in Forms is used in support of the User Interface – it has no meaning in a modern VB.Net environment. There will be little 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 VBA code modules and the Form's VBA code.
A summary of conversion effort required
These are the issues facing the developer:
- Project – There is the option of using an MDI Form or a Multi-Tabbed Form
- Menu – A TreeView control would amply replace the old Switchboard or Menu Form
- Forms – The existing Forms need to be completely rewritten
- Modules – Business logic code should be extracted
- Reports – The existing Microsoft Access reports can still be used
- Tables – The existing Back-End Microsoft Access Tables can still be used
Implementing Visual Basic.Net Standards
The conversion effort very much depends upon the coding standards used by the previous Microsoft Access programmers. Usually, depending upon the experience of the programmers and the age when the system was first created – the standards will leave a lot to be desired.
Module coding standards that will ease the conversion should be converted first. There are several modifications that can be done initially to make the later code conversion easier:
- Consistent indentation
- Declare all Variables with a Type
- Use a prefix of say "m_" or "g_" for Module or Global variables
- Ensure that Global variables declared are indeed needed globally
- Set Option Explicit in each code Module
- 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
- Eliminate all 'bang' constructs – 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.
And finally, use an Access code analyser to identify major problem areas.
Converting DAO to ADO
DAO functions like Recordsets and Querydefs will need to be converted to either ADO or ADO.Net (essential for Web development). ADO.Net has replaced 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 for the desktop application.
After the Initial Conversion to Visual Basic.Net
In order to get the new VB.Net system working efficiently and eliminating code warnings, the following issues need to be attended to:
- 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 an explicit data type.
- Use Option Strict to ensure efficiency and avoid data Type errors.
- Set Option Infer to Off – it best to declare all variables.
Welcome to the joys of Visual Basic.Net!