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 ...