Page #254 (Office-Wide Changes)

Access has always been a great database system. As it has evolved, it has gained even more functionality, including new import and export formats, database replication, data access pages, and, most recently, PivotTables and PivotCharts.

Access users will be pleased to see that Access now supports multiple Undo and Redo commands-a long-awaited feature. With enhanced PivotTables and brand-new PivotCharts, users can analyze their data from different perspectives in either tabular or graphical format.

For Access programmers, the new Printer object and Printers collection make working with printers in code much easier. Access projects are now easier to work with, and Access now supports Extensible Markup Language (XML) as an import and export format.

Data access pages are easier to design, and a new banded report format allows data levels to be expanded or collapsed. A Save To Data Access Page selection for forms lets you quickly create a data access page from an Access form.

These changes and many others make Access even more powerful and easier to use. The sections that follow take a closer look at the changes and improvements specific to Access 2002.

New Database Format

Access 2002 offers a new database format designed to better handle new properties and features, including some that might be available in future versions of Access. If you don't need to share a database with other users who are running earlier versions of Access, you can convert an existing database to the new format, or you can create a new database in Access 2002 format, which will let you use great new features such as PivotCharts.

The new database format is not the default format for new databases in Access 2002. Unless you manually change the Default File Format setting on the Advanced tab of the Options dialog box, new databases will be created in Access 2000 format. Figure 1-2 shows the Advanced tab of the Options dialog box with the default Access 2000 selection in the Default File Format box.

Figure 1-2. The Access 2000 format is still the default selection for new databases.

The database format is displayed in a database's title bar, in parentheses after the database name, as shown in Figure 1-3.

Figure 1-3. The Access 2002 database format is indicated in a database's title bar.

For more information about working with databases in different Access versions, see Appendix A, "Setup and Installation."

Improvements to Compact And Repair

The Compact And Repair utility has two components (which were separate utilities prior to Access 2000). The Compact component reduces database size by removing temporary objects, sometimes resulting in an amazing reduction of size-as much as 90 percent compaction. The Repair component repairs some database problems.

The Compact And Repair utility has been improved so that it is able to repair databases containing broken forms and reports more frequently than before. Additionally, it appears that some earlier version databases with corrupted forms or reports are repaired when they are converted to Access 2002 format, even without using Compact And Repair.

For more information on working with the Compact And Repair database utility, see Chapter 15, "Using Add-Ins to Expand Access Functionality."

Better Handling of Broken References

Access 2002 handles broken references better than earlier versions do, and it provides more informative error messages when references to code libraries can't be found. This makes it easier to correct broken references. If a back-end table is moved or renamed, however, you'll still get the Could not find file message. When you convert a database from an earlier version of Access, generally any references to Microsoft components (such as DAO, Word, or Outlook) will automatically be upgraded to the correct version. But if you have references set to non-Microsoft products, they might not be upgraded. Figure 1-4 shows the error message for a missing reference to the Find And Replace MDE file in a database converted from Access 97. (Find And Replace is an Access add-in.)

Figure 1-4. This error message offers details about a missing reference in a converted database.

Multiple Undo and Redo

Support for multiple Undo and Redo commands is a long-awaited Access feature now available in Design view for database (MDB) tables and queries; Access project (ADP) views, stored procedures, and functions; and forms, reports, data access pages, macros, and modules. The Design toolbars of most Access database objects now have drop-down Undo and Redo action lists. The Undo and Redo lists for tables, queries, forms, reports, and macros work just like their familiar counterparts in Microsoft Word.

In modules, the Undo/Redo functionality is slightly different: Undo and Redo buttons are available but not action lists, so to undo multiple actions, you simply click the Undo button repeatedly. Figure 1-5 shows the Undo list for an Access form in Design view.

Figure 1-5. You can open the Undo drop-down list in an Access form in Design view.

The new multiple Undo/Redo functionality has some limitations. For example, the list of items that can be undone isn't saved when you switch between views for MDB tables, ADP views, ADP stored procedures, ADP functions, and data access pages. (This limitation is not surprising, given that these are quite different types of objects.) Linked tables don't have Undo functionality because their structures can't be modified-instead, they must be modified in their native databases. And Undo doesn't work in PivotTables (although it does work-at least for some actions-in PivotCharts).

Undo functionality has not changed for Datasheet and Form views; they have only a single-level Undo for changes to data.

PivotChart and PivotTable Views

Access 2002 forms have two new views, PivotChart and PivotTable, that enable you to look at your data in new ways. PivotCharts and PivotTables give you the flexibility to dynamically change the way your data is summarized and displayed by moving rows and columns and rearranging various elements on a form. As soon as you make the change, the PivotChart or PivotTable is redrawn, showing a new view of your data.

Access 2002 provides a wizard to help you design these views, although PivotTables and PivotCharts are quite easy to create without a wizard, so it isn't needed as much as for some other database objects. PivotTables are actually Excel objects embedded in Access forms, so some of the tools on the PivotTable and PivotChart toolbars resemble their Excel counterparts. Figure 1-6 shows a PivotTable listing the number of orders for products by country and salesperson.

For more information on PivotCharts and PivotTables, see Chapter 12, "Using PivotTables and PivotCharts to Analyze Data."

Figure 1-6. This PivotTable displays orders by country and salesperson and allows users to swap rows and columns if desired.

New Form and Control Events

While Access was already rich in form and control events, Access 2002 has a number of new events for forms and reports, which give Access programmers more control over the appearance of reports (in print preview) and make it possible to run code from even more user actions than before.

Table 1-1 describes the new events available for forms. Many of these events apply only to PivotTable or PivotChart views.

When the event name used in the properties sheet differs from the event name used in code (and listed in the Object Browser), Tables 1-1 and 1-2 list the code version of the event name in parentheses after the properties sheet version.

Table 1-1. New form events

Event	Description
OnUndo	Occurs when a user undoes all edits to a form
OnMouseWheel (MouseWheel)	Occurs when the user rolls the mouse wheel in Form, Datasheet, PivotTable, or PivotChart view
BeforeScreenTip	Occurs before a ScreenTip is displayed for an element in a PivotTable or PivotChart
OnCmdEnabled (CommandEnabled)	Occurs when an Office Web component determines whether the specified command is enabled
OnCmdChecked (CommandChecked)	Occurs when an Office Web component determines whether the specified command is checked
OnCmdBeforeExecute (CommandBeforeExecute)	Occurs before a specified command is executed
OnCmdExecute (CommandExecute)	Occurs after a specified command is executed
OnDataChange (DataChange)	Occurs when certain properties are changed or when certain methods are executed in PivotTable view
OnDataSetChange (DataSetChange)	Occurs when the data set changes in a data-bound PivotTable
OnPivotTableChange (PivotTableChange)	Occurs when the specified PivotTable field, field set, or total is added or deleted
OnSelectionChange (SelectionChange)	Occurs when the user makes a new selection in a PivotTable or PivotChart
OnViewChange (ViewChange)	Occurs when the specified PivotTable or PivotChart view is redrawn
OnConnect	Occurs when a PivotTable connects to a data source
OnDisconnect	Occurs when a PivotTable disconnects from a data source
BeforeQuery	Occurs when a PivotTable queries its data source
OnQuery (Query)	Occurs when a PivotTable query becomes necessary
AfterLayout	Occurs after all charts in a PivotChart have been laid out, but before they have been rendered
BeforeRender	Occurs before any object in a PivotChart has been rendered
AfterRender	Occurs after the specified object in a PivotChart has been rendered
AfterFinalRender	Occurs after all elements in a PivotChart have been rendered

Controls have two new events that work similarly to form events of the same name. (See Table 1-2).

Table 1-2. New control events

Event	Description
OnDirty (Dirty)	Occurs when data in a control is changed
OnUndo	Occurs when a user undoes changes to data in a control

New Form and Report Properties and Methods

Access offers several new properties and methods that give you increased control over the appearance and function of the forms and reports you create. These new capabilities include the following:

• You can now create a custom icon on the taskbar for forms and reports as well as for the application itself (replacing the standard form and report icons). To create a custom icon for forms and reports, choose Tools, Startup to open the Startup dialog box, select an icon file, and check the Use As Form And Report Icon check box, as shown in Figure 1-7. Both the application and its forms and reports will use this custom icon on the taskbar.

  You might have to close the database (or a form or report) and then reopen it to display the new icon. This is particularly likely when you're changing back to the standard icon from a custom icon.

  

  Figure 1-7. You can specify that the application icon should also be used for forms and reports displayed on the taskbar.

• A number of properties previously available only for forms are now also available for reports: Modal, PopUp, BorderStyle, AutoResize, AutoCenter, MinMaxButtons, CloseButton, and ControlBox. These properties give developers more control over how a report looks in print preview.
• The OpenReport method now has a windowmode argument that lets you open a report in hidden mode or as an icon (minimized).
• Both forms and reports now have a Move method that you can use to move and resize the form or report (like the old MoveSize action). This means that you can move or resize a form without first selecting it, which is much more convenient. (MoveSize works only on the currently selected object.)

Shortcut Keys and Accessibility

A number of new shortcut keys and accessibility features make Access much easier to use without a mouse. Table 1-3 lists these shortcut keys.

Table 1-3. New Access shortcut keys

Shortcut key	Description
F4	In Design view, opens the properties sheet
F7	When a form or report is open in Design view (with the focus on the Design view window or a properties sheet), takes the user to the code window, open to the form or report code module
Shift+F7	When the focus is on a properties sheet in Design view, moves the focus back to the design area without closing the properties sheet
F8	In a form or report in Design view, opens the field list; in a data access page in Design view, toggles the field list on or off
Ctrl+Right Arrow key or Ctrl+period	Moves to the next view when you're working with tables, queries, forms, reports, pages, views, and stored procedures
Ctrl+Left Arrow key or Ctrl+comma	Moves to the previous view when you're working with tables, queries, forms, reports, pages, views, and stored procedures
Ctrl+Tab	Navigates from a form or report section to a subsection
Enter	In Design view, with a field selected in the field list in a form or report, adds the selected field to the form or report design surface

For more information on working with forms in Design view, see Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data."

Interface Enhancements

Even if you're comfortable with the Access interface and didn't see any need for enhancements, you'll find that the following changes to the user interface in Access 2002 make it easier for you to view your information the way you want to see it and to get the help you need without leaving the Access windows:

• Two new zoom powers, 1000% and 500%, have been added to the Zoom option for Print Preview.
• The Access menu bar now provides an Ask A Question box, where you can quickly enter a word or phrase to search for in Help. The drop-down list displays the previous few questions, so you can ask the same question again if you need to. After entering a word or phrase and pressing Enter, you'll see a list of appropriate Help topics to choose from. Figure 1-8 shows the list of topics provided after the term PivotTable is entered.

  

  Figure 1-8. Type a phrase into the Ask A Question box to view a list of relevant Help topics.

• You can now open a subform in its own window either by right-clicking the subform and choosing Subform In New Window on the shortcut menu or by selecting the subform and choosing View, Subform In New Window. (These improvements also apply to subreports.) This is not so much a new feature as a restoration of a much-missed feature in earlier versions of Access.

Conversion Error Logging

When you're converting an Access 95, Access 97, or Access 2000 database to either Access 2000 or Access 2002 format, any errors that occur during the conversion are logged to a table. You'll find this table helpful when you need to track down and fix any conversion problems.

Figure 1-9 shows the Conversion Errors table for an Access 97 database converted to Access 2000 format (which, as mentioned, is the default format in Access 2002).

Figure 1-9. The Conversion Errors table helps you find and fix any conversion errors.

Expanded Programmability

Access 2002 provides several new properties and methods that let Access programmers obtain information about database objects, perform housekeeping chores, add or remove items in a list, and more, including the following:

• You can now pass a database password when you open a database programmatically by using the new bstrPassword parameter for the OpenCurrentDatabase method of the Access Application object.
• You can now obtain the DateCreated and DateModified properties for any Access object by using the new DateCreated and DateModified properties of the AccessObject object. For example, the following expression yields the date on which the frmColors form was last modified:

   CurrentProject.AllForms("frmColors").DateModified 

• Combo boxes and list boxes now have AddItem and RemoveItem methods, making them work more like these controls do on Microsoft Visual Basic and Visual Basic for Applications (VBA) forms. These methods can be used only when a combo box's or list box's RowSourceType property is set to Value List.

  See Chapter 6, "Working with Form Controls," for an example of code that uses the AddItem and RemoveItem methods.

• The Access Application object now has a CompactRepair method to use in code. This method corresponds to the Compact And Repair Database command. (Choose Tools, Database Utilities.)
• The Access Application object has a new ConvertAccessProject method that you can use to convert an Access project from one Access version to another.
• The Access Application object has a new BrokenReference property. This is a Boolean property that tells you whether a database has any broken references. (You still have to iterate through the References collection to locate and fix any broken references.)
• When a module is edited and saved without compiling, only the changed module is saved, and then the entire project is saved when the project is compiled. This can save a good deal of compile time, particularly in large databases.

Printer Object and Printers Collection

The Access object model has a new Printer object and a Printers collection, making it much easier to work with printers in code than the old PrtDevMode, PrtDevNames, and PrtMip properties of reports, which were hard to understand and use. The Printer object is far more intuitive-it has properties corresponding to the options in the Page Setup dialog box.

The Printer object and Printers collection let you print reports on a specific printer, using the appropriate paper sizes and trays and special features such as duplexing, without having to first open the report and save it with that printer selected.

The Printer object and Printers collection are described in more detail in Chapter 20, "Customizing Your Database Using VBA Code."

Better Support of Multilingual Text and Graphics

Access 2002 adds significant new support for meeting the challenges of working in multiple languages, including the following:

• If you install the required fonts, multilingual text (English, Asian, and complex script) is displayed better in tables, forms, and reports.
• You can now output Access objects to Unicode RTF, HTML, text, and Excel file formats, thereby preserving multilingual text.
• A new Spelling tab has been added to the Options dialog box (accessed by choosing Tools, Options), allowing you to select a number of languages and other language-specific options. Figure 1-10 shows the Spelling tab with the Canadian French dictionary language selected.

  

  Figure 1-10. The new Spelling tab offers a wide selection of language-related options.

• A new International tab, shown in Figure 1-11, has been added to the Options dialog box, allowing you to select reading directions and other options for complex script languages.

  

  Figure 1-11. The International tab of the Options dialog box provides support for languages with complex scripts.

• Grouped line manipulation has been improved for Asian versions of Access. You can group a set of table-like lines, and then move individual lines by pressing Alt and an arrow key.
• The IMEMode property of the ComboBox, ListBox, and TextBox objects is now available to all users at all times, if the supported Input Method Editors (IMEs) are installed. This property allows you to set properties related to various Asian languages, such as kanji conversion, full-pitch or half-pitch hiragana, and katakana.