Microsoft Access to show its age. Most of the techniques (file server, VBA, DAO, database engine, Jet, etc.). Again 20 years or more. Microsoft will continue to support VBA for years to come, but the focus is changing rapidly, which supports visual programming Basic.Net and SQL Server.
Enterprise management system, a combination of the interface VB.NET with SQL Server database support is ideal. Visual Basic is the rule of Custodian of the business-oriented and SQL-effective and easy to maintain.
Reasons VB.NET Conversion Upgrade
Reasons to upgrade from Microsoft Access to Visual Basic.Net and SQL Server database is:
The company is a strategic decision
There is concern on the future of Visual Basic for Applications (VBA)
Difficult and expensive to support the law is outdated and error-torn
Difficulties and costs to support the law written by amateurs there a standard material
Need to replace the system is only effective management “like grown-turvy, upside down”
This model replaced the need for access to more efficient practical alternative
The need to record transactions and recovery to ensure data integrity of the system
Quirks and access to many forms of forced
Performance issues and adjust the constant
High network traffic
Limited number of users at one time
Table limited storage capacity record
Demanding more security
For this reason, can not convert the current administrative systems to gain access to VB.NET and SQL Server database, there is a necessity.
And new applications will be designed in the administration today by the professional developer using VB.NET front and rear database SQL Server.
The proposed conversion strategies
It is almost impossible to change the existing code VBA, and access forms and reports on the NET. It is more cost effective to try to attract business logic rules that are not otherwise, and start from scratch to create new forms of interfaces and the use of SQL to the database backend.
This note details the strategies needed to reduce some of the obstacles that will be filled with the conversion process, and reduces the programming effort.
Determine the business rules
Models used more than logic in VBA code to support user interface – no sense in an environment of VB.NET. There would be little benefit from any attempt to change the shape of an icon. Should VBA to VB.NET conversion efforts focus on identifying and changing the business rules contained in the unit code VBA.
Conversion strategies for decision
The project – and there is the option of using models or multi-format MDI tab.
The list – should be enough TreeView control to determine the MDI form
Form – this form is likely that the use of a bound or unbound.
Reports – Crystal Reports or SQL Server Reporting Services (Business Intelligence Development Studio – are no longer supported) and issue a report similar to the old Access report.
It is still possible to call an Access database and Access reports from VB.NET. This could make the process of rapid transformation.
Table – and can be re-designed because of the lack of normalization or index is incorrect. The gallery can be added the prefix default dbo_ scheme which uses SQL Server to each table name.
Standard implementation of the VBA
Efforts to convert a lot depends on the standard used by the programmer by access code – usually, depending on the expertise of a programmer and age when first established system, the standards leave much to be desired.
And apply the coding and unit standards that will facilitate the return to the first access system. There are some changes that can be done to make easier the conversion code is then:
Follow the line
Reporting of all variables of type
I use the prefix “m_” or “g_” units or global variables
Make sure that the global variables needed at the global level
Option is set clearly in each symbol of the unity
Add the variables used but not declared
Add the data type for all variables, except the dimensions or parameter
Adding a return type of data to all the functions of
The use of mathematical functions such as DateAdd until now, not ‘+’ or ‘-’
Elimination of All Eval function
Avoid, if possible, and build a ‘bang’ -! Customers are customers coordinate
Would be very useful if the control plane can be named with descriptive names, rather than Text2, or Text1 Command1, Command2 it. Unfortunately, you can not change the name of the field cause problems, because it can not be mentioned in the soil in other forms, macros, reports, units or questions. After these changes, and access to the system is usually a nightmare to debug.
Convert DAO to ADO and ADO.Net
Will convert the DAO records, and serves as Querydefs to either noise or ADO.Net. Replaced ADO.Net bang, bang, but still supported. If you are familiar with ADO, and stick with it – a simple and effective as ADO.Net.
For more of a challenge, in an attempt to substitute LINQ to ADO or ADO.Net. But for the final production system, pending the implementation of more efficient than LINQ in Visual Studio 2012.
After the initial conversion in VB.NET
Will have a data type constants.
All the variables in the function or routine, by default, and declared with the keyword ByVal. And must in any place are converted value for the keyword ByRef.
It should be optional parameter in functions and procedures sub predetermined value.
Constants have this kind of data is clear.
Use option strict to ensure efficiency and avoid the type of data errors.
Enter the option to show.
Welcome to the Visual Basic object-oriented happiness!
Neville Silverman is a Microsoft software developer based in Sydney, Australia, and was a Visual Basic and Microsoft Access programmer designer and programmer for several years.
Has created a Microsoft Access database and SQL Server database and Microsoft Visual Basic for client systems. By developing and supporting software systems for small and medium enterprises. Management system is custom built to meet the needs of companies – software solutions that are cost-effective, efficient and easy to use.
Access to the database is a specialized system of optimism. Gave his life more than a system using a database Access, and avoid the effort and costs of developing a service provider.