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:

→ For an explanation of the benefits of referential integrity, see "Maintaining Data Integrity and Accuracy," p. 192.

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.

NOTE

You can base forms and reports on data from Access or SQL Server tables, or linked SharePoint Lists, but it's more common to use a query as the data source for forms and reports. An SQL Server view is the direct counterpart of an Access SELECT query. You also can use SQL Server inline functions and stored procedures as data sources for forms and reports.

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.

TIP

Microsoft now recommends using macros wherever possible because macros will run under more restrictive security settings than VBA. Microsoft also raises the dubious contention that macros are simpler for new users to write than VBA code. In an attempt to make macros more palatable to application developers, the Access team created a new class of embedded macros and added event-handling actions.

Only Access uses these macros, so learning to construct them gains you no leverage with the many other applications that use VBA. What's worse, macros have a very limited programming repertoire. If you intend to create Access applications for others to use, learning to write VBA code is highly recommended.

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.

NOTE

Chapter 2, "Building Simple Tracking Applications," shows you how to create a complete Access database application from the Tasks online template in a few minutes.

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.

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.

→ For more information on Layout view, see "Form and Report Layout View," p. 53.

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.