Administration system of many companies, the front end of VB.Net and backend with SQL Server is the perfect. SQL Server is systematic 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 need to replace clunky Access Forms with a more efficient alternative
- 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
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.
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
- 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
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.