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 programming emphasis is rapidly changing in favour of Visual Basic.Net and SQL Server.
For company administration systems, the combination of a VB.Net FrontEnd with an SQL Server BackEnd database is the ideal. Visual Basic is business rules oriented and SQL Server is efficient and easy to maintain.
VB.Net Upgrade Conversion Reasons
The reasons for an upgrade from Microsoft Access to Visual Basic.Net and an SQL Server database 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 difficulty and cost in supporting code written by amateurs without regard to standards
- The need to replace an inefficient administration system that just “grew like Topsy”
- The need to replace clunky Access Forms with a more efficient alternative
- The need for Transaction logging and recovery to ensure system data integrity
- 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 an SQL Server database may become an imperative.
Most new administration applications will nowadays be designed by the professional developer using a VB.Net Front-End and an SQL Server Back-End database.
Suggested Conversion Strategy
It is almost impossible to convert existing VBA code, Access Forms and Reports to VB.NET. It is much more cost-effective to attempt to extract only the existing business rules logic, and start from scratch creating new FrontEnd forms and using an SQL database as the BackEnd.
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
Most of the VBA code logic in Forms 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.
The Conversion Strategy Decisions
- Project – There is the option of using an MDI Forms or a Multi-Tabbed Form.
- Menu – A TreeView control should be adequate to select MDI Forms
- Forms – There is the option to use Bound or Unbound forms.
- Reports – Crystal Reports or SQL Server Reporting Services (Business Intelligence Development Studio – is no longer supported) will produce reports similar to the old Access reports.
- It is still possible to call an Access Database and the Access reports from VB.Net. This can make the transition process quicker.
- 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 coding standards used by the previous Access programmers – and 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 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 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 more adventuresome, try the LINQ alternative to ADO or ADO.Net. But for the final production system, wait for the more efficient implementation of LINQ in Visual Studio 2012.
After the Initial Conversion to VB.Net
- 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.
- Set Option Infer off.
Welcome to the joys of Object Oriented Visual Basic!
AUTOPOST by BEDEWY VISIT GAHZLY
