Professional SQL Server™ 2005 Integration Services

8.2. Access

MS Access is the database of choice for countless individual users and small workgroups. It has many great features and wizards that enable a small application or prototype to be quickly developed. Often, when an application has outgrown its humble Access origins, discussions of moving the data to SQL Server emerge. Many times, the client will be rewritten as a Web or desktop application using VB.NET or another language. Sometimes the plan will be to link to the SQL Server tables, utilizing the existing Access front-end. Unfortunately, if the original application was poorly designed, moving the data to SQL Server will not improve performance.

Designing an application with an SQL back-end and Access front-end that performs well is beyond the scope of this book. To learn more about creating Access applications where SQL Server hosts the data, read Microsoft Access Developer's Guide to SQL Server, by Andy Baron and Mary Chipman (SAMS, 2000) or Microsoft Access Projects with Microsoft SQL Server, by Ralf Albrecht and Natascha Nicol (Microsoft Press, 2002).

Also, keep in mind that Access select queries will be imported into SQL Server as tables. Any queries that must be ported to the SQL Server database will have to be rewritten. Many select queries can be rewritten as views. Update, append, delete, create table and parameterized select queries can be rewritten as stored procedures if you need to move them to the SQL Server database. There may also be VBA (Visual Basic ...