Chapter 10: Building SQL Server Applications with Access Projects

So far, all the examples in this book have dealt with standard Access database files (ACCDB files). An Access Project (ADP file) is another type of Access database file that is designed specifically for working with SQL Server databases. In this chapter, you will explore how to create and manage SQL Server databases using Access Projects. You’ll learn how to create new Access Projects from scratch and explore the various SQL Server objects that you can create and manage from an Access Project, such as tables, stored procedures, views, and functions. You will also migrate an existing Access database to SQL Server and provide guidelines for resolving any problems that occur during the migration.

Tip 

This chapter focuses on using Access Projects to work with features that are specific to SQL Server, but it does not cover forms and modules. You still write forms and modules in Access Projects as you have done in prior chapters.

Access Projects - Using Access as a Front End to SQL server

As a quick recap, you are already familiar with standard Access database files: the ACCDB files. The ACCDB file is typically used in a standalone manner, meaning that all the tables, forms, modules, macros, and reports for the application are stored within the single file. In Chapter 7, you learned that it is also possible to use a standard Access ACCDB file in a client-server environment by linking to external database tables such as SQL Server. An example of a client-server architecture is shown in Figure 10-1, which illustrates a case where the user interface resides on the client and the database resides on the server.

Figure 10-1

When linking to a SQL Server database from a standard Access database, you can only work graphically with SQL Server tables from the Access environment. You cannot view and modify stored procedures, views, and functions from within the Access environment.

Access Projects are a special type of Access file designed to work specifically with SQL Server databases. Access Projects provide you with additional graphical tools for manipulating various SQL Server objects - not just the tables. When you are designing client-server applications that use SQL Server as the database, you should consider using an Access Project instead of a standard database so that you will have these additional design features.

The following table shows where the various objects that you can see from within the Access Project are actually stored.

Open table as spreadsheet

Object	Location	Comments
Tables	SQL Server	SQL Server uses different data types.
Queries	SQL Server	These can be stored as stored procedures, views, or functions.
Database Diagrams	SQL Server
Forms	Access file
Reports	Access file
Macros	Access file
Modules	Access file