- Understanding Access Functions and Modes
- Opening the Northwind.accdb Sample Database
- Understanding Access's Table Windows
- Navigating the Home and Create Ribbons
- Using the Function Keys
- Setting Default Options
- Creating a Customized Template File
- Using Access Online Help
- Spelunking the Database Utilities
- Packaging, Signing, and Distributing an Access 2007 Database
- In the Real WorldReading the Ribbon UI's Tealeaves
In this chapter
- Understanding Access Functions and Modes 116
- Opening the Northwind.accdb Sample Database 120
- Understanding Access's Table Windows 123
- Navigating the Home and Create Ribbons 127
- Using the Function Keys 141
- Setting Default Options 143
- Creating a Customized Template File 158
- Using Access Online Help 158
- Spelunking the Database Utilities 162
- Packaging, Signing, and Distributing an Access 2007 Database 166
- Troubleshooting 170
- In the Real World—Reading the Ribbon UI's Tealeaves 171
Understanding Access Functions and Modes
Access, unlike word processing and spreadsheet applications, is a truly multifunctional program. Although word processing applications, for example, have many sophisticated capabilities, their basic purpose is to support text entry, page layout, and formatted printing. The primary functions and supporting features of all word processing applications are directed to these ends. You perform all word processing operations with views that represent a sheet of paper. Most spreadsheet applications use the row-column metaphor for all their functions. In contrast, Access consists of a multitude of related tools for generating, organizing, segregating, displaying, printing, and publishing data. The following sections describe Access's basic functions and operating modes.
Defining Access Functions
To qualify as a full-fledged relational database management system (RDBMS), an application must perform the following four basic but distinct functions, each with its own presentation to the user:
- Data organization involves creating and manipulating tables that contain data in conventional tabular (row-column or spreadsheet) format, called Datasheet view by Access.
- List management substitutes Access tables linked to SharePoint lists. SharePoint lists behave similarly to Access tables, but don't maintain referential integrity with foreign key constraints.
- Table joining and data extraction use queries to connect multiple tables by data relationships and create virtual (temporary) tables, called Recordsets, stored in your computer's RAM or temporary disk files. Expressions are used to calculate values from data (for example, you can calculate an extended amount by multiplying unit price and quantity) and to display the calculated values as though they were a field in one of the tables.
- Data entry and editing require design and implementation of data viewing, entry, and editing forms as an alternative to tabular presentation. A form lets you, rather than the application, control how the data is presented. Most users find forms much easier to use for data entry than tabular format, especially when many fields are involved.
- Data presentation requires the creation of reports that you can view, print, or publish on the Internet or an intranet (the last step in the process). Charts and graphs summarize the data for those officials who take the "broad brush" approach.
The basic functions of Access are organized into the application structure shown in Figure 3.1. If you're creating a new database, you use the basic functions of Access in the top-down sequence shown in Figure 3.1.
Figure 3.1 This diagram shows the relationship of the basic and supporting functions of Access. Reports have a one-way relationship with other functions, because you can't use a report to modify data.
Four supporting functions apply to all basic functions of Access:
- Macros are sequences of actions that automate repetitive database operations. In Access 97 and earlier versions, macros were the most common means of automating database operations. In versions 2000 through 2003, macros were supported for backward compatibility only and Microsoft recommended Visual Basic for Applications (VBA) to automate Access applications.
- Modules are containers for functions and procedures written in the VBA programming language. You use VBA functions to make calculations that are more complex than those that can be expressed easily by a series of conventional mathematical symbols. You run a VBA subprocedure by attaching it to particular event, such as clicking a command button with the mouse when a form or page is the active object.
- Security features for Access 2007 applications have been downgraded dramatically. You no longer can grant access to user groups and individuals with user-level security. Nor can you restrict users' ability to view or modify objects in the database except by creating an encrypted .accde file, which corresponds to earlier versions' .mde file.
- Printing lets you print virtually anything you can view in Access's run mode. Printing is the most common form of distributing reports, but you also can export reports to web pages or to Portable Document Format (Adobe .pdf), Microsoft XML Paper Specification (.xps), or Report Snapshot (.snp) files.
The terms open and close have the same basic usage in Access as in other Windows applications but usually involve more than one basic function:
- Opening a database makes its content available to the application through the Navigation Pane, which replaces earlier versions' Database window. You can open only one database at a time in the Access user interface, but you can link tables from Access, client/server, and other desktop databases, as well as Windows SharePoint Services (WSS) 3.0 or Microsoft Office SharePoint Services (MOSS) 2007 lists. You also can open multiple databases with VBA code.
- Opening a table displays a Datasheet view of its contents. Access automatically creates the first table of a new database and defines its structure by the data you enter in it.
- Opening a SELECT query, the most common query type, opens one or more tables and displays the data specified by the query in Datasheet view. You can change data in the tables associated with the query if the query's Recordset is updatable (write-enabled).
- Opening a form or report automatically opens the table or query that's associated with it. As mentioned earlier, forms and reports usually are associated with (called bound to) queries rather than tables.
- Closing a query closes the associated tables.
- Closing a form or report closes the associated query and its tables or the table to which it's bound.
You open existing database objects by double-clicking the corresponding item in the Navigation pane. Closing a query, form, or report doesn't close its associated objects (table, query, or both) if you've opened them independently.
Defining Access Operating Modes
Access has four basic operating modes:
Startup mode occurs after you launch Access 2007 but before you open an existing database or create a new one. By default, Startup mode displays the Getting Started with Microsoft Office Access window, which gives you the options of creating a new blank (empty) database, or creating an Access application from one of 10 local (also called out-of-the-box) template files or online templates in one of three categories (see Figure 3.2). Sample isn't a template category.
Figure 3.2 When you launch Access 2007 for the first time, the Getting Started with Microsoft Access window opens and lets you create a new blank database or generate a database from one of the 10 local templates or more online templates in three categories.
After you've opened one or more databases, the last one opens automatically when you launch Access. You must click the Office button and choose Close Database or New from the gallery to return to the Getting Started with Microsoft Access window.
- Run mode displays your table, form, and report designs as tabbed documents in a single window (the default display type). Run mode displays tables and queries in Datasheet view, forms in Form view, and reports in Report view or Print Preview for reports. Report view is new in Access 2007.
- Design mode lets you create and modify the structure of tables and queries; develop forms to display and edit your data; format reports for printing; design macros; or write VBA code in the separate VBA Editor application. Access calls design mode Design view.
- Layout mode lets you alter the layout of the forms and reports that you created in Design mode or generated from a template. The primary advantage of layout mode is that you can adjust the size and location of controls (typically text boxes) with live data visible. Data sources (tables or queries) for your forms or reports have content to gain the most out of layout mode. Layout mode, which Access calls Layout view, is new in Access 2007.
You can choose Datasheet, Form, Report, Layout, or Design view from the Home ribbon's Views group or you can press Alt and the appropriate shortcut key. Access's shortcut keys are the same as Access 200x's, despite the dramatic change to Office 2007's user interface.