Conversion of Access Database Systems to Visual Fundamental Us dot Internet and SQL Server

Microsoft Access is showing wear and tear. The majority of the techniques (File Server, VBA, DAO, Jet Database Engine, etc) hark back 20 years. Microsoft continues supporting VBA for years to come, however the programming emphasis is quickly altering towards Visual Fundamental.Internet and SQL Server.

For company administration systems, the mixture of the VB.Internet FrontEnd by having an SQL Server BackEnd database is the perfect. Visual Fundamental is business rules oriented and SQL Server is efficient and low maintenance.

VB.Internet Upgrade Conversion Reasons

The reason why to have an upgrade from Microsoft Use of Visual Fundamental.Internet as well as an SQL Server database are:

A proper corporate decision

Concern about the way forward for Visual Fundamental for Applications (VBA)

The problem and price in supporting obsolete and error ridden code

The problem and price in supporting code compiled by amateurs without regard to standards

The necessity to replace an inefficient administration system that simply “increased like Topsy”

The necessity to replace clunky Access Forms having a more effective alternative

The requirement for Transaction logging and recovery to make sure system data integrity

The numerous quirks of Access bound forms

The performance problems and constant tuning

Our prime Network traffic

The small group of synchronised users

The limited Table record storage capacity

Greater security needs

Therefore, the conversion of existing Access administrative systems to VB.Internet as well as an SQL Server database can become crucial.

Most new administration applications will nowadays be created by the professional developer utilizing a VB.Internet Front-Finish as well as an SQL Server Back-Finish database.

Recommended Conversion Strategy

It’s nearly impossible to transform existing VBA code, Access Forms and Reports to VB.Internet. It is a lot more cost-effective to try to extract just the existing business rules logic, and begin on your own creating new FrontEnd forms and taking advantage of an SQL database because the BackEnd.

This note details the techniques required to minimise a few of the hurdles that’ll be met with alteration, and lower the programming effort.

Find out the Business rules

The majority of the VBA code logic in Forms can be used for that support from the Interface – it’s no meaning within the VB.Internet atmosphere. You will see little or free to become acquired from the make an effort to convert the shape related code. The VBA to VB.Internet conversion effort should focus on the identification and conversion of economic rules within the VBA code modules.

The Conversion Strategy Decisions

Project – There’s a choice of utilizing an MDI Forms or perhaps a Multi-Tabbed Form.

Menu – A TreeView control ought to be sufficient to pick MDI Forms

Forms – There’s the choice to make use of Bound or Unbound forms.

Reports – Very Reports or SQL Server Reporting Services (Business Intelligence Development Studio – is not supported) will produce reports like the old Access reports.

It’s still easy to call an Access Database and also the Access reports from VB.Internet. This could result in the transition process faster.

Tables – These might have to be redesigned because of insufficient normalisation or incorrect indexing. The ‘dbo_’ default schema prefix that SQL Server uses might have to be included to each Table name.

Applying VBA Standards

The conversion effort greatly is determined by the coding standards utilized by the prior Access programmers – in most cases, based upon the expertise of the programmers and also the age once the system was initially produced, the factors leaves a great deal to be preferred.

Module coding standards which will ease the conversion ought to be implemented first within the Access system. There are many modifications that you can do to help make the later code conversion simpler:

Consistent indentation

Declared all Variables having a Type

Make use of a prefix of say “m_” or “g_” for Module or Global variables

Make sure that Global variables are essential globally

Set Option Explicit in every code Module

Add Variables used although not declared

Give a data Type to any or all Variables, whether dimensioned or perhaps a parameter

Give a return data type to any or all Functions

Use functions like DateAdd for date arithmetic, rather of ‘ ‘ or ‘-‘

Eliminate all Eval functions

Avoid, where possible, the ‘bang’ construct – i.e. Forms!Customers!CustomerID

It might be useful if Control fields might be renamed with descriptive names, rather of Text1, Text2 or Command1, Command2. Regrettably altering an area name may cause problems, because the Field might be known in other kinds, Macros, Reports, Modules or Queries. After such changes, the Access system usually turns into a nightmare to debug.

Converting DAO to ADO or ADO.Internet

DAO functions like Recordsets and Querydefs will have to be transformed into either ADO or ADO.Internet. ADO.Internet has replaced ADO, but ADO continues to be supported. If you’re acquainted with ADO, stick to it – it’s simpler than and merely as efficient as ADO.Internet.

For that more adventuresome, try the LINQ option to ADO or ADO.Internet. As well as the final production system, wait for a more effective implementation of LINQ in Visual Studio 2012.

Following the Initial Conversion to VB.Internet

Constants will require an information type.

All variables in Functions or Subroutines will, automatically, be declared having a ByVal keyword. Wherever something must be came back, the keyword ought to be altered to ByRef.

Optional parameters in Function and Sub routines should have a default value.

Constants will require explicit data types.

Use Option Strict to make sure efficiency and steer clear of data Type errors.

Set Option Infer off.

Thanks for visiting the fun of Object Oriented Visual Fundamental!

Neville Silverman, located in Sydney Australia, is a Visual Fundamental programmer and Microsoft Access programmer and Database design specialist for several years.

He’s produced numerous Microsoft Access databases, SQL Server Databases and Microsoft Visual Fundamental systems for clients. He develops and supports software systems for that smaller business. Administrative systems are custom made to suit company needs – software programs which are economical, efficient and user-friendly.

Running business successfully would depend on how you face the challenges. When you avail the SQL server data file recovery you would get time to concentrate on some research work that would let you expand your product portfolio or service categories.