Neville
Silverman

Visual Basic Programmer, Sydney
Microsoft Access Database Programmer

Custom Built Software
System Optimisation
Office Automation
Phone Australia
(02) 9453-0456

Microsoft Access to Microsoft SQL Server Migration

Microsoft Access to Microsoft SQL Server MigrationThe 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, response time 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.

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

SQL Server Maintenance Planning

Before your up-sizing 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 using a high speed network connection to the SQL Server database of an Internet Service Provider – or using the cloud offerings of Amazon or Microsoft. This may prove a cost effective alternative to avoid the maintenance overhead.

Linking Microsoft Access to SQL Server

One of the up-sizing options for Microsoft Access is to continue using the forms, reports, macros and code you have already have – and replace the Access Back-End database with a SQL Server database. This allows the best of both worlds: the ease of use of an existing Access database Front-End – with the reliability, speed and security of SQL Server.

Microsoft Access has the ability to Link (using ODBC) to an SQL Server database for the 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 Microsoft 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 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 to manipulate large Tables is most practicable. This can be a phased implementation, as one by one the inefficiencies in Linked Table usage are identified.

The problems with 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 SQL Server. Using ADP initially will avoid any problems of a later migration.

There have been few (if any) ADP enhancements over the last 10 years, and support has been dropped. With no viable future, a Visual Basic Front-End is the best option for the Front-End now.

Rewrite using Visual Basic.NET and ASP.Net

If Microsoft Access is no longer able to keep up with an organisation's requirements, the project will have to be redesigned from scratch. Visual Basic.Net or 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.

SQL Server Express 2016

Microsoft SQL Server 2016 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 2016 Express comes in 3 editions:

  • SQL Server 2016 Express – just the basic database engine
  • SQL Server 2016 Express with Tools – includes Management Studio
  • SQL Server 2016 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 2016 Express database.

SQL Server Express limitations

There are no limitations on the number of Databases or Users. SQL Server 2016 Express is however limited to one CPU, 1 GB memory and 10 GB database files. This should be sufficient for the upgrade of a small Microsoft Access system.

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.

The ODBC API is being replaced by OLEDB

ODBC (Open Database Connectivity) is an open standard Application Programming Interface (API) that provides access independent of the database or operating system – but only to relational databases. It provides an efficient link for an Access Front-End database to a SQL Server Back-End database.

The OLE DB (Object Linking and Embedding Database) API is a Microsoft proprietary technology that provides access to data irrespective of its format. VBA with ADO, using OLE DB, is an efficient method of accessing records in an SQL Server database through code.

If you are using VB.Net – ODBC has lost favour to OLE DB, being a more universal standard. ADO is being phased out (together with all COM objects) and replaced by native ADO.NET. SQL Server 2012 will be the last version of SQL Server to support ODBC. All SQL Server features will thereafter be updated to use OLE DB instead.

A news flash: Microsoft is trying to be more "Open" and ODBC is making a comeback!