Microsoft® SQL Server® 2008 Bible

Chapter 38. Access as a Front End to SQL Server

IN THIS CHAPTER

• Access–SQL Server use case scenarios

• Migrating from Access to SQL Server

• Designing your Access front end

• Connecting to SQL Server

• Monitoring and troubleshooting

• Ad hoc querying and reporting

Access was originally conceived as a standalone desktop database product, combining in one file a relational database engine (Jet) as well as user interface objects, such as forms, reports, macros, designers, and a VBA coding environment. Its very name, Access, denoted one of its most powerful features: the ability to easily incorporate data originating from heterogeneous data sources. When Access was introduced, installable ISAM drivers were used primarily for linking to the prevailing desktop databases, such as Paradox, dBase, and FoxPro, or to Lotus and Excel spreadsheets. An ODBC installable ISAM enables connecting to many server-based database systems, such as SQL Server and Oracle, and additional drivers have been added to connect to Microsoft Exchange Server data and to HTML tables. In Access 2003, Windows SharePoint Services support was added, which enabled linking to SharePoint lists, allowing them to be queried and updated as if they were standard relational tables.

Access 2007 introduced a new version of the Jet database engine, ACE, which is designed to support Access-specific features but is 100% backward compatible with Jet. When it comes to the mechanics of working with SQL Server, the underlying architecture is fundamentally ...