Microsoft Access 2000 introduced an advanced facility that allows you to create a project file (with an .adp extension) that contains only your forms, reports, macros, and modules. When you create a new project file, you can specify an SQL Server database to support the project. SQL Server stores the tables and queries you use in the application that you design in the project. You can connect your project file to a Microsoft SQL Server version 6.5 database on a server or to a version 7.0 or later database on a server or on your desktop. Included with the 2007 Microsoft Office release is a special edition of SQL Server 2005, the Microsoft SQL Server Desktop Engine (MSDE), that you can install to run on your desktop computer.
You will see available tables in the server database as table objects in your project. You will also see views, functions, and stored procedures as query objects. Access 2007 includes special table and query editors to allow you to work directly with the objects in SQL Server. Your project file also contains forms, reports, macros, and modules that are virtually identical to those you develop in a desktop database (.accdb).
To see the differences in tables and queries in a project file, start Access and then open the Contacts.adp sample project file.
|Inside Out-Opening the Sample Project File|| |
To be able to open the Contacts.adp file successfully, you must first install Microsoft SQL Server 2005 Express Edition or have access to an SQL Server edition that allows you Create authority. You can download SQL Server 2005 Express Edition from www.microsoft com/sql/editions/express/defaultmspx. You’ll need to attach the sample database files to a computer running a server version of Microsoft Windows, such as Microsoft Windows Server 2003, and possibly modify the connection properties of the sample project so that Access knows where to find the tables and queries required by the project See the Appendix, “Installing Your Software,” for details about how to install and start SQL Server 2005 Express Edition. See Chapter 26, “Building Tables in an Access Project,” for details about setting project connection properties. If you are unable to perform these steps at this time, you can still read through this section to gain an understanding of some of the differences in project files.
Open the Navigation Pane menu and select Object Type under Navigate To Category. Open the menu again and select Tables under Filter By Group to see all the tables defined in the SQL Server database connected to the project. Figure 3–32 shows you the tables in the ContactsSQL database that is connected to the Conrad Systems Contacts project file.
Figure 3–32: The Navigation Pane in a project file shows the tables in the database on SQL Server.
As you can see, the Navigation Pane in a project file looks very similar to the one in a desktop database. You can see one additional object type listed on the Navigation Pane menu-Database Diagrams. SQL Server allows you to create a diagram of all the tables in your database, and the diagram shows you the relationships that you have defined between the tables.
Select the tblContacts table in the Navigation Pane, and press Ctrl+Enter to see the table in Design view, as shown in Figure 3–33.
Figure 3–33: When you open a table in Design view in an Access project, you’re editing the table in the database on SQL Server.
As you can see, the table design grid in an Access project is very similar to the one in a desktop database. (See Figure 3–8.) In an SQL Server database, fields are called columns. SQL Server supports a wider variety of data types than does a desktop database. Many of the data types are identical, but they have different names in SQL Server. For example, the int data type in SQL Server is the same as the Long Integer data type in a desktop database. If you want, you can click the Datasheet View option in the Views group to switch to Datasheet view, but you’ll find that Datasheet view in an Access project is identical to that in a desktop database. You can learn all the details for creating tables in a project in Chapter 26. Close the table design grid to return to the Navigation Pane.
Although all query objects in a desktop database are called simply “queries,” you’ll find that SQL Server stores three different types of objects-views, functions, and stored procedures-that Access displays when you click Queries under Filter By Group on the Navigation Pane menu, as shown in Figure 3–34.
Figure 3–34: The list of queries in an Access project shows the views, functions, and stored procedures saved in the database on SQL Server.
A view returns a filtered view of data from one or more tables. A function can return a table, or it can perform a calculation and return a single value, much like a Visual Basic function. The difference is that a function that you see in the queries list in a project file Navigation Pane executes on SQL Server, and the server returns the result to your project. A stored procedure can be as simple as an SQL statement that returns rows from one or more tables, or it can contain a complex program written in Transact-SQL that tests conditions and perhaps updates one or more tables in your database.
In many cases, you can design a view, function, or stored procedure using a query designer that is similar to the designer you use in a desktop database. To see an example of a query in a project file’s query designer, scroll down the list of queries in the Conrad Systems Contacts sample project file, select qryContactProductsForInvoice in the Navigation Pane, and then press Ctrl+Enter. Access displays the query in Design view, as shown in Figure 3–35.
Figure 3–35: When you open a query in the query designer in an Access project, you’re editing the view, function, or stored procedure stored in the server database.
This query is a function that returns columns from three tables. The query designer in an Access project is similar in some ways to the designer in a desktop database (see Figure 3–12). You can see the tables used in the query in the top pane of the designer window. In the center pane are the columns (fields) used in the query, but the columns are listed vertically here instead of horizontally as in the desktop database designer. In the bottom pane, you can see the SQL statement that defines this query on the server. You can close this pane if you like and work exclusively in the designer. Access reflects any change you make on the design grid by modifying the displayed SQL. When you become more expert in SQL, you can also modify the SQL statement, and Access changes the top two panes accordingly.
You can learn about the details of creating a query in an Access project in Chapter 27, “Building Queries in an Access Project,” on the companion CD. For details about the SQL database language, see Article 2, “Understanding SQL,” also on the companion CD.
You can close the query design grid now. As noted earlier, the forms, reports, macros, and modules in a project file are virtually identical to those in a desktop database. You can learn about the minor differences for forms and reports in Chapter 28, “Designing Forms in an Access Project,” and Chapter 29, “Building Reports in an Access Project,” both on the companion CD.