Microsoft Access to SQL Server Migration

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 system management become increasingly problematic. The volumes and functionality requirements of an Access database will often exceed the original concept.

Database migration is then essential for administration systems so that they can be provided with a more secure and robust environment.

Learn SQL Server

By keeping the application within the Microsoft family of products (Microsoft Access and MSSQL), and engaging an experienced consultant, the upgrade process can be manageable and cost effective.

MSSQL Maintenance

Before your upsizing project is deployed, there should be an administrative plan in place for the new MSSQL 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 using a high speed network connection to the SQL Server database of an Internet Service Provider. This may prove a cost effective alternative to avoid the maintenance overhead.

Linking Microsoft Access to MSSQL

One of the upsizing options for MSAccess is to continue using the forms, reports, macros and code you have already have - and replace the MSAccess BackEnd database with a SQL Server database. This allows the best of both worlds: the ease of use of an existing MSAccess database FrontEnd - with the reliability, speed and security of MSSQL.

MSAccess has the ability to Link (using ODBC) to an SQL Server database for the table data. All table data is moved to the SQL Server database, 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 the SQL Server database occurs through the Microsoft Jet engine. The Jet engine must translate every query and data access operation to MSSQL compliant commands. This adds overhead in performance, and additional MSSQL license connections are required.

This alternative is the best and cheapest for Microsoft Access applications with a small number of users.

Using ActiveX Data Objects (ADO)

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 one by one the inefficiencies in Linked Table usage are identified.

Access Data Projects (ADP)

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.

The ADP Front-End uses 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 MSSQL. 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 and this option is a dead-end.

Using.NET technologies

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.

Internship With MTT - First Batch Completed!

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.

SQL Server Express 2008 Express (SSE or SSX)

MS SQL 2008 Express is a scaled down, free edition of SQL Server. MS SQL Express makes it easy to develop applications that need database management capabilities.

MS SQL 2008 Express comes in 3 editions:

MS SQL 2008 Express - just the basic database engine

MS SQL 2008 Express with Tools - includes Management Studio

MS SQL 2008 Express with Advanced Services

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. This should be sufficient for the upgrade of small Microsoft Access systems. Note that SQL Server 2008 Express can only work with other 2008 versions.

SQL Server Express is ideal for the small company and also for the Microsoft Visual Basic developer. If data volumes or the traffic increases, the live system can be readily migrated to the full blown version of SQL Server.

Neville Silverman, based in Sydney Australia, has been a Visual Basic programmer and Microsoft Access programmer and Database design specialist for many years.

He has created numerous Microsoft Access databases, SQL Server Databases and Microsoft Visual Basic systems for clients. He develops and supports software systems for the small to medium sized business. Administrative systems are custom built to fit company requirements - software solutions that are cost effective, efficient and user-friendly. Visit https://www.manzoorthetrainer.com/

Resource