VB Programing support Access Database Microsoft Office VB.Net & SQL Server nev@NevVB.com.au Sydney, Australia (612) 9453-0456 Contact Details 23/02/2012
The ease of use and power of Microsoft Access comes at a price. As a company or the functionality required grows, the issues of data security, reliability, and management become increasingly problematic. The volumes and response requirements of an Access database will often exceed the original concept. Database migration is essential for systems to move to a more secure and robust environment. By keeping the application within the Microsoft family of products (Microsoft Access and SQL Server), and engaging an experienced consultant, the upgrade process can be manageable and cost effective.
Before your upsizing project is deployed, there should be an administrative plan in place for the new SQL Server system. The database administrator (DBA) needs to create backup strategies, recovery, administrative procedures, automation, optimisation, etc. For the small company, there is the option of a high speed network connection to SQL Server of an Internet Service Provider. This may prove a cost effective solution.
One of the key benefits of Access when it comes to upsizing is that you can redesign your application to continue to use the forms, reports, macros and code you have already designed in Access, and replace the Jet engine with SQL Server. This allows the best of both worlds: the ease of use of Access, with the reliability and security of SQL Server.
Microsoft Access has the ability to link (using ODBC) to SQL Server for table data. All table data is moved to SQL Server, leaving all forms, reports, queries and logic in the existing Access database. Because the existing application logic is largely unchanged, this is the most cost-effective migration technique. For a small effort, the benefits (reliability, security, maintenance, etc) of SQL Server can be achieved.
The disadvantage of this approach is that all access to SQL Server occurs through the Jet engine. The Jet engine must translate every query and data access operation to SQL Server compliant commands. This adds overhead in performance, and additional SQL Server license connections are required.
This alternative is best for Microsoft Access applications with a small number of users and small database sizes.
For greater efficiency, some of the SQL Server Tables may need to be accessed using the ADO and OLEDB (replaces ODBC) technologies. Some changes are needed – the Jet database engine uses different data types, and a different SQL grammar from SQL Server.
A combination of Linking small Tables and using ADO for large Tables is most practicable. This can be a phased implementation, as inefficiencies are identified in Linked Table usage.
ADP is an alternative to the usual File/Server configuration with an Access Front-End and an Access Back-End database. The user-friendly Forms and Reports, as well as the VBA are managed as before in an Access ADP Front-End database. Using a Client/Server configuration, all Tables and Queries are stored in an SQL Server database. The advantage of this arrangement is the ability to use the highly efficient Views and Stored Procedures of SQL Server. Most of the work is handled on the Server, minimising Network traffic and the consequent bottlenecks.
All new Access projects that could possibly evolve into corporate databases, should use ADP as the Front-End and SQL Server 2008 Express (SSX) as the Back-End database. The SSX database is free, and can be readily migrated to the full-blown version of SQL Server. Using ADP initially will avoid any problems of a later migration.
ADP may be superseded by SharePoint, so this may not be the best option. Also, there have been few ADP enhancements over the last 10 years. It would seem that support has been dropped.
If Microsoft Access is no longer able to keep up with an organisation's requirements, the project will have to be redesigned from scratch. New technologies such as Visual Basic.Net and ASP.Net can be used to rewrite the application.
The key advantage of this approach is flexibility. You can create an application that can target Windows desktops or the Web. It is perfectly feasible and cost effect to use an ASP.Net Website for an administration system, especially where the users are geographically dispersed.
Microsoft SQL Server 2008 Express is a scaled down, free edition of SQL Server. SQL Server Express makes it easy to develop applications that need database management capabilities.
SQL Server 2008 Express comes in 3 editions:
The Advanced Services edition is full-featured and includes Full Text Search, Reporting Services and Report Designer. It also has an Import and Export Wizard, making it easy to transfer data from a Microsoft Access database to an SQL Server 2008 Express database.
There are no limitations on the number of databases or users. SQL Server 2008 Express is however limited to one processor, 1 GB memory (the excess will not be used) and 10 GB database files. More than enough for any Microsoft Access upgrade. Note that SQL Server 2008 Express can only work with other 2008 versions.
SQL Server Express is ideal for the small company and the Microsoft Visual Basic developer. The SQL Server Express database can be used for a Website or as a Back-End to an Access application. If data volume or traffic increases, the live system can be readily migrated to the full version of SQL Server.