Using Access to Integrate with SQL Server Databases

Unlike an Access database (a file with the .mdb extension), which contains both data (in tables) and the database objects (such as forms and reports) that you use to work with the data, a Microsoft Access project (a file with the .adp extension) gets its data from a Microsoft SQL Server database and only contains forms, reports, and data access pages that display the data for analysis. You can connect an Access project to a remote SQL Server 2000 database, a local SQL Server 2000 database, or a local installation of the SQL Server 2000 Desktop Engine. The fact that an Access project does not contain any data itself allows you to use the features of SQL Server for very large databases or large numbers of concurrent users and use the familiar Access user interface for queries (called views, functions, and stored procedures in SQL Server terminology), data entry forms, reports, and so on.

To create an Access project and connect to a SQL Server database, click New on the File menu, click Project (Existing Data) if you’re using Access 2002, or Microsoft Access Projects if you’re using Access 2000. Provide a file name and location, and then click Create. On the Connection tab, select or enter a server name, provide your server logon credentials, select a database, and then click OK.

SQL Server uses different terms and data types than Access. You’ll notice terms such as identity seed, varchar, and stored procedure when you create or modify data tables or queries. To understand these terms, as well as how to work in more detail with Access projects, refer to the book Microsoft Access Projects with Microsoft SQL Server (Microsoft Press, 2002).

SQL Server itself can seem somewhat intimidating to first-time users. The key point to remember is that with Access projects, you don’t have to be an expert in the inner workings of SQL Server. You need to understand a few differences in terminology, but otherwise you can use the skills you’ve mastered in this chapter to analyze SQL Server data through Access projects.