Access® 2010 Bible

Access® 2010 Bible

Released June 2010
Publisher(s): Wiley
ISBN: 9780470475348

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The expert guidance you need to get the most out of Access 2010

Get the Access 2010 information you need to succeed with this comprehensive reference. If this is your first encounter with Access, you'll appreciate the thorough attention to database fundamentals and terminology. If you're familiar with earlier versions, you can jump right into Access 2010 enhancements such as the new Access user interface and wider use of XML and Web services.

  • Takes you under the hood of Microsoft Access 2010, the database application included with Microsoft Office 2010

  • Explores the latest enhancements, such as a new user interface and wider use of XML and Web services; also, how to exchange data with Word, Excel, PowerPoint, and other Office apps

  • Covers how to create tables, manipulate datasheets, and work with multiple tables

  • Explains the seven database objects and how to use a seven-step design method to build a database tailored to your needs

  • Shows you how to build forms, use Visual Basic and the VBA Editor, automate query parameters, create functions and subroutines, use XML to create data access pages, and more

  • Includes a CD with all source code from the book and working examples, plus bonus shareware, freeware, trial, demo and evaluation programs that work with or enhance Microsoft Office

You’ll want to keep this soup-to-nuts Access reference close at hand!

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of contents

  1. Copyright
  2. About the Author
  3. About the Technical Editor
  4. Credits
  5. Acknowledfments
  6. Introduction
    1. Is This Book for You?
      1. Yes — If you have no database experience
      2. Yes — If you've used other database managers like FileMaker
      3. Yes — If you want to learn the basics of Visual Basic for Applications (VBA) programming
      4. Yes — If you are an Access 2007 developer
    2. Pardon My Dust!
    3. Conventions Used in This Book
    4. Icons and Alerts
    5. How This Book Is Organized
      1. Part I: Access Building Blocks
      2. Part II: Programming Microsoft Access
      3. Part III: More-Advanced Access Techniques
      4. Part IV: Professional Database Development
      5. Part V: Access and Windows SharePoint Services
      6. Part VI: Access as an Enterprise Platform
      7. Part VII: Appendixes
    6. Guide to the Examples
      1. The main Switchboard menu
      2. Data tables
      3. The Products form
      4. The Invoice form
  7. I. Access Building Blocks
    1. 1. An Introduction to Database Development
      1. 1.1. The Database Terminology of Access
        1. 1.1.1. Databases
        2. 1.1.2. Tables
        3. 1.1.3. Records and fields
        4. 1.1.4. Values
      2. 1.2. Relational Databases
      3. 1.3. Access Database Objects
        1. 1.3.1. Datasheets
        2. 1.3.2. Queries
        3. 1.3.3. Data-entry and display forms
        4. 1.3.4. Reports
        5. 1.3.5. Database objects
      4. 1.4. A Five-Step Design Method
        1. 1.4.1. Step 1: The overall design — from concept to reality
        2. 1.4.2. Step 2: Report design
        3. 1.4.3. Step 3: Data design
          1. 1.4.3.1. Customer information
          2. 1.4.3.2. Sales information
          3. 1.4.3.3. Line-item information
        4. 1.4.4. Step 4: Table design
        5. 1.4.5. Step 5: Form design
      5. 1.5. Summary
    2. 2. Creating Access Tables
      1. 2.1. Getting Started with Access
        1. 2.1.1. The Templates section
        2. 2.1.2. The Office Backstage View
      2. 2.2. Creating a Database
      3. 2.3. The Access 2010 Environment
        1. 2.3.1. The Navigation Pane
        2. 2.3.2. The ribbon
        3. 2.3.3. Other relevant features of the Access environment
      4. 2.4. Creating a New Table
        1. 2.4.1. Designing tables
        2. 2.4.2. Using the Design tab
          1. 2.4.2.1. Primary Key
          2. 2.4.2.2. Insert Rows
          3. 2.4.2.3. Delete Rows
          4. 2.4.2.4. Property Sheet
          5. 2.4.2.5. Indexes
        3. 2.4.3. Working with fields
          1. 2.4.3.1. Naming a field
          2. 2.4.3.2. Specifying a data type
          3. 2.4.3.3. Specifying data validation rules
          4. 2.4.3.4. Assigning field data types
            1. 2.4.3.4.1. Text data type
            2. 2.4.3.4.2. Memo data type
            3. 2.4.3.4.3. Number data type
            4. 2.4.3.4.4. Date/Time data type
            5. 2.4.3.4.5. Currency
            6. 2.4.3.4.6. AutoNumber
            7. 2.4.3.4.7. Yes/No
            8. 2.4.3.4.8. OLE Object
            9. 2.4.3.4.9. Attachment
            10. 2.4.3.4.10. Hyperlink data type
            11. 2.4.3.4.11. Lookup Wizard
          5. 2.4.3.5. Entering a field description
      5. 2.5. Creating tblCustomers
        1. 2.5.1. Using AutoNumber fields
        2. 2.5.2. Completing tblCustomers
      6. 2.6. Changing a Table Design
        1. 2.6.1. Inserting a new field
        2. 2.6.2. Deleting a field
        3. 2.6.3. Changing a field location
        4. 2.6.4. Changing a field name
        5. 2.6.5. Changing a field size
        6. 2.6.6. Handling data conversion issues
        7. 2.6.7. Assigning field properties
          1. 2.6.7.1. Common properties
          2. 2.6.7.2. Format
            1. 2.6.7.2.1. Number and Currency field formats
            2. 2.6.7.2.2. Custom numeric formats
          3. 2.6.7.3. Date/Time field formats
            1. 2.6.7.3.1. Built-in Date/Time formats
            2. 2.6.7.3.2. Custom Date/Time formats
          4. 2.6.7.4. Text and Memo field formats
          5. 2.6.7.5. Yes/No field formats
            1. 2.6.7.5.1. Hyperlink data-type format
          6. 2.6.7.6. Input Mask
            1. 2.6.7.6.1. The Input Mask Wizard
          7. 2.6.7.7. Caption
          8. 2.6.7.8. Validation Rule and Validation Text
          9. 2.6.7.9. Required
          10. 2.6.7.10. AllowZeroLength
          11. 2.6.7.11. Indexed
            1. 2.6.7.11.1. The AutoIndex option
            2. 2.6.7.11.2. When to index
      7. 2.7. Understanding tblCustomers Field Properties
      8. 2.8. Setting the Primary Key
        1. 2.8.1. Choosing a primary key
        2. 2.8.2. Creating the primary key
        3. 2.8.3. Creating composite primary keys
      9. 2.9. Indexing Access Tables
        1. 2.9.1. The importance of indexes
        2. 2.9.2. Multiple-field indexes
        3. 2.9.3. When to index tables
      10. 2.10. Printing a Table Design
      11. 2.11. Saving the Completed Table
      12. 2.12. Manipulating Tables
        1. 2.12.1. Renaming tables
        2. 2.12.2. Deleting tables
        3. 2.12.3. Copying tables in a database
        4. 2.12.4. Copying a table to another database
      13. 2.13. Adding Records to a Database Table
      14. 2.14. Understanding Attachment Fields
      15. 2.15. Summary
    3. 3. Designing Bulletproof Databases
      1. 3.1. Building Bulletproof Databases
      2. 3.2. Data Normalization
        1. 3.2.1. First normal form
        2. 3.2.2. Second normal form
          1. 3.2.2.1. Further optimization: Adding tables to the scheme
          2. 3.2.2.2. Breaking the rules
        3. 3.2.3. Third normal form
        4. 3.2.4. Denormalization
      3. 3.3. Table Relationships
        1. 3.3.1. Connecting the data
        2. 3.3.2. One-to-one
        3. 3.3.3. One-to-many
        4. 3.3.4. Many-to-many
      4. 3.4. Integrity Rules
        1. 3.4.1. No primary key can contain a null value
        2. 3.4.2. All foreign key values must be matched by corresponding primary keys
      5. 3.5. Keys
        1. 3.5.1. Deciding on a primary key
        2. 3.5.2. The benefits of a primary key
        3. 3.5.3. Designating a primary key
          1. 3.5.3.1. Single-field versus composite primary keys
          2. 3.5.3.2. Natural versus surrogate primary keys
          3. 3.5.3.3. Creating primary keys
        4. 3.5.4. Creating relationships and enforcing referential integrity
          1. 3.5.4.1. Specifying the Join Type between tables
          2. 3.5.4.2. Enforcing referential integrity
            1. 3.5.4.2.1. Choosing the Cascade Update Related Fields option
            2. 3.5.4.2.2. Choosing the Cascade Delete Related Records option
        5. 3.5.5. Viewing all relationships
        6. 3.5.6. Deleting relationships
        7. 3.5.7. Application-specific integrity rules
      6. 3.6. Summary
    4. 4. Selecting Data with Queries
      1. 4.1. Introducing Queries
        1. 4.1.1. What queries are
        2. 4.1.2. Types of queries
        3. 4.1.3. What queries can do
        4. 4.1.4. What queries return
      2. 4.2. Creating a Query
      3. 4.3. Adding Fields
        1. 4.3.1. Adding a single field
        2. 4.3.2. Adding multiple fields
      4. 4.4. Displaying the Recordset
      5. 4.5. Working with Fields
        1. 4.5.1. Selecting a field in the QBE grid
        2. 4.5.2. Changing field order
        3. 4.5.3. Resizing columns in the QBE grid
        4. 4.5.4. Removing a field
        5. 4.5.5. Inserting a field
        6. 4.5.6. Providing an alias for the field name
        7. 4.5.7. Showing a field
      6. 4.6. Changing the Sort Order
      7. 4.7. Displaying Only Selected Records
        1. 4.7.1. Understanding selection criteria
        2. 4.7.2. Entering simple string criteria
        3. 4.7.3. Entering other simple criteria
      8. 4.8. Printing a Query's Recordset
      9. 4.9. Saving a Query
      10. 4.10. Creating Multi-Table Queries
        1. 4.10.1. Viewing table names
        2. 4.10.2. Adding multiple fields
        3. 4.10.3. Multi-table query limitations
        4. 4.10.4. Overcoming query limitations
          1. 4.10.4.1. Updating a unique index (primary key)
          2. 4.10.4.2. Replacing existing data in a query with a one-to-many relationship
          3. 4.10.4.3. Updating fields in queries
      11. 4.11. Working with the Table Pane
        1. 4.11.1. Looking at the join line
        2. 4.11.2. Manipulating Field Lists
        3. 4.11.3. Moving a table
        4. 4.11.4. Removing a table
        5. 4.11.5. Adding more tables
      12. 4.12. Creating and Working with Query Joins
        1. 4.12.1. Using ad hoc table joins
        2. 4.12.2. Specifying the type of join
        3. 4.12.3. Deleting joins
      13. 4.13. Understanding Table Join Types
        1. 4.13.1. Working with inner joins (equi-joins)
        2. 4.13.2. Understanding outer joins
        3. 4.13.3. Working with self-joins
        4. 4.13.4. Creating a Cartesian product
      14. 4.14. Summary
    5. 5. Using Operators and Expressions in Access
      1. 5.1. Introducing Operators
        1. 5.1.1. Looking at the types of operators
          1. 5.1.1.1. Mathematical operators
            1. 5.1.1.1.1. The addition operator: +
            2. 5.1.1.1.2. The subtraction operator: −
            3. 5.1.1.1.3. The multiplication operator: *
            4. 5.1.1.1.4. The division operator: /
            5. 5.1.1.1.5. The integer division operator: \
            6. 5.1.1.1.6. The exponentiation operator: ^
            7. 5.1.1.1.7. The modulo division operator: Mod
          2. 5.1.1.2. Comparison operators
            1. 5.1.1.2.1. The equal operator: =
            2. 5.1.1.2.2. The not-equal operator: <>
            3. 5.1.1.2.3. The less-than operator: <
            4. 5.1.1.2.4. The less-than-or-equal-to operator: <=
            5. 5.1.1.2.5. The greater-than operator: >
            6. 5.1.1.2.6. The greater-than-or-equal-to operator: >=
          3. 5.1.1.3. String operators
            1. 5.1.1.3.1. The concatenation operator: &
            2. 5.1.1.3.2. The Like and Not Like operators
          4. 5.1.1.4. Boolean (logical) operators
            1. 5.1.1.4.1. The And operator
            2. 5.1.1.4.2. The Or operator
            3. 5.1.1.4.3. The Not operator
          5. 5.1.1.5. Miscellaneous operators
            1. 5.1.1.5.1. The Between...And operator
            2. 5.1.1.5.2. The In operator
            3. 5.1.1.5.3. The Is operator
        2. 5.1.2. Operator precedence
          1. 5.1.2.1. The mathematical precedence
          2. 5.1.2.2. The comparison precedence
          3. 5.1.2.3. The Boolean precedence
      2. 5.2. Using Operators and Expressions in Queries
        1. 5.2.1. Using query comparison operators
        2. 5.2.2. Understanding complex criteria
        3. 5.2.3. Using functions in select queries
        4. 5.2.4. Referencing fields in select queries
      3. 5.3. Entering Single-Value Field Criteria
        1. 5.3.1. Entering character (Text or Memo) criteria
        2. 5.3.2. The Like operator and wildcards
        3. 5.3.3. Specifying non-matching values
        4. 5.3.4. Entering numeric criteria
        5. 5.3.5. Entering true or false criteria
        6. 5.3.6. Entering OLE object criteria
      4. 5.4. Using Multiple Criteria in a Query
        1. 5.4.1. Understanding an Or operation
        2. 5.4.2. Specifying multiple values with the Or operator
        3. 5.4.3. Using the Or cell of the QBE pane
        4. 5.4.4. Using a list of values with the In operator
        5. 5.4.5. Using And to specify a range
        6. 5.4.6. Using the Between...And operator
        7. 5.4.7. Searching for Null data
      5. 5.5. Entering Criteria in Multiple Fields
        1. 5.5.1. Using And and Or across fields in a query
        2. 5.5.2. Specifying Or criteria across fields of a query
        3. 5.5.3. Using And and Or together in different fields
        4. 5.5.4. A complex query on different lines
      6. 5.6. Summary
    6. 6. Working with Datasheet View
      1. 6.1. Understanding Datasheets
      2. 6.2. Looking at the Datasheet Window
        1. 6.2.1. Moving within a datasheet
        2. 6.2.2. Using the Navigation buttons
        3. 6.2.3. Examining the Datasheet ribbon
          1. 6.2.3.1. Views
          2. 6.2.3.2. Clipboard
          3. 6.2.3.3. Sort & Filter
          4. 6.2.3.4. Records
          5. 6.2.3.5. Find
          6. 6.2.3.6. Window
          7. 6.2.3.7. Text Formatting
      3. 6.3. Opening a Datasheet
      4. 6.4. Entering New Data
        1. 6.4.1. Saving the record
        2. 6.4.2. Understanding automatic data-type validation
        3. 6.4.3. Knowing how properties affect data entry
          1. 6.4.3.1. Standard text data entry
          2. 6.4.3.2. Date/Time data entry
          3. 6.4.3.3. Number/Currency data entry with data validation
          4. 6.4.3.4. OLE object data entry
          5. 6.4.3.5. Memo field data entry
      5. 6.5. Navigating Records in a Datasheet
        1. 6.5.1. Moving between records
        2. 6.5.2. Finding a specific value
      6. 6.6. Changing Values in a Datasheet
        1. 6.6.1. Manually replacing an existing value
        2. 6.6.2. Changing an existing value
      7. 6.7. Using the Undo Feature
      8. 6.8. Copying and Pasting Values
      9. 6.9. Replacing Values
      10. 6.10. Adding New Records
      11. 6.11. Deleting Records
      12. 6.12. Displaying Records
        1. 6.12.1. Changing the field order
        2. 6.12.2. Changing the field display width
        3. 6.12.3. Changing the record display height
        4. 6.12.4. Changing display fonts
        5. 6.12.5. Displaying cell gridlines and alternate row colors
        6. 6.12.6. Aligning data in columns
        7. 6.12.7. Hiding and unhiding columns
        8. 6.12.8. Freezing columns
        9. 6.12.9. Saving the changed layout
        10. 6.12.10. Saving a record
      13. 6.13. Sorting and Filtering Records in a Datasheet
        1. 6.13.1. Sorting your records with QuickSort
        2. 6.13.2. Filtering a selection
        3. 6.13.3. Filtering by form
      14. 6.14. Focusing on Special Features of Datasheets
      15. 6.15. Printing Records
        1. 6.15.1. Printing the datasheet
        2. 6.15.2. Using the Print Preview window
      16. 6.16. Summary
    7. 7. Creating Basic Access Forms
      1. 7.1. Formulating Forms
        1. 7.1.1. Creating a new form
          1. 7.1.1.1. Using the Form command
          2. 7.1.1.2. Using the Form Wizard
        2. 7.1.2. Looking at special types of forms
          1. 7.1.2.1. Navigation forms
          2. 7.1.2.2. Multiple-items forms
          3. 7.1.2.3. Split forms
          4. 7.1.2.4. Datasheet forms
        3. 7.1.3. Resizing the form area
        4. 7.1.4. Saving your form
      2. 7.2. Working with Controls
        1. 7.2.1. Categorizing controls
          1. 7.2.1.1. Bound controls
          2. 7.2.1.2. Unbound controls
          3. 7.2.1.3. Calculated controls
        2. 7.2.2. Adding a control
          1. 7.2.2.1. Using the Controls group
          2. 7.2.2.2. Using the Field List
        3. 7.2.3. Selecting and deselecting controls
          1. 7.2.3.1. Selecting a single control
          2. 7.2.3.2. Selecting multiple controls
          3. 7.2.3.3. Deselecting controls
        4. 7.2.4. Manipulating controls
          1. 7.2.4.1. Resizing a control
          2. 7.2.4.2. Sizing controls automatically
          3. 7.2.4.3. Moving a control
          4. 7.2.4.4. Aligning controls
          5. 7.2.4.5. Modifying the appearance of a control
          6. 7.2.4.6. Grouping controls
          7. 7.2.4.7. Attaching (and reattaching) a label to a control
          8. 7.2.4.8. Changing a control's type
          9. 7.2.4.9. Copying a control
          10. 7.2.4.10. Deleting a control
      3. 7.3. Introducing Properties
        1. 7.3.1. Displaying the Property Sheet
        2. 7.3.2. Getting acquainted with the Property Sheet
        3. 7.3.3. Changing a control's property setting
        4. 7.3.4. Naming control labels and their captions
      4. 7.4. Summary
    8. 8. Working with Data on Access Forms
      1. 8.1. Using Form View
        1. 8.1.1. Looking at the Home ribbon tab
          1. 8.1.1.1. The Views group
          2. 8.1.1.2. The Clipboard group
          3. 8.1.1.3. The Sort & Filter group
          4. 8.1.1.4. The Records group
          5. 8.1.1.5. The Find group
          6. 8.1.1.6. The Window group
          7. 8.1.1.7. The Text Formatting group
        2. 8.1.2. Navigating among fields
        3. 8.1.3. Moving among records in a form
      2. 8.2. Changing Values in a Form
        1. 8.2.1. Knowing which controls you can't edit
        2. 8.2.2. Working with pictures and OLE objects
        3. 8.2.3. Entering data in the Memo field
        4. 8.2.4. Entering data in the Date field
        5. 8.2.5. Using option groups
        6. 8.2.6. Using combo boxes and list boxes
        7. 8.2.7. Switching to Datasheet view
        8. 8.2.8. Saving a record
      3. 8.3. Printing a Form
      4. 8.4. Working with Form Properties
        1. 8.4.1. Changing the title bar text with the Caption property
        2. 8.4.2. Creating a bound form
        3. 8.4.3. Specifying how to view the form
        4. 8.4.4. Removing the Record Selector
        5. 8.4.5. Looking at other form properties
      5. 8.5. Adding a Form Header or Footer
      6. 8.6. Changing the Layout
        1. 8.6.1. Changing a control's properties
        2. 8.6.2. Setting the Tab Order
        3. 8.6.3. Aligning controls
        4. 8.6.4. Modifying the format of text in a control
        5. 8.6.5. Using the Field List to add controls
      7. 8.7. Creating a Calculated Control
      8. 8.8. Converting a Form to a Report
      9. 8.9. Summary
    9. 9. Presenting Data with Access Reports
      1. 9.1. Introducing Reports
        1. 9.1.1. Identifying the different types of reports
          1. 9.1.1.1. Tabular reports
          2. 9.1.1.2. Columnar reports
          3. 9.1.1.3. Mailing-label reports
        2. 9.1.2. Distinguishing between reports and forms
      2. 9.2. Creating a Report, from Beginning to End
        1. 9.2.1. Defining the report layout
        2. 9.2.2. Assembling the data
        3. 9.2.3. Creating a report with the Report Wizard
          1. 9.2.3.1. Creating a new report
          2. 9.2.3.2. Selecting the grouping levels
          3. 9.2.3.3. Defining the group data
          4. 9.2.3.4. Selecting the sort order
          5. 9.2.3.5. Selecting summary options
          6. 9.2.3.6. Selecting the layout
          7. 9.2.3.7. Opening the report design
          8. 9.2.3.8. Adjusting the report's layout
          9. 9.2.3.9. Choosing a theme
          10. 9.2.3.10. Creating new theme color schemes
          11. 9.2.3.11. Using the Print Preview window
          12. 9.2.3.12. Publishing in alternate formats
          13. 9.2.3.13. Viewing the Report Design window
        4. 9.2.4. Printing or viewing the report
          1. 9.2.4.1. Printing the report
          2. 9.2.4.2. Viewing the report
        5. 9.2.5. Saving the report
      3. 9.3. Banded Report Design Concepts
        1. 9.3.1. The Report Header section
        2. 9.3.2. The Page Header section
        3. 9.3.3. The Group Header section
        4. 9.3.4. The Detail section
        5. 9.3.5. The Group Footer section
        6. 9.3.6. The Page Footer section
        7. 9.3.7. The Report Footer section
      4. 9.4. Creating a Report from Scratch
        1. 9.4.1. Creating a new report and binding it to a query
        2. 9.4.2. Defining the report page size and layout
        3. 9.4.3. Placing controls on the report
        4. 9.4.4. Resizing a section
          1. 9.4.4.1. Modifying the appearance of text in a control
        5. 9.4.5. Working with text boxes
          1. 9.4.5.1. Adding and using text-box controls
          2. 9.4.5.2. Entering an expression in a text control
          3. 9.4.5.3. Sizing a text-box control or label control
          4. 9.4.5.4. Deleting and cutting attached labels from text controls
          5. 9.4.5.5. Pasting labels into a report section
          6. 9.4.5.6. Moving label and text controls
          7. 9.4.5.7. Modifying the appearance of multiple controls
        6. 9.4.6. Changing label and text-box control properties
        7. 9.4.7. Growing and shrinking text-box controls
        8. 9.4.8. Sorting and grouping data
          1. 9.4.8.1. Creating a group header or footer
        9. 9.4.9. Sorting data within groups
          1. 9.4.9.1. Removing a group header or footer
          2. 9.4.9.2. Hiding a section
          3. 9.4.9.3. Sizing a section
          4. 9.4.9.4. Moving controls between sections
        10. 9.4.10. Adding page breaks
      5. 9.5. Improving the Report's Appearance
        1. 9.5.1. Adjusting the page header
        2. 9.5.2. Creating an expression in the group header
        3. 9.5.3. Enhancing the Detail section
        4. 9.5.4. Creating a report header
      6. 9.6. Summary
  8. II. Programming Microsoft Access
    1. 10. VBA Programming Fundamentals
      1. 10.1. Introducing Visual Basic for Applications
      2. 10.2. Understanding VBA Terminology
      3. 10.3. Starting with VBA Code Basics
      4. 10.4. Migrating from Macros to VBA
        1. 10.4.1. Knowing when to use macros and when to use VBA
        2. 10.4.2. Converting your existing macros to VBA
        3. 10.4.3. Using the Command Button Wizard
      5. 10.5. Creating VBA Programs
        1. 10.5.1. Events and event procedures
          1. 10.5.1.1. Subprocedures
          2. 10.5.1.2. Functions
        2. 10.5.2. Modules
          1. 10.5.2.1. Looking at the types of modules
            1. 10.5.2.1.1. Standard modules
            2. 10.5.2.1.2. Form and report modules
          2. 10.5.2.2. Creating a new module
            1. 10.5.2.2.1. Working in the code window
            2. 10.5.2.2.2. The declarations section
            3. 10.5.2.2.3. Creating a new procedure
            4. 10.5.2.2.4. Using IntelliSense
            5. 10.5.2.2.5. Compiling procedures
            6. 10.5.2.2.6. Saving a module
            7. 10.5.2.2.7. Creating procedures in the form or report design window
            8. 10.5.2.2.8. Editing an existing procedure
            9. 10.5.2.2.9. Checking your results in the Immediate window
      6. 10.6. Understanding VBA Branching Constructs
        1. 10.6.1. Branching
          1. 10.6.1.1. The If...Then...Else...End If construct
          2. 10.6.1.2. The Select Case...End Select statement
        2. 10.6.2. Looping
          1. 10.6.2.1. The Do...Loop statement
          2. 10.6.2.2. The For...Next statement
      7. 10.7. Working with Objects and Collections
        1. 10.7.1. An object primer
        2. 10.7.2. The With statement
        3. 10.7.3. The For Each statement
      8. 10.8. Looking at Access Options for Developers
        1. 10.8.1. The Editor tab of the Options dialog box
          1. 10.8.1.1. Auto Indent
          2. 10.8.1.2. Auto Syntax Check
          3. 10.8.1.3. Break on all Errors
          4. 10.8.1.4. Require Variable Declaration
          5. 10.8.1.5. Compile on Demand
          6. 10.8.1.6. Auto List Members
          7. 10.8.1.7. Auto Quick Info
          8. 10.8.1.8. Auto Data Tips
        2. 10.8.2. The Project Properties dialog box
          1. 10.8.2.1. Project Name
          2. 10.8.2.2. Project Description
          3. 10.8.2.3. Conditional Compilation Arguments
        3. 10.8.3. Command-line arguments
      9. 10.9. Summary
    2. 11. Mastering VBA Data Types and Procedures
      1. 11.1. Introducing the Access VBA Editor
      2. 11.2. Using Variables
        1. 11.2.1. Naming variables
        2. 11.2.2. Declaring variables
          1. 11.2.2.1. The Dim keyword
          2. 11.2.2.2. The Public keyword
          3. 11.2.2.3. The Private keyword
      3. 11.3. Working with Data Types
        1. 11.3.1. Comparing implicit and explicit variables
        2. 11.3.2. Forcing explicit declaration
        3. 11.3.3. Using a naming convention
        4. 11.3.4. Understanding variable scope and lifetime
          1. 11.3.4.1. Examining scope
          2. 11.3.4.2. Determining a variable's lifetime
      4. 11.4. Understanding Subs and Functions
        1. 11.4.1. Understanding where to create a procedure
        2. 11.4.2. Calling VBA procedures
        3. 11.4.3. Creating subs
      5. 11.5. Creating Functions
        1. 11.5.1. Handling parameters
        2. 11.5.2. Calling a function and passing parameters
        3. 11.5.3. Creating a function to calculate sales tax
      6. 11.6. Simplifying Code with Named Arguments
      7. 11.7. Summary
    3. 12. The Access Event Model
      1. 12.1. Programming Events
        1. 12.1.1. Understanding how events trigger VBA code
        2. 12.1.2. Creating event procedures
      2. 12.2. Identifying Common Events
        1. 12.2.1. Form event procedures
          1. 12.2.1.1. Essential form events
          2. 12.2.1.2. Form mouse and keyboard events
          3. 12.2.1.3. Form data events
        2. 12.2.2. Control event procedures
        3. 12.2.3. Report event procedures
        4. 12.2.4. Report section event procedures
          1. 12.2.4.1. The Format event
          2. 12.2.4.2. Form PivotTable events
      3. 12.3. Paying Attention to Event Sequence
        1. 12.3.1. Looking at common event sequences
        2. 12.3.2. Writing simple form and control event procedures
          1. 12.3.2.1. Opening a form with an event procedure
          2. 12.3.2.2. Running an event procedure when closing a form
          3. 12.3.2.3. Using an event procedure to confirm record deletion
      4. 12.4. Summary
    4. 13. Accessing Data with VBA Code
      1. 13.1. Understanding SQL
        1. 13.1.1. Viewing SQL statements in queries
        2. 13.1.2. A SQL primer
          1. 13.1.2.1. SELECT
          2. 13.1.2.2. ALL
          3. 13.1.2.3. DISTINCT
          4. 13.1.2.4. DISTINCTROW
          5. 13.1.2.5. TOP
          6. 13.1.2.6. FROM
          7. 13.1.2.7. WHERE
          8. 13.1.2.8. ORDER BY
      2. 13.2. Working with Data
      3. 13.3. Understanding ADO Objects
        1. 13.3.1. The ADO Connection object
        2. 13.3.2. The ADO Command object
        3. 13.3.3. The ADO Recordset object
          1. 13.3.3.1. Navigating Recordsets
          2. 13.3.3.2. Understanding CursorType
          3. 13.3.3.3. Detecting the recordset end or beginning
          4. 13.3.3.4. Counting records
      4. 13.4. Understanding DAO Objects
        1. 13.4.1. The DAO DBEngine object
        2. 13.4.2. The DAO Workspace object
        3. 13.4.3. The DAO Database object
        4. 13.4.4. The DAO TableDef object
        5. 13.4.5. The DAO QueryDef object
        6. 13.4.6. The DAO Recordset object
        7. 13.4.7. The DAO Field objects (recordsets)
      5. 13.5. Writing VBA Code to Update a Table
        1. 13.5.1. Updating fields in a record using ADO
        2. 13.5.2. Updating a calculated control
          1. 13.5.2.1. Recalculating a control when updating or adding a record
          2. 13.5.2.2. Checking the status of a record deletion
        3. 13.5.3. Adding a new record
        4. 13.5.4. Deleting a record
        5. 13.5.5. Deleting related records in multiple tables
      6. 13.6. Summary
    5. 14. Debugging Your Access Applications
      1. 14.1. Testing Your Applications
      2. 14.2. Using the Module Options
        1. 14.2.1. Auto Syntax Check
        2. 14.2.2. Require Variable Declaration
        3. 14.2.3. Auto List Members
        4. 14.2.4. Auto Quick Info
        5. 14.2.5. Auto Data Tips
        6. 14.2.6. Break on All Errors
        7. 14.2.7. Compile on Demand
      3. 14.3. Organizing VBA Code
      4. 14.4. Compiling VBA Code
      5. 14.5. Traditional Debugging Techniques
        1. 14.5.1. Using MsgBox
        2. 14.5.2. Using Debug.Print
      6. 14.6. Using the Access Debugging Tools
        1. 14.6.1. Running code with the Immediate window
        2. 14.6.2. Suspending execution with breakpoints
        3. 14.6.3. Looking at variables with the Locals window
        4. 14.6.4. Setting watches with the Watches window
        5. 14.6.5. Using conditional watches
        6. 14.6.6. Using the Call Stack window
      7. 14.7. Summary
    6. 15. Using Access Data Macros
      1. 15.1. Introducing Data Macros
      2. 15.2. Looking at How Data Macros Are Created
        1. 15.2.1. Using the Macro Designer
        2. 15.2.2. Using the Action Catalog
          1. 15.2.2.1. Program flow
          2. 15.2.2.2. Data blocks
          3. 15.2.2.3. Data actions
      3. 15.3. Discovering Table Events
        1. 15.3.1. Before events
        2. 15.3.2. After events
      4. 15.4. Building Macros
        1. 15.4.1. Adding macro items
        2. 15.4.2. Manipulating macro items
        3. 15.4.3. Moving macro items
        4. 15.4.4. Collapsing macro items
        5. 15.4.5. Saving a macro as XML
      5. 15.5. Recognizing the Limitations of Data Macros
      6. 15.6. Summary
  9. III. More-Advanced Access Techniques
    1. 16. Working with External Data
      1. 16.1. Looking at How Access Works with External Data
        1. 16.1.1. Types of external data
        2. 16.1.2. Ways of working with external data
          1. 16.1.2.1. When to link to external data
          2. 16.1.2.2. When to import external data
          3. 16.1.2.3. When to export internal data
      2. 16.2. Linking External Data
        1. 16.2.1. Linking to external database tables
        2. 16.2.2. Limitations of linked data
        3. 16.2.3. Linking to other Access database tables
        4. 16.2.4. Linking to ODBC data sources
        5. 16.2.5. Linking to xBase files
        6. 16.2.6. Linking to non-database data
          1. 16.2.6.1. Linking to Excel
          2. 16.2.6.2. Linking to HTML files
          3. 16.2.6.3. Linking to text files
      3. 16.3. Working with Linked Tables
        1. 16.3.1. Setting view properties
        2. 16.3.2. Setting relationships
        3. 16.3.3. Optimizing linked tables
        4. 16.3.4. Deleting a linked table reference
        5. 16.3.5. Viewing or changing information for linked tables
      4. 16.4. Using Code to Link Tables in Access
        1. 16.4.1. The Connect and SourceTableName properties
        2. 16.4.2. Checking links
      5. 16.5. Summary
    2. 17. Importing and Exporting Data
      1. 17.1. Looking at Your Options for Importing and Exporting
      2. 17.2. Importing External Data
        1. 17.2.1. Importing from another Access database
        2. 17.2.2. Importing from an Excel spreadsheet
        3. 17.2.3. Importing a SharePoint list
        4. 17.2.4. Importing data from text files
          1. 17.2.4.1. Delimited text files
          2. 17.2.4.2. Fixed-width text files
        5. 17.2.5. Importing an XML document
        6. 17.2.6. Importing an HTML document
        7. 17.2.7. Importing Access objects other than tables
        8. 17.2.8. Importing an Outlook folder
        9. 17.2.9. Importing dBase tables
        10. 17.2.10. Troubleshooting import errors
          1. 17.2.10.1. Import errors for new tables
          2. 17.2.10.2. Import errors for existing tables
      3. 17.3. Exporting to External Formats
        1. 17.3.1. Exporting objects to other Access databases
        2. 17.3.2. Exporting through ODBC drivers
        3. 17.3.3. Functionality exclusive to exports
      4. 17.4. Summary
    3. 18. Advanced Access Query Techniques
      1. 18.1. Using Calculated Fields
      2. 18.2. Calculated Fields and the Expression Builder
      3. 18.3. Counting Records in a Table or Query
      4. 18.4. Finding the Top (n) Records in a Query
      5. 18.5. How Queries Save Field Selections
        1. 18.5.1. Hiding (not showing) fields
        2. 18.5.2. Renaming fields in queries
      6. 18.6. Query Design Options
      7. 18.7. Setting Query Properties
      8. 18.8. Creating Queries That Calculate Totals
        1. 18.8.1. Showing and hiding the Total row in the QBE pane
        2. 18.8.2. The Total row options
          1. 18.8.2.1. Group By
          2. 18.8.2.2. Expression
          3. 18.8.2.3. Using a Where clause
          4. 18.8.2.4. Aggregate functions
        3. 18.8.3. Performing totals on all records
        4. 18.8.4. Performing totals on groups of records
          1. 18.8.4.1. Calculating totals for a single group
          2. 18.8.4.2. Calculating totals for several groups
        5. 18.8.5. Specifying criteria for a total query
          1. 18.8.5.1. Specifying criteria for a Group By field
          2. 18.8.5.2. Specifying criteria for an Aggregate Total field
          3. 18.8.5.3. Specifying criteria for a Non-Aggregate Total field
        6. 18.8.6. Creating expressions for totals
      9. 18.9. Creating Crosstab Queries
        1. 18.9.1. Understanding the crosstab query
        2. 18.9.2. Creating the crosstab query
      10. 18.10. Understanding Action Queries
        1. 18.10.1. Types of action queries
        2. 18.10.2. Creating action queries
          1. 18.10.2.1. Creating an update action query
          2. 18.10.2.2. Creating a new table using a make-table query
          3. 18.10.2.3. Creating queries to append records
          4. 18.10.2.4. Creating a query to delete records
          5. 18.10.2.5. Running an action query
        3. 18.10.3. Troubleshooting action queries
          1. 18.10.3.1. Data-type errors in appending and updating
          2. 18.10.3.2. Key violations in action queries
          3. 18.10.3.3. Record-locked fields in multiuser environments
          4. 18.10.3.4. Text fields
      11. 18.11. Summary
    4. 19. Advanced Access Form Techniques
      1. 19.1. Setting Control Properties
        1. 19.1.1. Customizing default properties
        2. 19.1.2. Manipulating controls at runtime
        3. 19.1.3. Reading control properties
      2. 19.2. Working with Subforms
      3. 19.3. Designing Forms
        1. 19.3.1. Using the Tab Stop property
        2. 19.3.2. Tallying check boxes
        3. 19.3.3. Adding animation
        4. 19.3.4. Using SQL for a faster refresh
        5. 19.3.5. Selecting data for overtyping
        6. 19.3.6. Toggling properties with Not
        7. 19.3.7. Creating an auto-closing form
        8. 19.3.8. Setting up combo boxes and list boxes
        9. 19.3.9. Determining whether a form is open
      4. 19.4. Tackling Advanced Forms Techniques
        1. 19.4.1. Using the Page Number and Date/Time controls
        2. 19.4.2. Using the Image control
        3. 19.4.3. Morphing a control
        4. 19.4.4. Using the Format Painter
        5. 19.4.5. Offering more end-user help
        6. 19.4.6. Adding background pictures
        7. 19.4.7. Fine-tuning your form's behavior with form events
          1. 19.4.7.1. ApplyFilter
        8. 19.4.8. Filter event
      5. 19.5. Using the Tab Control
      6. 19.6. Using Dialog Boxes to Collect Information
        1. 19.6.1. Composing the SQL statement
        2. 19.6.2. Adding a default button
        3. 19.6.3. Setting a Cancel button
        4. 19.6.4. Removing the control menu
        5. 19.6.5. Closing the form
      7. 19.7. Summary
    5. 20. Advanced Access Report Techniques
      1. 20.1. Grouping and Sorting Data
        1. 20.1.1. Grouping data alphabetically
        2. 20.1.2. Grouping on date intervals
        3. 20.1.3. Hiding repeating information
        4. 20.1.4. Hiding a page header
        5. 20.1.5. Starting a new page number for each group
      2. 20.2. Formatting Data
        1. 20.2.1. Creating numbered lists
        2. 20.2.2. Adding bullet characters
        3. 20.2.3. Adding emphasis at runtime
        4. 20.2.4. Avoiding empty reports
        5. 20.2.5. Avoiding null values in a tabular report
        6. 20.2.6. Inserting vertical lines between columns
        7. 20.2.7. Adding a blank line every n records
        8. 20.2.8. Even-odd page printing
        9. 20.2.9. Using different formats in the same text box
        10. 20.2.10. Centering the title
        11. 20.2.11. Easily aligning control labels
        12. 20.2.12. Micro-adjusting controls
      3. 20.3. Adding Data
        1. 20.3.1. Adding more information to a report
        2. 20.3.2. Adding the user's name to a bound report
      4. 20.4. Trying More Techniques
        1. 20.4.1. Displaying all reports in a combo box
        2. 20.4.2. Fast printing from queried data
        3. 20.4.3. Hiding forms during Print Preview
        4. 20.4.4. Using snaking columns in a report
        5. 20.4.5. Exploiting two-pass report processing
        6. 20.4.6. Assigning unique names to controls
      5. 20.5. Summary
    6. 21. Building Multiuser Applications
      1. 21.1. Working on a Network
        1. 21.1.1. Network performance
        2. 21.1.2. File location
          1. 21.1.2.1. All files on the server
          2. 21.1.2.2. Distributed installation
          3. 21.1.2.3. All files on the client
        3. 21.1.3. Data sources
      2. 21.2. Considering the Options for Opening a Database
      3. 21.3. Splitting a Database for Network Access
        1. 21.3.1. Detailing the benefits of splitting a database
        2. 21.3.2. Knowing where to put which objects
        3. 21.3.3. Using the Database Splitter add-in
      4. 21.4. Finding the Key to Locking Issues
        1. 21.4.1. Access's built-in record-locking features
          1. 21.4.1.1. Default Open Mode
          2. 21.4.1.2. Default Record Locking
          3. 21.4.1.3. Refresh Interval
          4. 21.4.1.4. Number of Update Retries
          5. 21.4.1.5. ODBC Refresh Interval
          6. 21.4.1.6. Update Retry Interval
        2. 21.4.2. Record-lock error handling
          1. 21.4.2.1. Error 3260: Couldn't update; currently locked by user . . .
          2. 21.4.2.2. Error 3186: Couldn't save; currently locked by user x on machine y
          3. 21.4.2.3. Error 3188: Could not update; currently locked by another session on this machine
          4. 21.4.2.4. Error 3197: Data has changed; operation stopped
          5. 21.4.2.5. A function to handle lock errors
      5. 21.5. Reducing Multiuser Errors with Unbound Forms
        1. 21.5.1. Creating an unbound form
        2. 21.5.2. Making an unbound form work
          1. 21.5.2.1. Opening the form
          2. 21.5.2.2. Navigating through records
          3. 21.5.2.3. Using navigation buttons
          4. 21.5.2.4. Using a combo box
          5. 21.5.2.5. Editing data
      6. 21.6. Summary
    7. 22. Integrating Access with Other Applications
      1. 22.1. Using Automation in Access
        1. 22.1.1. Understanding how Automation works
        2. 22.1.2. Creating Automation references
        3. 22.1.3. Binding your VBA object variables to objects in the Automation interface
          1. 22.1.3.1. Early-binding an object
          2. 22.1.3.2. Late-binding an object
        4. 22.1.4. Creating an instance of an Automation object
          1. 22.1.4.1. Creating a new instance with New
          2. 22.1.4.2. Creating a new instance with CreateObject
        5. 22.1.5. Getting an existing object instance
        6. 22.1.6. Working with Automation objects
        7. 22.1.7. Closing an instance of an Automation object
      2. 22.2. Looking at an Automation Example Using Word
        1. 22.2.1. Creating an instance of a Word object
        2. 22.2.2. Making the instance of Word visible
        3. 22.2.3. Creating a new document based on an existing template
        4. 22.2.4. Inserting data
        5. 22.2.5. Activating the instance of Word
        6. 22.2.6. Moving the cursor in Word
        7. 22.2.7. Discarding the Word object instance
        8. 22.2.8. Inserting pictures by using bookmarks
        9. 22.2.9. Using Office's macro recorder
      3. 22.3. Collecting Data with Microsoft Outlook
        1. 22.3.1. Creating an e-mail
        2. 22.3.2. Managing replies
          1. 22.3.2.1. Choosing how to process replies
          2. 22.3.2.2. Automatically processing replies
          3. 22.3.2.3. Manually processing replies
      4. 22.4. Summary
    8. 23. Handling Errors and Exceptions
      1. 23.1. Dealing with Errors
        1. 23.1.1. Logical errors
        2. 23.1.2. Runtime errors
          1. 23.1.2.1. When fatal errors occur
          2. 23.1.2.2. When Access reports the error
          3. 23.1.2.3. When the application handles the errors
          4. 23.1.2.4. When unanticipated application errors occur
      2. 23.2. Identifying Which Errors Can Be Detected
        1. 23.2.1. What an error handler is
        2. 23.2.2. How to set a basic error trap
      3. 23.3. Trapping Errors with VBA
        1. 23.3.1. The Err object
        2. 23.3.2. VBA error-handling statements
          1. 23.3.2.1. On Error GoTo Label
          2. 23.3.2.2. On Error GoTo 0
          3. 23.3.2.3. On Error Resume Next
          4. 23.3.2.4. VBA Resume statements
            1. 23.3.2.4.1. Resume
            2. 23.3.2.4.2. Resume Next
            3. 23.3.2.4.3. Resume Label
        3. 23.3.3. The Error event
        4. 23.3.4. The ADO Errors collection
      4. 23.4. Summary
  10. IV. Professional Database Development
    1. 24. Optimizing Access Applications
      1. 24.1. Understanding Module Load on Demand
        1. 24.1.1. Organizing your modules
        2. 24.1.2. Pruning the call tree
      2. 24.2. Using the .accdb Database File Format
      3. 24.3. Distributing .accde Files
      4. 24.4. Understanding the Compiled State
        1. 24.4.1. Putting your application's code into a compiled state
        2. 24.4.2. Losing the compiled state
        3. 24.4.3. Distributing applications in a compiled or uncompiled state
          1. 24.4.3.1. Distributing source code for your application
          2. 24.4.3.2. Putting an application in an uncompiled state
          3. 24.4.3.3. Storing commonly used code in a library
          4. 24.4.3.4. Creating a library reference for distributed applications
      5. 24.5. Improving Absolute Speed
        1. 24.5.1. Tuning your system
        2. 24.5.2. Getting the most from your tables
          1. 24.5.2.1. Creating efficient indexes
          2. 24.5.2.2. Deciding which fields to index
          3. 24.5.2.3. Using multiple-field indexes
        3. 24.5.3. Getting the most from your queries
        4. 24.5.4. Getting the most from your forms and reports
          1. 24.5.4.1. Minimizing form and report complexity and size
          2. 24.5.4.2. Using bitmaps on forms and reports
          3. 24.5.4.3. Speeding up list boxes and combo boxes
        5. 24.5.5. Getting the most from your modules
          1. 24.5.5.1. Using appropriate data types
          2. 24.5.5.2. Writing faster routines
            1. 24.5.5.2.1. Using control variables
            2. 24.5.5.2.2. Using field variables
            3. 24.5.5.2.3. Increasing the speed of finding data in code
          3. 24.5.5.3. Eliminating dead code and unused variables
        6. 24.5.6. Increasing network performance
      6. 24.6. Improving Perceived Speed
        1. 24.6.1. Using a splash screen
        2. 24.6.2. Loading and keeping forms hidden
        3. 24.6.3. Using the hourglass
        4. 24.6.4. Using the built-in progress meter
        5. 24.6.5. Creating a progress meter as a pop-up form
        6. 24.6.6. Speeding up the progress meter display
      7. 24.7. Working with Large Access Databases
        1. 24.7.1. Understanding how databases grow in size
        2. 24.7.2. Recognizing that compiling and compacting may not be enough
          1. 24.7.2.1. Rebooting to get a clean memory map
          2. 24.7.2.2. Fixing a corrupt form by removing the record source
          3. 24.7.2.3. Creating a new database and importing all objects
        3. 24.7.3. Using the decompile option
        4. 24.7.4. Detecting an uncompiled database and automatically recompiling
        5. 24.7.5. Making small changes to large databases
      8. 24.8. Summary
    2. 25. Advanced Data Access with VBA
      1. 25.1. Adding an Unbound Combo Box to a Form to Find Data
        1. 25.1.1. Using the FindRecord method
        2. 25.1.2. Using a bookmark
      2. 25.2. Filtering a Form
        1. 25.2.1. With code
        2. 25.2.2. With a query
          1. 25.2.2.1. Creating a parameter query
          2. 25.2.2.2. Creating an interactive filter dialog box
          3. 25.2.2.3. Linking the dialog box to another form
      3. 25.3. Summary
    3. 26. Bulletproofing Access Applications
      1. 26.1. Introducing Bulletproofing
      2. 26.2. Looking at the Characteristics of Bulletproofed Applications
      3. 26.3. Identifying the Principles of Bulletproofing
        1. 26.3.1. Building to a specification
        2. 26.3.2. Becoming one with documentation
          1. 26.3.2.1. Documenting the code you write
          2. 26.3.2.2. Documenting the application
        3. 26.3.3. Considering your users
          1. 26.3.3.1. What they know
          2. 26.3.3.2. What they want
        4. 26.3.4. Getting the application to the users
          1. 26.3.4.1. Using professional installation tools
          2. 26.3.4.2. Providing the Access runtime module to users
        5. 26.3.5. Enabling the users to actually use the application
          1. 26.3.5.1. Making the application easy to start
          2. 26.3.5.2. Using start-up options
          3. 26.3.5.3. Using a login form
          4. 26.3.5.4. Making a splash with a splash screen
        6. 26.3.6. Controlling the flow of information
          1. 26.3.6.1. Adding switchboards to the application
          2. 26.3.6.2. Controlling the ribbons
          3. 26.3.6.3. Hiding the Navigation Pane and removing menus
          4. 26.3.6.4. Displaying one form at a time
          5. 26.3.6.5. Choosing the user-interface style
            1. 26.3.6.5.1. Tabbed Documents
            2. 26.3.6.5.2. Overlapping Windows
        7. 26.3.7. Keeping the user informed
          1. 26.3.7.1. Getting your message across with a message box
          2. 26.3.7.2. Creating and using a progress meter
        8. 26.3.8. Tracking down problems
          1. 26.3.8.1. Checking for obvious hardware errors
          2. 26.3.8.2. Maintaining usage logs
        9. 26.3.9. Securing the environment
          1. 26.3.9.1. Setting start-up options in code
          2. 26.3.9.2. Disabling start-up bypass
            1. 26.3.9.2.1. Setting property values
            2. 26.3.9.2.2. Getting property values
        10. 26.3.10. Protecting your database
          1. 26.3.10.1. Building bulletproof forms
          2. 26.3.10.2. Validating user input
          3. 26.3.10.3. Using the /runtime option
          4. 26.3.10.4. Encrypting or encoding a database
          5. 26.3.10.5. Removing a database password
          6. 26.3.10.6. Protecting Visual Basic code
          7. 26.3.10.7. Understanding macro security
            1. 26.3.10.7.1. Enabling sandbox mode
            2. 26.3.10.7.2. The Trust Center
        11. 26.3.11. Continuing to improve the product
      4. 26.4. Summary
    4. 27. Using the Windows Application Programming Interface
      1. 27.1. What the Windows API Is
      2. 27.2. Reasons to Use the Windows API
        1. 27.2.1. Common code base
        2. 27.2.2. Tested and proven code
        3. 27.2.3. Cross-platform compatibility
        4. 27.2.4. Smaller application footprint
        5. 27.2.5. Application consistency
      3. 27.3. DLL Documentation
        1. 27.3.1. Finding the documentation
        2. 27.3.2. Making sense of the documentation
        3. 27.3.3. Recognizing what you can't do with the API
      4. 27.4. How to Use the Windows API
        1. 27.4.1. The Declare statement
          1. 27.4.1.1. Function or Sub
          2. 27.4.1.2. Function name
          3. 27.4.1.3. Lib "LibraryName"
          4. 27.4.1.4. Alias "AliasName"
          5. 27.4.1.5. ArgumentList
            1. 27.4.1.5.1. ByVal or ByRef?
          6. 27.4.1.6. As DataType
        2. 27.4.2. Wrapper functions
      5. 27.5. API Examples
        1. 27.5.1. Retrieving system information
          1. 27.5.1.1. GetCommandLineA
          2. 27.5.1.2. GetWindowsDirectoryA
          3. 27.5.1.3. GetTempPathA
          4. 27.5.1.4. GetVersionExA
          5. 27.5.1.5. GetUserNameA
          6. 27.5.1.6. GetComputerNameA
          7. 27.5.1.7. GetDriveTypeA
          8. 27.5.1.8. GetVolumeInformationA
          9. 27.5.1.9. GetSystemDirectoryA
        2. 27.5.2. Going over general-purpose Windows API functions
          1. 27.5.2.1. GetParent
          2. 27.5.2.2. GetWindowTextA
          3. 27.5.2.3. GetClassNameA
          4. 27.5.2.4. SetWindowTextA
        3. 27.5.3. Manipulating application settings with the Windows API
          1. 27.5.3.1. GetPrivateProfileStringA
          2. 27.5.3.2. GetPrivateProfileIntA
          3. 27.5.3.3. GetProfileStringA
          4. 27.5.3.4. WritePrivateProfileStringA
          5. 27.5.3.5. WriteProfileStringA
      6. 27.6. Summary
    5. 28. Object-Oriented Programming with VBA
      1. 28.1. Introducing Object-Oriented Programming
        1. 28.1.1. Getting to know objects
          1. 28.1.1.1. Understanding what objects are
          2. 28.1.1.2. Using objects in applications
        2. 28.1.2. Defining objects with class modules
        3. 28.1.3. Looking at a simple class module
        4. 28.1.4. Adding a class module to a database
        5. 28.1.5. Creating simple product properties
        6. 28.1.6. Creating methods
        7. 28.1.7. Using the product object
        8. 28.1.8. Creating bulletproof property procedures
      2. 28.2. Recognizing the Benefits of Object-Oriented Programming
        1. 28.2.1. Encapsulating functionality
        2. 28.2.2. Simplifying programming tasks
        3. 28.2.3. Managing a class's interface
      3. 28.3. Using Property Procedures
        1. 28.3.1. Looking at the types of property procedures
          1. 28.3.1.1. Property Let
          2. 28.3.1.2. Property Set
          3. 28.3.1.3. Property Get
        2. 28.3.2. Exploring property-value persistence
        3. 28.3.3. Heeding property procedure rules
      4. 28.4. Modifying the Product Class
        1. 28.4.1. Retrieving product details
        2. 28.4.2. Looking at the new ProductID property
        3. 28.4.3. Adding a new property to provide extra information
        4. 28.4.4. Adding a new method to the product class
      5. 28.5. Learning about Class Events
        1. 28.5.1. The Class_Initialize event procedure
        2. 28.5.2. The Class_Terminate event procedure
      6. 28.6. Adding Events to Class Modules
        1. 28.6.1. Learning about events in Access
        2. 28.6.2. Recognizing the need for events
        3. 28.6.3. Creating custom events
        4. 28.6.4. Raising events
        5. 28.6.5. Trapping custom events
        6. 28.6.6. Passing data through events
        7. 28.6.7. Exploiting Access class module events
      7. 28.7. Summary
    6. 29. Customizing Access Ribbons
      1. 29.1. Why Replace Toolbars and Menus?
        1. 29.1.1. New controls for Access ribbons
        2. 29.1.2. SplitButton
        3. 29.1.3. DropDown
        4. 29.1.4. Gallery
        5. 29.1.5. SuperTips
      2. 29.2. Working with the Access Ribbon
        1. 29.2.1. Tabs
        2. 29.2.2. Groups
        3. 29.2.3. Controls
        4. 29.2.4. Managing the ribbon
      3. 29.3. Working with the Quick Access Toolbar
      4. 29.4. Editing the Default Access Ribbon
      5. 29.5. Developing Custom Access Ribbons
        1. 29.5.1. The ribbon creation process
        2. 29.5.2. Using VBA callbacks
      6. 29.6. The Ribbon Hierarchy
      7. 29.7. Getting Started with Access Ribbons
        1. 29.7.1. Step 1: Design the ribbon and build the XML
        2. 29.7.2. Step 2: Write the callback routines
        3. 29.7.3. Step 3: Create the USysRibbons table
        4. 29.7.4. Step 4: Add XML to USysRibbons
        5. 29.7.5. Step 5: Specify the custom ribbon property
      8. 29.8. The Basic Ribbon XML
      9. 29.9. Adding Ribbon Controls
        1. 29.9.1. Specifying imageMso
        2. 29.9.2. The Label control
        3. 29.9.3. The Button control
        4. 29.9.4. Separators
        5. 29.9.5. Check boxes
        6. 29.9.6. The DropDown control
        7. 29.9.7. The SplitButton Control
      10. 29.10. Using Visual Web Developer
      11. 29.11. Managing Ribbons
      12. 29.12. Completely Removing the Access Ribbon
      13. 29.13. Summary
    7. 30. Using Access Macros
      1. 30.1. An Introduction to Macros
        1. 30.1.1. Creating a macro
        2. 30.1.2. Assigning a macro to an event
      2. 30.2. Multi-Action Macros
      3. 30.3. Submacros
      4. 30.4. Conditions
        1. 30.4.1. Opening reports using conditions
        2. 30.4.2. Multiple actions in conditions
      5. 30.5. Temporary Variables
        1. 30.5.1. Enhancing a macro you've already created
        2. 30.5.2. Using temporary variables to simplify macros
        3. 30.5.3. Using temporary variables in VBA
      6. 30.6. Error Handling and Macro Debugging
        1. 30.6.1. The OnError action
        2. 30.6.2. The MacroError object
        3. 30.6.3. Debugging macros
      7. 30.7. Embedded Macros
      8. 30.8. Macros versus VBA Statements
        1. 30.8.1. Choosing between macros and VBA
        2. 30.8.2. Converting existing macros to VBA
      9. 30.9. Summary
    8. 31. Distributing Access Applications
      1. 31.1. Defining the Current Database Options
        1. 31.1.1. Application options
          1. 31.1.1.1. Application Title
          2. 31.1.1.2. Application Icon
          3. 31.1.1.3. Display Form
          4. 31.1.1.4. Display Status Bar
          5. 31.1.1.5. Document Window Options
          6. 31.1.1.6. Use Access Special Keys
          7. 31.1.1.7. Compact on Close
          8. 31.1.1.8. Remove Personal Information from File Properties on Save
          9. 31.1.1.9. Use Windows-Themed Controls on Forms
          10. 31.1.1.10. Enable Layout View
          11. 31.1.1.11. Enable Design Changes for Tables in Datasheet View
          12. 31.1.1.12. Check for Truncated Number Fields
          13. 31.1.1.13. Picture Property Storage Format
        2. 31.1.2. Navigation options
          1. 31.1.2.1. The Display Navigation Pane check box
          2. 31.1.2.2. The Navigation Options button
        3. 31.1.3. Ribbon and toolbar options
          1. 31.1.3.1. Ribbon Name
          2. 31.1.3.2. Shortcut Menu Bar
          3. 31.1.3.3. Allow Full Menus
          4. 31.1.3.4. Allow Default Shortcut Menus
        4. 31.1.4. Name AutoCorrect Options
      2. 31.2. Testing the Application before Distribution
      3. 31.3. Polishing Your Application
        1. 31.3.1. Giving your application a consistent look and feel
        2. 31.3.2. Adding common professional components
          1. 31.3.2.1. A splash screen
          2. 31.3.2.2. An application switchboard
          3. 31.3.2.3. An About box
      4. 31.4. Bulletproofing an Application
        1. 31.4.1. Using error trapping on all Visual Basic procedures
        2. 31.4.2. Separating tables from the rest of the application
        3. 31.4.3. Documenting the application
      5. 31.5. Summary
  11. V. Access and Windows SharePoint Services
    1. 32. Understanding Windows SharePoint Services
      1. 32.1. Introducing SharePoint
      2. 32.2. Reviewing Various Types of SharePoint Sites
      3. 32.3. Working with SharePoint Lists
      4. 32.4. Looking at a SharePoint Web Site
        1. 32.4.1. Editing SharePoint list items
        2. 32.4.2. Creating SharePoint lists
      5. 32.5. Summary
    2. 33. Integrating Access with SharePoint
      1. 33.1. Introducing SharePoint as a Data Source
      2. 33.2. Sharing Access Data with SharePoint
        1. 33.2.1. Linking to SharePoint lists
        2. 33.2.2. Exporting Access tables to SharePoint
        3. 33.2.3. Moving Access tables to SharePoint
      3. 33.3. Using SharePoint Templates
      4. 33.4. Summary
    3. 34. Understanding Access Services
      1. 34.1. Explaining Managed Applications
      2. 34.2. Looking at Web Publishing in Access
        1. 34.2.1. Why SharePoint?
        2. 34.2.2. Leveraging SharePoint features
        3. 34.2.3. Publishing Access applications to SharePoint
      3. 34.3. Understanding Access Services
      4. 34.4. Examining Access Web Application Limits
        1. 34.4.1. Not public-facing
        2. 34.4.2. Fewer than 20,000 rows of data
        3. 34.4.3. Modest transactional requirements
      5. 34.5. Summary
    4. 35. Deploying Access Applications to SharePoint
      1. 35.1. Looking at SharePoint Deployment Options
        1. 35.1.1. Enhanced table exporting option
        2. 35.1.2. Publishing to SharePoint option
          1. 35.1.2.1. Understanding publishing to SharePoint deployment
          2. 35.1.2.2. Revisiting Access Services
          3. 35.1.2.3. Using Compatibility Checker
          4. 35.1.2.4. Publishing to SharePoint
      2. 35.2. Dealing with Compatibility Checker Problems
        1. 35.2.1. General errors
        2. 35.2.2. Relationship and lookup errors
          1. 35.2.2.1. Incompatible fields
          2. 35.2.2.2. Table relationships
          3. 35.2.2.3. Primary key constraints
          4. 35.2.2.4. Lookup field issues
        3. 35.2.3. Form and report errors
          1. 35.2.3.1. Unsupported form controls
          2. 35.2.3.2. Problems with form events
          3. 35.2.3.3. Control issues
          4. 35.2.3.4. Unsupported report controls
          5. 35.2.3.5. Reports do not support events
          6. 35.2.3.6. Reports do not support subreports
          7. 35.2.3.7. Object name violations
        4. 35.2.4. Query errors
          1. 35.2.4.1. Problems with SQL statements
          2. 35.2.4.2. Query join issues
          3. 35.2.4.3. Invalid expression errors
          4. 35.2.4.4. Invalid query name
          5. 35.2.4.5. Queries cannot include subqueries
        5. 35.2.5. Macro errors
          1. 35.2.5.1. Reserved word collisions
          2. 35.2.5.2. Incompatible macro actions
        6. 35.2.6. Schema errors
          1. 35.2.6.1. Lookup field issues
          2. 35.2.6.2. Field data type issues
          3. 35.2.6.3. Primary key issues
          4. 35.2.6.4. Attachment field limitations
          5. 35.2.6.5. Calculated field issues
          6. 35.2.6.6. Table field issues
          7. 35.2.6.7. Table name issues
          8. 35.2.6.8. Custom field formats are not compatible
          9. 35.2.6.9. Unique property must be false
          10. 35.2.6.10. AutoNumbers are reserved for primary keys
      3. 35.3. Summary
  12. VI. Access as an Enterprise Platform
    1. 36. Client/Server Concepts
      1. 36.1. Looking at the Parts of Client/Server Architecture
        1. 36.1.1. Applications
        2. 36.1.2. The back office
          1. 36.1.2.1. The database
          2. 36.1.2.2. Access's role in client/server computing
          3. 36.1.2.3. Web servers and application servers
      2. 36.2. Making Sense of Multi-Tier Architecture
        1. 36.2.1. Two-tier systems
        2. 36.2.2. Three-tier systems
      3. 36.3. Putting It All Together: Access, Client-Server, and Multiple Tiers
        1. 36.3.1. Access as a database repository
        2. 36.3.2. Access as an Internet database
      4. 36.4. Summary
    2. 37. SQL Server as an Access Companion
      1. 37.1. Connecting to SQL Server
        1. 37.1.1. Introducing connection strings
        2. 37.1.2. Connecting to SQL Server from Access
      2. 37.2. Working with SQL Server Objects
        1. 37.2.1. Using SQL Server tables from Access
        2. 37.2.2. Views
        3. 37.2.3. Stored procedures
        4. 37.2.4. Triggers
      3. 37.3. Summary
    3. 38. Upsizing Access Databases to SQL Server
      1. 38.1. Introducing SQL Server Express
      2. 38.2. Upsizing Access and the Upsizing Wizard
        1. 38.2.1. Before upsizing an application
        2. 38.2.2. Running the Upsizing Wizard
        3. 38.2.3. Working with an Access ADP file
        4. 38.2.4. Comparing Access to SQL Server data types
      3. 38.3. Summary
  13. VII. Appendixes
    1. A. Access 2010 Specifications
      1. A.1. Microsoft Access Database Specifications
      2. A.2. Microsoft SQL Server Express Specifications
    2. B. What's New in Access 2010
      1. B.1. The User Interface
      2. B.2. Publish Access to the Web
      3. B.3. Tables
      4. B.4. Datasheet View
      5. B.5. Forms
      6. B.6. Reports
      7. B.7. Macros
      8. B.8. Security
      9. B.9. SharePoint
      10. B.10. Summary
    3. C. What's on the CD-ROM
      1. C.1. System Requirements
      2. C.2. Using the CD
      3. C.3. What's on the CD
        1. C.3.1. Example files
        2. C.3.2. eBook version of Access 2010 Bible
      4. C.4. Troubleshooting

Product information

  • Title: Access® 2010 Bible
  • Author(s):
  • Release date: June 2010
  • Publisher(s): Wiley
  • ISBN: 9780470475348