Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Getting to Know the Database Objects

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. An Access application also includes several miscellaneous objects, including relationships, command bars, database properties, and import/export specifications. With these objects, you can create a powerful, user-friendly, integrated application. Figure 1.1 shows the Access Database window. Notice the seven categories of objects listed in the database container. The following sections take you on a tour of the objects that make up an Access database.

Tables: A Repository for Your Data

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

To view all the tables that are contained in the open database, click the Tables icon in the Objects list. (Note that you won't see any hidden tables unless you have checked the Hidden Objects check box in the Tools, Options dialog's 'View page.) If you want to view the data in a table, 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, which includes all the table's fields and records. (See Figure 1.2.) 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. This book does not cover these techniques. You can find them in the Access user manual or any introductory Access book, such as

Sams Teach Yourself Microsoft Office Access 2003 in 24 Hours .

As a developer, you most often want to view the table's design, which is the blueprint or template for the table. To view a table's design, click the Design icon with the table selected. (See Chapter 2, "What Every Developer Needs to Know About Databases and Tables," covers these topics.

NOTE

In an ADP, although Access shows the tables in the Database window, it does not store them in the Access project file. Instead, it stores the tables in the SQL Server database to which you connect the ADP.

Relationships: Tying the Tables Together

To properly maintain your data's integrity and ease the process of working with other objects in the database, you must define relationships among the tables in your database. You accomplish this using the Relationships window. To view the Relationships window, with the Database window active, select Tools, Relationships, or click Relationships on the toolbar. (See Figure 1.4.)

In this window, you can view and maintain the relationships in the database. If you or a fellow developer have set up some relationships, but you don't see any in the Relationships dialog, 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.4 have a join line between tables with a number 1 and an infinity symbol (). This indicates a one-to-many relationship between the tables. If you double-click the join line, the Edit Relationships dialog opens (see Figure 1.5). In this dialog, you can specify the exact nature of the relationship between tables. The relationship between Customers and Orders, 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 that the check box to Cascade Update Related Fields is checked. This means that if the user updates a CustomerID, Access updates all records containing that CustomerID in the Orders table. Because Cascade Delete Related Records is not checked, the user cannot delete customers from the Customers table if they have corresponding orders in the Orders table.

Chapter 3, "Relationships: Your Key to Data Integrity," extensively covers the process of defining and maintaining relationships. It also covers the basics of relational database design. For now, remember that you should establish relationships both conceptually and literally as early in the design process as possible. They are integral to successfully designing and implementing your application.

NOTE

In an ADP, relationships appear in the Database window as Database Diagrams. They are not stored in the Access project file. They are instead stored in the SQL Server database to which you connect the ADP.

Queries: Stored Questions or Actions You Apply to Your Data

Queries in Access are powerful and multifaceted. Select queries enable you to view, summarize, and perform calculations on the data in your tables. Action queries let you add to, update, and delete table data. To run a query, select Queries from the Objects list and then double-click the query you want to run, or click 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.6). 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 , based on your tables' data.

When you store a query, only its definition, layout or formatting properties, and datasheet are actually stored in the database. Access offers an intuitive, user-friendly tool for you to design your queries. Chapter 4, "What Every Developer Needs to Know About Query Basics," and Chapter 11, "Advanced Query Techniques" both cover queries. Because queries are the foundation for most forms and reports, I cover them throughout this book as they apply to other objects in the database.

NOTE

In an ADP, queries appear in the Database window as views, stored procedures, and functions. Access does not store them in the Access project file. It instead stores them in the SQL Server database to which you connect the ADP.

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 take on many traits, and they're very flexible and powerful.

To view any form, select Forms from the Objects list. Then double-click the form you want to view, or click to select the form you want to view, and click Open. Figure 1.8 illustrates a form in Form view. This 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. 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. To view the design of a form, select the Forms icon from the Objects list (refer to Chapter 5, "What Every Developer Needs to Know About Forms," and Chapter 9, "Advanced Form Techniques" officially cover forms. I also cover forms throughout this text as they apply to other examples of building an application.

Reports: Turning Data into Information

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

If you haven't guessed yet, you can view reports in Design view, as shown in Chapter 6, "What Every Developer Needs to Know About Reports," and Chapter 10, "Advanced Report Techniques," cover reports. I also cover them throughout the book as they apply to other examples.

Data Access Pages: Forms Viewed in a Browser

Data access pages, discussed earlier in the chapter, first appeared in Access 2000. They enable you to view and update the data in your database from within a browser. Although Access stores them outside the Access database (.mdb) file, you create and maintain them in a manner similar to that of forms. Figure 1.12 shows a data access page viewed within Access. Although data access pages are targeted toward 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.13 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. This makes working with data access pages, and the deployment of your application over an intranet, very easy.

TIP

Access 10 introduced the capability to save an Access form as a data access page. This new feature makes it easier to develop forms used by Access users and browser-based users simultaneously.

Macros: A Means of Automating Your 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 enable you to build logic into your application flow. Generally, you use VBA code contained in modules, rather than macros, to do the tasks your application must perform. This is because VBA code modules give you more flexibility and power than macros do. Although in earlier versions of Access you could perform certain tasks only by using macros, developers today rarely use macros. This book therefore does not cover the development of applications using macros.

To run a macro, 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, 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.14). The macro pictured has four columns. The first column is the Macro Name column, where you can specify the name of a subroutine within a macro. The second column enables you to specify a condition. The action in the macro's third column won't execute unless the condition for that action evaluates to true. The fourth 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.14, the selected action is MsgBox, which accepts four arguments: Message, Beep, Type, and Title.

Modules: The Foundation to the Application Development Process

Modules , the foundation of any application, let you create libraries of functions that you can use throughout your application. You usually include subroutines and functions in the modules that you build. Functions always return a value; subroutines do not. By using code modules, you can do the following:

• Perform error handling

• Declare and use variables

• Loop through and manipulate recordsets

• Call Windows API and other library functions

• Create and modify system objects, such as tables and queries

• Perform transaction processing

• Perform many functions not available with macros

• Test and debug complex processes

• Create library databases

These are just a few of the tasks you can accomplish with modules. To view the design of an existing module, click Modules in the Objects list, choose the module you want to modify, and click Design to open the Module Design window (see Chapter 7, "VBA: An Introduction," and Chapter 12, "Advanced VBA Techniques," discuss modules and VBA, respectively. I also cover modules and VBA extensively throughout this book.