Special Edition Using Microsoft® Access 2000

Table of contents

1. Copyright
   1. Dedication
2. Preface
   1. About the Author
   2. Acknowledgments
   3. Tell Us What You Think!
3. Introduction
   1. Who Should Read this Book
   2. How this Book Is Organized
      1. Part I: Learning Access Fundamentals
      2. Part II: Getting the Most Out of Queries
      3. Part III: Designing Forms and Reports
      4. Part IV: Publishing Data on Intranets and the Internet
      5. Part V: Integrating Access with Other Office 2000 Applications
      6. Part VI: Using Advanced Access Techniques
      7. Part VII: Programming and Converting Access Applications
      8. Glossary
      9. The Accompanying CD-ROM
   3. How this Book Is Designed
   4. Typographic Conventions Used in this Book
      1. Key Combinations and Menu Choices
      2. SQL Statements and Keywords in Other Languages
         1. Typographic Conventions Used for VBA
   5. System Requirements for Access 2000
   6. Other Sources of Information for Access
      1. Books
      2. Periodicals
      3. Internet
4. I. Learning Access Fundamentals
   1. 1. Access 2000 for Access 95 and 97 Users: What's New
      1. Putting Access 2000 in Perspective
      2. Deploying Data Access Pages with Office Web Components
         1. The Office Web Components
         2. Test Drive a DAP
         3. DAP Design Mode
      3. Creating Access Data Projects for the Microsoft Data Engine
         1. New ADP Tools and Objects
         2. Explore the NorthwindCS Project and da Vinci Toolset
      4. Integrating the Office VBA Editor with Access
      5. Working with the HTML Source Editor for DAP and DHTML
      6. Viewing and Editing Related Records in Subdatasheets
      7. Conforming Jet and SQL Server 7.0/MSDE Databases
      8. Giving Access a Year 2000 Facelift
         1. Four-Digit Year Option Settings
         2. The Database Window
         3. Forms and Reports
         4. Name AutoCorrect
         5. Office 2000-Related Enhancements or Impediments
            1. Synthetic Single-Document Interface
            2. Intellimenus
            3. HTML Help
            4. Online Collaboration
      9. Installing Access 2000
         1. Making an Initial Installation of Access 2000
         2. Customizing Access 2000
      10. Installing and Configuring the Microsoft Data Engine
      11. In the Real World—Why Upgrade?
   2. 2. Building Your First Access 2000 Application
      1. Understanding Access's Approach to Application Design
      2. Creating an Access Application from a Template File
      3. Touring the Contact Management Application
         1. Table Objects in the Database Window
         2. The Switchboard Form
         3. Access Forms
         4. Access Reports
         5. Access Modules
      4. Using the Switchboard Manager
      5. Exploring Form Design View and VBA Class Modules
      6. The Real World—Putting What You've Learned in Perspective
   3. 3. Navigating Within Access
      1. Understanding Access Functions and Modes
         1. Defining Access Functions
         2. Defining Access Operating Modes
      2. Understanding Access's Table Display
         1. Maximized Document Windows
         2. Document Windows Minimized to Icons
         3. The Toolbars in Table Datasheet View
            1. The Table Datasheet Toolbar
            2. The Datasheet Formatting Toolbar
         4. Toolbar Customization
         5. Right-Click Shortcut Menus
      3. Using the Function Keys
         1. Global Function Keys
         2. Function-Key Assignments for Fields, Grids, and Text Boxes
      4. Setting Default Options
         1. System Defaults
            1. View Options
            2. General Options
            3. Edit/Find Options
            4. Keyboard Options
            5. Advanced Options
         2. Defaults for Datasheet View
      5. Using Access Help
         1. Context-Sensitive Help
         2. The Help Menu
         3. The Microsoft Access Help Window
            1. The Contents Page
            2. Understanding the Help Window
            3. The Answer Wizard Page
            4. The Index Page
            5. The Help Window Options
         4. The Office Assistant
      6. Using the Database Utilities
         1. Compacting and Repairing Databases
         2. Converting Databases to Access 2000 Format
         3. Converting Databases to Access 97 Format
         4. Adding a Switchboard Form
         5. Creating .mde Files
      7. Troubleshooting
         1. Compile Errors in the Convert Database Process
      8. In the Real World—HTML Help or Hindrance
   4. 4. Working with Access Databases and Tables
      1. Defining the Elements of Access Databases
      2. Understanding Relational Databases
      3. Using Access Database Files and Tables
         1. The Access System Database
         2. Access Library Databases
      4. Creating a New Database
      5. Understanding the Properties of Tables and Fields
      6. Choosing Field Data Types, Sizes, and Formats
         1. Choosing Field Sizes for Numeric and Text Data
            1. Subtypes for Numeric Data
            2. Fixed-Width Text Fields
            3. Subtypes for the OLE Object Data Type
         2. Selecting a Display Format
            1. Standard Formats for Number, Date/Time, and Yes/No Data Types
            2. The Null Value in Access Tables
            3. Custom Display Formats
         3. Using Input Masks
      7. Using the Northwind Traders Sample Database
         1. Using the Table Wizard to Create New Tables
      8. Adding a New Table to an Existing Database
         1. Designing the Personnel Actions Table
            1. Determining What Information the Table Should Include
            2. Assigning Information to Fields
         2. Creating the Personnel Actions Table
         3. Creating a Table Directly in Datasheet View
      9. Setting Default Values of Fields
      10. Working with Relations, Key Fields, and Indexes
          1. Establishing Relationships Between Tables
          2. Enforcing Referential Integrity
             1. Understanding How Referential Integrity Is Enforced
             2. Cascading Updates and Deletions
          3. Selecting a Primary Key
          4. Adding Indexes to Tables
      11. Altering Fields and Relationships
          1. Rearranging the Sequence of Fields in a Table
          2. Changing Field Data Types and Sizes
             1. Numeric Fields
             2. Text Fields
             3. Conversion Between Number, Date, and Text Field Data Types
          3. Changing Relationships Between Tables
      12. Copying and Pasting Tables
      13. Troubleshooting
          1. Gaps in AutoNumber Field Values
          2. Extra Indexes Added by Access
      14. In the Real World—Database Strategy and Table Tactics
          1. Why Table Design Comes Before Database Design in This Book
          2. Naming Conventions for Tables and Fields
   5. 5. Entering, Editing, and Validating Data in Tables
      1. Entering Test Data in Access Tables
      2. Using Keyboard Operations for Entering and Editing Data
         1. Creating an Experimental Copy of Northwind.mdb
         2. Using Data-Entry and Editing Keys
         3. Using Key Combinations for Windows Clipboard Operations
         4. Using Shortcut Keys for Fields and Text Boxes
         5. Setting Data-Entry Options
      3. Adding Records to a Table
      4. Selecting, Appending, Replacing, and Deleting Table Records
      5. Validating Data Entry
         1. Adding Field-Level Validation Rules
         2. Adding Table-Level Validation Rules and Using the Expression Builder
      6. Adding Records to the Personnel Actions Table
      7. Entering Personnel Actions Table Data and Testing Validation Rules
      8. Troubleshooting
         1. Field Property Values Cause Paste Failures
         2. Multiple Record Selection Causes Silent Paste Failures
         3. Error Messages from Validation Enforcement
      9. In the Real World—Heads-Down Data Entry
         1. Comparing Heads-Down Keypunch Data Entry with Access's Datasheet View
         2. Replacing the Punched Card Verifying Step
         3. Where Not to Use Datasheet Entry
   6. 6. Sorting, Finding, and Filtering Data in Tables
      1. Understanding the Role of Sorting and Filtering
      2. Sorting Table Data
         1. Freezing Display of a Table Field
         2. Sorting Data on a Single Field
         3. Sorting Data on Multiple Fields
         4. Removing a Table Sort Order and Thawing Columns
      3. Finding Matching Records in a Table
      4. Replacing Matched Field Values Automatically
      5. Filtering Table Data
         1. Filtering by Selection
         2. Filtering by Form
         3. Advanced Filters and Sorts
         4. Adding a Multifield Sort and Compound Filter Criteria
         5. Using Composite Criteria
         6. Saving Your Filter as a Query and Loading a Filter
      6. Customizing Datasheet View
      7. Copying, Exporting, and Mailing Sorted and Filtered Data
      8. Troubleshooting
         1. Filter by Form Doesn't Find the Expected Records
      9. In the Real World—Computer-Based Sorting and Searching
         1. The Influence of Computer Power on Knuth's Approach
         2. Knuth and Indexes
   7. 7. Linking, Importing, and Exporting Tables
      1. Moving Data from and to Other Applications
      2. Understanding How Access Handles Tables in Other Database File Formats
         1. Identifying PC Database File Formats
         2. Linking and Importing External ISAM Tables
         3. Linking Visual FoxPro Tables with ODBC
         4. Dealing with Images in External Files
         5. Converting Field Data Types to Access Data Types
         6. Using the Linked Table Manager Add-in to Relink Tables
         7. Importing Versus Linking Database Files as Tables
      3. Importing and Linking Spreadsheet Files
         1. Creating a Table by Importing an Excel Worksheet
         2. Linking Excel Worksheets
      4. Working with Microsoft Outlook and Exchange Folders
      5. Exporting and Importing Jet 4.0 Tables with Outlook
         1. Linking with the Exchange/Outlook Wizard
      6. Importing Text Files
         1. Using the Import Text Wizard
         2. The Import Text Wizard's Advanced Options
         3. Using Delimited Text Files
            1. Comma-Delimited Text Files Without Text-Identifier Characters
            2. Comma-Delimited Text Files with Text-Identifier Characters
            3. Tab-Delimited Text Files
         4. Handling Fixed-Width Text Files
         5. Appending Text Data to an Existing Table
      7. Using the Clipboard to Import Data
         1. Pasting New Records to a Table
         2. Replacing Records by Pasting from the Clipboard
      8. Exporting Data from Access Tables
         1. Exporting Data Through the Windows Clipboard
         2. Exporting Data as a Text File
         3. Exporting Data in Other File Formats
      9. Troubleshooting
         1. The Incorrect Password Dialog
         2. The Null Value in Index Dialog
         3. The Missing Memo File Dialog
         4. Importing Fixed-Width Text Files
         5. Importing Spreadsheets
      10. In the Real World—Microsoft Giveth and Microsoft Taketh Away
5. II. Getting the Most Out of Queries
   1. 8. Designing Access Queries
      1. Introducing Queries
      2. Trying the Simple Query Wizard
      3. Using the Query Design Window
         1. Selecting Fields for Your Query
         2. Selecting Records by Criteria and Sorting the Display
         3. Creating More Complex Queries
         4. Changing the Names of Query Column Headers
         5. Printing Your Query as a Report
         6. Using the Data from Your Query
      4. Creating Other Types of Queries
         1. Creating and Using a Simple Make-Table Action Query
         2. Adding a Parameter to Your Make-Table Query
      5. Troubleshooting
         1. Missing Required Fields
         2. Non-Updatable Summary Queries
      6. In the Real World—Query Design Optimization
         1. The Art of Query Design
         2. The Scientific Side of Query Design
   2. 9. Understanding Query Operators and Expressions
      1. Writing Expressions for Query Criteria and Data Validation
      2. Understanding the Elements in Expressions
         1. Operators
            1. Arithmetic Operators
            2. Assignment and Comparison Operators
            3. Logical Operators
            4. Concatenation Operators
            5. Identifier Operators
            6. Other Operators
         2. Literals
         3. Identifiers
         4. Functions
            1. Using the Immediate Window
            2. The Variant Data Type in Access and VBA
            3. Functions for Date and Time
            4. Text-Manipulation Functions
            5. Numeric, Logical, Date/Time, and String Data-Type Conversion Functions
         5. Intrinsic and Named Constants
      3. Creating Access Expressions
         1. Expressions for Creating Default Values
         2. Expressions for Validating Data
         3. Expressions for Query Criteria
            1. Entering a Query Criterion
            2. Using the Expression Builder to Add Query Criteria
         4. Expressions for Calculating Query Field Values
         5. Other Uses for Expressions
      4. Troubleshooting
         1. Query Expressions Fail to Execute
         2. Four-Digit Years Turn into Two Digits in Query Criteria
      5. In the Real World—The Algebra of Access Expressions
   3. 10. Creating Multitable and Crosstab Queries
      1. Introducing Joins on Tables
      2. Joining Tables to Create Multitable Queries
         1. Creating Conventional Single-Column Equi-Joins
         2. Specifying a Sort Order for the Query Result Set
         3. Creating Queries from Tables with Indirect Relationships
         4. Creating Multicolumn Equi-Joins and Selecting Unique Values
      3. Using Lookup Fields in Tables
         1. Adding a Foreign-Key Dropdown List with the Lookup Wizard
         2. Adding a Fixed-Value Lookup List to a Table
      4. Adding Subdatasheets to a Table or Query
         1. Table Subdatasheets
         2. Query Subdatasheets
      5. Outer, Self, and Theta Joins
         1. Creating Outer Joins
         2. Creating Self-Joins
         3. Creating Not-Equal Theta Joins with Criteria
      6. Updating Table Data with Queries
         1. Characteristics That Determine Whether You Can Update a Query
         2. Formatting Data with the Query Field Properties Window
      7. Making All Fields of Tables Accessible
      8. Making Calculations on Multiple Records
         1. Using the SQL Aggregate Functions
         2. Making Calculations Based on All Records of a Table
         3. Making Calculations Based on Selected Records of a Table
      9. Designing Parameter Queries
         1. Adding a Parameter to the Monthly Sales Query
         2. Specifying the Parameter's Data Type
      10. Creating Crosstab Queries
          1. Using the Wizard to Generate a Quarterly Product Sales Crosstab Query
          2. Designing a Monthly Product Sales Crosstab Query
          3. Using Fixed Column Headings with Crosstab Queries
      11. Creating Queries from Tables in Other Databases
      12. Troubleshooting
          1. Missing Objects in Queries
          2. Queries with Linked Tables Aren't Updatable
      13. In the Real World—Optimizing Multitable Queries
          1. Subdatasheets
          2. Aggregate Queries
          3. Crosstab Queries
   4. 11. Modifying Data with Action Queries
      1. Getting Acquainted with Action Queries
      2. Creating New Tables with Make-Table Queries
         1. Designing and Testing the Select Query
         2. Converting the Select Query to a Make-Table Query
         3. Establishing Relationships for the New Table
         4. Using the New tblShipAddresses Table
      3. Creating Action Queries to Append Records to a Table
      4. Deleting Records from a Table with an Action Query
      5. Updating Values of Multiple Records in a Table
      6. Testing Cascading Deletion and Updating
         1. Creating the Test Tables and Establishing Relationships
         2. Testing Cascading Deletion
         3. Testing Cascading Updates
      7. Troubleshooting
         1. Appending Records Causes Primary Key Problems
         2. Access Won't Create a Relationship to a New Table
      8. In the Real World—Alternatives to Action Queries
         1. Browse-Mode Updating
         2. Form-Based Updating
         3. Updating with SQL Statements
         4. Updating with SQL Server Stored Procedures
6. III. Designing Forms and Reports
   1. 12. Creating and Using Forms
      1. Understanding the Role of Access Forms and Controls
      2. Creating a Transaction-Processing Form with the Form Wizard
         1. Choosing Data Sources for the Form
         2. Creating the Basic Form with the Form Wizard
      3. Using the Form Design Window
         1. Elements of the Form Design Window
         2. Form Design Toolbar Buttons and Menu Choices
         3. The Formatting Toolbar
         4. Default Values for Forms
         5. Using AutoFormat
            1. Applying an AutoFormat
            2. Creating, Customizing, and Deleting AutoFormats
         6. Changing an Object's Colors
            1. Background Colors
            2. Changing the Background Bitmap
            3. Foreground Color, Border Color, and Border Style
            4. Creating Custom Colors with the Color Builder
      4. Selecting, Editing, and Moving Form Elements and Controls
         1. Changing the Size of the Form Header and Form Footer
         2. Selecting, Moving, and Sizing a Single Control
         3. Aligning Controls to the Grid
         4. Selecting and Moving Multiple Control
         5. Aligning a Group of Controls
         6. Using the Windows Clipboard and Deleting Controls
         7. Changing the Color and Border Style of a Control
         8. Changing the Content of Text Controls
         9. Using the Format Painter
      5. Rearranging the Personnel Actions Form
         1. Setting Properties of the Main Form
         2. Setting the Properties of a Subform
      6. Using Transaction-Processing Forms
         1. Toolbar Buttons in Form View
         2. Using the Personnel Actions Form
         3. Appending New Records to the Personnel Actions Table
         4. Editing Existing Data
         5. Committing and Rolling Back Changes to Tables
      7. Modifying the Properties of a Form or Control After Testing
         1. Changing the Order of Fields for Data Entry
         2. Removing Fields from the Tab Order
      8. In the Real World—The Art of Form Design
         1. Understand the Audience
         2. Design in Client Monitor Resolution
         3. Strive for Consistency and Simplicity
   2. 13. Designing Custom Multitable Forms
      1. Expanding Your Form Design Repertoire
      2. Understanding the Access Toolbox
         1. Control Categories
         2. The Toolbox
         3. Access's Control Wizards, Builders, and Toolbars
            1. Access Control Wizards
            2. Access Builders
            3. Customizable Toolbars
      3. Using the Toolbox To Add Controls
         1. Creating the Query on Which to Base the Main Form
         2. Creating a Blank Form with a Header and Footer
         3. Adding a Label to the Form Header
         4. Formatting Text and Adjusting Text Control Sizes
         5. Creating Bound, Multiline, and Calculated Text Boxes
            1. Adding Text Boxes Bound to Fields
            2. Adding a Calculated Text Box and Formatting Date/Time Values
            3. Using the Clipboard with Controls
         6. Changing the Default View and Obtaining Help for Properties
         7. Adding Option Groups with the Wizard
         8. Using the Clipboard to Copy Controls to Another Form
         9. Using Combo and List Boxes
            1. Using the Combo Box Wizard
            2. Using the Query Builder to Populate a Combo Box
            3. Creating a Combo Box with a List of Static Values
         10. Creating a Combo Box to Find Specific Records
         11. Creating a Tab Control
             1. Adding Tab Control Pages
             2. Changing the Page Order
             3. Deleting a Tab Control Page
             4. Setting the Tab Control's Properties
             5. Placing Other Controls on Tab Pages
         12. Changing One Control Type to Another
      4. Completing the Main Personnel Actions Entry Form
      5. Creating a Subform Using the Subform/Subreport Wizard
      6. Modifying the Design of Continuous Forms
      7. Overriding the Field Properties of Tables
      8. Adding Page Headers and Footers for Printing Forms
      9. Troubleshooting
         1. Error Messages on Copied Controls
      10. In the Real World—Access Wizardry
   3. 14. Printing Basic Reports and Mailing Labels
      1. Understanding the Relationship Between Forms and Reports
      2. Categorizing Types of Access Reports
      3. Creating a Grouping Report with the Report Wizard
      4. Using Access's Report Windows
      5. Using AutoFormat and Customizing Report Styles
      6. Modifying a Basic Wizard Report
         1. Deleting, Relocating, and Editing Existing Controls
         2. Adding Calculated Controls to a Report
            1. Changing the Report's Record Source
            2. Adding the Calculated Fields
         3. Aligning and Formatting Controls and Adjusting Line Spacing
            1. Aligning Controls Horizontally and Vertically
            2. Formatting Controls
            3. Adjusting Line Spacing
      7. Adjusting Margins and Printing Conventional Reports
      8. Preventing Widowed Records with the Group Keep Together Property
      9. Printing Multicolumn Reports as Mailing Labels
      10. Troubleshooting
          1. Eliminating Empty Pages
          2. Unexpected Parameters Dialogs
      11. In the Real World—The Apocryphal Paperless Office
   4. 15. Preparing Advanced Reports
      1. Creating Reports from Scratch
      2. Grouping and Sorting Report Data
         1. Grouping Data
            1. Grouping by Numeric Values
            2. Grouping by Alphabetic Code Characters
            3. Grouping with Subgroups
            4. Using a Function to Group by Range
            5. Grouping on Date and Time
         2. Sorting Data Groups
      3. Working from a Blank Report
         1. Using a Report as a Subreport
         2. Creating the Monthly Sales by Category Report
      4. Incorporating Subreports
         1. Adding a Linked Subreport to a Bound Report
         2. Using Unlinked Subreports and Unbound Reports
      5. Customizing De Novo Reports
         1. Adding and Deleting Sections of Your Report
         2. Controlling Page Breaks and Printing Page Headers and Footers
      6. Reducing the Length of Reports
      7. Adding Other Controls to Reports
      8. Mailing Report Snapshots
         1. Viewing and Printing the Report Snapshot
      9. Troubleshooting
         1. Link Expression Errors
         2. Report Snapshots Won't Open
      10. In the Real World—The Art of Report Design
7. IV. Publishing Data on Intranets and the Internet
   1. 16. Working with Hyperlinks and HTML
      1. Data-Enabling Web Pages
      2. Putting Microsoft's Internet Program in Perspective
         1. The Digital Nervous System and Windows DNA
         2. Microsoft's Internet-Related Client Products
         3. Microsoft's Server-Side Components
         4. Microsoft Technologies Supporting Internet-Related Products
      3. Navigating the Web and Intranets with Hyperlinks
      4. Understanding Access 2000's Hyperlink Field Data Type
         1. Testing Hyperlinks in the Northwind Orders Table
         2. Editing and Inserting Conventional Hyperlinks
         3. Linking to Bookmarks in a Word Document
         4. Specifying Hyperlinks to Pages on an Intranet Server or the Web
         5. Adding a ScreenTip to the Hyperlink
         6. Using Table Hyperlinks to Open an Access Object
      5. Using Hyperlinks with Access Controls
      6. Specifying Other Internet Uniform Resource Locators
      7. Troubleshooting
         1. Intranet Connection Problems
      8. In the Real World—To Internet or Not
         1. Microsoft Gets a Clue and the Virus
         2. Innoculation Against Internet Fever
   2. 17. Generating Web Pages from Tables and Queries
      1. Easing the Way to Web-Based Decision Support
      2. Exporting Table and Query Datasheets to HTML
         1. Creating an Unformatted Web Page
         2. Creating a Web Page from a Query
         3. Using HTML Templates
         4. Using the Access HTML Templates Included with Office 2000
         5. Exporting a Query Datasheet with a Template
         6. Exporting Reports to HTML
      3. Importing Data from HTML Tables
      4. Creating Dynamic Web Pages
         1. Understanding ASP
         2. Creating an ODBC Data Source for ASP
         3. Exporting an Access Query to ASP
      5. Troubleshooting
         1. HTML Table Import Errors
      6. In the Real World—ASP versus DAP
   3. 18. Designing Data Access Pages
      1. Moving to a New Access Form Model
      2. Understanding Access's Dynamic HTML Implementation
         1. Technologies Supporting DHTML and DAP
         2. DOM HTML and DHTML
      3. Getting Acquainted with DAP
         1. The Review Products Page
         2. The Review Orders Page
         3. The HTML Source Editor
         4. The Analyze Sales PivotTable Page
         5. The Sales Page
         6. Read-Write Pages
      4. Using the Page Wizard to Create Simple DAP
      5. Using AutoPage to Create Columnar DAP
         1. Using the Record Navigation Control's Filter and Sort Features
      6. Modifying the Design of AutoPage DAP
         1. Altering Record Navigation Control Properties
      7. Starting a DAP from Scratch
         1. Adding a PivotTable with the Page Field List
         2. Working with the PivotTable List Control in IE 5.0
         3. Altering Pivot Control Properties in IE 5.0
      8. Adding Charts to DAP with a PivotTable List
         1. Designing the Query for the PivotTable List
         2. Adding and Formatting the PivotTable List
         3. Using the Chart Wizard to Bind an Office Chart to the PivotTable List
         4. Generating a Grouped Page
         5. Creating a Three-Level Hierarchical Grouped Page Structure
         6. Filling in the Details
         7. Adding Fields of Related Tables and Captions
      9. Troubleshooting
         1. DAP Lose Their Style
         2. DAP Clients Can't Find the Jet Data Source
      10. In the Real World—Are DAP Ready for Prime Time?
          1. Internet Economics 101
          2. Sweet Spot(s) on the Software Elasticity Curve
8. V. Integrating Access with Other Office 2000 Applications
   1. 19. Adding Charts and Graphics to Forms and Reports
      1. Enlivening Forms and Reports with Graphics
      2. Creating Graphs and Charts with Microsoft Graph 2000
         1. Creating the Query on Which to Base the Graph
         2. Using the Chart Wizard to Create an Unlinked Graph
         3. Modifying the Design Features of Your Graph
         4. Creating a Graph from a Crosstab Query
         5. Linking the Graph to a Single Record of a Table or Query
      3. Using the Chart Web Control in Pages
         1. Designing Queries for the Chart Web Control
         2. Adding an Office Chart Based on the Single-Column Series
         3. Altering the Properties of the Office Chart
      4. Adding a Bound Object Control to a Form or Report
         1. Including Photos in the Personnel Actions Query
         2. Displaying the Employee's Picture in the Personnel Actions Form
         3. Scaling Graphic Objects
         4. Examining Bitmap Image File Formats
      5. Using the Image Control
      6. Troubleshooting
         1. Reversing the X-Axis and Legend Labels
         2. Fixing Errors When Opening OLE Objects
      7. In the Real World—Visualizing Data
         1. Meaning, Significance, and Visualization
         2. Management by Trend Exception
   2. 20. Using Access with Microsoft Excel
      1. Slicing and Dicing Data with PivotTables
      2. Using the Access PivotTable Wizard
         1. Creating the Query for the PivotTable
         2. Generating a PivotTable Form with the Wizard
      3. Manipulating PivotTables in Excel
         1. Improving PivotTable Formatting
         2. Slicing PivotTable Data
         3. Slicing by Filtering
         4. Collapsing or Expanding the Display of Detail Data
         5. Changing a Dimension's Axis
      4. Formatting PivotTable Reports
      5. Creating a PivotChart from a PivotTable
      6. Using Excel as an OLE Server
         1. Embedding a Conventional Excel Worksheet in a Form
         2. Extracting Values from an OLE Object
         3. Linking to a Range of Cells in an Excel Worksheet
      7. Troubleshooting
         1. Disabled Refresh Data Menu Choice
         2. Excel Workbook Won't Embed in an Object Frame
         3. Excel Adornments Don't Appear in Activated Object Frame
      8. In the Real World—OLAP and PivotTables
         1. Measures and Dimensions
         2. So What's OLAP Have to Do with Access 2000?
   3. 21. Using Access with Microsoft Word and Mail Merge
      1. Integrating Access 2000 with Word 2000
      2. Using the Access Mail Merge Wizard
         1. Creating and Previewing a New Form Letter
         2. Using an Existing Main Merge Document with a New Data Source
      3. Using Word 2000's Mail Merge Feature with Access Databases
         1. Creating a New Mail Merge Data Source with Microsoft Query and an ODBC Data Source
         2. Creating Form Letters from an Existing Query
      4. Embedding or Linking Word Documents in Access Tables
         1. Embedding or Linking a Word 2000 Document in a Table
         2. Creating a Form to Display the Embedded Document
      5. Troubleshooting
         1. Missing OLE Server Registry Entries
      6. In the Real World—Microsoft Query and OLE DB
9. VI. Using Advanced Access Techniques
   1. 22. Exploring Relational Database Design and Implementation
      1. Reviewing Access 2000's New Database Design Features
      2. Integrating Objects and Relational Databases
      3. Understanding Database Systems
         1. The Objectives of Database Design
         2. The Process of Database Design
         3. The Object-Oriented Approach to Database Design
            1. Considering Static and Dynamic Properties of Objects
            2. Describing Data Entities and Their Attributes
            3. Accounting for the Behavior of Objects with Methods
            4. Combining Different Entities in a Single Table
         4. Database Terminology
         5. Types of Tables and Keys in Relational Databases
      4. Data Modeling
         1. Application Databases
         2. Subject Databases
         3. Diagrammatic Data Models
         4. Database Schema
      5. Normalizing Data to the Relational Model
         1. Normalization Rules
            1. First Normal Form
            2. Second Normal Form
            3. Third Normal Form
            4. Fourth Normal Form
            5. Fifth Normal Form and Combined Entities
         2. Types of Relationships
            1. One-to-One Relationships
            2. One-to-Many Relationships
            3. Many-to-One Relationships
            4. Many-to-Many Relationships and Fourth Normal Form
      6. Using Access 2000's Table Analyzer Wizard
      7. Generating a Data Dictionary with the Database Documenter
      8. Using Access Indexes
      9. Enforcing Database Integrity
         1. Ensuring Entity Integrity and Auditability
         2. Maintaining Referential Integrity
      10. Troubleshooting
          1. AutoNumber Starting Value Errors
      11. In the Real World—Why Learn Relational Theory?
   2. 23. Working with Structured Query Language
      1. Understanding the Role of SQL in Access 2000
      2. Using Access to Learn SQL
      3. Understanding SQL Grammar
      4. Writing SELECT Queries in SQL
         1. Using SQL Punctuation and Symbols
         2. Using SQL Statements to Create Access Queries
         3. Using the SQL Aggregate Functions
         4. Creating Joins with SQL
         5. Using UNION Queries
         6. Implementing Subqueries
      5. Writing Action and Crosstab Queries
      6. Adding IN to Use Tables in Another Database
         1. Working with Another Access Database
         2. Using the IN Clause with Other Types of Databases
         3. Creating Tables with Jet DDL
         4. Comparing ANSI and Jet SQL
         5. SQL Reserved Words in Access
         6. Jet SQL Reserved Words Corresponding to ANSI SQL Keywords
         7. Access Functions and Operators Used in Place of ANSI SQL Keywords
         8. Jet SQL Reserved Words, Operators, and Functions Not in ANSI SQL
         9. Jet's DISTINCTROW and SQL's DISTINCT Keywords
         10. Common ANSI SQL Keywords and Features Not Supported by Jet SQL Reserved Words
      7. Using SQL Statements in Forms, Reports, and Macros
      8. Troubleshooting
         1. Unexpected Enter Parameter Dialogs
      9. In the Real World—SQL As a Second Language
         1. Making Custom Queries Easy for Users
         2. Microsoft English Query
   3. 24. Securing Multiuser Network Applications
      1. Networking Access 2000 Applications
         1. Installing Access in a Networked Environment
         2. Sharing Your Access Database Files with Other Users
            1. Creating a Folder and System File for File Sharing
            2. Preparing to Share Your Database Files
            3. Splitting Databases for File Sharing
            4. Fixing Links, Data Sources, and Hyperlinks of DAP
               1. Updating Database Window Links to DAP
               2. Changing the Data Source of DAP
            5. Choosing Workgroups with the Workgroup Administrator
         3. Using Command-Line Options to Open a Shared Database
         4. Maintaining Database Security
            1. Specifying the Principles of Database Security on a LAN
            2. Password-Protecting a Single Database
            3. Managing Groups and Users
               1. Establishing Your Own Admins Name, Password, and PID
               2. Establishing Members of Access Groups
               3. Adding a New Group
               4. Deleting Users and Groups
               5. Clearing Forgotten Passwords
         5. Understanding Database Object Ownership
            1. Owner Permissions for Objects
            2. Changing the Ownership of Database Objects
         6. Granting and Revoking Permissions for Database Objects
            1. Using the Security Wizard to Change Permissions
            2. Altering Group Permissions Manually
            3. Granting Additional Permissions to Specific Users
            4. Granting Permissions for a Database in Another Workgroup
         7. Sharing Databases on the Network
            1. Sharing Database Files on a Windows 9x Network
            2. Sharing Files with User-Level Security
            3. Sharing Database Files from a Network Server
         8. Accessing the Shared Workgroup and Data Files
            1. Attaching the Shared Workgroup System File
            2. Refreshing the Links to the Shared Data File
         9. Administering Databases and Applications
            1. Backing Up and Restoring Databases
            2. Compacting and Repairing Database Files
            3. Encrypting and Decrypting Database Files
         10. Troubleshooting
             1. Invalid Path Errors
         11. In the Real World—Shared-File versus Client/Server Back Ends
   4. 25. Creating Access Data Projects
      1. Moving Access to the Client/Server Model
      2. Understanding the Role of MSDE
         1. SQL Server Versions and Features
         2. MSDE Benefits
      3. Installing and Starting MSDE
      4. Getting Acquainted with ADP
         1. Accommodating MSDE and SQL Server 7.0 Features
         2. Running the NorthwindCS Sample Project
      5. Using the Project Designer
         1. Working with MSDE Tables
         2. Exploring MSDE Views
         3. Diagramming Table Relationships
         4. Writing Stored Procedures
         5. Adding Triggers to a Table
      6. Using the Upsizing Wizard to Create ADP
         1. Running the Access Upsizing Wizard
         2. Checking the Wizard's Successes and Failures
      7. Downsizing Databases with the DTS Wizard
      8. Connecting to Remote MSDE Databases
         1. Using the SQL Server Client Network Utility
         2. Testing and Using Remote Databases
      9. Establishing MSDE Security
      10. Troubleshooting
          1. Remote Database Connection Problems
      11. In the Real World—ADP on Trial
          1. ADP Drawbacks
          2. SQL Server Advantages—ADP, MSDE, and SQL Server 7.0
10. VII. Programming and Converting Access Applications
    1. 26. Writing Visual Basic for Applications Code
       1. Understanding the Role of VBA in Access
       2. Introducing VBA 6.0
          1. Where You Use VBA Code
          2. Typographic and Naming Conventions Used for VBA
          3. Modules, Functions, and Subprocedures
          4. Elements of Modules
          5. References to VBA and Access Modules
          6. Data Types and Database Objects in VBA
          7. Variables and Naming Conventions
             1. Implicit Variables
             2. Explicit Variables
             3. Scope and Duration of Variables
             4. User-Defined Data Types
             5. VBA Arrays
             6. Named Database Objects as Variables in VBA Code
             7. Object Properties and the With…End With Structure
          8. Symbolic Constants
             1. Access System-Defined Constants
             2. Access Intrinsic Constants
          9. VBA Named and Optional Arguments
       3. Controlling Program Flow
          1. Branching and Labels
             1. Skipping Blocks of Code with GoTo
             2. Avoiding Spaghetti Code by Not Using GoTo
          2. Conditional Statements
             1. The If…Then…End If Structure
             2. The Select Case…End Select Construct
          3. Repetitive Operations: Looping
             1. Using the For…Next Statement
             2. Using For…Next Loops to Assign Values to Array Elements
             3. Understanding the Do While…Loop and Do Until…Loop
             4. Making Sure Statements in a Loop Occur at Least Once
       4. Handling Runtime Errors
          1. Detecting the Type of Error with the Err Object
          2. Using the Error Event in Form and Report Modules
       5. Exploring the VBA Editor
          1. The Toolbar of the Module Window
          2. Module Shortcut Keys
          3. The VBA Help System
       6. Examining the Utility Functions Module
          1. Adding a Breakpoint to the IsLoaded() Function
          2. Printing to the Immediate Window with the Debug Object
          3. Using Text Comparison Options
       7. In the Real World—Macro Schizophrenia
    2. 27. Understanding Universal Data Access, OLE DB, and ADO
       1. Gaining a Perspective on Microsoft's New Data Access Components
       2. Interfacing with a Wide Range of Data Sources
          1. Redesigning from the Bottomup with OLE DB
          2. Mapping OLE DB Interfaces to ADO
          3. Comparing ADO and DAO Objects
       3. Creating ADODB.Recordsets
          1. Designing a Form Bound to an ADODB.Recordset Object
       4. Binding Controls to a Recordset with Code
       5. Making the Form Updatable
          1. Connecting to the NorthwindCS MSDE Database
          2. Substituting DAO 3.6 for ADO 2.1
       6. Exploring Top-Level ADO Properties, Methods, and Events
          1. Object Browser and ADO
       7. Working with the ADODB.Connection Object
          1. Connection Properties
          2. Errors Collection and Error Objects
          3. Connection Methods
          4. Connection Events
       8. Using the ADODB.Command Object
          1. Command Properties
          2. Parameters Collection
          3. Parameter Object
          4. Command Methods
             1. Code to Pass Parameter Values to a Stored Procedure
       9. Understanding the ADODB.Recordset Object
          1. Recordset Properties
             1. Fields Collection and Field Objects
          2. Recordset Methods
          3. Disconnected Recordsets
          4. Recordset Events
       10. Troubleshooting
           1. Cursor Types for the Form.Recordset Property
           2. MSDataShape and SQLOLEDB Providers
           3. Spaces in ADO Object Names
       11. In the Real World—Struggling with ADO
           1. Why Learn ADO?
           2. Where's ADOX?
           3. What's the Upshot?
    3. 28. Responding to Events with VBA 6.0
       1. Introducing Event-Driven Programming
       2. Understanding the Role of Class Modules
          1. The Main Switchboard Class Module
          2. Event-Handling Code in the Main Switchboard Form
       3. Examining Project Class Module Members in the Object Browser and Project Explorer
       4. Adding Event-Handling Code with the Command Button Wizard
       5. Using Functions to Respond to Events
       6. Understanding Access 2000's Event Repertoire
       7. Working with Access 2000's DoCmd Methods
          1. Arguments of DoCmd Methods
       8. Customizing Applications with CommandBar Objects
       9. Specifying a Custom CommandBar and Setting Other Startup Properties
       10. Referring to Access Objects with VBA
           1. Referring to Open Forms or Reports and Their Properties
           2. Referring to Controls and Their Properties
           3. Referring to Controls on a Subform or the Main Form
       11. Using Alternative Collection Syntax
       12. Responding to Data Events Triggered by Forms and Controls
       13. Troubleshooting
           1. Calling Procedures and Functions in Class Modules
           2. Missing Objects in Collections
       14. In the Real World—Dealing with Event-Driven Programming
    4. 29. Programming Combo and List Boxes
       1. Streamlining Decision Support Front Ends
       2. Constraining Query Choices with Combo Boxes
          1. Designing the Decision-Support Query
          2. Creating the Form and Adding a List Box
          3. Adding the Query Combo Boxes to the Form
       3. Adding Code to Create the Query's SQL Statement
       4. Converting Your Combo Box Form to an Access Data Project
          1. Importing and Testing the Combo Box Form
          2. Conforming Row Source SQL Statements to Transact-SQL Syntax
       5. Drilling Down from a List Box Selection
          1. Creating the Drill-Down Query and Adding the List Box
          2. Programming the Drill-Down List Box
       6. Adding New Features to List and Combo Boxes
          1. Iterating List Box Items and Selecting an Item
          2. Adding an Option to Select All Countries or Products
       7. Dealing with Jet-Specific Functions in Migrating to ADP
       8. Troubleshooting
          1. Run-Time Error '2465'
          2. Spurious Paramater Messages
       9. In the Real World—Access Combo and List Boxes
    5. 30. Working with ADO Recordsets, Forms, and Controls
       1. Navigating Recordsets with VBA
          1. Generating the Temporary Recordset
          2. Applying Move… Methods
          3. Using the EOF and BOF Properties in Loops
          4. Using the AbsolutePosition Property
       2. Using the Find Method and Bookmarks
       3. Modifying Rows of Recordsets
          1. Editing and Adding Rows
          2. Deleting Rows
       4. Populating a Combo Box from a Recordset
          1. Creating frmCombo2 and Altering the Combo Box Design
          2. Populating the Combo Boxes with Code
       5. Altering the Sequence of Combo Box Lists
       6. Filling List Boxes from Recordset Objects
       7. Formatting Value List Combo Box Columns
       8. Porting frmComboVBA to an Access Data Project
       9. Troubleshooting
          1. Making ADODB.Recordsets Updatable
       10. In the Real World—Adapting to ADO
    6. 31. Migrating Access 9x Applications to Access 2000
       1. Understanding the .mdb File Upgrade Process
       2. Converting Unsecured Files from Access 9x to Access 2000
          1. Upgrading on First Opening the File in Access 2000
       3. Upgrading After Opening the File in Access 2000
       4. Converting Secure Access 9x Files
          1. Upgrading in a Mixed Access 9x and 2000 Environment
          2. Upgrading the Back-End Database and Workgroup File
       5. Upgrading Access 2.0 Application .mdb Files to Access 2000
          1. Converting from Win16 to Win32 Function Calls
             1. Converting and Adding References to Libraries and Add-Ins
          2. Accommodating the 32-Index Limit on Tables
          3. Converting 16-Bit OLE Controls to 32-Bit ActiveX Controls
       6. Troubleshooting
          1. Missing Library or Project Message
       7. In the Real World—The Upgrade Blues
11. VIII. Appendix
    1. A. Glossary