Access Database Programming
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 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.
Linking Microsoft Access to SQL Server
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.
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 inefficiencies are identified in Linked
Table usage.
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.
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.
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 in and out of SQL Server 2008 Express databases.
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
4 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.
|