Want to read this on your NOOK? Request as NOOK Book from the publisher

Thank you for requesting this book as a NOOK book from the publisher.

Customers Who Bought This Also Bought

More About This Book

Overview
Editorial Reviews
Product Details
Related Subjects
Meet the Author
Table of Contents
First Chapter

Overview

Conquer Access—from the inside out!Hey, you know your way around Access—so now dig into Version 2002 and really put your databases to work! This award-winning, supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and handy workarounds in concise, fast-answer format—it’s all muscle and no fluff. Discover the best and fastest ways to perform everyday tasks, and challenge yourself to new levels of Access mastery.

Build on what you already know about Access and quickly dive into what’s new
Ace database design fundamentals and avoid common pitfalls
Create custom forms, controls, and queries
Link to data from other databases and Microsoft® Office applications
Get more data-crunching power with code and add-ins
Learn the tricks that bring your forms and reports to life
Use data access pages and XML to deliver dynamic data on line
Bolt your Access front end to an enterprise-strength back end, such as Microsoft SQL Server™
Write your own code modules using Microsoft Visual Basic® for Applications (VBA)Learn from an award winner!

Microsoft Access Version 2002 Inside Out won the 2004 Referenceware Excellence Award in the category of Desktop and Office Applications at the Waterside 2004 conference. The award, sponsored by Books 24x7 and Waterside Productions, Inc., recognizes the book’s the quality of content organization, depth and quality of subject matter coverage, and graphics.

CD-ROM FEATURES:

Intuitive HTML interface
Extensive collection of Microsoft add-ins and third-party utilities, demos, and trials
Sample databases and VBA code
Complete eBook—easy to browse and print!
Sample chapters from other INSIDE OUT Office XP books
Web links to Microsoft Tools on the Web, online troubleshooters, and product support
Microsoft Visio® auto-demos
Interactive tutorials

A Note Regarding the CD or DVD

The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via O'Reilly Media's Digital Distribution services. To download this content, please visit O'Reilly's web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to booktech@oreilly.com.

Read More Show Less

Editorial Reviews

From Barnes & Noble

The Barnes & Noble Review
You've experimented with Microsoft Access over the years -- heck, maybe you've gotten reasonably proficient with it. Now you're working with Access 2002, and you're ready to put the pedal to the metal. Helen Feddema, a longtime Office guru and Access newsletter editor, has written the definitive book you're looking for.

Feddema covers all the different ways you can approach Access: as a traditional desktop database, a web database, or as a front end to enterprise SQL Server databases. If you're into extending Access with VBA, you'll find plenty of help. Won't write code to save your life? You'll still learn how to supercharge your forms, reports, and queries, and analyze your data far more effectively.

Feddema hears plenty of questions from confused Access users, and this book's tips and troubleshooting techniques show it. If you've ever tried to figure out why you're getting #Error messages on your reports, or why your data loses its formatting when you export it, or why your report shows the correct data in Access 2002 but incorrect data in Access 2000, wonder no more.

Access 2002 definitely includes some new goodies (improved Data Access Pages, new PivotTables, and multiple undo come to mind). But the core of the program hasn't changed much since Access 2000. So this book is worth serious consideration even if you haven't upgraded to Office XP just yet.

Oh, by the way: If you don't want an 1,100-page book cluttering your "real" desktop, there's a complete e-copy on CD-ROM. (Bill Camarda)

Bill Camarda is a consultant, writer, and web/multimedia content developer with nearly 20 years' experience in helping technology companies deploy and market advanced software, computing, and networking products and services. He served for nearly ten years as vice president of a New Jerseybased marketing company, where he supervised a wide range of graphics and web design projects. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks For Dummies®, Second Edition.

Read More Show Less

Product Details

ISBN-13: 9780735612839
Publisher: Microsoft Press
Publication date: 12/19/2001
Series: Inside Out Series
Edition description: REV
Pages: 1152
Product dimensions: 7.38 (w) x 9.20 (h) x 1.91 (d)

Meet the Author

Helen Feddema grew up in New York City. She was ready for computers when she was 12, but computers were not ready for her yet, so she got a B.S. in philosophy from Columbia and an M.T.S. in theological studies from Harvard Divinity School, while working at various office jobs. It was at HDS that she got her first computer, an Osborne, and soon computers were her primary interest. She started with word processing and spreadsheets, went on to learn dBASE, and did dBASE development for six years, part of this time as a corporate developer. After being laid off in a flurry of corporate downsizing, she started doing independent consulting and development, using dBASE, ObjectVision, WordPerfect and Paradox. Always looking for something new and better, Helen beta tested Access 1.0 and soon recognized that this was the database she had been looking for ever since Windows 3.0 was introduced. Since that time, she has worked as a developer of Microsoft Office applications, concentrating on Access, Word, and Outlook. Helen coauthored Inside Microsoft Access, (New Riders, 1992), and wrote two books for Pinnacle's "The Pros Talk Access" series, Power Forms and Power Reports (1994). She also coauthored Access How-Tos for the Waite Group Press (1995), and more recently contributed to The Microsoft Outlook Handbook (Osborne-McGraw-Hill), Que's Special Edition: Using Microsoft Outlook 97 (1997), Office Annoyances (O'Reilly, 1997), and Outlook Annoyances (O'Reilly, 1998). She also contributed chapters to Que's Special Edition: Using Microsoft Project 98 (1997) and Teach Yourself Project (1998). Most recently, Helen co-authored Sybex' MCSD: Access 95 Study Guide (1998). She has also been a regular contributor to Pinnacle's Smart Access and Office Developer journals, Woody's Underground Office newsletter, PC Magazine's Undocumented Office and the MS Office and VBA Journal. She recently contributed articles on Menu Manager and Outlook Automation Access add-ins and Access-Word data merging to Smart Access, as well as writing the Access Archon column for the Woody's Office Watch e-zine. Helen sometimes beta tests seven or eight products at once, mostly Microsoft, but with some from other vendors as well. She lives in the mid-Hudson area of New York state, with three cats and three computers. Helen maintains a web page with a large selection of code samples concentrating on connecting Access, Outlook, Word, and Excel. She is an MVP on the WOPR Lounge, a threaded discussion group devoted to Microsoft Office.

Read More Show Less

	Dedication
	Acknowledgments
	We'd Like to Hear from You
	Conventions and Features Used in This Book
Pt. 1	Access Fundamentals	1
Ch. 1	Exploring What's New in Access 2002	3
Ch. 2	Overview of Basic Access Features	27
Ch. 3	Introduction to Database Design	55
Ch. 4	Creating a Database	83
Pt. 2	Forms and Reports	115
Ch. 5	Creating Forms for Entering, Editing, and Viewing Data	117
Ch. 6	Working with Form Controls	179
Ch. 7	Using Reports to Print Data	233
Ch. 8	Using Design Tools	301
Pt. 3	Queries and Recordsets	337
Ch. 9	Using Queries to Select Data	339
Ch. 10	Using Action Queries	395
Ch. 11	Working with Advanced Queries	421
Ch. 12	Using PivotTables and PivotCharts to Analyze Data	457
Pt. 4	Customizing Access	493
Ch. 13	Customizing Access Toolbars and Menus (Command Bars)	495
Ch. 14	Using Macros	545
Ch. 15	Using Add-Ins to Expand Access Functionality	571
Pt. 5	Importing and Exporting Data	623
Ch. 16	Importing and Linking Data to Access	625
Ch. 17	Exporting Data from Access	683
Pt. 6	Advanced Access Techniques	733
Ch. 18	Working with Data Access Pages	735
Ch. 19	Creating Access Projects	773
Ch. 20	Customizing Your Database Using VBA Code	809
Ch. 21	Creating Your Own Add-Ins	863
Pt. 7	Appendixes	963
	App. A: Setup and Installation	965
App. B	Distributing Access Applications	989
App. C	Security	1001
App. D	Replication	1015
App. E	Upsizing to SQL Server	1025
App. F	Control Properties	1035
	Index of Troubleshooting Topics	1055
	Index	1061

Read More Show Less

First Chapter

Chapter 1.
Exploring What's New in Access 2002

Office-Wide Changes
- Task Panes
- Smart Tags
- Speech Recognition
Access Changes
- New Database Format
- Improvements to Compact And Repair
- Better Handling of Broken References
- Multiple Undo and Redo
- PivotChart and PivotTable Views
- New Form and Control Events
- New Form and Report Properties and Methods
- Shortcut Keys and Accessibility
- Interface Enhancements
- Conversion Error Logging
- Expanded Programmability
- Printer Object and Printers Collection
- Better Support of Multilingual Text and Graphics
ADP Project Changes
- SQL Server 2000 Desktop Engine
- Running Access Projects Against SQL Server 2000
- Running ADP Projects Against All Versions of SQL Server
- Using Recordsets
XML and Access
- XML Import/Export
- XSL Transformations and Presentations
Data Access Page Changes
- Data Access Page Designer
- Improved Properties Sheet
- Deploying Data Access Pages
- Other DAP Improvements

Chapter 1 Exploring What's New in Access 2002

Whether you're a new user of Microsoft Access or an experienced Access database developer, you'll find a number of solid improvements in Access 2002. With an eye toward enhancements both in form and function, the changes in Access make working with the interface more intuitive than ever; extend the reach of your data by giving you new options for displaying, reporting, and publishing; and provide more programming control over the functionality and appearance of the databases you create.

As you'll see, Microsoft Access 2002 includes many new features, fixes, improvements, and other significant additions to functionality. This chapter provides a brief overview of these new and improved features. These features will be covered in more detail in their respective chapters.

Office-Wide Changes

Some new features in Access 2002 are not specific to Access. Instead, they are implementations of new features in Microsoft Office XP. Many of the improvements in Office XP are geared toward improving the user interface—making the features easier to get to than in prior versions—and extending the overall functionality of the applications so that you can use them together more intuitively, with less duplication of effort. Those benefits, along with features that help users recover from system crashes and preserve important files, will make your work with Access easier as well.

Task Panes

Access 2002 replaces the earlier Startup dialog box with the new Office-wide task pane feature, and it's a welcome change. When you open Access, the Access task pane appears on the right side of the Access window, with the New File mode selected. The task pane displays a list of files you've worked with recently, to make it easy to reopen a database. The task pane also serves as a search pane when you click the Search button on the Access toolbar, and it lists available items to paste when you select the Clipboard mode by choosing Edit, Office Clipboard. Whatever task pane mode is selected, you can continue working in the Access window while you select the options you need from the task pane.

You can also create a new database, data access page, or project; create a database from a template; and (a brand-new option in Access 2002) create a database that is a copy of an existing database, to save you the trouble of importing numerous objects into the new database. Figure 1-1 shows the Access 2002 task pane as it appears when you open Access.

Figure 1-1. The task pane offers choices for opening or creating databases when Access is opened. (Image unavailable)

See Chapter 2, "Overview of Basic Access Features," for more details on the new Access task pane.

Smart Tags

Smart tags are a new feature in Office XP that do just what their name implies—they provide tags with links that lead to more information about a specific person, company, place, or thing. They provide a "smart" use of data by allowing you to share information when you're working with Microsoft Word, Microsoft Excel, and Microsoft Internet Explorer. Access 2002 doesn't support smart tags directly, but you can use the tags in Word or Excel data that you import to or export from Access database tables.

NOTE:
For more information about using smart tags with Office XP applications, see Microsoft Office XP Inside Out (Microsoft Press, 2001), Microsoft Excel 2002 Inside Out (Microsoft Press, 2001), or Microsoft Word 2002 Inside Out (Microsoft Press, 2001).

Speech Recognition

Access now supports speech recognition for both voice dictation and command and control operations, allowing users to dictate text and navigate Access menus by using speech and voice commands. Speech recognition could be a wonderful boon to users who have difficulty using a keyboard or mouse—or for those users who simply want to cut down on unnecessary keystrokes.

For best results with speech recognition and dictation, you need a high-quality microphone. You should also be prepared to spend considerable time "training" the speech recognition software to understand your tone and phraseology. The effectiveness and functionality of speech recognition are sure to increase in subsequent versions of Office; for now, however, it's something worth trying if you ever find yourself with the time to experiment.

NOTE:
Speech recognition is not available in Design view for tables, queries, or diagrams in Access projects (ADP files).

Access 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. (Image unavailable)

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. (Image unavailable)

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. (Image unavailable)

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. (Image unavailable)

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

NOTE:
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. (Image unavailable)

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.

NOTE:
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.
NOTE:
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. (Image unavailable)
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. (Image unavailable)
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. (Image unavailable)

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. (Image unavailable)
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. (Image unavailable)
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.

ADP Project Changes

A number of new features make it easier to work with Access projects, the ADP files that are the Access front end to data in SQL Server tables. Some of these new features include a new version of the Microsoft Database Engine that allows you to work with projects even if you don't have SQL Server, as well as many enhancements to project front ends.

For more information about working with Access projects, see Chapter 19, "Creating Access Projects," and Appendix E, "Upsizing to SQL Server."

SQL Server 2000 Desktop Engine

SQL Server 2000 Desktop Engine (formerly the Microsoft Database Engine, or MSDE) is included with SQL Server 2000 and is provided on the Microsoft Office CD in the MSDE2000 folder. To install MSDE, double-click Setup.exe in this folder. Figure 1-12 shows the folder on the Office CD with Setup.exe selected. With MSDE installed, you can create projects even if you don't have the full version of SQL Server installed.

Figure 1-12. You can install MSDE from the MSDE2000 folder on the Office CD. (Image unavailable)

The SQL Server 2000 Desktop Engine setup includes a new version of the Microsoft Data Access Components (MDAC)—version 2.6. These components help you integrate data from a wide variety of sources and include Microsoft ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC).

For more information about the SQL Server 2000 Desktop Engine and how it compares with SQL Server, see Chapter 3, "Introduction to Database Design."

Running Access Projects Against SQL Server 2000

Access 2002 and SQL Server 2000 work together more tightly than ever before. Here are some of the highlights:

.Using SQL Server functions SQL Server 2000 functions provide the same functionality as Jet parameterized queries. Use functions to replace stored procedures that returned a single result set, or any place a view was used. You can use these functions as record and row sources for forms, reports, data access pages, combo boxes, and other objects.

Extended property support. SQL Server 2000 now provides support for extended properties, letting you set up lookup relationships, validation text, formatting, subdatasheets, and other features of tables, views, and functions, just as with MDB files.

Updatable views. You can now update values in a view or function directly by using a datasheet. Any update that can be performed using an MDB query can now be performed using a SQL Server view or function.

Linked Table Wizard. The Access Linked Table Wizard can now be used to create tables linked to a SQL Server database, an MDB file, or other OLE DB sources.

Copy Database File and Transfer Database support. Access 2002 allows you to attach SQL Server MDF files to your local server. You can now use the Copy Database File command against the current ADP database on a local server to create a copy of the MDF file, so that you can move it to a different server. You can use the Transfer Database command to transfer a database from one server to another, even if you're not working on your local server. To access these commands, choose Tools, Database Utilities.

Running ADP Projects Against All Versions of SQL Server

If you're running an earlier version of SQL Server, you'll also see some changes, as follows:

Batch updates in a form. The new BatchUpdates property on the Data tab of a form's properties sheet allows you to tell Access to batch all data entry and send it to the server when the user navigates off the form, closes the form, or chooses Records, Save All Records. You can also create a button that programmatically saves or undoes all records, thereby eliminating the need to create unbound forms that gather all the data and commit changes at one time.

Better input parameter support for functions and stored procedures. You can now specify parameter values for a record source, just as with an MDB-based form or report. For example, if the following SQL statement is used as a report's record source, when the report is run, a parameter dialog box will appear, asking for the Country value:

SELECT * FROM tblCustomers WHERE Country = @ [Enter a country for the Customers report]

This new feature gives you more flexibility, eliminating the need to create different record sources with specific filters.

Password security. The new Set Login Password command (choose Tools, Security) allows you to change your logon password for the current logon specified for the ADP connection, without having to log off first.

SQL replication and Security dialog boxes removed. Because of security changes in SQL Server, Access no longer offers SQL replication and security. You must use SQL Server 2000 Enterprise Manager to implement replication and security for your database.

Using Recordsets

You can now use recordsets (sets of records that behave as objects) as row sources for combo boxes and lists, which gives you another option for providing lists of items to select. Additionally, disconnected recordsets can be used for all ADP objects that have RecordSource and RowSource properties.

XML and Access

Significant XML support has been added to Access, both in the core product and in data access pages. XML extends HTML to deal with structured data from many applications and allows the creation of custom data formats for special situations. Some of the new features are described briefly in this section.

XML Import/Export

You can create XML data or schema documents from Jet or SQL Server structures and data, or you can use XML data or schema documents to import (either programmatically or through the export user interface) data or structure into either SQL Server or Jet.

Import User Interface

The Import dialog box (choose File, Get External Data, Import) now provides an XML Documents selection, which allows you to import schema and data documents into either SQL Server or Jet. Figure 1-13 shows the XML Documents selection.

Figure 1-13. You can import an XML document into either SQL Server or Jet. (Image unavailable)

Export User Interface

The Access table/query/report/form export feature (choose File, Export with a table, query, report, or form selected) now includes an XML Documents Save As Type selection, as shown in Figure 1-14.

Figure 1-14. You can export an Access database object to an XML document. (Image unavailable)

XSL Transformations and Presentations

With Extensible Stylesheet Language (XSL), you can create your own style sheets for formatting XML data. You can create your own XSL data transformations for changing the data document format, and you can also create your own presentation formats and add them to the drop-down lists within the Export and Import dialog boxes by adding a metaheader to your XSL document.

Data Access Page Changes

Data access pages give you the means of publishing pages on the Web that work with data in Access or SQL Server databases. Data access pages were first introduced in Access 2000, but they weren't very easy to use. Access 2002 provides many enhancements that make it much easier to create and modify data access pages. In this section, we'll look briefly at each of these enhancements.

See Chapter 18, "Working with Data Access Pages," for more details on the changes to data access pages.

Data Access Page Designer

When you create a new data access page (or open an existing data access page in Design view), you'll find a host of improvements and new features, including the following:

The Designer now supports multilevel Undo and Redo.
Microsoft Internet Explorer 5.5 and 6.0 users have both keyboard and mouse multiselect support for applying sizing, horizontal and vertical spacing, alignment, and property settings to data access pages.
Internet Explorer 5.5 and 6.0 users can also see the control's actual size while they're resizing it. Snap-to-grid support has been improved, and the Size To Fit command sizes the control to the size of its content.
Multiselect drag-and-drop is now available from the field list.
Banded (grouped) pages have more functionality, including intuitive drop zones, automatic indenting of group levels, a group-level Properties drop-down option on each group's shortcut menu, and automatic formatting for caption and footer properties.
Banded pages are updatable. You can control updating by using the group-level properties AllowAdditions, AllowDeletions, and AllowEdits.
The AutoSum feature makes it easy to create aggregates by selecting a control and clicking AutoSum.
Controls, sections, group-level properties, and page properties now have shortcut menus.
You can create customized navigation controls by applying the class name of the control to most HTML element types. For example, you can turn a label into a Next navigation control by appending MsoNavNext to the class name of the label.
The Layout Wizard now supports Tabular, PivotChart, and Spreadsheet options.
You can now bind the Spreadsheet control to data.
The Designer now inherits extended properties from both Jet and SQL Server 2000 databases so that lookups are dropped as lookups instead of as static values, and label properties are set appropriately.

Improved Properties Sheet

Data access page properties are now easier to use because of better organization of the properties sheet and several new builders that help you select the appropriate value for a property. You'll find the following enhancements:

Only relevant properties are shown for each element type. Some unneeded properties have been removed, and a richer set of applicable properties are exposed.
You can set interior properties of ActiveX controls through the properties sheet.
The properties sheet supports builders, including Page Connection, Color Picker, and Zoom.
You can turn on a record selector by right-clicking a section, choosing Group Level Properties on the shortcut menu, and then setting the RecordSelector property to True. (The default setting is False, so the record selector isn't displayed for that group.) The record selector is a vertical band on the left side of the data access page. However, unlike with Access forms, you can't click the record selector to delete a record from a data access page after selecting it because the Delete Record option on the Edit menu is disabled for data access pages.
All pages use alternate row colors by default, but you can change this setting in the Group Level properties sheet.

Deploying Data Access Pages

Access 2002 includes several new features that make it easier to deploy your data access pages on the Web after you've designed them, as follows:

Access 2000 data access pages will be converted to Access 2002 format when they're opened in Design view in an Access 2002 database. Solutions created using Access 2000 will still work after you install the new Office Web Components.
A page-level script notifies the user with appropriate messages when the correct browser or Office Web Components are not present.
Users can set relative paths to Access databases, although only when the page is opened through the file system, not through HTTP.
Developers can point all pages in a solution to Office Data Connection (ODC) or Universal Data Link (UDL) files by using the page's ConnectionFile property. When the page is loaded, it retrieves the connection file and sets the connection specified in the file. Set the default connection for all new pages on the Pages tab of the Options dialog box, by choosing Tools, Options.
Developers can set the default pages folder on the Pages tab of the Options dialog box.
The page's link property is exposed both programmatically and in the Page Properties dialog box in the Database window.
With the OfflineCDF, OfflineSource, and OfflineType properties, you can build applications that work with SQL Server and Jet replication or XML data files. This feature is fully integrated with the Internet Explorer offline synchronization model.

NOTE:
See Microsoft FrontPage 2002 Inside Out (Microsoft Press, 2001) for more information about deploying Access data on the Web using Microsoft FrontPage. FrontPage has its own set of tools for this purpose, some of them more powerful than the tools in Access.

Other DAP Improvements

There are a few other miscellaneous changes related to data access pages that make them easier to work with, including a Data Access Page selection in the Save As dialog box for forms, some features that allow you to link to XML documents, and a number of Data Source control attributes.

Working Off Line

You can prepare a data access page for offline work by setting a number of page properties, all available through the object model, or by opening the data access page in Internet Explorer and selecting Make Available Offline in the Add Favorite dialog box. For details on working with data access pages off line, see Chapter 18, "Working with Data Access Pages."

Saving Forms and Reports as Data Access Pages

You can now save forms and reports as data access pages by selecting Data Access Page in the Save As dialog box, as shown in Figure 1-15. Saving a form as a data access page saves a lot of time when you want the page to look just like an existing form. You don't have to design the data access page from scratch; instead, you can create it from a form you've already created.

Figure 1-15. You can save a form as a data access page and save the time it takes to design the page from scratch. (Image unavailable)

Creating an XML Data Document

You can create an XML document or XML data island (XML data embedded in HTML code) from a data access page by using the ExportXML method.

Binding Data to an XML Data Document

You can set the properties described in Table 1-4 either on the page's properties sheet or through code at runtime.

Table 1-4. XML data binding properties

Property	Value	Description
XMLLocation	dscXMLDataEmbedded	Data appears on the page as a data island.
	dscXMLDataFile	Data is an XML data document.
XMLDataTarget	dscXMLDataEmbedded	Sets the ID of the data island.
	dscXMLDataFile	Sets the UNC, URL, or absolute path and name of the XML document.
UseXMLData	True	Connection to the live source is dropped and the data access page is bound to the XML source.

Binding to an Arbitrary Recordset

You can now bind data access pages to any arbitrary recordset. Use the SetRootRecordset method to bind to disconnected and persisted recordsets.

New Data Source Control Events

The Data Source control is used behind the scenes to bind data access pages to a data source. Unlike other controls, it has no representation in the interface; you program it in VBA code. This control now has several new events that give you more places to respond to user actions.

Table 1-5 lists the new Data Source control events.

Table 1-5. New Data Source control events

Event	Description
AfterDelete	Occurs after a record deletion has been confirmed and the record has actually been deleted, or after the deletion is canceled
AfterInsert	Occurs after a new record is added to the recordset
AfterUpdate	Occurs after a record is updated with changed data or after the record loses focus
BeforeDelete	Occurs before the record is actually deleted
BeforeInsert	Occurs when the first character is entered into a new record, but before the record is actually added to the recordset
BeforeUpdate	Occurs before a record is updated with changed data or before the record loses focus
Dirty	Occurs when the contents of a record change, and before the BeforeUpdate event
Focus	Page-level event that occurs when a section receives the focus (as opposed to the Current event, a recordset-level event that occurs when the record changes in the underlying recordset)
RecordExit	Occurs after all update events have fired and before the record loses currency

New Data Source Control Constants

Table 1-6 lists the new Data Source control constants. These constants are used to set the Data Source control properties or are returned by its events, enabling you to take different actions in code depending on what choice a user makes in the interface.

Table 1-6. New Data Source control constants

Constant	Description
dscDeleteOK	Indicates that delete operation succeeded
dscDeleteCancel	Indicates that delete operation was canceled through code
dscDeleteUserCancel	Indicates that delete operation was canceled by the user
dscDisplayAlertContinue	Determines whether a custom error message or no error message is displayed
dscDataAlertDisplay	Indicates that standard error dialog box should be displayed
dscRefreshData	Causes the Refresh method to refresh the data cache while maintaining the current connection

New Data Source Control Function

The new EuroConvert function converts a number to euros or from euros to another currency. You can also use this function to convert a number from one currency to another by using the euro as an intermediate value.

Updating Data Access Pages

Banded data access pages are now updatable, and child recordsets are fully updatable, enabling you to add, modify, and delete records from the child recordsets assigned to each band of data.

The Group Level properties sheet allows you to control whether the child recordsets are updatable by setting the following properties:

AllowEdits
AllowAdditions
AllowDeletes

These properties are set to False by default for converted DAPs. DAPs created in the interface have these properties set to True.

Read More Show Less

Customer Reviews

Be the first to write a review

( 0 )

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Anonymous

Posted Sat Oct 27 00:00:00 EDT 2007

Really Pointless.

Deeply disappointing. The author babbles and babbles with little useful information. He would be better off going through step by step instructions. Need to know about Functions? See the CD -- which incidently only lists them, doesn't explain them. For Macros? He doesn't like them, so general info, no real how to. If you are going to call a book INSIDE OUT, it implies that it is thorough. This isn't thorough, it it verbose for no reason. I don't know who his audience is. Too wordy and unhelpful for newbies, too wordy and useless for advanced. Maybe it is for people who just like to read technical books. Skip this one.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Anonymous

Posted Fri May 09 00:00:00 EDT 2003

hmmmmmmm

i had to give the book 3 stars because there was not 2.5 stars. The book is not bad, it does have it weak side! This book is ok if u like pictures, because it has plenty of them. I would recomend 'Access Bible 2002' excellent book, but that book is more for people who have a basic idea of Access. For non-Access users, i would recommend, 'Access 2002 for Dummies' this book is a jewel if your a idiot. The only way to really get good with access, is practice practice practice, and take your time, you cant rush into a program like this and expect to be the database king, that shyt aint happening.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Anonymous

Posted Thu May 22 00:00:00 EDT 2003

This book is for Access Achievers only

Be advised that this book starts off slow but it gets involved in a hurry! if you are a novice in Access I would suggest that you start off with Step By Step and then graduate to 'inside Out'!
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.

If you find inappropriate content, please report it to Barnes & Noble

Microsoft Access Version 2002 Inside Out

Overview

Customers Who Bought This Also Bought

More About This Book

Overview

Editorial Reviews

From Barnes & Noble

Product Details

Related Subjects

Meet the Author

Table of Contents

First Chapter

Chapter 1. Exploring What's New in Access 2002

Chapter 1 Exploring What's New in Access 2002

Office-Wide Changes

Task Panes

Smart Tags

Speech Recognition

Access Changes

New Database Format

Improvements to Compact And Repair

Better Handling of Broken References

Multiple Undo and Redo

PivotChart and PivotTable Views

New Form and Control Events

New Form and Report Properties and Methods

Shortcut Keys and Accessibility

Interface Enhancements

Conversion Error Logging

Expanded Programmability

Printer Object and Printers Collection

Better Support of Multilingual Text and Graphics

ADP Project Changes

SQL Server 2000 Desktop Engine

Running Access Projects Against SQL Server 2000

Running ADP Projects Against All Versions of SQL Server

Using Recordsets

XML and Access

XML Import/Export

XSL Transformations and Presentations

Data Access Page Changes

Data Access Page Designer

Improved Properties Sheet

Deploying Data Access Pages

Other DAP Improvements

Customer Reviews

5 Star

4 Star

3 Star

2 Star

1 Star

Really Pointless.

hmmmmmmm

This book is for Access Achievers only

Chapter 1.
Exploring What's New in Access 2002