Access™ 2007 Bible

Access™ 2007 Bible

Released January 2007
Publisher(s): Wiley
ISBN: 9780470046739

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

I recommend this book for anyone who wants a strong foundation in Access.

—Jeff Lenamon, CIBC World Markets

Updated edition with exciting new Access 2007 features!

Harness the power of Access 2007 with the expert guidance in this comprehensive reference. Beginners will appreciate the thorough attention to database fundamentals and terminology. Experienced users can jump right into Access 2007 enhancements like the all-new user interface and wider use of XML and Web services. Each of the book's six parts thoroughly focuses on key elements in a logical sequence, so you have what you need, when you need it. Designed as both a reference and a tutorial, Access 2007 Bible is a powerful tool for developers needing to make the most of the new features in Access 2007.

  • Build Access tables using good relational database techniques

  • Construct efficient databases using a five-step design method

  • Design efficient data-entry and data display forms

  • Utilize the improved Access report designer

  • Use Visual Basic(r) for Applications and the VBA Editor to automate applications

  • Build and customize Access 2007 ribbons

  • Seamlessly exchange Access data with SharePoint(r)

  • Employ advanced techniques such as the Windows(r) API and object-oriented programming

  • Add security and use data replication in your Access applications

What's on the CD-ROM?

Follow the examples in the book chapter by chapter using the bonus materials on the CD-ROM. You'll find separate Microsoft Access database files for each chapter and other working files, including

  • All the examples and databases used in the book, including database files, images, data files in various formats, and icon files used in the book's examples

  • A complete sample application file, including queries, reports, objects, and modules, that you can use as a reference

See the CD-ROM appendix for details and complete system requirements.

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

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. 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
    2. Conventions Used in This Book
    3. Icons and Alerts
    4. 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 as an Enterprise Platform
      6. Part VI: Appendixes
    5. Guide to the Examples
      1. The Main Menu Switchboard
      2. Understanding the Data Tables
      3. Understanding the Products Form
      4. Understanding the Product Form Subform
    6. Understanding the Contacts Form
      1. Using the Sales Form
  6. 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
        1. 1.2.1. Working with multiple tables
        2. 1.2.2. Knowing why you should create multiple tables
      3. 1.3. Access Database Objects and Views
        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. Designing the system's 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: What fields are required?
          1. 1.4.3.1. Determining contact information
          2. 1.4.3.2. Determining sales information
          3. 1.4.3.3. Determining line item information
        4. 1.4.4. Step 4: Table design
        5. 1.4.5. Step 5: Form design: Input
      5. 1.5. Summary
    2. 2. Creating Access Tables
      1. 2.1. Getting Started with Access 2007
        1. 2.1.1. The Templates section
        2. 2.1.2. The File menu
      2. 2.2. Creating a Database
      3. 2.3. The Access 2007 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. The importance of naming conventions
        2. 2.4.2. The table design process
          1. 2.4.2.1. Adding a new table to the database
        3. 2.4.3. Using the Design ribbon tab
          1. 2.4.3.1. Primary Key
          2. 2.4.3.2. Insert Rows
          3. 2.4.3.3. Delete Rows
          4. 2.4.3.4. Property Sheet
          5. 2.4.3.5. Indexes
        4. 2.4.4. Working with fields
          1. 2.4.4.1. Naming a field
          2. 2.4.4.2. Specifying a data type
          3. 2.4.4.3. Designing data-entry rules
          4. 2.4.4.4. Designing lookup tables
          5. 2.4.4.5. Assigning field data types
            1. 2.4.4.5.1. Text data type
            2. 2.4.4.5.2. Memo data type
            3. 2.4.4.5.3. Number data type
            4. 2.4.4.5.4. Date/Time data type
            5. 2.4.4.5.5. Currency
            6. 2.4.4.5.6. AutoNumber
            7. 2.4.4.5.7. Yes/No
            8. 2.4.4.5.8. OLE Object
            9. 2.4.4.5.9. Attachment
            10. 2.4.4.5.10. Hyperlink data type
            11. 2.4.4.5.11. Lookup Wizard
          6. 2.4.4.6. Entering a field description
      5. 2.5. Creating tblContacts
        1. 2.5.1. AutoNumber fields and Access
        2. 2.5.2. Completing tblContacts
      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. 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. ValidationRule
          9. 2.6.7.9. Validation Text
          10. 2.6.7.10. Required
          11. 2.6.7.11. AllowZeroLength
          12. 2.6.7.12. Indexed
            1. 2.6.7.12.1. AutoIndex Option
            2. 2.6.7.12.2. When to Index
      7. 2.7. Understanding tblContacts Field Properties
        1. 2.7.1. Understanding the Lookup Property window
      8. 2.8. Setting the Primary Key
        1. 2.8.1. Understanding unique values
        2. 2.8.2. Choosing a primary key
        3. 2.8.3. Creating the primary key
        4. 2.8.4. 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
        4. 2.9.4. Indexing tblContacts
      10. 2.10. Printing a Table Design
      11. 2.11. Saving the Completed Table
      12. 2.12. Manipulating Tables in a Database Window
        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 the Attachment Data Type
      15. 2.15. Summary
    3. 3. Designing Bulletproof Databases
      1. 3.1. Data Normalization
        1. 3.1.1. First normal form
        2. 3.1.2. Second normal form
          1. 3.1.2.1. Further optimization: Adding tables to the scheme
          2. 3.1.2.2. Breaking the rules
        3. 3.1.3. Third normal form
        4. 3.1.4. More on anomalies
        5. 3.1.5. Denormalization
      2. 3.2. Table Relationships
        1. 3.2.1. Connecting the data
        2. 3.2.2. One-to-one
        3. 3.2.3. One-to-many
        4. 3.2.4. Many-to-many
        5. 3.2.5. Pass-through
      3. 3.3. Integrity Rules
      4. 3.4. Understanding Keys
        1. 3.4.1. Deciding on a primary key
        2. 3.4.2. Recognizing the benefits of a primary key
        3. 3.4.3. Designating a primary key
          1. 3.4.3.1. Single field versus composite primary keys
          2. 3.4.3.2. Natural versus surrogate primary keys
          3. 3.4.3.3. Creating primary keys
        4. 3.4.4. Creating relationships and enforcing referential integrity
          1. 3.4.4.1. Specifying the Join Type between tables
          2. 3.4.4.2. Enforcing referential integrity
            1. 3.4.4.2.1. Choosing the Cascade Update Related Fields option
            2. 3.4.4.2.2. Choosing the Cascade Delete Related Records option
        5. 3.4.5. Viewing all relationships
        6. 3.4.6. Deleting relationships
        7. 3.4.7. Application-specific integrity rules
      5. 3.5. Summary
    4. 4. Selecting Data with Queries
      1. 4.1. Understanding Queries
        1. 4.1.1. What is a query?
        2. 4.1.2. Types of queries
        3. 4.1.3. Query capabilities
        4. 4.1.4. How recordsets work
      2. 4.2. Creating a Query
        1. 4.2.1. Using the Query window
        2. 4.2.2. Navigating the Query Design window
        3. 4.2.3. Using the Query Design ribbon
        4. 4.2.4. Using the QBE grid of the Query Design window
      3. 4.3. Selecting 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. Adding More Than One Table to a Query
      11. 4.11. Working with the Table/Query Pane
        1. 4.11.1. The join line
        2. 4.11.2. Manipulating the Field List window
        3. 4.11.3. Moving a table
        4. 4.11.4. Removing a table
        5. 4.11.5. Adding more tables
      12. 4.12. Adding Fields from More Than One Table
        1. 4.12.1. Viewing the table names
        2. 4.12.2. Adding multiple fields
      13. 4.13. Understanding Multi-Table Query Limitations
        1. 4.13.1. Updating limitations
        2. 4.13.2. Overcoming query limitations
          1. 4.13.2.1. Updating a unique index (primary key)
          2. 4.13.2.2. Replacing existing data in a query with a one-to-many relationship
          3. 4.13.2.3. Design tips for updating fields in queries
      14. 4.14. Creating and Working with Query Joins
        1. 4.14.1. Joining tables
        2. 4.14.2. Specify the type of join
        3. 4.14.3. Deleting joins
      15. 4.15. Understanding Table Join Types
        1. 4.15.1. Inner joins (equi-joins)
        2. 4.15.2. Changing join properties
        3. 4.15.3. Inner and outer joins
          1. 4.15.3.1. Inner joins
          2. 4.15.3.2. Creating a right outer join
          3. 4.15.3.3. Creating a left outer join
        4. 4.15.4. Creating a Cartesian product
      16. 4.16. Summary
    5. 5. Using Operators and Expressions in Access
      1. 5.1. What Are Operators?
        1. 5.1.1. Types of operators
          1. 5.1.1.1. Mathematical operators
            1. 5.1.1.1.1. The * (multiplication) operator
            2. 5.1.1.1.2. The + (addition) operator
            3. 5.1.1.1.3. The − (subtraction) 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 Mod (Modulo) operator
          2. 5.1.1.2. Relational 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 (similar to) 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 (reserved word) 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. Moving beyond Simple Queries
        1. 5.2.1. Using query comparison operators
        2. 5.2.2. Understanding complex criteria selection
        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 (Number, Currency, or Counter) criteria
        5. 5.3.5. Entering Yes/No (logic) criteria
        6. 5.3.6. Entering a criterion for an OLE object
      4. 5.4. Entering Multiple Criteria in One Field
        1. 5.4.1. Understanding an Or operation
        2. 5.4.2. Specifying multiple values for a field using 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. Understanding an And query
        6. 5.4.6. Specifying a range using the And operator
        7. 5.4.7. Using the Between...And operator
        8. 5.4.8. 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 And criteria across fields of a query
        3. 5.5.3. Specifying Or criteria across fields of a query
        4. 5.5.4. Using And and Or together in different fields
        5. 5.5.5. A complex query on different lines
      6. 5.6. Creating a New Calculated Field in a Query
      7. 5.7. Summary
    6. 6. Working with Datasheet View
      1. 6.1. Understanding Datasheets
      2. 6.2. The Datasheet Window
        1. 6.2.1. Moving within a datasheet
        2. 6.2.2. The Navigation buttons
        3. 6.2.3. The Datasheet ribbon
      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. Understanding 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. Replacing an existing value manually
        2. 6.6.2. Changing an existing value
        3. 6.6.3. Fields that you can't edit
      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. Using the QuickSort feature
        2. 6.13.2. Using Filter by Selection
        3. 6.13.3. Using Filter by Form
      14. 6.14. Printing Records
        1. 6.14.1. Printing the datasheet
        2. 6.14.2. Using the Print Preview window
      15. 6.15. Summary
    7. 7. Creating Basic Access Forms
      1. 7.1. Adding Forms Using the Ribbon
        1. 7.1.1. Creating a new form
        2. 7.1.2. Creating a split form
        3. 7.1.3. Creating a multiple-items form
        4. 7.1.4. Creating a form using the Form Wizard
        5. 7.1.5. Creating a datasheet form
        6. 7.1.6. Creating a blank form
      2. 7.2. Adding Controls
        1. 7.2.1. Resizing the form area
        2. 7.2.2. Saving the form
        3. 7.2.3. Understanding controls
        4. 7.2.4. The different control types
          1. 7.2.4.1. Understanding bound, unbound, and calculated controls
        5. 7.2.5. The two ways to add a control
          1. 7.2.5.1. Using the Controls group to add a control
          2. 7.2.5.2. Using the Field List to add a control
          3. 7.2.5.3. Which method to use
      3. 7.3. Selecting Controls
        1. 7.3.1. Selecting a single control
        2. 7.3.2. Selecting multiple controls
        3. 7.3.3. Deselecting controls
      4. 7.4. Manipulating Controls
        1. 7.4.1. Resizing a control
        2. 7.4.2. Sizing controls automatically
        3. 7.4.3. Moving a control
        4. 7.4.4. Aligning controls
        5. 7.4.5. Modifying the appearance of a control
        6. 7.4.6. Modifying the appearance of multiple controls
        7. 7.4.7. Grouping controls
        8. 7.4.8. Deleting a control
        9. 7.4.9. Attaching a label to a control
        10. 7.4.10. Copying a control
        11. 7.4.11. Changing the control type
      5. 7.5. Understanding properties
        1. 7.5.1. Displaying the Property Sheet
        2. 7.5.2. Understanding the Property Sheet
        3. 7.5.3. Changing a control's property setting
          1. 7.5.3.1. Naming control labels and their captions
      6. 7.6. Summary
    8. 8. Working with Data on Access Forms
      1. 8.1. Using Form View
        1. 8.1.1. The Form ribbon
        2. 8.1.2. Navigating between fields
        3. 8.1.3. Moving between records in a form
      2. 8.2. Changing Values in a Form
        1. 8.2.1. Controls that you can't edit
        2. 8.2.2. Working with pictures and OLE objects
        3. 8.2.3. Memo field data entry
        4. 8.2.4. Date field data entry
        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
        1. 8.3.1. Using the Print Preview window
      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. Eliminating the Record Selector bar
        5. 8.4.5. 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
          1. 8.6.4.1. 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. Understanding Reports
        1. 9.1.1. Understanding report types
          1. 9.1.1.1. Tabular reports
          2. 9.1.1.2. Columnar reports
          3. 9.1.1.3. Mailing labels
        2. 9.1.2. Distinguishing between reports and forms
        3. 9.1.3. Understanding the process of creating a report
          1. 9.1.3.1. Defining the report layout
          2. 9.1.3.2. Assembling the data
      2. 9.2. Creating a Report with Report Wizards
        1. 9.2.1. Creating a new report
        2. 9.2.2. Selecting the grouping levels
        3. 9.2.3. Defining the group data
        4. 9.2.4. Selecting the sort order
        5. 9.2.5. Selecting summary options
        6. 9.2.6. Selecting the layout
        7. 9.2.7. Choosing the style
        8. 9.2.8. Opening the report design
        9. 9.2.9. Using the Print Preview window
        10. 9.2.10. Viewing the Report Design window
      3. 9.3. Printing a Report
      4. 9.4. Saving the Report
      5. 9.5. Starting with a Blank Form
        1. 9.5.1. Layout view
        2. 9.5.2. Report Design view
      6. 9.6. Banded Report Writer Concepts
        1. 9.6.1. The Report Designer sections
          1. 9.6.1.1. The Report Header section
          2. 9.6.1.2. The Page Header section
          3. 9.6.1.3. The Group Header section
          4. 9.6.1.4. The Detail section
          5. 9.6.1.5. The Group Footer section
          6. 9.6.1.6. The Page Footer section
          7. 9.6.1.7. The Report Footer section
      7. 9.7. Creating a Report from Scratch
        1. 9.7.1. Creating a new report and binding it to a query
        2. 9.7.2. Defining the report page size and layout
        3. 9.7.3. Placing controls on the report
        4. 9.7.4. Resizing a section
          1. 9.7.4.1. Modifying the appearance of text in a control
        5. 9.7.5. Working with text boxes and their attached label controls
          1. 9.7.5.1. Adding and using text box controls
          2. 9.7.5.2. Entering an expression in a text control
          3. 9.7.5.3. Sizing a text box control or label control
          4. 9.7.5.4. Deleting and cutting attached labels from text controls
          5. 9.7.5.5. Pasting labels into a report section
          6. 9.7.5.6. Moving label and text controls
          7. 9.7.5.7. Modifying the appearance of multiple controls
        6. 9.7.6. Changing label and text box control properties
        7. 9.7.7. Growing and shrinking text box controls
        8. 9.7.8. Sorting and grouping data
          1. 9.7.8.1. Creating a group header or footer
        9. 9.7.9. Sorting data within groups
          1. 9.7.9.1. Removing a group header or footer
          2. 9.7.9.2. Hiding a section
          3. 9.7.9.3. Sizing a section
          4. 9.7.9.4. Moving controls between sections
        10. 9.7.10. Adding page breaks
      8. 9.8. Making the Report Presentation Quality
        1. 9.8.1. Adjusting the Page Header
        2. 9.8.2. Creating an expression in the group header
        3. 9.8.3. Changing the picture properties and the Detail section
        4. 9.8.4. Creating a standard page footer
        5. 9.8.5. Saving your report
      9. 9.9. Summary
  7. II. Programming Microsoft Access
    1. 10. VBA Programming Fundamentals
      1. 10.1. Understanding the Limitations of Macros
      2. 10.2. Introducing Visual Basic for Applications
      3. 10.3. Understanding VBA Terminology
      4. 10.4. Migrating from Macros to VBA
        1. 10.4.1. When to use macros and when to use VBA
        2. 10.4.2. Converting existing macros to VBA
        3. 10.4.3. Using the Command Button Wizard to create VBA code
      5. 10.5. Creating VBA Programs
        1. 10.5.1. Understanding events and event procedures
          1. 10.5.1.1. Subprocedures
          2. 10.5.1.2. Functions
        2. 10.5.2. Understanding modules
          1. 10.5.2.1. Form and report modules
          2. 10.5.2.2. Standard modules
        3. 10.5.3. Creating a new module
          1. 10.5.3.1. Working in the Code window
          2. 10.5.3.2. The Declarations section
          3. 10.5.3.3. Creating a new procedure
          4. 10.5.3.4. Using IntelliSense
          5. 10.5.3.5. Compiling procedures
          6. 10.5.3.6. Saving a module
          7. 10.5.3.7. Creating procedures in the Form or Report Design window
          8. 10.5.3.8. Editing an existing procedure
          9. 10.5.3.9. Checking your results in the Immediate window
      6. 10.6. Understanding VBA Branching Constructs
        1. 10.6.1. Conditional execution
          1. 10.6.1.1. The If...Then...Else...End If construct
          2. 10.6.1.2. The Select Case...End Select statement
      7. 10.7. Repetitive Looping
        1. 10.7.1. The Do...Loop statement
        2. 10.7.2. The For...Next statement
      8. 10.8. Working with Objects and Collections
        1. 10.8.1. The With statement
        2. 10.8.2. The For Each statement
      9. 10.9. Using Compiler Directives
      10. 10.10. Access 2007 Options for Developers
        1. 10.10.1. Editor tab in Options dialog box
        2. 10.10.2. The Project Properties dialog box
          1. 10.10.2.1. Project name
          2. 10.10.2.2. Project description
          3. 10.10.2.3. Conditional compilation arguments
        3. 10.10.3. Command-line arguments
      11. 10.11. Summary
    2. 11. Mastering VBA Data Types and Procedures
      1. 11.1. 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 procedures and functions
        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
      6. 11.6. Creating a Function to Calculate Taxes
        1. 11.6.1. Named arguments
      7. 11.7. Summary
    3. 12. Understanding the Access Event Model
      1. 12.1. Programming Events
        1. 12.1.1. How do events trigger VBA code?
        2. 12.1.2. Where to trigger event procedures
      2. 12.2. Common Events
      3. 12.3. Form Event Procedures
        1. 12.3.1. Primary form events
        2. 12.3.2. Form mouse and keyboard events
        3. 12.3.3. Form data events
        4. 12.3.4. Form PivotTable events
        5. 12.3.5. Form Access Data Project events
      4. 12.4. Control Event Procedures
      5. 12.5. Event Order
        1. 12.5.1. Opening a form with an event procedure
        2. 12.5.2. Running an event procedure when closing a form
        3. 12.5.3. Using an event procedure to confirm record deletion
        4. 12.5.4. Using the MsgBox() function
      6. 12.6. Report Event Procedures
        1. 12.6.1. Running an event procedure as a report opens
      7. 12.7. Report Section Event Procedures
        1. 12.7.1. Using the Format event
      8. 12.8. Summary
    4. 13. Using Expressions in Queries and VBA Code
      1. 13.1. Understanding Expressions
        1. 13.1.1. The parts of an expression
        2. 13.1.2. Creating an expression
          1. 13.1.2.1. Entering object names
          2. 13.1.2.2. Entering text
          3. 13.1.2.3. Entering date/time values
          4. 13.1.2.4. Expression Builder
        3. 13.1.3. Special identifier operators and expressions
          1. 13.1.3.1. The ! (exclamation) operator
          2. 13.1.3.2. The dot (.) identifier operator
        4. 13.1.4. Special keywords and properties
      2. 13.2. Understanding Functions
        1. 13.2.1. Using functions in Access
        2. 13.2.2. Types of functions
          1. 13.2.2.1. Immediate window
          2. 13.2.2.2. Conversion functions
          3. 13.2.2.3. Date/Time functions
          4. 13.2.2.4. Financial (SQL) functions
          5. 13.2.2.5. Financial (monetary) functions
          6. 13.2.2.6. Mathematical functions
          7. 13.2.2.7. String manipulation functions
          8. 13.2.2.8. Programming functions
          9. 13.2.2.9. Domain functions
        3. 13.2.3. Using the DLookUp() function for lookup tables
      3. 13.3. Summary
    5. 14. Accessing Data with VBA Code
      1. 14.1. Understanding SQL
        1. 14.1.1. Viewing SQL statements in queries
        2. 14.1.2. An SQL primer
          1. 14.1.2.1. The SELECT keyword
          2. 14.1.2.2. Specifying SELECT predicates
          3. 14.1.2.3. The FROM clause of an SQL statement
          4. 14.1.2.4. The WHERE clause of an SQL statement
          5. 14.1.2.5. The ORDER BY clause
          6. 14.1.2.6. Specifying the end of an SQL statement
      2. 14.2. Creating Programs to Update a Table
        1. 14.2.1. Updating fields in a record using ADO
        2. 14.2.2. Updating a calculated field for a record
          1. 14.2.2.1. Recalculating a field when updating or adding a record
          2. 14.2.2.2. Checking the status of a record deletion
        3. 14.2.3. Adding a new record
        4. 14.2.4. Deleting a record
        5. 14.2.5. Deleting related records in multiple tables
      3. 14.3. Summary
    6. 15. Using the VBA Debugging Tools
      1. 15.1. Testing and Debugging Your Applications
      2. 15.2. Understanding the Sources of Errors
        1. 15.2.1. Syntactical errors
        2. 15.2.2. Logical errors
        3. 15.2.3. Runtime errors
        4. 15.2.4. Avoiding errors
      3. 15.3. Using the Module Options
        1. 15.3.1. Auto Syntax Check
        2. 15.3.2. Break on All Errors
        3. 15.3.3. Require Variable Declaration
        4. 15.3.4. Compile on Demand
        5. 15.3.5. Auto List Members
        6. 15.3.6. Auto Quick Info
        7. 15.3.7. Auto Data Tips
      4. 15.4. Compiling VBA Code
      5. 15.5. Traditional Debugging Techniques
        1. 15.5.1. Using MsgBox
        2. 15.5.2. Using compiler directives
        3. 15.5.3. Using Debug.Print
      6. 15.6. Using the Access Debugging Tools
        1. 15.6.1. Getting to know the Immediate window
        2. 15.6.2. Running code with the Immediate window
        3. 15.6.3. Suspending execution with breakpoints
        4. 15.6.4. Stepping through statements
        5. 15.6.5. Using the Locals window
        6. 15.6.6. Setting watches
        7. 15.6.7. Using conditional watches
        8. 15.6.8. Using the Call Stack window
      7. 15.7. Summary
  8. III. More Advanced Access Techniques
    1. 16. Working with External Data
      1. 16.1. Access and External Data
        1. 16.1.1. Types of external data
        2. 16.1.2. Methods 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. Data in unsupported programs
          4. 16.1.2.4. Automating import operations
      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 dBASE databases (tables)
        6. 16.2.6. Linking to Paradox tables
        7. 16.2.7. Linking to non-database data
          1. 16.2.7.1. Linking to Excel
          2. 16.2.7.2. Linking to HTML files
          3. 16.2.7.3. Linking to text files
        8. 16.2.8. Splitting an Access database
      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. Types of Imports and Exports
      2. 17.2. Importing External Data
        1. 17.2.1. Importing from another Access database
        2. 17.2.2. Importing spreadsheet data
        3. 17.2.3. Importing a SharePoint list
        4. 17.2.4. Importing text file data
          1. 17.2.4.1. Delimited text files
          2. 17.2.4.2. Fixed-width text files
          3. 17.2.4.3. Using the Import Specification window
        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 through ODBC drivers
        10. 17.2.10. Importing non-Access, PC-based database tables
          1. 17.2.10.1. Modifying imported table elements
        11. 17.2.11. Troubleshooting import errors
          1. 17.2.11.1. Import errors for new tables
          2. 17.2.11.2. Import errors for existing tables
          3. 17.2.11.3. The Import Errors table
      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. Finding the Number of Records in a Table or Query
      3. 18.3. Finding the Top (n) Records in a Query
      4. 18.4. How Queries Save Field Selections
        1. 18.4.1. Hiding (not showing) fields
        2. 18.4.2. Renaming fields in queries
        3. 18.4.3. Hiding and unhiding columns in Design View
      5. 18.5. Setting Query Properties
      6. 18.6. Creating Queries That Calculate Totals
        1. 18.6.1. Query Wizard summaries
        2. 18.6.2. Aggregate queries in Design View
          1. 18.6.2.1. Group By category
          2. 18.6.2.2. Expression category
          3. 18.6.2.3. Where category
          4. 18.6.2.4. Aggregate category
        3. 18.6.3. Grand totals in aggregates
        4. 18.6.4. Subtotals in aggregates
        5. 18.6.5. Filtering aggregates with criteria
      7. 18.7. Crosstab Queries
      8. 18.8. Duplicate and Unmatched Queries
        1. 18.8.1. Find Duplicates Query Wizard
        2. 18.8.2. Find Unmatched Query Wizard
      9. 18.9. SQL-Specific Queries
        1. 18.9.1. Creating union queries
        2. 18.9.2. Creating pass-through queries
        3. 18.9.3. Creating data definition queries
        4. 18.9.4. Creating SQL subqueries in an Access query
      10. 18.10. Action Queries
        1. 18.10.1. Types of action queries
        2. 18.10.2. Viewing the results of an action query
          1. 18.10.2.1. Viewing a query before using update and delete queries
          2. 18.10.2.2. Switching to the result table of a make-table or append query
        3. 18.10.3. Action queries cannot be reversed
        4. 18.10.4. Creating Action Queries
          1. 18.10.4.1. Creating an update action query to change values
          2. 18.10.4.2. Creating a new table using a make-table query
          3. 18.10.4.3. Creating queries to append records
          4. 18.10.4.4. Creating a query to delete records
          5. 18.10.4.5. Saving an action query
          6. 18.10.4.6. Running an action query
        5. 18.10.5. Troubleshooting action queries
          1. 18.10.5.1. Data-type errors in appending and updating
          2. 18.10.5.2. Key violations in action queries
          3. 18.10.5.3. Record-locked fields in multiuser environments
          4. 18.10.5.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. Form Design Techniques
        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. Combo box techniques
        9. 19.3.9. Determining whether a form is open
      4. 19.4. Advanced Forms Techniques
        1. 19.4.1. Page Number and Date/Time controls
        2. 19.4.2. Image control
        3. 19.4.3. Control "morphing"
        4. 19.4.4. Format Painter
        5. 19.4.5. Offering more end-user help
        6. 19.4.6. Adding background pictures
        7. 19.4.7. Form events
          1. 19.4.7.1. ApplyFilter
          2. 19.4.7.2. 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. Hide Repeating Information
      2. 20.2. Alphabetically Group Data
      3. 20.3. Group on Date Intervals
      4. 20.4. Create Numbered Lists
      5. 20.5. Add Bullet Characters
      6. 20.6. Add Emphasis at Runtime
      7. 20.7. Hide a Page Header
      8. 20.8. Avoid Empty Reports
      9. 20.9. Start a New Page Number for Each Group
      10. 20.10. Avoid Null Values in a Tabular Report
      11. 20.11. Add More Information to Report
      12. 20.12. Add the User's Name to a Bound Report
      13. 20.13. Add Vertical Lines between Columns
      14. 20.14. Add a Blank Line Every n Records
      15. 20.15. Even-Odd Page Printing
      16. 20.16. Display All Reports in a Combo Box
      17. 20.17. Use Different Formats in the Same Text Box
      18. 20.18. Fast Printing from Queried Data
      19. 20.19. Hide Forms during Print Preview
      20. 20.20. A Few Quick Report Tips
        1. 20.20.1. Center the title
        2. 20.20.2. Easily align control labels
        3. 20.20.3. Micro-adjust controls
        4. 20.20.4. Always assign unique names to controls
      21. 20.21. Use Snaking Columns in a Report
      22. 20.22. Exploiting Two-Pass Report Processing
      23. 20.23. Summary
    6. 21. Building Multiuser Applications
      1. 21.1. Network Issues
        1. 21.1.1. Network performance
        2. 21.1.2. File location
        3. 21.1.3. Data sources
        4. 21.1.4. Special network situations
      2. 21.2. Database Open Options
      3. 21.3. Splitting Databases for Network Access
        1. 21.3.1. Where to put which objects
        2. 21.3.2. Using the Database Splitter add-in
      4. 21.4. Locking Issues
        1. 21.4.1. Access's built-in record-locking features
        2. 21.4.2. Default Open Mode
        3. 21.4.3. Number of Update Retries
        4. 21.4.4. Update Retry Interval
        5. 21.4.5. Refresh Interval
        6. 21.4.6. ODBC Refresh Interval
      5. 21.5. Record-Lock Error Handling
        1. 21.5.1. A function to handle lock errors
        2. 21.5.2. Error 3260: Couldn't update; currently locked by user . . .
        3. 21.5.3. Error 3186: Couldn't save; currently locked by user x on machine y
        4. 21.5.4. What about Error 3188?
        5. 21.5.5. Error: 3197: Data has changed; operation stopped
      6. 21.6. Using Unbound Forms in Multiuser Environments
        1. 21.6.1. Creating an unbound form
        2. 21.6.2. Making it work
        3. 21.6.3. The Tag property
        4. 21.6.4. The form Open event
        5. 21.6.5. Moving through records
        6. 21.6.6. Editing data
      7. 21.7. Summary
    7. 22. Integrating Access with Other Applications
      1. 22.1. Using Automation to Integrate with Office
        1. 22.1.1. Creating Automation references
          1. 22.1.1.1. Early binding an object
          2. 22.1.1.2. Late binding an object
        2. 22.1.2. Creating an instance of an Automation object
          1. 22.1.2.1. Using the New keyword to create a new instance
          2. 22.1.2.2. Using the CreateObject function to create a new instance
        3. 22.1.3. Getting an existing object instance
        4. 22.1.4. Working with Automation objects
        5. 22.1.5. Closing an instance of an Automation object
      2. 22.2. 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. Using Bookmarks to insert data
        5. 22.2.5. Activating the instance of Word
        6. 22.2.6. Moving the cursor in Word
        7. 22.2.7. Closing the instance of the Word object
        8. 22.2.8. Inserting pictures by using bookmarks
        9. 22.2.9. Using Office's Macro Recorder
      3. 22.3. Collecting Data with Outlook 2007
        1. 22.3.1. Creating an e-mail
        2. 22.3.2. Managing replies
          1. 22.3.2.1. Import settings
          2. 22.3.2.2. Settings for automatic processing
          3. 22.3.2.3. Manually processing replies
      4. 22.4. Summary
    8. 23. Integrating Access with SharePoint
      1. 23.1. What Is SharePoint?
        1. 23.1.1. What is SharePoint Services technology?
        2. 23.1.2. What is a SharePoint Portal Server?
      2. 23.2. SharePoint Applications: Types of Web Sites
      3. 23.3. What Is a SharePoint List?
      4. 23.4. Introducing a SharePoint Services Web Site
      5. 23.5. Integrating Access 2007 and SharePoint
        1. 23.5.1. Sourcing data from a SharePoint Services Web site
        2. 23.5.2. Sourcing data from an Access database
      6. 23.6. SharePoint and Different Operating Systems
      7. 23.7. SharePoint Lists as External Data
      8. 23.8. Summary
    9. 24. Using ActiveX Controls
      1. 24.1. Understanding Objects
        1. 24.1.1. Looking at the types of objects
        2. 24.1.2. Using bound and unbound objects
        3. 24.1.3. Linking and embedding
      2. 24.2. Embedding Objects
        1. 24.2.1. Embedding an unbound object
          1. 24.2.1.1. Pasting an unbound object
          2. 24.2.1.2. Inserting an image-type object
          3. 24.2.1.3. Changing the display of an image
        2. 24.2.2. Embedding bound objects
        3. 24.2.3. Adding a bound OLE object
        4. 24.2.4. Adding a picture to a bound object frame
        5. 24.2.5. Editing an embedded object
      3. 24.3. Linking Objects
      4. 24.4. Creating a Graph or Chart
        1. 24.4.1. The different ways to create a graph
        2. 24.4.2. Creating graphs using the Toolbox
      5. 24.5. Embedding a Graph in a Form
        1. 24.5.1. Assembling the data
        2. 24.5.2. Adding the graph to the form
        3. 24.5.3. Customizing a graph
      6. 24.6. Integration with Microsoft Office
        1. 24.6.1. Checking the spelling of one or more fields and records
        2. 24.6.2. Using OLE automation with Office
        3. 24.6.3. Creating an Excel-type PivotTable
      7. 24.7. Summary
    10. 25. Handling Errors and Exceptions
      1. 25.1. Dealing with Errors
        1. 25.1.1. Logical errors
        2. 25.1.2. Runtime errors
          1. 25.1.2.1. Fatal errors
          2. 25.1.2.2. Simple runtime errors
          3. 25.1.2.3. Default error handling in Access
          4. 25.1.2.4. Unanticipated errors
      2. 25.2. Which Errors Can Be Detected?
        1. 25.2.1. What is an error handle?
        2. 25.2.2. Setting a basic error trap
      3. 25.3. Trapping Errors with VBA
        1. 25.3.1. The Error event
        2. 25.3.2. The ADO Errors collection
        3. 25.3.3. The Err object
        4. 25.3.4. VBA Error statement variations
          1. 25.3.4.1. On Error Resume Next
          2. 25.3.4.2. On Error GoTo <label>
          3. 25.3.4.3. On Error GoTo 0
          4. 25.3.4.4. VBA Resume statements
            1. 25.3.4.4.1. Resume
            2. 25.3.4.4.2. Resume Next
            3. 25.3.4.4.3. Resume <label>
      4. 25.4. Summary
  9. IV. Professional Database Development
    1. 26. Optimizing Access Applications
      1. 26.1. Understanding Module Load on Demand
        1. 26.1.1. Organizing your modules
        2. 26.1.2. Access 2007 prunes the call tree
      2. 26.2. Using the Access 2007 Database File Format
      3. 26.3. Distributing .accde Files
      4. 26.4. Understanding the Compiled State
        1. 26.4.1. Putting your application's code into a compiled state
        2. 26.4.2. Losing the compiled state
        3. 26.4.3. Distributing applications in a compiled or uncompiled state
          1. 26.4.3.1. Distributing source code for your application
          2. 26.4.3.2. Putting an application in an uncompiled state
          3. 26.4.3.3. Organizing commonly used code that is never modified into a library
          4. 26.4.3.4. Creating a library reference for distributed applications
      5. 26.5. Improving Absolute Speed
        1. 26.5.1. Opening a database exclusively
        2. 26.5.2. Compacting a database
        3. 26.5.3. Tuning your system
        4. 26.5.4. Getting the most from your tables
          1. 26.5.4.1. Creating efficient indexes
          2. 26.5.4.2. Deciding which fields to index
          3. 26.5.4.3. Using multiple-field indexes
        5. 26.5.5. Getting the most from your queries
        6. 26.5.6. Getting the most from your forms and reports
          1. 26.5.6.1. Minimizing form and report complexity and size
          2. 26.5.6.2. Using bitmaps on forms and reports
          3. 26.5.6.3. Speeding up list boxes and combo boxes
        7. 26.5.7. Getting the most from your modules
          1. 26.5.7.1. Using appropriate data types
          2. 26.5.7.2. Writing faster routines
            1. 26.5.7.2.1. Using control variables
            2. 26.5.7.2.2. Using field variables
            3. 26.5.7.2.3. Increasing the speed of finding data in code
          3. 26.5.7.3. Eliminating dead code and unused variables
        8. 26.5.8. Increasing Network performance
      6. 26.6. Improving Perceived Speed
        1. 26.6.1. Using a splash screen
        2. 26.6.2. Loading and keeping forms hidden
        3. 26.6.3. Using the hourglass
        4. 26.6.4. Using the built-in progress meter
        5. 26.6.5. Creating a progress meter with a pop-up form
        6. 26.6.6. Speeding up the progress meter display
      7. 26.7. Working with Large Program Databases in Access 2007
        1. 26.7.1. How databases grow in size
        2. 26.7.2. Compiling and compacting may not be enough
        3. 26.7.3. Rebooting gives you a clean memory map
        4. 26.7.4. Fixing a single corrupt form by removing the record source
        5. 26.7.5. Creating a new database and importing all objects
        6. 26.7.6. Using the decompile option in Access 2007
        7. 26.7.7. Recapping the six steps to large database success
        8. 26.7.8. Detecting an uncompiled database and automatically recompiling
        9. 26.7.9. Making small changes to large databases — Export
      8. 26.8. Summary
    2. 27. Advanced Data Access with VBA
      1. 27.1. Adding an Unbound Combo Box
      2. 27.2. Using the FindRecord Method
      3. 27.3. Using the Bookmark to Locate a Record
      4. 27.4. Filtering a Form Using Code
      5. 27.5. Using a Query to Filter a Form Interactively
        1. 27.5.1. Creating a parameter query
        2. 27.5.2. Creating an interactive dialog box
        3. 27.5.3. Linking the dialog box to another form
      6. 27.6. Summary
    3. 28. Bulletproofing Access Applications
      1. 28.1. What Is Bulletproofing?
      2. 28.2. Characteristics of Bulletproof Applications
      3. 28.3. Bulletproofing Goes Beyond Code
        1. 28.3.1. Document the code you write
        2. 28.3.2. Build to a specification
        3. 28.3.3. Document the application
        4. 28.3.4. Use professional installation tools
        5. 28.3.5. Provide the Access runtime module to users
        6. 28.3.6. Consider the user's skill and training requirements
        7. 28.3.7. Understand the user's motivations
        8. 28.3.8. Check for obvious hardware errors
        9. 28.3.9. Continue to improve the product
      4. 28.4. Principles of Bulletproofing
        1. 28.4.1. Make the application easy to start
        2. 28.4.2. Use startup options
        3. 28.4.3. Use a login form
        4. 28.4.4. Use a splash screen
        5. 28.4.5. Add switchboards to the application
        6. 28.4.6. Control the ribbons
        7. 28.4.7. Hide the Navigation Pane and remove menus
        8. 28.4.8. Display one form at a time
        9. 28.4.9. Trap unwanted keystrokes
        10. 28.4.10. Build bulletproof forms
        11. 28.4.11. Validate user input
        12. 28.4.12. Keep the user informed
        13. 28.4.13. Maintain usage logs
      5. 28.5. Develop to a Specification
      6. 28.6. Securing the Environment
      7. 28.7. Setting Startup Options in Code
      8. 28.8. Disable Startup Bypass
        1. 28.8.1. Setting property values
        2. 28.8.2. Getting property values
      9. 28.9. Providing User Feedback
        1. 28.12. Creating and using a progress meter
      10. 28.10. Adding Logging to Applications
      11. 28.11. Summary
    4. 29. Securing Access Applications
      1. 29.1. Understanding Jet Security
        1. 29.1.1. Understanding workgroup files
        2. 29.1.2. Understanding permissions
        3. 29.1.3. Understanding security limitations
      2. 29.2. Choosing a Security Level to Implement
      3. 29.3. Creating a Database Password
      4. 29.4. Using the /runtime Option
      5. 29.5. Using the Current Database Options
      6. 29.6. Using the Jet User-Level Security Model
        1. 29.6.1. Enabling security
        2. 29.6.2. Working with workgroups
          1. 29.6.2.1. Creating a new workgroup
          2. 29.6.2.2. Joining an existing workgroup
        3. 29.6.3. Working with users
          1. 29.6.3.1. Adding and deleting user accounts
          2. 29.6.3.2. Creating and changing user passwords
        4. 29.6.4. Working with groups
          1. 29.6.4.1. Adding and deleting groups
          2. 29.6.4.2. Assigning and removing group members
        5. 29.6.5. Securing objects by using permissions
          1. 29.6.5.1. Setting an object's owner
          2. 29.6.5.2. Setting object permissions
            1. 29.6.5.2.1. Setting default object permissions
            2. 29.6.5.2.2. Setting database permissions
            3. 29.6.5.2.3. Securing your database for distribution: A basic approach
      7. 29.7. Using the Access Security Wizard
      8. 29.8. Encrypting/Encoding a Database
      9. 29.9. Decrypting/Decoding a Database
      10. 29.10. Protecting Visual Basic Code
      11. 29.11. Preventing Virus Infections
        1. 29.11.1. Enabling sandbox mode
        2. 29.11.2. The Trust Center
      12. 29.12. Summary
    5. 30. Using the Windows API
      1. 30.1. What Is the Windows API?
        1. 30.1.1. Dynamic linking
      2. 30.2. Why Use the Windows API?
        1. 30.2.1. Common codebase
        2. 30.2.2. Tested and proven code
        3. 30.2.3. Cross-platform compatibility
        4. 30.2.4. Smaller application footprint
      3. 30.3. DLL Documentation
        1. 30.3.1. Where to find documentation
        2. 30.3.2. Deciphering the documentation
        3. 30.3.3. Data types
        4. 30.3.4. What you can't do with the API
      4. 30.4. How to Use the Windows API
        1. 30.4.1. The Declare statement
          1. 30.4.1.1. Function or Sub
          2. 30.4.1.2. Function name
          3. 30.4.1.3. Lib "LibraryName"
          4. 30.4.1.4. Alias "AliasName"
          5. 30.4.1.5. ArgumentList
            1. 30.4.1.5.1. ByVal or ByRef?
          6. 30.4.1.6. As DataType
        2. 30.4.2. Using wrapper functions
        3. 30.4.3. What is this "hwnd" thing?
      5. 30.5. API Examples
        1. 30.5.1. Retrieving system information
          1. 30.5.1.1. GetCommandLineA
          2. 30.5.1.2. GetWindowsDirectoryA
          3. 30.5.1.3. GetTempPathA
          4. 30.5.1.4. GetVersionExA
          5. 30.5.1.5. GetUserNameA
          6. 30.5.1.6. GetComputerNameA
          7. 30.5.1.7. GetDriveTypeA
          8. 30.5.1.8. GetDiskFreeSpaceA
          9. 30.5.1.9. GetVolumeInformationA
          10. 30.5.1.10. GetSystemDirectoryA
        2. 30.5.2. General-purpose Windows API functions
          1. 30.5.2.1. GetParent
          2. 30.5.2.2. GetWindowTextA
          3. 30.5.2.3. GetClassNameA
          4. 30.5.2.4. SetWindowTextA
        3. 30.5.3. Manipulating application settings with the Windows API
          1. 30.5.3.1. GetPrivateProfileStringA
          2. 30.5.3.2. GetPrivateProfileIntA
          3. 30.5.3.3. GetProfileStringA
          4. 30.5.3.4. WritePrivateProfileStringA
          5. 30.5.3.5. WriteProfileStringA
        4. 30.5.4. Controlling applications with the Registry
          1. 30.5.4.1. RegCreateKeyEx
          2. 30.5.4.2. RegSetValueExA
          3. 30.5.4.3. RegFlushKey
          4. 30.5.4.4. RegQueryValueEx
          5. 30.5.4.5. RegCloseKey
          6. 30.5.4.6. RegDeleteKey
      6. 30.6. Summary
    6. 31. Using the Access Replication Features
      1. 31.1. Understanding Replication
      2. 31.2. A Replication Demonstration
        1. 31.2.1. Creating the database
        2. 31.2.2. Changing the replica
      3. 31.3. Replication Pros and Cons
      4. 31.4. Introducing the Access Replication Tools
        1. 31.4.1. Using Access menus
        2. 31.4.2. Replicating through VBA and DAO
      5. 31.5. Creating a Replica Set
        1. 31.5.1. Using Access replication menus
        2. 31.5.2. Programmatic replication
      6. 31.6. Understanding Database Security in Replication Sets
      7. 31.7. The Importance of Local Objects
      8. 31.8. Resolving Replication Conflicts
        1. 31.8.1. Design errors
        2. 31.8.2. Synchronization conflicts
        3. 31.8.3. Manual conflict resolution
        4. 31.8.4. Synchronization errors
      9. 31.9. Replication Topologies
      10. 31.10. Replication from the Developer's Perspective
      11. 31.11. Understanding the Changes to Database Objects
        1. 31.11.1. Globally unique ID
        2. 31.11.2. New system tables
        3. 31.11.3. New fields
          1. 31.11.3.1. s_Generation
          2. 31.11.3.2. s_Lineage
        4. 31.11.4. Changes to AutoNumber fields
      12. 31.12. Changes to the Design Master Structure
        1. 31.12.1. Changes to data
        2. 31.12.2. Controlling replica creation
        3. 31.12.3. Things to avoid
          1. 31.12.3.1. Avoid making backups of replicas
          2. 31.12.3.2. Avoid high-volume updates
          3. 31.12.3.3. Replication housekeeping tasks
      13. 31.13. Programming Replication
        1. 31.13.1. Keeping objects local
        2. 31.13.2. Converting a database to a Design Master
        3. 31.13.3. Making new replicas
        4. 31.13.4. Synchronizing replicas
        5. 31.13.5. Replication properties
        6. 31.13.6. Moving the Design Master
        7. 31.13.7. Scheduling synchronization events
      14. 31.14. Partial Replicas
        1. 31.14.1. Using the Partial Replica Wizard
        2. 31.14.2. Choosing the type of replica
        3. 31.14.3. Programming partial replication with VBA
          1. 31.14.3.1. Creating the partial replica
          2. 31.14.3.2. Setting the partial replica's filter criteria
          3. 31.14.3.3. Creating table relationships in the partial replica
          4. 31.14.3.4. Filling the partial replica with data
          5. 31.14.3.5. Replicating partial replicas
          6. 31.14.3.6. Maintaining referential integrity in partial replicas
      15. 31.15. Advanced Replication Considerations
      16. 31.16. Summary
    7. 32. Object-Oriented Programming with VBA
      1. 32.1. Benefits of Object-Oriented Programming
        1. 32.1.1. Object basics
          1. 32.1.1.1. What's an object?
          2. 32.1.1.2. Using objects in applications
        2. 32.1.2. Class module basics
        3. 32.1.3. A simple class module
        4. 32.1.4. Adding a class module to a database
        5. 32.1.5. Creating simple product properties
        6. 32.1.6. Creating methods
        7. 32.1.7. Using the product object
        8. 32.1.8. Create bulletproof property procedures
      2. 32.2. Other Advantages of Object-Oriented Programming Techniques
      3. 32.3. Object-Oriented Programming Rules
        1. 32.3.1. Never reveal a user interface component, such as a message box, from a class module
        2. 32.3.2. Preserve the class's interface as the class is updated
      4. 32.4. Using Property Procedures
        1. 32.4.1. Persisting property values
        2. 32.4.2. Property Let syntax
        3. 32.4.3. Property Set syntax
        4. 32.4.4. Property Get syntax
        5. 32.4.5. Property procedure rules
      5. 32.5. Extending the Product Class
        1. 32.5.1. Retrieving product details
        2. 32.5.2. The new ProductID property
        3. 32.5.3. A new property
      6. 32.6. Product Methods
      7. 32.7. Class Events
        1. 32.7.1. The Class_Initialize event procedure
        2. 32.7.2. The Class_Terminate event procedure
      8. 32.8. Adding Events to Class Modules
        1. 32.8.1. An Access events primer
        2. 32.8.2. The need for events
        3. 32.8.3. Creating custom events
        4. 32.8.4. Raising events
        5. 32.8.5. Trapping custom events
        6. 32.8.6. Passing data through events
        7. 32.8.7. Exploiting Access class module events
        8. 32.8.8. Access forms are objects
      9. 32.9. Summary
    8. 33. Reusing VBA Code with Libraries
      1. 33.1. What Are Libraries?
        1. 33.1.1. Traditional Access programming
        2. 33.1.2. Sharing code between applications
        3. 33.1.3. Access library basics
        4. 33.1.4. Access 2007 library database references
          1. 33.1.4.1. Explicit library references
          2. 33.1.4.2. Programming references
      2. 33.2. Creating Library Databases
        1. 33.2.1. Create the objects and write the functions
        2. 33.2.2. Save as .accde
        3. 33.2.3. Load the database as a library
      3. 33.3. Debugging Library Databases
      4. 33.4. Library Database Object References
      5. 33.5. Summary
    9. 34. Customizing Access Ribbons
      1. 34.1. Why Replace Toolbars and Menus?
        1. 34.1.1. New controls for Access ribbons
        2. 34.1.2. SplitButton
        3. 34.1.3. DropDown
        4. 34.1.4. Gallery
        5. 34.1.5. SuperTips
      2. 34.2. Working with the Access 2007 Ribbon
        1. 34.2.1. Tabs
        2. 34.2.2. Groups
        3. 34.2.3. Controls
        4. 34.2.4. Managing the ribbon
      3. 34.3. Working with the Quick Access Toolbar
      4. 34.4. Access 2007 Ribbons: The Developer's Perspective
        1. 34.4.1. The ribbon creation process
        2. 34.4.2. Using VBA callbacks
      5. 34.5. The Ribbon Hierarchy
      6. 34.6. Getting Started with Access 2007 Ribbons
        1. 34.6.1. Step 1: Design the ribbon and build the XML
        2. 34.6.2. Step 2: Write the callback routines
        3. 34.6.3. Step 3: Create the USysRibbons table
        4. 34.6.4. Step 4: Add XML to USysRibbons
        5. 34.6.5. Step 5: Specify the custom ribbon property
      7. 34.7. The Basic Ribbon XML
      8. 34.8. Adding Ribbon Controls
        1. 34.8.1. Label control
        2. 34.8.2. Separator
        3. 34.8.3. Check boxes
        4. 34.8.4. DropDown control
      9. 34.9. Using Visual Web Developer 2005
      10. 34.10. Managing Ribbons
      11. 34.11. Completely Removing the Access 2007 Ribbon
      12. 34.12. Summary
    10. 35. Distributing Access Applications
      1. 35.1. Defining the Current Database Options
        1. 35.1.1. Application Options
        2. 35.1.2. Navigation Options
        3. 35.1.3. Toolbar Options
      2. 35.2. Testing the Application before Distribution
      3. 35.3. Polishing Your Application
        1. 35.3.1. Giving your application a consistent look and feel
        2. 35.3.2. Adding common professional components
      4. 35.4. Bulletproofing an Application
        1. 35.4.1. Using error trapping on all Visual Basic procedures
        2. 35.4.2. Separating the tables from the rest of the application
        3. 35.4.3. Documenting the application
        4. 35.4.4. Creating a Help system
        5. 35.4.5. Implementing a security structure
      5. 35.5. Summary
    11. 36. Using Access Macros
      1. 36.1. Understanding Macros
        1. 36.1.1. The Hello World macro
        2. 36.1.2. Assign a macro to an event
      2. 36.2. Multiaction Macros
        1. 36.2.1. Running multiple action queries
      3. 36.3. Macro Names
        1. 36.3.1. Opening forms
      4. 36.4. Using Conditions
        1. 36.4.1. Opening reports using conditions
        2. 36.4.2. Multiple actions in conditions
      5. 36.5. Using Temporary Variables
        1. 36.5.1. Enhanced Hello World macro
        2. 36.5.2. Enhanced reporting macro
        3. 36.5.3. Using temporary variables in VBA
      6. 36.6. Handling Errors and Debugging Macros
        1. 36.6.1. The OnError action
        2. 36.6.2. The MacroError object
        3. 36.6.3. Debugging macros
      7. 36.7. Embedded Macros
      8. 36.8. Macros versus VBA Statements
        1. 36.8.1. Converting existing macros to VBA
      9. 36.9. Summary
  10. V. Access as an Enterprise Platform
    1. 37. Using XML in Access 2007
      1. 37.1. Introducing HTML and XML
        1. 37.1.1. What is HTML?
        2. 37.1.2. What is XML?
        3. 37.1.3. What is a DOM?
        4. 37.1.4. Formatting and transforming XSL(T)
        5. 37.1.5. Sharing data using XML
        6. 37.1.6. Some really advanced XML stuff
        7. 37.1.7. XML as a database
      2. 37.2. DAO, ADO, ADO.Net, Access 2007, and XML
      3. 37.3. Summary
    2. 38. SharePoint as a Data Source
      1. 38.1. Building Access Interfaces with SharePoint
        1. 38.1.1. Copying from Access to SharePoint
        2. 38.1.2. Building an Access form using SharePoint data
        3. 38.1.3. Building an Access report using SharePoint data
      2. 38.2. SharePoint Application Types
        1. 38.2.1. Tracking applications
        2. 38.2.2. Collaborative applications and databases
      3. 38.3. Microsoft SharePoint Designer 2007
      4. 38.4. Summary
    3. 39. Client/Server Concepts
      1. 39.1. The Parts of Client/Server Architecture
        1. 39.1.1. Applications
        2. 39.1.2. The back office
          1. 39.1.2.1. The database
          2. 39.1.2.2. Web servers and application servers
      2. 39.2. Multitier Architecture
        1. 39.2.1. Two-tier systems
        2. 39.2.2. Three-tier systems
      3. 39.3. What Is an OLTP Database?
      4. 39.4. Access, Client/Server, and Multiple Tiers
        1. 39.4.1. Where does Access 2007 fit?
          1. 39.4.1.1. Access as a database repository
          2. 39.4.1.2. Access as both database and application
          3. 39.4.1.3. Access as an Internet database
      5. 39.5. Summary
    4. 40. SQL Server as an Access Companion
      1. 40.1. Connecting to SQL Server
        1. 40.1.1. What is a listener?
        2. 40.1.2. What is a connection string?
        3. 40.1.3. Connecting to SQL Server from Access
        4. 40.1.4. SQL Server security
          1. 40.1.4.1. Comparing Access 2007 and SQL Server Security
          2. 40.1.4.2. Access DDL Security Commands
      2. 40.2. Working with SQL Server Objects from Access
        1. 40.2.1. Using SQL Server tables from Access
        2. 40.2.2. Views in SQL Server
        3. 40.2.3. Stored procedures, functions, and triggers
      3. 40.3. Summary
    5. 41. Upsizing Access Databases to SQL Server
      1. 41.1. Upsizing Access and the Upsizing Wizard
        1. 41.1.1. Before upsizing an application
        2. 41.1.2. Running the Upsizing Wizard
        3. 41.1.3. Working with an Access ADP file
        4. 41.1.4. Comparing Access 2007 to SQL Server data types
      2. 41.2. Summary
  11. VI. Appendixes
    1. A. Access 2007 Specifications
      1. A.1. Microsoft Access Database Specifications
      2. A.2. Access Data Projects (ADP) Specifications
      3. A.3. Microsoft SQL Server 2005 Database Specifications
    2. B. What's on the CD-ROM
      1. B.1. System Requirements
      2. B.2. Using the CD
      3. B.3. Files and Software on the CD
        1. B.3.1. The Complete Access Auto Auctions Example
        2. B.3.2. Example files for the Access 2007 Bible
        3. B.3.3. eBook version of Access 2007 Bible
      4. B.4. Troubleshooting
    3. C. What's New in Access 2007
      1. C.1. The User Interface
      2. C.2. Tables
      3. C.3. Datasheet View
      4. C.4. Forms
      5. C.5. Reports
      6. C.6. Macros
      7. C.7. Security
      8. C.8. SharePoint
      9. C.9. Summary
  12. Wiley Publishing, Inc.End-User License Agreement

Product information

  • Title: Access™ 2007 Bible
  • Author(s):
  • Release date: January 2007
  • Publisher(s): Wiley
  • ISBN: 9780470046739