A Preview of the Database Components

As mentioned previously, tables, queries, forms, reports, data access pages, macros, and modules combine to comprise an Access database. Each of these objects has a special function. The following sections take you on a tour of the objects that make up an Access database.

Tables: A Repository for Data

Tables are the starting point for an application. Whether data is stored in an Access database or you are referencing external data by using linked tables, all the other objects in a database either directly or indirectly reference tables.

To view all the tables that are contained in an open database, you click the Tables icon in the Objects list. (Note that you don't see any hidden tables unless you have checked the Hidden Objects check box in the Options dialog box's View page.) If you want to view the data in a table, you double-click the name of the table you want to view. (You can also select the table and then click the Open button.) Access displays the table's data in a datasheet that includes all the table's fields and records (see Figure 1.4). You can modify many of the datasheet's attributes and even search for and filter data from within the datasheet. If the table is related to another table (such as the Northwind Customers and Orders tables), you can also expand and collapse the subdatasheet to view data stored in child tables. Hour 2, "Tables Introduced," covers these techniques.

As an Access power user or developer, you will often want to view the table's design, which is the blueprint or template for the table. To view a table's design (see Figure 1.5), you click the Design icon with the table selected. In Design view, you can view or modify all the field names, data types, and field and table properties. Access gives you the power and flexibility you need to customize the design of tables. Hour 8, "Creating Tables," and Hour 14, "Power Table Techniques," cover these topics.

Relationships: Tying the Tables Together

To properly maintain data's integrity and ease the process of working with other objects in a database, you must define relationships among the tables in a database. You accomplish this by using the Relationships window. To view the Relationships window, with the Database window active, you select Tools | Relationships or you click Relationships on the toolbar. In this window, you can view and maintain the relationships in the database (see Figure 1.6). If you or a fellow user or developer have set up some relationships, but you don't see any in the Relationships window, you can select Relationships | Show All to unhide any hidden tables and relationships (you might need to click to expand the menu for this option to appear).

Notice that many of the relationships in Figure 1.6 have join lines between tables and show a number 1 on one side of the join and an infinity symbol on the other. This indicates a one-to-many relationship between the tables. If you double-click a join line, the Edit Relationships dialog box opens (see Figure 1.7). In this dialog box, you can specify the exact nature of the relationship between tables. The relationship between the Customers and Orders tables in Figure 1.7, for example, is a one-to-many relationship with referential integrity enforced. This means that the user cannot add orders for customers who don't exist. Notice in Figure 1.7 that the check box Cascade Update Related Fields is checked. This means that if the user updates a CustomerID field, Access updates all records containing that CustomerID value in the Orders table. Because Cascade Delete Related Records is not checked in Figure 1.7, the user cannot delete from the Customers table customers who have corresponding orders in the Orders table.

Hour 9, "Creating Relationships," extensively covers the process of defining and maintaining relationships. It also covers the basics of relational database design. For now, you should remember that you should establish relationships both conceptually and literally as early in the design process as possible. Relationships are integral to successfully designing and implementing your application.

Queries: Stored Questions or Actions You Apply to Data

Queries in Access are powerful and multifaceted. A query retrieves data from a database based on criteria you specify. An example would be a query that retrieves all employees who live in Florida. Select queries allow you to view, summarize, and perform calculations on the data in tables. Action queries let you add to, update, and delete table data. To run a query, you select Queries from the Objects list and then double-click the query you want to run, or you can click in the list of queries to select the query you want to run and then click Open. When you run a Select query, a datasheet appears, containing all the fields specified in the query and all the records meeting the query's criteria (see Figure 1.8). When you run an Action query, Access runs the specified action, such as making a new table or appending data to an existing table. In general, you can update the data in a query result because the result of a query is actually a dynamic set of records, called a dynaset, that is based on the tables' data. A dynaset is a subset of data on which you can base a form or report.

When you store a query, Access stores only the query's definition, layout, or formatting properties in the database. Access offers an intuitive, user-friendly tool that helps you design queries: the Query Design window (see Figure 1.9). To open this window, you select Queries from the Objects list in the Database window, choose the query you want to modify, and click Design. The query pictured in Figure 1.9 selects data from Employees, Orders, and Order Subtotals tables. It displays the Country, LastName, and FirstName fields from the Employees table, the ShippedDate and OrderID fields from the Orders table, and the Subtotal field from the Order Subtotals query. The query's output displays only records within a specific Shipped Date range. This special type of query is called a parameter query. It prompts for criteria at runtime, using the criteria entered by the user to determine which records it includes in the output. Hours 10, "Creating Queries," and 15, "Power Query Techniques," both cover the process of designing queries. Because queries are the foundation for most forms and reports, they are covered throughout this book as they apply to other objects in the database.

Forms: A Means of Displaying, Modifying, and Adding Data

Although you can enter and modify data in a table's Datasheet view, you can't control the user's actions very well, nor can you do much to facilitate the data-entry process. This is where forms come in. Access forms can have many traits, and they're very flexible and powerful.

To view a form, you select Forms from the Objects list. Then you double-click the form you want to view or click in the list of forms to select the form you want to view and then click Open. Figure 1.10 illustrates a form in Form view. This Customer Orders form is actually three forms in one: one main form and two subforms. The main form displays information from the Customers table, and the subforms display information from the Orders table and the Order Details table (tables that are related to the Customers table). As the user moves from customer to customer, the form displays the orders associated with that customer. When the user clicks to select an order, the form displays the products included on that order.

Like tables and queries, you can also view forms in Design view. The Design view provides tools you can use to edit the layout of a report. To view the design of a form, you select the Forms icon from the Objects list, choose the form whose design you want to modify, and then click Design. Figure 1.11 shows the Customer Orders form in Design view. Notice the two subforms within the main form. Hours 11, "Creating Forms," and 16, "Power Form Techniques," cover forms in more detail.

Reports: Turning Data into Information

Forms allow you to enter and edit information, but with reports, you can display information, usually to a printer. Figure 1.12 shows a report in Preview mode. To preview any report, you select Reports from the Objects list. You double-click the report you want to preview or choose the report you want to preview from the list of reports in the Database window, and then you click Preview. Notice the graphic in the report in Figure 1.12, as well as other details, such as the shaded line. Like forms, reports can be elaborate and exciting, and they can contain valuable information.

As you may have guessed, you can view reports in Design view, as shown in Figure 1.13. To view the design of a report, you select Reports from the Objects list, select the report you want to view, and click Design. Figure 1.13 illustrates a report with many sections; in the figure you can see the Page Header, CategoryName Header, Detail section, CategoryName Footer, Page Footer, and Report Footer sections—just a few of the many sections available on a report. Just as a form can contain subforms, a report can contain subreports. Hour 12, "Creating Reports," and Hour 17, "Power Report Techniques," cover the process of designing reports.

Pages: Forms Viewed in a Browser

Data access pages, discussed earlier in this hour, first appeared in Access 2000. They allow you to view and update the data in a database from within a browser. Although Access stores them outside the Access database file (that is, the .MDB file), you create and maintain them similarly to the way you create and maintain forms. Figure 1.14 shows a data access page viewed within Access. Although Microsoft targets data access pages for use with a browser, you can preview them within the Access application environment.

You can also view and modify data access pages in Design view. Figure 1.15 shows a data access page in Design view. As you can see, the Design view of a data access page is similar to that of a form in Design view. This makes working with data access pages, and the deployment of an application over an intranet, very easy.

Macros: A Means of Automating a System

Macros in Access aren't like the macros in other Office products. You can't record them, as you can in Microsoft Word or Excel, and Access does not save them as Visual Basic for Applications (VBA) code. With Access macros, you can perform most of the tasks that you can manually perform from the keyboard, menus, and toolbars. Macros allow you to build logic into your application flow.

To run a macro, you select Macros from the Objects list, click the macro you want to run, and then click Run. Access then executes the actions in the macro. To view a macro's design, you select Macros from the Objects list, select the macro you want to modify, and click Design to open the Macro Design window (see Figure 1.16). The macro pictured in Figure 1.16 has four columns. The Macro Name column is where you can specify the name of a subroutine within a macro. The Condition column allows you to specify a condition. The Action column is where you specify an action for the macro. (The action in the macro's Action column won't execute unless the condition for that action evaluates to true.) The Comment column lets you document the macro. In the bottom half of the Macro Design window, you specify the arguments that apply to the selected action. In Figure 1.16, the selected action is MsgBox, which accepts four arguments: Message, Beep, Type, and Title.

Modules: The Foundation of the Application Development Process

Modules, the foundation of any complex Access application, let you create libraries of functions that you can use throughout an application. You usually include subroutines and functions in the modules that you build. A function always returns a value; a subroutine does not. By using code modules, you can do just about anything with an Access application. Figure 1.17 shows an example of a module. As its title implies, Hour 23, "VBA Introduced," introduces you to the concept of modules. If you want more information on modules after reading this book, see Alison Balter's Mastering Access Office 2003 Desktop Development, published by Sams, which provides extensive coverage of modules and Access coding techniques.