Access to SQL Server Migration: Access Solutions using SQL Server

Bill Ramos (@billramo) posted Mary Chipman’s (@marychipman) Access to SQL Server Migration: Access Solutions using SQL Server, Part 1 and Access to SQL Server Migration: Access Solutions using SQL Server, Part 2 on 3/19/2011. From Part 1’s introduction:

In Part 1 and Part 2 I’ll discuss your options for continuing to use Access as a front-end client once you have migrated the data to SQL Server. Some people come to SQL Server migration as Access developers who are new to SQL Server, whereas others may be IT professionals who know little about Access, so I will attempt to bridge any gaps with useful information for everyone. The SSMA Migration Assistant for Access (SSMA) makes it easy to link converted SQL Server tables and queries to the Access database you started from. For applications of any complexity, migrating the data is by far the easiest step. If your existing Access application suffered from intractable problems when the tables were in native Jet/ACE format, and you have done nothing to fix or at least understand those problems, then you are most likely going to have even bigger problems with SQL Server.

The Access client application has often been dismissed as a "toy" because of its ease of use, but when understood and used appropriately with SQL Server on the back end, those very features can help you quickly deliver cost-effective results with a low TCO. Access is not a suitable client for Web or n-tier applications that require a separate data access layer, although it can be used in conjunction with other clients, such as ASP.NET. There are always tradeoffs involved between ease of development, efficiency and security when using Access in a 2-tier, client-server scenario. You will need to determine which of the following techniques are best suited to your needs.

From Part 2’s introduction:

In Part 2 I'll continue the discussion about how you can "tread lightly" on your SQL Server by making your Access front end an efficient and economical data consumer. I'll focus on techniques you can use to keep your application running efficiently, freeing up network and server resources in the process.

Mary is an independent consultant, an Access security expert, and a former developer/writer for the Microsoft Access team. She’s co-author of the classic Microsoft Access Developer's Guide to SQL Server.