Welcome to Bucaro TecHelp!

Bucaro TecHelp
HTTPS Encryption not required because no account numbers or
personal information is ever requested or accepted by this site

About Bucaro TecHelp About BTH User Agreement User Agreement Privacy Policy Privacy Site Map Site Map Contact Bucaro TecHelp Contact RSS News Feeds News Feeds

Conversion of Access Database Systems to Visual Basic Dot NET and SQL Server

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.

RSS Feed RSS Feed

Follow Stephen Bucaro Follow @Stephen Bucaro

Fire HD
[Site User Agreement] [Privacy Policy] [Site map] [Search This Site] [Contact Form]
Copyright©2001-2024 Bucaro TecHelp 13771 N Fountain Hills Blvd Suite 114-248 Fountain Hills, AZ 85268