Microsoft Access to Microsoft 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.
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.
SQL Server Maintenance
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 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 SQL Server
One of the upsizing 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.
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 ADP enhancements over the last 10 years, and it would seem that support
has been dropped. With an uncertain future, an Access or VB.Net Front-End seems the best option
now.
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.
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)
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:
- SQL Server 2008 Express – just the basic database engine
- SQL Server 2008 Express with Tools – includes Management Studio
- SQL Server 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.
SQL Server Express limitations
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 a small Microsoft Access system.
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.
The Future of OLEDB and ODBC
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 is an efficient way
of accessing data on an SQL Server database through code.
If you are using VB.Net – OLE DB has lost favour to ODBC, 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 OLE DB. All SQL Server features
will thereafter be updated to use ODBC instead.