Chapter 19 - Creating Access Projects | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Projects were introduced in Microsoft Access 2000; they’ve been enhanced for easier use in Access 2002. An Access project is a front end to data stored in a SQL Server database. It includes the forms and reports—or, if the application is on the Web, data access pages—that users need to manipulate the data in the SQL Server database. A project has no native tables—just interface objects such as forms, reports, data access pages, and modules. A project has no queries either; the queries you see in a project are interfaces to SQL Server database objects (views, stored procedures, and user-defined functions) that take the place of Access queries.

This chapter focuses on creating and using Access project front ends to data stored in SQL Server databases. Fortunately, you don’t have to start from scratch; you can use the same tools to create forms, reports, macros, and other interface objects that you use when you work in an Access database. Throughout this chapter, you’ll find references to other chapters where you can review the procedures you need.

When you open a project, you’ll see a database diagrams group in the Database window, representing a type of object not found in Access databases. Database diagrams, like tables in projects, are actually SQL Server objects, and thus they reside in the SQL Server back-end database. Figure 19-1 depicts the relationship between an Access project and the back-end SQL Server database. Objects in gray are located in the SQL Server database, and those in black are in the Access project front end.

figure 19-1. an access project serves as a front end to a sql server back-end database.

Figure 19-1. An Access project serves as a front end to a SQL Server back-end database.

note

Acomplete discussion of SQL Server database design and features is beyond the scope of this book. A good book to check out is Inside Microsoft SQL Server 2000 by Kalen Delaney Based on the first edition by Ron Soukup (Microsoft Press, 2000), for a discussion of SQL Server.

The Query Designer is somewhat different in Access projects, but it will still be familiar. If you use the SQL Server Upsizing Wizard to migrate the objects in an Access database to a project, most of the views and stored procedures will be created automatically. (See Appendix E, "Upsizing to SQL Server," for more details on using the wizard.)

tip - Download SQL Server Books Online

SQL Server Books Online documents SQL Server extensively; it’s installed with SQL Server 2000. If you don’t have SQL Server 2000 installed, you can download this help file from the SQL Server Books Online Web site, at http://www.microsoft.com/SQL/techinfo/productdoc/2000/books.asp. Alternatively, you can purchase the SQL Server 2000 180-day trial CD; it also installs this useful information resource for SQL Server.

SQL Server Requirements

To use Access projects, you must have some version of SQL Server installed. This can be a regular installation of SQL Server (Desktop, Standard, or Enterprise) or the SQL Server 2000 Database Engine provided with Microsoft Office XP. To test whether you need to install the SQL Server 2000 Database Engine (which isn’t installed automatically with Office XP), choose Help, Sample Databases, Northwind Sample Access Project. (Note: In Office 2000, the corresponding component was called the Microsoft Database Engine [MSDE]. This acronym is still commonly used for the Office XP component.)

If you don’t have any edition of SQL Server installed, you’ll see the message shown in Figure 19-2.

figure 19-2. a message informs you if you need to install the sql server 2000 database engine.

Figure 19-2. A message informs you if you need to install the SQL Server 2000 Database Engine.

If you do have SQL Server installed, you’ll get a briefer message (shown in Figure 19-3) asking whether you want to install the NorthwindCS sample database.

figure 19-3. a message asks whether you want to install the northwind project.

Figure 19-3. A message asks whether you want to install the Northwind project.

To install the SQL Server 2000 Database Engine, insert the Office CD and run SETUP.EXE from the MSDE2000 folder. (If you have Microsoft Office XP Developer, a separate CD contains Microsoft SQL Server 2000 for Microsoft Office XP Developer. Insert that CD, and follow the instructions on screen to install the Personal Edition of SQL Server.)