Conversion of Access Database Systems to Visual Basic Dot NET and SQL Server
By Neville Silverman
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.
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.
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
