Navigating the New Access 2007 User Interface

Date: May 18, 2007

Access consists of a multitude of related tools for generating, organizing, segregating, displaying, printing, and publishing data. This sample chapter describe Access's basic functions and operating modes.

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.

TIP

new 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.

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.

Opening the Northwind.accdb Sample Database

cd_icon_only The Northwind Traders sample database (Northwind.accdb) is the primary Access application used in this book's examples. Access 2007 doesn't include a sample database, so the accompanying CD-ROM provides an upgraded and updated version of Northwind.mdb from Access 2003 and earlier. Transferring the CD-ROM's sample chapter files to your computer places Northwind.accdb in your \SEUA12\Nwind\ folder, which also contains a copy of Access 2003's Northwind.mdb file in Access 2000 format.

After installing the sample files from the CD-ROM, open Northwind.accdb and display its Home ribbon and default Navigation Pane by doing the following:

Launch Microsoft Office Access 2007, if it isn't running.
Click the Office button to open the gallery (menu) and choose Open to launch the Open dialog. Navigate to your \SEUA12\Nwind folder, which contains three sample files (see Figure 3.3).

Figure 3.3 The Open dialog lets you open almost all varieties of Access 2000 through 2007 database files.
Select Northwind.accdb, and click Open to open the Switchboard form as a tabbed document (see Figure 3.4). The message bar displays a security warning with an Options button. The content that's been disabled is the VBA code in the Utility Functions module.

Figure 3.4 The Switchboard form's default page lets you select one of four categories of sample forms and sales reports to open. The Navigation pane displays all database objects in an Outlook-style sidebar.
Optionally, click the Products and Suppliers button and then click a button to open one of the sample forms or reports. Figure 3.5 shows the Suppliers and Products List in Form view.

Figure 3.5 The Suppliers and Products form displays values from the Suppliers table's record for the selected supplier above a datasheet view containing related products' records from the Order Details table.

After you open Northwind.accdb for the first time, it opens automatically when you launch Access, and an entry for the database appears in the Office gallery's Recent Documents pane. It's quicker to open Northwind.accdb or any other recently used databases from the Recent Documents pane.

Understanding Access's Table Windows

You're probably familiar with the terms for and behavior of many new components that comprise the basic window in which all Office 2007 applications run. Ribbons, groups, command buttons, and the Quick Access Toolbar (QAT) replace conventional hierarchical menus and toolbars. As with other Office 2007 applications, the presentation of Access windows varies with each basic function that Access performs. Because tables are the basic component of relational databases, the examples that follow use Table Datasheet view. Figure 3.6 shows Access 2007's display for run-mode operations with tables; Table 3.1 describes the window's Access-related components.

Figure 3.6 Access uses the default document interface (MDI) to display all database objects except code in modules and scripts for pages. The VBA editor and Microsoft Script editor are separate applications.

1. Office button

2. Ribbon tab

3. Quick-Access toolbar

4. Tabbed document

5. Selected cell

6. Button group

7. Contextual ribbon

8. Field headers

9. Ribbon

10. Online/Offline help

11. Document Close button

12. Gallery

13. Context menu

14. Context submenu

15. Record scrollbar

16. Status bar

17. View shortcuts

18. Field scrollbar

19. Quick Search textbox

20. Filter status

21. Record indicator

22. Record Navigation bar

23. Open subdatasheet

24. New Record

25. Status message

26. Navigation item

27. Navigation group

28. Show/Hide Navigation items

29. Navigation pane

30. Row Selection button

31. Select All button

32. Show Navigation gallery

33. Navigation pane expand/retract

34. Open View or Gallery button

Table 3.1. Components of the Access Display for Tables

	Term	Description
	Office Button	Opens the Office gallery (menu) with New, Open, Save, Save As, Print, Manage, E-Mail, Publish, and Close Database choices, as well as an Access Options button to open the Access Options dialog and an Exit Access button.
	Ribbon Tab	Selects the active ribbon from the four standard ribbons—Home, Create, External Data, Database Tools—and one or two contextual (Tools) ribbons, such as Table Tools, Datasheet(w) or Table Tools, Design.
	Quick Access Toolbar	Lets you add icons that act as shortcuts to command buttons on all ribbons and most galleries. The default choices are Save, Undo, and Redo.
	Tabbed Document	Access 2007's default window for displaying all database objects in any view.
	Selected Cell	The currently selected cell into which you can type data.
	Button Group	A collection of a ribbon's command buttons that perform related tasks.
	Contextual Ribbon	A ribbon that appears in response to the selected object type (table, query, form, or report) and mode (run or design).
	Field Headers	Displays the name of the field and, when clicked, selects all cells of the column. Right-clicking opens a context menu with choices similar to those of the context submenu shown in Figure 3.6.
	Ribbon	The standard navigation window for Office 2007 that's customized for each Office application.
	Online/Offline Help	Opens Access's help window, which draws from help content on Office Online as well as local help files.
	Document Close Button	Closes the active tabbed document.
	Gallery	A graphic menu with command button icons that represent choices. Access uses galleries to display buttons that aren't visible in a group.
	Context Menu	An extension to a gallery or a floating right-click menu that offers choices that depend on the selected button or object type.
	Context Submenu	A second or third menu hierarchy.
	Record Scroll Bar	Scrolls table records or query rows.
	Status Bar	Displays context information or user-specified text.
	View Shortcuts	Provides a context-based alternative to selection from the Views group's gallery: Datasheet, PivotChart, PivotTable, Form, Report, Design.
	Field Scroll Bar	Scrolls table fields or query columns.
	Quick Search Text Box	Typing text searches for the first instance of the characters in any field. If a match is found, pressing Enter finds the next occurrence.
	Filter Status	Advises the user if all records are visible (No Filter) or a filter has been applied (Filtered).
	Record Indicator	Displays the number of the current record and the total number of records displayed.
	Record Navigation Bar	Provides VCR-like buttons (First, Previous, Next, and Last) for selecting the current table record or query row and a New Row button to navigate to the tentative append record, if the table or query is updatable.
	Open Subdatasheet	Opens a table's subdatasheet that displays records in a related table, if a subdatasheet has been defined.
	New Record	The tentative append record that becomes a new record when you type in at least one field.
	Status Message	Context information or user-specified text.
	Navigation Item	A shortcut to a database object; double-clicking the item opens it in a tabbed document (the default) or a modal dialog form.
	Navigation Group	A named collection of related navigation items.
	Show/Hide Navigation Items	Expands or collapses the list of a navigation group's items.
	Navigation Pane	An Outlook-style, customizable, shutter-bar list of all database objects, except those that are hidden deliberately.
	Row Selection Button	Click to make the row the current row.
	Select All Button	Click to select all rows and columns (the equivalent of pressing Ctrl+A).
	Show Navigation Gallery	Click to open or close the Navigation gallery; right-click to open a context menu with Category, Sort By, View By, Show All Groups, Paste, Navigation Options, and Search Bar choices.
	Navigation Pane Expand/Retract	Expand or retract the Navigation Pane. The default state is expanded.
	Open View or Gallery Button	Clicking the icon displays the specified view; clicking View opens a gallery of the available views for the object.

Navigating the Home and Create Ribbons

The Home, Create, External Data, and Database Tools ribbons vary only slightly as you change objects, operating modes, screen resolution, or window width. Access enables or disables a few command buttons and gallery items in response to changes of object type and view. Familiarity with the Home and Create ribbons is required to get up to speed with Access 2007, so this chapter covers these ribbons in detail.

The Home Ribbon

Figure 3.7 is a multiple-exposure, split view of the Home ribbon for table Datasheet view in 1,024x768 resolution. The View, Font Color, Text Highlight Color, Refresh All, Advanced Filter Options, and Go To galleries are open.

Figure 3.7 Control buttons on ribbons haven't replaced all hierarchical Office menus. Drop-down galleries and context menus substitute icons, lists, or both for earlier Access versions' conventional Windows menu choices.

Table 3.2 lists the Home ribbon's command buttons, keyboard shortcuts (also called KeyTips), and actions. Press Alt+H to activate the KeyTips, release the Alt key, and then sequentially press the keys shown in the Shortcut column.

Table 3.2. The Home Ribbon's Command Buttons and Their Actions in Table Datasheet View

Icon	Command Button	Shortcut Alt+H, ...	Command Action
Views Group
	Datasheet View	W, H	Changes to Datasheet view
	PivotChart View	W, O	Changes to PivotChart view
	PivotTable View	W, V	Changes to PivotTable view
	Design View	W, D	Changes to Design view
Clipboard Group
	Paste	V, P (Ctrl+V)	Pastes Clipboard content
	Paste, Special	V, S	Pastes Clipboard content in selected format
None	Paste, Append	V, N	Inserts records copied to the Clipboard
	Cut	X (Ctrl+X)	Cuts selected content to the Clipboard
	Copy	C (Ctrl+C)	Copies selected content to the Clipboard
None	Office Clipboard	F, O	Opens the Office Clipboard task pane
Font Group
	Format Painter	F, P	Copies the format from one object to another
None	Font, Face	F, F	Sets the focus to the Font Face list box
None	Font, Size	F, S	Sets the focus to the Font Size list box
	Bold	1 Ctrl+B	Applies bold attribute to selected text
	Italic	2 Ctrl+I	Applies italic attribute to selected text
	Underline	3 Ctrl+U	Applies underline attribute to selected text
	Align Left	A, L	Aligns selected text left
	Align Center	A, C	Centers selected text
	Align Right	A, R	Aligns selected text right
	Font Color	F, C	Opens font color picker
	Fill/Back Color	F, B	Opens fill/background color picker
	Gridlines	B	Opens gridlines gallery
	Alternate Fill/Back Color	F, A	Opens fill/background color picker for alternate rows
None	Datasheet Formatting	L	Opens the Datasheet Formatting dialog (see Figure 3.8)
Rich Text Group (for rich-text-enabled Memo fields only)
	Decrease List Level	A, O	Decreases rich-text indent level
	Increase List Level	A, I	Increases rich-text indent level
	Left-to-Right	A, F	Enables changing rich-text entry direction
	Numbering	N	Starts a rich-text numbered list
	Bullets	U	Starts a rich-text unordered list
	Text Highlight Color	I	Opens a color picker to highlight selected rich text
Records Group (see Chapter 6)
	Refresh All	K, R	Regenerates the Recordset and repaints the Datasheet
	New Record	K, N Ctrl++	Moves to the tentative append record
	Save	K, S Shift+Enter	Saves changes to a record
	Delete	K, D (Del)	Deletes the selected (current) record
	Totals	T	Toggles the appearance of a totals row below the tentative append record
	Spelling	S (F7)	Starts the spelling checker for the selected object and opens the Spelling: Language dialog (see Figure 3.9)
	More choices	P	Opens a context menu with Datasheet formatting
Sort & Filter Group (see Chapter 7)
	Sort Ascending	E	Sorts the selected field/column in ascending (A–Z) order
	Sort Descending	D	Sorts the selected field/column in descending (Z–A) order
	Clear All Sorts	F, R	Removes sorts from all fields/columns
	Filter	Q	Opens the filter context menu for the selected field/column
	Selection	O	Opens a context menu that lets you filter records by selection
	Advanced Filter/Sort	FV	Opens a context menu that lets you choose advanced filter/sort features
	Toggle Filter	J	Alternately applies and removes the current filter
Find Group (see Chapter 7)
	Find	F, D Ctrl+F	Opens the Find and Replace dialog with the Find page active
	Replace	R Ctrl+H	Opens the Find dialog with the Replace page active
	Go To	G	Opens a context menu with First, Previous, Next, Last, and New choices
	Select	H	Opens a context menu with Select and Select All choices

Figure 3.8 The Datasheet Formatting dialog consolidates most Datasheet appearance settings in a single location.

Figure 3.9 Access's Spelling: Language dialog is common to all Office 2007 applications.

NOTE

The QAT and ribbon UI comprise a window that's independent of the Access window that contains the Navigation pane and tabbed documents. When you press Alt once or twice to set focus to the ribbon window and display the KeyTips, pressing the left or right arrow key cycles focus through the primary and context-specific ribbons, QAT, and Office button. Pressing Tab or an arrow key cycles the focus through the selected ribbon's command buttons. You move between ribbon and command button selection with the up- and down-arrow keys. Pressing Enter with a command button selected executes its action.

Context-specific Table Tools Ribbons

Opening any Access object except a module in Design view adds one or more context-specific ObjectType Design Tools ribbons. Similarly, opening a form or report in Layout view adds ObjectType Layout Tools ribbons. Opening a table in Datasheet or Design view adds a Table Tools, Datasheet ribbon. Changing to Design view substitutes a Table Tools, Design ribbon. The following sections describe these two context-sensitive ribbons briefly.

The Table Tools, Datasheet Ribbon

Microsoft encourages Access users to create tables in Datasheet view, type data in the default empty column provided, add new columns as needed, and populate the new columns. As mentioned earlier, opening a new empty database creates an empty starter table. Alternatively, you can add a starter table by clicking the Create ribbon's Table button. In either case, the Table Tools, Datasheet ribbon opens by default.

TIP

You can discourage users from making table design changes in Datasheet view by clearing the Enable Design Changes for Tables in Datasheet View check box in the Application Options group of the Access Options dialog's Current Database page, as described in the later section "The Current Database Page."

To prevent users from changing options, you must split the database and secure the front end, as described in Chapter 19, "Linking Access Front Ends to Access and Client/Server Tables."

Figure 3.10 is a split view of the Table Tools, Datasheet ribbon for a database (in 1,024x768 resolution) that includes tables linked from SharePoint lists. The term SharePoint refers to Windows SharePoint Services (WSS) 3.0 or Microsoft Office SharePoint Server (MOSS) 2007.

Figure 3.10 The Table Tools, Datasheet ribbon for a database with tables linked to SharePoint adds a SharePoint Lists group with command buttons for common operational and maintenance duties for a site.

Table 3.3 lists the Table Tools, Datasheet ribbon's command buttons, shortcut keystrokes, and command actions. Like primary ribbons, you press Alt+H, release the Alt key, and then press the shortcut key. The Views button behaves identically to the same button on the Home ribbon. This ribbon doesn't have galleries, but three buttons open task panes, one button opens the Relationships window, and all buttons in the SharePoint Lists group open SharePoint pages.

Table 3.3. The Table Tools, Datasheet Ribbon's Command Buttons and Their Actions in Table Datasheet View

Icon	Command Button	Shortcut Alt+W, ...	Command Action
Fields & Columns Group (disabled for SharePoint lists)
	New Field	D	Opens the Field Templates task pane (see Figure 3.11, left) to select a data type and adds a field
	Add Existing Fields	X	Opens the Field List task pane (see Figure 3.11, center) to clone a field from any database table
	Lookup Column	L	Starts the Lookup Wizard to add lookup properties to a field
	Insert Column	I	Inserts a field to the left of existing columns
	Delete Column	T	Deletes the selected column
	Rename Column	N	Enables renaming the column, usually from Field1
Data Type and Formatting Group
None	Data Type	J	Lets you select one of Access's nine data types: Text, Memo, Number, Date/Time, Currency, Yes/No, OLE Object, Hyperlink, or Attachment (disabled for SharePoint lists)
None	Format	F	Lets you select one of Access's seven Number or seven Date/Time formats
	Unique	U	Adds a no-duplicates index to the selected field, which requires each cell value to be unique
	Is Required	Q	Prevents users from leaving empty cells in the selected field
	Apply Currency Format	A, N	Formats the Number data with the Windows default currency format
	Apply Percentage Format	P	Multiplies the Number data by 100 and adds two decimal digits (does not affect the cell value)
	Apply Comma Number Format	K	Adds comma (or dot) thousands separators and two decimal digits
	Decrease Decimals	0	Reduces the number of decimal digits
	Increase Decimals	9	Increases the number of decimal digits
Relationships Group
	Relationships	E	Opens the Relationships window to enable establishing or editing relationships between tables
	Object Dependencies	O	Opens the Object Dependencies task pane (see Figure 3.11, right)
SharePoint List Group (visible only when a table linked to a SharePoint list is selected)
	Default View	S, V	Opens the selected linked SharePoint list's default view page in an Access Web Datasheet ActiveX control (see Figure 3.12)
	Refresh List	S, R	Causes the table to rewrite the selected linked SharePoint list data to the local Datasheet
	Modify Columns and Settings	S, M	Opens SharePoint's Customize ListName page on which you can change the design of the selected list
	Alert Me	S, A	Sends you an email message when users make specific types of changes to the selected list
	Modify Workflow	S, W	Opens SharePoint's Change Workflow Settings: ListName page for the selected linked list
	Permissions	S, P	Opens the Permissions: ListName page for the selected linked list

Figure 3.11 Access 2007 relies on task panes for operations that are more complex than galleries can handle.

Figure 3.12 An Access database has a Categories table linked to this SharePoint Categories list. Paperclip icons in a column indicate that the column uses the SharePoint (or Access) Attachment data type.

The Table Tools, Design Ribbon

Changing to table Design view replaces the Table Tools, Datasheet ribbon with the Table Tools, Design ribbon shown in Figure 3.13. Table Design view is the better choice for designing tables than typing data items to generate an ad-hoc table structure. Design view and the Table Tools, Design ribbon expose many more field and table properties than Datasheet view and the Table Tools, Datasheet ribbon.

Figure 3.13 The simpler Table Tools, Design ribbon replaces the Datasheet version in table Design view. The field design grid and the Field Properties pane set values for individual fields. Property Sheet settings apply to the entire table.

Table 3.4 lists the Table Tools, Design ribbon's command buttons, shortcut keystrokes, and command actions.

Table 3.4. The Table Tools, Design Ribbon's Command Buttons and Their Actions in Table Design View

Icon	Command Button	Shortcut Alt+D, ...	Command Action
Tools Group
	Primary Key	P	Toggles the status of the selected column(s) as the primary key for the table
	Builder	B	Opens the Expression Builder dialog when entering Default Value or Validation Rule property values
	Test Validation Rules	V	Tests new or modified validation rules with existing data
	Insert Rows	I	Inserts a new field grid row above the current row
	Delete Rows	R	Deletes the selected field grid row(s)
	Lookup Column	L	Inserts a new field grid row and starts the Lookup Wizard
Show/Hide Group
	Property Sheet	H, P	Toggles visibility of the Property Sheet pane
	Indexes	X	Opens the Indexes: TableName dialog to add indexes on fields other than the primary key field

TIP

Almost all nontrivial databases contain more than one table because a single-table database is the functional equivalent of a spreadsheet or a SharePoint list. Before you design a table for a production database that requires two or more related tables, read—or at least skim—Chapter 4, "Exploring Relational Database Theory and Practice," and Chapter 5, "Working with Access Databases and Tables."

Many novice database designers find the usability or performance of their application deteriorates greatly as the number of table rows increases. Changing table design to overcome deficiencies after users enter large amounts of data is time-consuming, frustrating, and prone to errors. Starting your first database project with one of the many Access database templates, even if you must modify it to suit your application, provides a reasonable degree of assurance that you won't "design yourself into a nonrelational corner."

The Create Ribbon

You use the Create ribbon to add new table, query, form, report, macro, and module objects to Access databases (see Figure 3.14).

Figure 3.14 The Create ribbon lets you add new Access objects to your database and take advantage of table and field templates, when applicable.

Table 3.5 lists the Create ribbon's command buttons, shortcut keystrokes, and command actions.

Table 3.5. The Create Ribbon's Command Buttons and Their Actions in Table Datasheet View

Icon	Command Button	Shortcut Alt+C, ...	Command Action
Tables Group (see Part II of this book)
	Table	T, N	Adds a new table with a single field in Datasheet view
	Table Templates	L	Opens a gallery that contains the following five command buttons
	Contacts	L, C	Adds an Outlook-compatible list for individuals from the Contacts and other application templates
	Tasks	L, T	Adds a task list that's suitable for managing a group's activities (from the Tasks application template)
	Issues	L, I	Adds an issue list that might be used for bug reporting and the like (from the Issues application template)
	Events	L, E	Adds a date-based list for scheduling events (from the Events application template)
	Assets	L, A	Adds a list that's designed specifically for tracking fixed assets (from the Assets application template)
	SharePoint Lists	S	Opens a gallery that contains the following six command buttons
	Contacts	S, C	Generates a Contacts list in the designated SharePoint site and links it and a User Information List to an Access table
	Tasks	S, T	Does the same for a Tasks list and table
	Issues	S, I	Does the same for an Issues list and table
	Events	S, E	Does the same for an Events list and table
	Custom	S, U	Generates a basic SharePoint list with visible ID (AutoNumber), Title (Text), and Attachments (Attachment) fields, as well as 11 hidden SharePoint-specific fields and links it to an Access table
	Existing SharePoint List	S, X	Lets you import or link the data from a SharePoint list you specify to an Access table
	Table Design	T, D	Adds a new Access table in Design view
Forms Group (see Chapters 14 and 15)
	Form	F, M	Generates a formatted columnar form from the selected table or query and adds a Datasheet subform bound to a related form, if present
	Split Form	P	Generates a formatted columnar form and a Datasheet from the selected table or query
	Multiple Items	M	Generates a formatted tabular list from the selected table or query
	PivotChart	C	Creates a form that contains a PivotChart control (see Chapter 18, "Adding Graphs, PivotCharts, and PivotTables")
	Blank Form	F, B	Creates an empty (blank) form in Layout view and opens the Field List pane
	More Forms	F, M	Opens a gallery with the following four command buttons
	Form Wizard	F, M, W	Starts the Form Wizard, which lets you create a columnar, tabular, Datasheet, or justified form from table fields or query columns you select with a format from one of 25 predesigned styles
	Datasheet	F, M, D	Creates a form that's indistinguishable from table Datasheet view
	Modal Dialog	F, M, M	Creates an empty modal dialog (overlapping window) in Layout view and opens the Field List pane
	PivotTable	F, M, T	Creates a form that contains a PivotTable control (see Chapter 18)
	Form Design	F, D	Opens a new blank form in Design view
Reports Group (see Chapters 16 and 17)
	Report	R, N	Generates a simple formatted list from the selected table or query with the same font size as forms and opens it in Report view
	Labels	B	Starts the Mailing Label Wizard to print mailing labels standard label sheets you specify
	Blank Report	R, B	Opens a blank report in Layout view for the selected table or query and opens the Field List pane
	Report Wizard	W	Starts the Report Wizard, which lets you base the report on a table or query you select, and add grouping, sort order, and format
	Report Design	R, D	Opens a new blank report for the selected table or query in Design view
Other Group (see Parts III and VII of this book)
	Query Wizard	Q, W	Opens the New Query dialog, which lets you select the Simple Query, Crosstab Query, Find Duplicates, or Find Unmatched Query Wizard to help you design a query from one or more tables
	Query Design	Q, D	Opens a new query in Design view and displays the Show Table dialog
	Macro	A	Opens a gallery with the following three command buttons
	Macro	A, A	Opens an empty standalone macro object for a nonembedded Access macro
	Module	A, M	Opens an empty VBA module in the VBA Editor application
	Class Module	A, C	Opens an empty VBA Class Module in the VBA Editor application

Using the Function Keys

Access assigns specific purposes to all 12 function keys of the 101-key extended keyboard. Some function-key combinations, such as Shift+F4 (which you press to find the next occurrence of a match with the Find dialog), derive from other Microsoft applications—in this case, Word.

Global Function Keys

Windows, rather than Access, uses global function-key assignments, except for F11, Ctrl+F1, and Alt+F1, to perform identical functions in all Windows applications. Table 3.6 lists the global function-key assignments.

Table 3.6. Global Function-Key Assignments

Key	Function
F1	Displays context-sensitive help related to the present basic function and status of Access. If a context-sensitive help topic isn't available, F1 opens the Microsoft Access Help task pane page, which lets you search online help for a keyword or open its table of contents.
Ctrl+F1	Toggles (alternates) visibility of the ribbon window in all Office 2007 members.
Ctrl+F4	Closes the active window.
Alt+F4	Exits Access or closes a dialog if one is open.
Ctrl+F6	Selects each open window in sequence as the active window.
F11	Toggles Navigation Pane visibility.
F12	Opens the selected object's Save As dialog.
Shift+F12	Saves your open database; the equivalent of the File menu's Save command.

Function-Key Assignments and Shortcut Keys for Fields, Grids, and Text Boxes

Access assigns function-key combinations that aren't reserved for global operations to actions specific to the basic function you're performing at the moment. Table 3.7 lists the function-key combinations that apply to fields, grids, and text boxes. (To present complete information, this table repeats some information that appears in the previous tables.)

Table 3.7. Function Keys for Fields, Grids, and Text Boxes

Key	Function
F2	Toggles between displaying the caret for editing and selecting the entire field.
Shift+F2	Opens the Zoom box to make typing expressions and other text easier.
F4	Opens a drop-down combo list or list box.
Shift+F4	Finds the next occurrence of a match of the text typed in the Find or Replace dialog, if the dialog is closed.
F5	Moves the caret to the record-number box. Type the number of the record that you want to display.
F6	In Table Design view, cycles between upper and lower parts of the window. In Form Design view, cycles through the header, body (detail section), and footer.
F7	Starts the spelling checker.
F8	Turns on extend mode. Press F8 again to extend the selection to a word, the entire field, the whole record, and then all records.
Shift+F8	Reverses the F8 selection process.
Ctrl+F	Opens the Find and Replace dialog with the Find page active.
Ctrl+H	Opens the Find and Replace dialog with the Replace page active.
Ctrl++ (plus sign)	Adds a new record to the current table or query, if the table or query is updatable.
Shift+Enter	Saves changes to the active record in the table.
Esc	Undoes changes in the current record or field. By pressing Esc twice, you can undo changes in the current field and record. Also cancels extend mode.

Setting Default Options

new You can set about 100 options that establish the default settings for Access. (But you aren't likely to want to change default options until you're more familiar with Access 2007.) This book is a reference as well as a tutorial guide, and options are a basic element of Access's overall structure, so this section explains how to change these settings.

You set defaults by clicking the Office button to open the gallery and then clicking the Access Options button to open the Access Options dialog's default Popular page (see Figure 3.15). The options you set on the Popular, Datasheet, Object Designers, Proofing, Advanced, Customize, and Add-Ins pages apply to the system as a whole. Settings on the Current Database page apply only to the database that's open when you change the settings.

Figure 3.15 The default Popular page of the Access Options properties dialog sets global option values that apply to all databases you open in Access 2007, as do all other pages except Current Database.

Most settings are option buttons and check boxes, although many other items require multiple-choice entries that you select from drop-down lists. In some cases, you must type a specific value in a text box. After you complete your changes, click OK to close the dialog to save your changes. If you decide not to implement your changes, click Cancel to exit without making any changes. The next few sections and their tables summarize options that affect Access as a whole and those options that affect viewing and printing data in Datasheet view.

The Popular Page

The Popular page (refer to Figure 3.15) contains the following control groups to set the most common default option for all Access databases and projects you create:

Top Options for Working with Access—Enables ClearType for LCD monitors. Also sets the ScreenTip style and default color scheme: Blue, Silver, or Black. (ScreenTips are the formatted ToolTips for ribbon command buttons.)
Creating Databases—Sets the default file format for new database files (Access 2007 .accdb, Access 2002–2003 .mdb, or Access 2000 .mdb). Also specifies the default .accdb or .mdb file location (My Documents for Windows XP; Documents for Windows Vista) and database sort order (General to use the Windows language's sort order).
Personalize Your Copy of Microsoft Office—Lets you change the default username and add or edit initials. The Language Settings button opens the Microsoft Office Language Settings 2007 dialog that's common to all Office 2007 applications (see Figure 3.16). This dialog lets you add additional editing languages and change the default editing language. However, languages other than that of your version of Office 2007 might require additional features, such as a Language Pack, to fully enable editing in those languages.

Figure 3.16 The Microsoft Office Language Settings 2007 dialog lets you make other editing languages available, but you might need additional resources to make full use of those languages.

The Current Database Page

The Current Database Page lets you change default properties of the currently open database or project with controls in the following groups:

Application Options—Lets you specify a custom application title and icon; substitute the custom icon for standard form and report icons; name a startup form to open when Access loads; hide the status bar at the bottom of the Access window; replace tabbed documents with nonmodal (overlapping) windows; disable special access keys (F11 for the Navigation Pane, Ctrl+G for the VBA Editor's Immediate window, and Ctrl+Break to halt VBA code execution); and automatically compact the database after closing the file (see Figure 3.17).

Figure 3.17 The Current Database page's Application Options group includes new option settings for tabbed documents, Layout view, designing tables in Datasheet view, and the Attachments field data type.

You also can remove personally identifiable information from the .accdb or .mdb file; disable Windows XP or Windows Vista themed controls; disable Layout view; disable making design changes in table Database view; disable testing for truncated numbers when changing number format; and convert all image files to Windows bitmap (.bmp) format for backward compatibility.
Navigation—The Display Navigation Pane check box enables hiding the Navigation Pane (see Figure 3.18). The Navigation Options button opens the Navigation Options dialog.

Figure 3.18 The Current Database page's remaining groups are more specialized than Application Options.

Ribbon and Toolbar Options—Lets you replace all ribbons, add groups and command buttons to existing ribbons by selecting a stored RibbonX (XML) document, or discourage users from editing objects. For example, you can specify a custom shortcut (context) menu bar; clear the Allow Full Menus check box to hide all ribbons except Home; and clear the Allow Default Shortcut Menu check box to hide noncustom context menus.

Name AutoCorrect Options—Enables a controversial process for conforming references to renamed Access objects. If you'd rather do the job yourself, clear the Track Name AutoCorrect Info and Perform Name AutoCorrect check boxes. (Don't bother trying Alt+A; all the check boxes have the same shortcut key combination.)

Filter Lookup Options—Lets you disable displaying lookup field lists from indexed, non-indexed, or ODBC fields in linked or client/server tables, or where the lists would have more than a specified number of items. As an example, a lookup list of customers in an orders table might have 10,000 or more items from which to choose, which could cause a substantial performance hit.

The Datasheet Page

The Datasheet page (see Figure 3.19) sets the defaults for table, query, and form Datasheets.

Figure 3.19 The Datasheet page sets design defaults for Datasheet views in new databases.

Following are descriptions of the page's three groups:

Default Colors—Provides color pickers for Font, Background, Alternate Background, and Gridlines colors.
Gridlines and Cell Effects—Enables customizing visibility of horizontal and vertical gridlines, as well as cell special effects and default column width.
Default Font—Lets you change the default 11-point Calibri font to any other Windows TrueType or OpenType font.

The Object Designers Page

The Object Designers page (see Figure 3.20) sets the defaults for table, query, form, and report Design view.

Figure 3.20 The Object Designers page's first two groups set design defaults for table Design view, query Design view, and SQL view.

Following are descriptions of the page's four groups:

Table Design—Sets the defaults for new field data types (Text) and default Text field size (255 characters, the maximum) and Number field size (Long Integer). By default, Access will add an index to any field that contains the characters "ID", "key", "code", or "num". You might want to remove the semicolon-separated string from the text box so that you, not Access, determines when to add indexes fields. Clearing the Show Property Update Options Buttons check box hides the drop-down lists for properties (such as Format) on the General page of table Design view's lower pane, which is not a recommended practice.
Query Design—Lets you disable auto-addition of table names to all query SQL statements, add an all-fields asterisk (*) to all query field lists, or disable automatically creating join lines between related tables or fields with the same name. You also can change the default design font from Segoe UI to a different family and larger size, and specify SQL Server–compatible syntax based on the ANSI SQL-92 standard. With the exception of font size, departing from the default query Design settings isn't recommended.
Forms/Reports—Enables changing how controls on forms and reports are selected (partial or full enclosure) and the names of form and report templates (see Figure 3.21). You can use an existing form or report as a template or create a form or report specifically as a template for the new objects you create. This book uses forms and reports generated from the default Normal templates. Marking the Always Use Event Procedures check box doesn't force Access 2007's Control and other wizards autogenerating VBA code; doing this only prevents wizards from generating embedded macro code.

Figure 3.21 The Object Designers page's last two groups specify design defaults for form and report Design view, and control design error checking.
Error Checking—Enables or disables Design-mode error checking and uses a color picker to select the error indicator smart tag's color.

The Proofing Page

The Proofing page enables customizing the AutoCorrect feature and Office spelling checker for all Access applications (see Figure 3.22).

Figure 3.22 The brief Proofing page lets you modify default AutoCorrect and spelling checker settings.

The Proofing page has these two groups:

AutoCorrect Options—Provides an AutoCorrect Options button to open the Office AutoCorrect dialog.
When Correcting Spelling in Microsoft Office Programs—Lets you set spell-checking options, including custom dictionaries in the Custom Dictionaries dialog, and specify a main dictionary language other than the default English (U.S.).

The Advanced Page

The Advanced page (see Figure 3.23) contains the following five groups:

Editing—Lets you customize the default cursor, arrow key, find/replace, confirmations, Datasheet IME (Input Method Editor) control, and Hijiri (Islamic or Arabic) lunar calendar options. (Saudi Arabia, Kuwait, and Yemen use the Hijiri calendar officially).

Figure 3.23 The Advanced page's Editing group enables customizing data entry defaults and use of the Hijiri calendar.

Display—Enables changing the number of most recently used (MRU) databases displayed in the Office button's gallery; hiding the status bar, animations, smart tags on Datasheets, and Smart Tags on form and reports; and showing the Names and Conditions columns when editing standalone or embedded macros (see Figure 3.24).

Figure 3.24 The Advanced page's Display, Printing, and General groups let you customize 17 more properties.
Printing—Lets you change the default printing margins (0.25 inch).

General—Lets Access raise an error if a RibbonX document for a customized ribbon is incorrect, add audio cues to keyboard and other actions, animate cursors for several operations, and require four-character year formatting for the current database, all databases, or both. The Web Options button opens a dialog of the same name for setting the style of hyperlinks.

Advanced—Enables specifying the last-opened database as the default when opening Access, changing the default open and record-locking mode, setting OLE/DDE and ODBC properties, and specifying command arguments to be used when starting Access (see Figure 3.25).

Figure 3.25 The Advanced page's Advanced group contains controls to set orphaned properties' default values.

The Customize Page

The Customize page lets you add command buttons—represented by 16x16-pixel icons—from any standard ribbon to the Quick Access Toolbar. The Customize page opens with a list of popular commands and their icons in the left list box and an Add button to move selected commands to the right list box, which contains the default Save, Undo, and Redo commands (see Figure 3.26). Access 2007 has more than 1,000 unique icons; this book uses about 200 different icons to identify commonly used command buttons.

Figure 3.26 The Customize page opens with the three default commands for the QAT and the Popular Commands list for adding QAT commands.

The Choose Commands From list lets you select commands from Access's 28 ribbons (tabs) or five other categories.

You can add the most popular commands to the QAT by clicking the arrow button to the right of the QAT to open the menu shown in Figure 3.27 and clicking the commands to add. Alternatively, right-click any command button in the selected ribbon and choose Add to Quick Access Toolbar from the context menu.

Figure 3.27 Clicking the arrow to the right of the QAT opens this menu, which lets you add the most popular commands quickly.

The Add-Ins Page

The Add-Ins page lets you manage Microsoft and third-party COM (Component Object Model) and Access add-in applications (see Figure 3.28). Microsoft includes a single COM add-in for managing replication conflicts, which is enabled only when necessary and isn't applicable to Access 2007 applications.

Figure 3.28 The Add-Ins page displays a single disabled COM add-in for resolving replication problems with earlier database versions.

Selecting COM Add-Ins in the Manage list and clicking Go opens the COM Add-Ins dialog, which lets you enable, add, or remove COM add-ins. Selecting Access Add-Ins and clicking Go opens the Access Add-In manager dialog, which lets you Add New or UnInstall Access add-in libraries (.accda, .accde, .mda, or .mde files). Third-party add-in suppliers usually include detailed instructions for installing and using their add-ins.

NOTE

The preferred add-in architecture for Access 2007 is managed COM add-ins created with Visual Studio 2005 or later. Visual Studio 2005 Tools for Office, Second Edition doesn't support Access 2007, so you must use Visual Studio's Shared Add-In Template to create them. Writing Access 2007 add-ins is beyond the scope of this book, but you can find more details on the process in Microsoft's "Creating Managed Add-Ins for Access 2007" white paper at http://msdn2.microsoft.com/en-us/library/aa902693.aspx.

The Trust Center Pages

The opening Trust Center page consists of links to Microsoft privacy statements and Microsoft Trustworthy Computing propaganda. The only feature of interest on this page is the Trust Center Settings button, which opens a second Trust Center page to establish Access-wide security settings.

The second Trust Center page offers the following subpages.

Trusted Publishers

Trusted Publishers can apply digital signatures from a code-signing certificate to Access packages or VBA code and class modules. Signing an Access package certifies that all database objects, not just code, have not been modified since being signed. If the certificate is valid, the database (and its code) is considered trusted when the user extracts it.

If you want to test code-signed packages without spending U.S.$99 to U.S.$199 per year, you can create a self-signed certificate with the SelfCert.exe application available at the \Programs\Microsoft Office\Microsoft Office Tools\Digital Certificate for VBA Projects. Figure 3.29 shows the Trusted Publishers page displaying a self-signed certificate for OakLeaf Systems.

Figure 3.29 A self-signed certificate, such as the OakLeafCodeSigningCertificate, can be used to create a package that doesn't generate a security warning upon extracting the database.

Trusted Locations

new Placing .accdb files in a trusted location (folder) is the most practical method to eliminate the need to enable VBA code and potentially dangerous macro actions for each Access 2007 session. By default, Access trusts the \Program Files\Microsoft Office\Office12\ACCWIZ folder that holds all Access wizard files, as shown in Figure 3.30.

Figure 3.30 Access automatically trusts the \Program Files\Microsoft Office\Office12\ACCWIZ folder so that Wizards will run without generating a security warning.

You add other folders and their subfolders as trusted documents by clicking the Add New Location button to open the Microsoft Office Trusted Location dialog, browsing to the folder you want to trust, marking the Subfolders of This Location Are Also Trusted check box (if applicable), adding an optional description, and clicking OK. You no longer see the security warning in the message bar when you open the database from the trusted location.

Add-Ins, Macro Settings, Message Bar, and Privacy Options

new The remaining Trust Center pages resemble groups of other Access Options pages (see Figure 3.31). The options names are sufficiently self-describing as to not warrant relisting here. The default selections shown in Figure 3.31 should be satisfactory for most applications.

Figure 3.31 The Add-Ins, Macro Settings, Message Bar, and Privacy Options pages might better have been grouped on a single page.

The Resources Page

The Resources page has the following buttons, many of which were choices of earlier versions' Help menu:

Check for Updates—Launches Internet Explorer (IE) 7 and runs Windows Update to check for operating system and Office 2007 updates.
Diagnose—Runs the Microsoft Office Diagnostics application to test for known solutions, check memory, verify other programs' compatibility with Office 2007, verify fixed disk(s), and validate Office 2007 setup programs.
Contact Us—Opens Office Online's Contact Us page, which has links to support sources, the international support website, customized Office support for developers and IT professionals, and Office Live support.
Activate—Starts the Activation Wizard or opens a "This product has already been activated" message box.
Go Online—Opens Office Online's default Office 2007 welcome page where you can register with a Windows Live ID (formerly Microsoft Passport account) for additional online services.
About—Opens the About Microsoft Office Access dialog, which has System Info and Tech Support buttons. Clicking System Info opens the System Information dialog shown in Figure 3.32. Clicking Tech Support opens a dialog with vague recommendations for obtaining support.

Figure 3.32 The System Information window, shown here running under Windows XP Professional, displays information on your hardware, system settings, and the applications you've opened.

TIP

If you have a serious problem with Access 2007 or other Office 2007 applications, a Microsoft Technical Support representative might request that you send a System Info (MSInfo, .nfo) file for inspection. To create an .nfo file in Windows XP, choose File, Save and supply a filename.

The .nfo file contains a substantial amount of information about your PC and the programs you've installed, which is needed to troubleshoot major problems, but .nfo files don't include confidential personal or corporate information, such as usernames and passwords.

Creating a Customized Template File

Once you've set the options for all databases and the current database, you might want to use the database as a template for all new databases you create. You can specify the database to use as the template for all new databases you create by saving it as \Program Files\Microsoft Office\Templates\1033\Access\Blank.accdb. This location is called the System Template Folder.

Alternatively, you can save it under Windows XP as \Documents and Settings\Application Data\Microsoft\Templates\Blank.accdb or under Windows Vista as \Users\UserName\Documents.

Using Access Online Help

new 158icon01 Access 2007 and other Office 2007 members share a common online help system that differs markedly from that of earlier releases. Access 2007's sizable Access Help window consists of a Table of Contents pane with a treeview list and, when you first click the Help button, the default Browse Access Help list in the right (content) pane (see Figure 3.33).

Figure 3.33 The Access Help window opens in normal (resizable) window mode and occupies the entire display by default. The Search menu lets you select the scope of a keyword search.

Unless you clear the Search Microsoft Office Online for Help Content When I'm Connected to the Internet check box on the Trust Center's Privacy Options page, help content from Office Online supplements the local computer's help files.

Searching for a Phrase

Typing a phrase without enclosing it between double quotes causes the help system to return topics with any of the words present. For example, typing Attachment data type in the Search text box and clicking the Search button returns more than 100 topics (see Figure 3.34). Many are obviously unrelated topics, such as "Enter or edit data in a control or column that supports rich text" and "Type ¢, £, ¥, ®, and other characters not on the keyboard." Clicking the link to open the topic in the right pane, pressing Ctrl+F to open IE 7's Find dialog, typing Attachment in the Find text box, and clicking Next or Previous returns no hits. Figure 3.34's Searched for: "Attachment data type" header incorrectly indicates that the search was for an exact phrase, despite missing quotes in the search expression.

Figure 3.34 Searching for multiple words quotes the Searched For expression, which erroneously indicates searches for a phrase.

Enclosing the search term in double quotes returns the four topics shown in Figure 3.35, which contain the exact phrase, as shown for the "Which file format should I use in Access 2007?" topic in Figure 3.36.

Figure 3.35 Searching for a quoted phrase in the text box wraps the Searched For expression in pairs of double quotes.

Figure 3.36 The text of the first topic shown in Figure 3.35 contains the expected "Attachment data type" phrase.

Searching Other Sources

Clicking the All of Microsoft Office Online link in the results page (refer to Figure 3.35) returns a web page with five topics; the additional topic is for InfoPath's "Insert a file attachment control" topic. An All of Office Online search doesn't restrict the scope to Access 2007 or any Access version.

Clicking the Get Answers from Other Access Users link opens a web form that requests you to type a question of 4 to 10 words. Typing How do I use Access's Attachment data type? in the text box and clicking Go returned the thread from the microsoft.public.access.modulesdaovba newsgroup whose header is shown in Figure 3.37. The thread contains very detailed answers to the question.

Figure 3.37 Microsoft newsgroups, such as microsoft.public.access.modulesdaovba and its siblings, supplement local help files and online help.

Clicking the Support Knowledge Base link executes a Knowledge Base search with Access 2007 as the search product and the quoted phrase as the search term. The first search results page contains Show Me links for Access 2007 and Access. Clicking Access 2007 opens the page shown in Figure 3.38, which adds Need More Help? and Recent KB Articles links.

Figure 3.38 Microsoft Knowledge Base (KB) articles are another good source of information on Access bugs, issues, and anomalies.

Spelunking the Database Utilities

Access 200x offered eight utility functions that you could access by choosing Tools, Database Utilities. Following are the locations of these tools in Access 2007:

Convert Database becomes Office, Save As, Access 2000 Database, Access 2002 - 2003 Database, or Access 2007 Database.
Compact and Repair Database moves to Office, Manage, Compact and Repair Database, which checks the database for consistency, repairs problems found, and then compacts it to save disk space. Access automatically replaces the existing database with the compacted or repaired version.
Back Up Database moves to Office, Manage, Back Up Database and opens the Save Backup As dialog and proposes to save your current database file as FileName_YYYY-MM-DD.accdb. Using the backup feature is a bit faster than making a copy with Windows Explorer.
Linked Table Manager moves to the Linked Table Manager command in the Database Tools ribbon's Database Tools group. It tests for the existence of linked .accdb or other types of data files and, if the links aren't valid, lets you change the path to the linked files. This choice is disabled if you don't have a database with linked tables open.
Database Splitter becomes the Access Database command in the Database Tools ribbon's Move Data group. It divides a single-file Access .accdb application with application and data objects into a front-end .accdb file and a back-end Access database. This choice is disabled if you don't have a database open. Chapter 19, "Linking Access Front Ends to Access and Client/Server Tables" covers linking to tables in an Access back-end database.
Switchboard Manager moves to a Switchboard Manager command in the Database Tools ribbon's Database Tools group. It creates a new Switchboard form if one isn't present in the current database and lets you edit the new or an existing Switchboard form. This choice is also disabled if you don't have a database open.

Upsizing Wizard becomes the SQL Server (w) command in the Database Tools ribbon's Move Data group. It lets you move tables and queries from the current Access database to SQL Server 2005 [Express] and, optionally, change the .accdb file containing application objects to an Access Data Project (.adp) file. Chapter 19 describes how to use the Upsizing Wizard to link an .adddb front end to SQL Server tables. Chapter 22, "Upsizing Access Applications to Access Data Projects," covers creating ADPs.
Make MDE File moves to the Make ACCDE command of the Database Tools ribbon's Database Tools Group. It creates a secure copy of the file, which prevents users from opening objects in Design view and viewing or changing VBA code.

Compacting and Repairing Databases

After you make numerous additions and changes to objects within a database file—especially deletions of large amounts of data in tables—the database file can become disorganized. When you delete a record, you don't automatically regain the space in the file that the deleted data occupied. You must compact the database to optimize its file size and the organization of data within the tables that the file contains. When you compact an Access file, you regain space only in 32KB increments.

To compact the current database, do the following:

Open the database you want to compact.
Choose Office, Manage, Compact and Repair Database. Access immediately closes the database and begins compacting it.

When Access finishes compacting the database, it opens the database and returns you to where you were in the application before. Your compacted database is stored with the same name it had before you compacted it.

A database can become corrupted as the result of the following problems:

Hardware problems that occur when writing to your database file, either locally or on a network server
Accidentally restarting the computer while Access databases are open
A power failure that occurs after you make modifications to an Access object but before you save the object

Occasionally, a file might become corrupted without Access detecting the problem. This lack of detection occurs most frequently with corrupted indexes. If Access or your application behaves strangely when you open an existing database and display its contents, try compacting and repairing the database.

Periodically compacting and repairing production database files usually is the duty of the database administrator in a multiuser environment, typically in relation to backup operations. You should back up your existing file on disk or tape before creating a compacted version. When you're developing an Access 2007 database, you should compact and repair the database frequently. Access 2007 databases that are not compacted grow in size much more rapidly during modification than earlier versions.

Converting Earlier Database Formats to Access 2007 Format

To convert earlier Access version .mdb database or .mda library files created with Access 95 through Access 2003 to the new database format of Access 2007, open the file in Access 2007 and click Office, Save As, Access 2007 Database. Chapter 31, "Upgrading Access 200X Applications to Access 2007," covers this conversion process in detail.

Creating .accde Files

An .accde file is a special version of an Access .accdb file. In an .accde file, all VBA code is stored only in compiled format, and the program source code for that database is unavailable. Also, users can no longer modify forms, reports, queries, or tables stored in that database, although those objects can be exported to other databases. Typically, .accde databases are used to create libraries of add-in wizards, deliver custom database applications intended for commercial or in-house distribution, and provide templates for forms, reports, queries, and other objects for use in other databases.

165icon01 You can convert any Access 2007 .accdb database to an .accde file by opening the file, clicking the Database Tools tab, and clicking the Make ACCDE button to open the Save As dialog. Navigate to the location for the .accde file and click Save to create and save the file. Then close the dialog.

Creating .accdr Runtime Files

new An .accdr file is called a runtime Access file. You create a runtime file simply by changing FileName.accde to FileName.accdr. The .accdr version hides the ribbon and Navigation Pane, so you must provide a switchboard or equivalent to open forms and reports. The QAT is disabled and the Office button's gallery offers Print, Close Database, and Exit buttons only. Runtime Access files provide a minimalist UI, as illustrated by Figure 3.39.

Figure 3.39 Access Runtime (.accdr) files open with the ribbon and Navigation Pane hidden and disabled.

The problem with .accdr files is that users quickly discover they can simply change the file extension from .accdr to .accdb to regain lost design and navigation features.

Packaging, Signing, and Distributing an Access 2007 Database

new The Publish menu of Office button's gallery offers the following two choices:

Document Management Server—Publishes the database to a WSS 3.0 or MOSS 2007 site and enables users to open a read-only or read-write copy, depending on their group membership. Chapter 25 shows you how to share databases from a SharePoint document library.
Package and Sign—Creates an Access Deployment file (.accdc) whose origin and integrity is certified by a digital signature. You can deploy database copies from an .accdc file published to a SharePoint document library. The sections that follow describe how to generate a self-signed digital certificate and then use the certificate to sign an Access Deployment file.

Generate and Install a Self-Certified Digital Certificate

To create a self-signed certificate, do the following:

Choose Start, Programs, Microsoft Office, Microsoft Office Tools, Digital Certificate for VBA Projects to open the Create Digital Certificate dialog. Type the name for the certificate in the text box (see Figure 3.40).

Figure 3.40 Type a certificate name in the text box and click OK to add the certificate to Windows' Personal certificate store.

NOTE

The dialog's boilerplate warns that Office only trusts the certificate on the machine on which you create it. Tests indicate that you can copy the certificate (.cer) file to another machine, then add the certificate to the Trusted Root Certification Authorities, Trusted Publishers, and Persons categories, and achieve the same result as if the certificate was created on the local computer.
Click OK to add the certificate to the Personal category of the Windows certificate store, which is managed by IE.
Launch IE. Click IE 7's Tools button or choose Tools, Internet Options in earlier versions to open the paged Internet Options dialog. Click the Content tab and Certificates button to open the Certificates dialog, and click the Personal tab to display the certificate you created in step 2 (see Figure 3.41).

Figure 3.41 IE's Certificates dialog displays the new self-signed certificate on the Personal page.

NOTE

The certificate isn't valid at this point because you aren't a trusted root certification authority. You must export the certificate to a file and then import it to the Trusted Root Certification Authority page to enable trust. If you double-click the entry for the certificate, a "This CA Root certificate is not trusted" warning appears on the Certificate dialog's general page.
Select the certificate, click Export to start the Export Certificate Wizard, and click Next to open the Export Private Key dialog. This option isn't available for self-signed certificates, so click Next to open the Export File Format dialog.
Accept the default DER-Encoded Binary X.509 (.CER) option, and click Next to open File to Export dialog.
Click Browse to open the Save As dialog, navigate to a folder in which to save the certificate, type a filename (OakLeafCodeSigningCertificate for this example), and click Save to save the file with a .cer extension. Click Finish to dismiss the Wizard and acknowledge the "Certificate export was successful" message.
Click the Certificates dialog's Trusted Root Certificate Authorities tab, click the Import button to start the Certificate Import Wizard, and click Next to open the File to Import dialog.
Click Browse to open the Open dialog, navigate to the location you specified in step 6, and double-click the certificate file. Click Next to open the Certificate Store dialog.
Accept the default Place All Certificates in the Following Store option, verify that the Certificate Store is Trusted Root Certificate Authorities, and click Next and then Finish to display a security warning (see Figure 3.42).

Figure 3.42 This security warning appears for any certificate you add to the Trusted Root Certificate Authorities list. The default list contains most generally accepted certificate authorities (CAs).
Click Yes to add the certificate to the Trusted Root Certificate Authorities group. Double-click the item to verify the certificate (see Figure 3.43).

Figure 3.43 A certificate with the purposes "Ensures software came from software publisher" and "Protects software from alteration after publication" is a code-signing certificate.
Repeat steps 7 to 9, except substitute Trusted Publishers for Trusted Root Certificate Authorities in each step. In this case, you don't receive the security warning described in step 9.
Click Office, Access Options, Trust Center, Trust Center Settings, Trusted Publishers and then verify that your certificate appears in the Trusted Publishers list (refer to Figure 3.29).
Click Macro Settings, and select the Disable All Macros Except Digitally Signed Macros option (refer to Figure 3.31). Click OK twice to save your changes and return to your source database.

NOTE

If you don't make the change in step 13, you'll see a security warning in the message bar when you test the extracted file in the next section.

Creating, Signing, and Testing the Package

To create, sign, and test the package file, do the following:

Open the .accdb file to package (preferably a database with VBA code in a class module or standalone module), and choose Office, Publish, Package and Sign to open the Select Certificate dialog.
Select the certificate you created in the preceding section (see Figure 3.44), and click OK to open the Create Microsoft Office Access Signed Package dialog.

Figure 3.44 You must specify a valid code-signing certificate before creating the package.
Set the distribution location, accept the filename and .accdc extension, and click Create to sign and compress the .accdb file to the .accdc file.
Navigate to the distribution location, and double-click the .accdc file to open the Extract Database To dialog.
Navigate to the location in which to save the .accdb file (not a trusted location), and click OK to extract the signed database file.
Open the extracted .accdb file.

Troubleshooting

trouble_shooting

Invalid Database Locked Messages When Compacting in Place

You receive the "database that is already opened" message shown in Figure 3.45 when you attempt to compact and repair the currently open database in place.

Figure 3.45 This message indicates that the operation you're attempting can't be completed because another instance of the database is running or an exclusive lock on the database file hasn't been released.

This message occurs if you—or you and another user—have two copies of Access running with the same database open. If you know that you have only a single instance of the database open, the message is the result of a locking bug. In most cases, closing and reopening Access solves the locking problem. If not, you need to reboot Windows and try again.

Compile Errors in the Convert Database Process

Error messages appear when converting to Access 200x from early Access versions.

Access 2.0 and earlier were 16-bit applications. The first error message you might receive is "There are calls to 16-bit dynamic-link libraries (.dll) in this application." In this case, you must alter the code of Declare statements to call the current 32-bit equivalents of the 16-bit DLLs. For example, you must change calls to functions in User.dll, Kernel.dll, and Gdi.dll to User32.dll, Kernel32.dll, and Gdi32.dll.

A more common error message when converting Access 2.0, 95, and 97 applications is "There were compilation errors during the enabling or conversion of this database." If you're converting from Access 2.0, many of these errors are likely to arise from Access Basic reserved words and symbol usage that VBA 6.0 doesn't support. Similar problems occur with applications that originated in Access 2.0 or earlier and were converted to Access 9x. In some cases, conversion of earlier application versions to Access 97, and then to Access 2007 format is easier than attempting direct conversion. See Chapter 31 for additional information on conversion issues.

In the Real World—Reading the Ribbon UI's Tealeaves

Most computer analysts and pundits have given 2007 Microsoft Office System's new Ribbon UI one or two thumbs up. The general consensus appears to be that replacing hierarchical menus and toolbar buttons with ribbons containing command buttons and galleries aids users in discovering application features. But comparative usability studies might have been skewed by the use of IntelliMenus and rafted toolbars in Office 2000 and later.

IntelliMenus, also called personalized or adaptive menus, attempted to cause usage patterns to determine which menu choices should appear by default. The most popular choices appeared first in a "short menu." After a few seconds (or if you clicked a chevron icon at the bottom of the menu list), the hidden choices expanded the list to a "long menu." After a few hours or days of work, users saw only short menus with the choices that they used frequently.

Jensen Harris, Group Program Manager of the Microsoft Office User Experience Team, made these basic observations about IntelliMenus in his Office User Interface Blog (http://blogs.msdn.com/jensenh/):

"There was no way to get the default 'short' menu right."
"Once the default short menu was wrong, the user was forced to scan the menu."
"Auto-customization, unless it does a perfect job, is usually worse than no customization at all."

Office versions also adopted "rafted toolbars," which enabled more than one toolbar to occupy the same vertical display space by exiling lesser-used buttons to an overflow (more buttons) area. According to Harris, rafted toolbars had the same deficiencies as IntelliMenus—just replace "menu" with "toolbar" in the preceding list. Another "feature" of rafted toolbars was the ability to drag and anchor them to any side of an Office application's window or allow them to float in its workspace.

The Ribbon UI eliminates the use of adaptive menus and rafted toolbars problems, but these miscreant approaches were wrong from the git-go. However, the new face on Access 2007 doesn't get rid of all hierarchical menus. Most galleries and many context menus have one or more levels of additional choices.

Jensen said in a December 2005 presentation to the BayCHI, the San Francisco Bay Area chapter of the Association for Computer Machinery (ACM) Special Interest Group on Computer-Human Interaction (SIGCHI), that fewer than 2% of Microsoft Word users customize it intentionally. However, the percentage of Access 2003 and earlier users and developers who customized Access toolbars and menus is probably closer to 20%. The arcane RibbonX approach to modifying or extending ribbons, groups, or galleries is far more complex than customizing toolbars and menu bars with Access macros or VBA.

When this book was written, the jury was out on the extent to which the new Ribbon UI increases the efficiency of Access users and developers, if at all. Forrester Research has determined that workers migrating to Microsoft Office 2007 will require "more intense" training than expected. This factor contributes to Forrester's estimate that most organizations won't upgrade to Office 2007 for three to five years. At the risk of damnation by faint praise, there's no question that the ribbon is a far better UI metaphor than Microsoft Bob.

P.S.: If you've never seen Microsoft Bob, the 1995 GUI shell intended to overlay and simplify the Windows 3.1 and 95 UI for new users, check out http://toastytech.com/guis/bob.html.