Microsoft® Access® 2010 Inside Out

Microsoft® Access® 2010 Inside Out

by Jeff Conrad and John Viescas
Released August 2010
Publisher(s): Microsoft Press
ISBN: 9780735649132

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

You're beyond the basics, so dive right in and really put your database skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It's all muscle and no fluff. Discover how the experts tackle Access 2010 -- and challenge yourself to new levels of mastery!

  • Master essential data management and design techniques

  • Import and link to data from spreadsheets, databases, text files, and other sources

  • Use action queries to quickly insert, update, or delete entire sets of data

  • Create custom forms to capture and display data

  • Design reports to calculate, summarize, and highlight critical data--and learn advanced techniques

  • Automate your application with macros and Visual Basic for Applications (VBA)

  • Use Access Services to extend your database application to the Web

  • Try out the sample client and web database applications in both 32-bit and 64-bit versions

    • A Note Regarding the CD or DVD

    The print version of this book ships with a CD or DVD. The sample client and web database applications are provided in both 32-bit and 64-bit versions. Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions.

    For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

    Table of contents

    1. Microsoft® Access® 2010 Inside Out
    2. Dedication
    3. A Note Regarding Supplemental Files
    4. Acknowledgments
    5. About the CD
      1. What’s on the CD
      2. Sample Applications
      3. System Requirements
      4. Support Information
    6. Conventions and Features Used in This Book
      1. Text Conventions
      2. Design Conventions
      3. Syntax Conventions
    7. Introduction
      1. Getting Familiar with Access 2010
      2. About This Book
    8. I. Understanding Access
      1. 1. What Is Access?
        1. What Is a Database?
          1. Relational Databases
          2. Database Capabilities
        2. Access as an RDBMS
          1. Data Definition and Storage
          2. Data Manipulation
          3. Data Control
        3. Access as an Application Development System
        4. Deciding to Move to Database Software
        5. Extending the Power of Access to the Web
      2. 2. Exploring the Access 2010 Interface
        1. Opening Access for the First Time
        2. Getting Started with Access 2010
          1. Opening an Existing Database
          2. Exploring the Microsoft Office Backstage View
            1. Info Tab
            2. Recent Tab
            3. New Tab
            4. Print Tab
            5. Save & Publish Tab
            6. Help Tab
          3. Taking Advantage of the Quick Access Toolbar
        3. Understanding Content Security
          1. Enabling a Database That Is Not Trusted
          2. Understanding the Trust Center
          3. Enabling Content by Defining Trusted Locations
        4. Understanding the Office Fluent Ribbon
          1. Home Tab
          2. Create Tab
          3. External Data Tab
          4. Database Tools Tab
          5. Customizing the Ribbon
        5. Understanding the Navigation Pane
          1. Exploring Navigation Pane Object Views
          2. Working with Custom Categories and Groups
            1. Exploring the Navigation Options Dialog Box
            2. Creating and Modifying a Custom Category
            3. Creating and Modifying Groups in a Custom Category
            4. Creating Object Shortcuts in Custom Groups
            5. Hiding Custom Groups in a Category
            6. Hiding and Renaming Object Shortcuts
            7. Revealing Hidden Shortcuts
          3. Sorting and Selecting Views in the Navigation Pane
          4. Manually Sorting Objects in the Navigation Pane
          5. Searching for Database Objects
        6. Using the Single-Document vs. the Multiple-Document Interface
        7. Modifying Global Settings via the Access Options Dialog Box
      3. 3. Access 2010 Overview
        1. The Architecture of Access
        2. Exploring a Desktop Database—Housing Reservations
          1. Tables
            1. Table Window in Design View
            2. Table Window in Datasheet View
          2. Queries
            1. Query Window in Design View
            2. Query Window in Datasheet View
          3. Forms
            1. Form Window in Design View
            2. Form Window in Layout View
            3. Form Window in Form View
          4. Reports
            1. Report Window in Design View
            2. Report Window in Print Preview
            3. Report Window in Layout View
            4. Report Window in Report View
          5. Macros
          6. Modules
        3. What Happened to Project Files (ADP)?
        4. The Many Faces of Access
    9. II. Creating a Database and Tables
      1. 4. Designing Client Tables
        1. Creating a New Database
          1. Using a Database Template to Create a Database
          2. Creating a New Empty Database
        2. Creating Your First Simple Table by Entering Data
        3. Creating a Table Using Application Parts
        4. Creating a Table Using Data Type Parts
        5. Creating a Table in Design View
        6. Defining Fields
          1. Understanding Field Data Types
          2. Setting Field Properties
          3. Completing the Fields in the Companies Table
          4. Defining Simple Field Validation Rules
          5. Defining Input Masks
        7. Defining a Primary Key
        8. Defining a Table Validation Rule
        9. Understanding Other Table Properties
        10. Defining Relationships
          1. Defining Your First Relationship
          2. Creating a Relationship on Multiple Fields
        11. Adding Indexes
          1. Single-Field Indexes
          2. Multiple-Field Indexes
        12. Setting Table Design Options
        13. Creating a Default Template for New Databases
        14. Printing a Table Definition
        15. Database Limitations
      2. 5. Modifying Your Table Design
        1. Before You Get Started
          1. Making a Backup Copy
          2. Checking Object Dependencies
        2. Deleting Tables
        3. Renaming Tables
        4. Changing Field Names
        5. Moving Fields
        6. Inserting Fields
        7. Copying Fields
        8. Deleting Fields
        9. Changing Data Attributes
          1. Changing Data Types
          2. Changing Data Lengths
          3. Dealing with Conversion Errors
          4. Changing Other Field Properties
        10. Reversing Changes
        11. Using the Table Analyzer Wizard
        12. Taking a Look at Lookup Properties
        13. Working with Multi-Value Lookup Fields
        14. Changing the Primary Key
        15. Compacting Your Database
      3. 6. Designing Web Tables
        1. Working with the Web
        2. Creating a New Web Database
          1. Using a Database Template to Create a Web Database
          2. Creating a New Empty Web Database
        3. Creating Your First Simple Web Table by Entering Data
        4. Creating a Web Table Using Application Parts
        5. Using Data Type Parts
        6. Creating Web Tables in Datasheet View
          1. Defining Web Fields
          2. Understanding Web Field Data Types
          3. Setting Field Properties for Web Databases
          4. Completing the Fields in the Vendors Web Table
          5. Creating Calculated Fields
          6. Defining Field Validation Rules for Web Databases
        7. Defining a Table Validation Rule for Web Databases
        8. Defining a Primary Key for Web Databases
        9. Understanding Other Web Table Properties
        10. Creating Lookup Fields in a Web Database
        11. Creating Relationships Using Lookup Fields
          1. Defining a Restrict Delete Relationship
          2. Defining a Cascade Delete Relationship
        12. Using the Web Compatibility Checker
          1. Analyzing the Web Compatibility Issues Table
          2. Preparing a Client Database for the Web
      4. 7. Creating Table Data Macros
        1. Uses of Data Macros
        2. The Data Macro Design Facility—An Overview
          1. Redesigning the Macro Window from Previous Versions of Access
          2. Touring the New Logic Designer
        3. Working with Before Events
          1. Before Change
            1. Including Comments
            2. Grouping Macros
            3. Using If Blocks to Create Conditional Expressions
            4. Raising Errors in Data Macros to Cancel Events
            5. Testing Your Data Macro
            6. Defining Multiple Actions
            7. Collapsing and Expanding Actions
            8. Moving Actions
          2. Preventing Duplicate Records Across Multiple Fields
          3. Before Delete
        4. Working with After Events
          1. After Insert
          2. After Update
          3. After Delete
        5. Working with Named Data Macros
          1. Creating Named Data Macros
          2. Saving Named Data Macros
          3. Calling Named Data Macros
          4. Renaming and Deleting Named Data Macros
          5. Analyzing Errors in the USysApplicationLog Table
          6. Using Parameters
          7. Using Local Variables
          8. Working with Return Variables
        6. Debugging Data Macros
        7. Understanding Recursion in Data Macros
        8. Sharing Data Macro Logic
      5. 8. Importing and Linking Data
        1. A Word About Open Database Connectivity (ODBC)
        2. Creating a Data Source to Link to an ODBC Database
        3. Importing vs. Linking Database Files
        4. Importing Data and Databases
          1. Importing dBASE Files
          2. Importing SQL Tables
          3. Importing Access Objects
        5. Importing Spreadsheet Data
          1. Preparing a Spreadsheet
          2. Importing a Spreadsheet
          3. Fixing Errors
        6. Importing Text Files
          1. Preparing a Text File
            1. Setting Up Delimited Data
            2. Setting Up Fixed-Width Data
          2. Importing a Text File
          3. Fixing Errors
        7. Modifying Imported Tables
        8. Linking Files
          1. Security Considerations
          2. Performance Considerations
          3. Linking Access Tables
          4. Linking dBASE Files
          5. Linking Text and Spreadsheet Files
          6. Linking SQL Tables
          7. Modifying Linked Tables
          8. Unlinking Linked Tables
          9. Using the Linked Table Manager
        9. Collecting Data via Email
          1. Collecting Data via HTML Forms
            1. Filling Out the HTML Form
            2. Having Outlook Automatically Process the Replies
          2. Collecting Data Using InfoPath Forms
            1. Filling Out the InfoPath Form
            2. Manually Processing the Replies
            3. Managing and Resending Data Collection Messages from Access
        10. Importing and Linking SharePoint Data
          1. Importing a List from a SharePoint Site
          2. Linking a SharePoint List into Access
        11. Saving Import Procedures
    10. III. Building Queries
      1. 9. Creating and Working with Simple Queries
        1. Selecting Data from a Single Table
          1. Specifying Fields
          2. Setting Field Properties
          3. Entering Selection Criteria
            1. Working with Dates and Times in Criteria
            2. AND vs. OR
            3. Between, In, and Like
          4. Using Expressions
            1. Creating Text Expressions
            2. Defining Arithmetic Expressions
          5. Using the Expression Builder
          6. Specifying Field Names
          7. Sorting Data
        2. Testing Validation Rule Changes
          1. Checking a New Field Validation Rule
          2. Checking a New Table Validation Rule
        3. Working in Query Datasheet View
          1. Moving Around and Using Keyboard Shortcuts
          2. Working with Subdatasheets
          3. Changing Data
            1. Understanding Record Indicators
            2. Adding a New Record
            3. Selecting and Changing Data
            4. Replacing Data
            5. Copying and Pasting Data
            6. Deleting Rows
          4. Working with Hyperlinks
            1. Activating a Hyperlink
            2. Inserting a New Hyperlink
            3. Editing an Existing Hyperlink
          5. Sorting and Searching for Data
            1. Sorting Data
            2. Searching for and Filtering Data
              1. Using Find
              2. Filtering by Selection
              3. Using the Filter Window
              4. Using Filter by Form
      2. 10. Building Complex Queries
        1. Selecting Data from Multiple Tables
          1. Creating Inner Joins
          2. Building a Query on a Query
          3. Using Outer Joins
            1. Building a Simple Outer Join
            2. Solving a Complex “Unmatched” Problem
        2. Using a Query Wizard
        3. Summarizing Information with Totals Queries
          1. Totals Within Groups
          2. Selecting Records to Form Groups
          3. Selecting Specific Groups
          4. Building Crosstab Queries
            1. Creating a Simple Crosstab Query
            2. Partitioning Data in a Crosstab Query
        4. Using Query Parameters
        5. Customizing Query Properties
          1. Controlling Query Output
          2. Working with Unique Records and Values
          3. Defining a Subdatasheet
          4. Other Query Properties
        6. Editing and Creating Queries in SQL View
        7. Limitations on Using Select Queries to Update Data
        8. Creating PivotTables and PivotCharts from Queries
          1. Building a Query for a PivotTable
          2. Designing a PivotTable
          3. Designing a PivotChart
        9. Creating Queries for the Web
      3. 11. Modifying Data with Action Queries
        1. Updating Groups of Rows
          1. Testing with a Select Query
          2. Converting a Select Query to an Update Query
          3. Running an Update Query
          4. Updating Multiple Fields
          5. Creating an Update Query Using Multiple Tables or Queries
        2. Creating a New Table with a Make-Table Query
          1. Creating a Make-Table Query
          2. Running a Make-Table Query
        3. Inserting Data from Another Table
          1. Creating an Append Query
          2. Running an Append Query
        4. Deleting Groups of Rows
          1. Testing with a Select Query
          2. Using a Delete Query
          3. Deleting Inactive Data
        5. Troubleshooting Action Queries
          1. Solving Common Action Query Errors and Problems
          2. Looking at an Error Example
    11. IV. Creating Forms
      1. 12. Using Forms in an Access Application
        1. Uses of Forms
        2. A Tour of Forms
          1. Headers, Detail Sections, and Footers
          2. Multiple-Page Forms
          3. Continuous Forms
          4. Split Forms
          5. Subforms
          6. Pop-Up Forms
          7. Modal Forms
          8. Special Controls
            1. Option Buttons, Check Boxes, Toggle Buttons, and Option Groups
            2. List Boxes and Combo Boxes
            3. Tab Controls
            4. Attachment Controls
            5. ActiveX Objects
            6. Command Buttons
            7. Navigational Control
            8. Web Browser Control
          9. PivotTables and PivotCharts
        3. Understanding Web Form Limitations
        4. Moving Around on Forms and Working with Data
          1. Viewing Data
            1. Moving Around
            2. Keyboard Shortcuts
        5. Adding Records and Changing Data
          1. Adding a New Record
          2. Changing and Deleting Data
        6. Searching for and Sorting Data
          1. Performing a Simple Search
          2. Using the Search Box
          3. Performing a Quick Sort on a Form Field
          4. Adding a Filter to a Form
        7. Printing Forms
      2. 13. Building a Form
        1. Forms and Object-Oriented Programming
        2. Starting from Scratch—A Simple Input Form
          1. Building a New Form with Design Tools
            1. The Form Design Tools Contextual Ribbon Tabs
            2. The Field List
            3. The Property Sheet
          2. Building a Simple Input Form for the tblCompanies Table
            1. Moving and Sizing Controls
            2. The Font Group
            3. The Control Formatting Group
            4. Setting Text Box Properties
            5. Setting Label Properties
            6. Setting Form Properties
          3. Customizing Colors and Checking Your Design Results
        3. Working with Quick Create and the Form Wizard
          1. Creating a Form with the Quick Create Commands
          2. Creating the Basic Products Form with the Form Wizard
          3. Modifying the Products Form
        4. Simplifying Data Input with a Form
          1. Taking Advantage of Combo Boxes and List Boxes
          2. Using Toggle Buttons, Check Boxes, and Option Buttons
        5. Working with Application Part Forms
      3. 14. Customizing a Form
        1. Aligning and Sizing Controls in Design View
          1. Sizing Controls to Fit Content
          2. Adjusting the Layout of Controls
          3. “Snapping” Controls to the Grid
          4. Lining Up Controls
        2. Enhancing the Look of a Form
          1. Lines and Rectangles
          2. Colors and Special Effects
          3. Fonts
        3. Setting Control Properties for Client Forms
          1. Formatting Properties
            1. Specifying a Format for Numbers and Currency
            2. Specifying a Format for Text
            3. Specifying a Format for Date/Time
            4. Specifying a Format for Yes/No Fields
          2. Adding a Scroll Bar
          3. Enabling and Locking Controls
          4. Setting the Tab Order
          5. Adding a Smart Tag
          6. Understanding Other Control Properties for Client Forms
        4. Setting Client Form Properties
          1. Allowing Different Views
          2. Setting Navigation Options
          3. Defining a Pop-Up and/or Modal Form
          4. Controlling Edits, Deletions, Additions, and Filtering
          5. Defining Window Controls
          6. Setting the Border Style
          7. Understanding Other Client Form Properties
        5. Setting Client Form and Control Defaults
          1. Changing Control Defaults
          2. Defining a Template Form
        6. Working with Web Forms in Layout View
        7. Starting from Scratch—A Simple Input Web Form
          1. Understanding Control Layouts and Control Anchoring
          2. Lining Up Controls
          3. Moving Controls Within a Control Layout
          4. Formatting a Column of Controls
          5. Resizing Controls
          6. Removing a Control Layout for Client Forms
          7. Splitting and Merging Cells
          8. Inserting Rows and Columns
          9. Using Web-Compatible Controls
          10. Adding Gridlines to Web Forms
          11. Adding Some Space with Control Padding
          12. Creating a Title
          13. Moving Controls to Different Sections on Web Forms
        8. Setting Control Properties for Web Forms
        9. Setting Web Form Properties
        10. Using Themes
        11. Working with Shared Resources
      4. 15. Advanced Form Design
        1. Basing a Form on a Multiple-Table Query
          1. Creating a Many-to-One Form
            1. Designing a Many-to-One Query
            2. Designing a Many-to-One Form
        2. Creating and Embedding Subforms
          1. Specifying the Subform Source
          2. Designing the Innermost Subform
          3. Designing the First Level Subform
          4. Embedding a Subform
          5. Specifying the Main Form Source
          6. Creating the Main Form
          7. Creating a Subdatasheet Subform
        3. Displaying Values in an Option Group
        4. Using Conditional Formatting in Client Forms
        5. Working with the Tab Control
        6. Creating Multiple-Page Client Forms
        7. Working with Client PivotChart Forms
          1. Building a Client PivotChart Form
          2. Embedding a Linked PivotChart
        8. Working with Navigation Controls
        9. Using Web Browser Controls
    12. V. Working with Reports
      1. 16. Using Reports
        1. Uses of Reports
        2. A Tour of Reports
          1. Print Preview—A First Look
          2. Headers, Detail Sections, Footers, and Groups
          3. Subreports
          4. Objects in Reports
          5. Report View—A First Look
        3. Printing Reports
          1. Print Setup
      2. 17. Constructing a Report
        1. Starting from Scratch—A Simple Report
          1. Building the Report Query
          2. Designing the Report
          3. Grouping, Sorting, and Totaling Information
          4. Completing the Report
        2. Using the Report Command
        3. Using the Report Wizard
          1. Specifying Report Wizard Options
          2. Viewing the Result
        4. Working with Web Reports in Layout View
          1. Modifying a Report Command-Created Web Report in Layout View
          2. Completing the Web Report
        5. Building a Web Report in Layout View
          1. Starting with a Blank Web Report
          2. Adding Grouping and Sorting
          3. Working with Control Layouts
          4. Adding Totals to Records
          5. Using Gridlines
          6. Adding the Final Formatting Touches
        6. Understanding Web Report Limitations
      3. 18. Advanced Report Design
        1. Building a Query for a Complex Report
        2. Creating the Basic Facility Occupancy By Date Report
        3. Defining the Grouping and Sorting Criteria
        4. Setting Section and Report Properties
          1. Section Properties for Reports
          2. Report Properties
        5. Using Calculated Values
          1. Adding the Print Date and Page Numbers
          2. Performing Calculations
            1. Performing a Calculation on a Detail Line
            2. Adding Values Across a Group
            3. Creating a Grand Total
          3. Hiding Redundant Values and Concatenating Text Strings
          4. Calculating Percentages
          5. Using Running Sum
          6. Taking Advantage of Conditional Formatting
        6. Creating and Embedding a Subreport
          1. Understanding Subreport Challenges
          2. Building a Client Report with a Subreport
            1. Building the Subreport Query
            2. Designing the Subreport
            3. Embedding a Subreport
        7. Adding a PivotChart to a Client Report
          1. Designing the PivotChart Form
          2. Embedding a PivotChart in a Client Report
    13. VI. Automating an Access Application Using Macros
      1. 19. Understanding Event Processing
        1. Access as a Windows Event-Driven Application
          1. Understanding Events in Windows
          2. Using Access Events to Build an Application
        2. Summary of Form and Report Events
        3. Understanding Event Sequence and Form Editing
      2. 20. Automating a Client Application Using Macros
        1. Uses of Macros
        2. The Macro Design Facility—An Overview
          1. Working with the Logic Designer
          2. Saving Your Macro
          3. Testing Your Macro
        3. Defining Multiple Actions
        4. Working with Submacros
        5. Understanding Conditional Expressions
        6. Using Embedded Macros
          1. Editing an Embedded Macro
          2. Creating an Embedded Macro
          3. Deleting an Embedded Macro
        7. Using Temporary Variables
        8. Trapping Errors in Macros
        9. Understanding Macro Actions That Are Not Trusted
        10. Making Your Application Come Alive with Macros
          1. Referencing Form and Report Objects
            1. Rules for Referencing Forms and Reports
            2. Rules for Referencing Form and Report Properties
            3. Rules for Referencing Form and Report Controls and Their Properties
            4. Rules for Referencing Subforms and Subreports
          2. Opening a Secondary Form
            1. Creating the SeeCityInformation Macro
          3. Synchronizing Two Related Forms
            1. Creating the SyncWeddingListAndCity Macro
          4. Validating Data and Presetting Values
            1. Validating Data
            2. Understanding the ValidateCitySetStateAndZip Macro
            3. Passing Status Information Between Linked Forms
            4. Presetting Values
      3. 21. Automating a Web Application Using Macros
        1. Creating Web Macros
        2. Using Macro Objects for Common Functionality
        3. Working with Web Form and Control Events
        4. Passing Parameters to Forms and Reports
        5. Exploring the Invoice Audit Web Form Macros
          1. Using the SetProperty Action with Web Form Controls
          2. Calling Named Data Macros and Using Return Variables
          3. Using BrowseTo Macro Actions to Browse to Forms and Reports
          4. Trapping Error Messages
        6. Checking SharePoint User Permission Group Levels
        7. Performing Different Actions When Opening a Web Form in a Browser
        8. Avoiding Type Coercion Issues
    14. VII. Working with the Web
      1. 22. Using Web Applications in a Browser
        1. Working with SharePoint
        2. Publishing Your Database to an Access Services Site
          1. Assigning a Web Display Form
          2. Understanding the Publish Process
          3. Analyzing Publish Errors
        3. Working with Your Application in a Web Browser
          1. Using Forms
          2. Using Reports
          3. Using Datasheet Forms
          4. Waiting for Server Processing
          5. Understanding Session Management
        4. Exploring the Access Services Shell
          1. Downloading a Web Application Back into Access
          2. Setting Site Permissions
          3. Reviewing the Modify Application Page
        5. Working with the Recycle Bin
        6. Extending Your Access Services Application
        7. Using Your Published Web Database in Access
          1. Making Changes to a Published Web Application
          2. Resolving Synchronization Conflicts
          3. Working Offline
          4. Saving a Web Application as a Local Database
        8. Instantiating an Access Services Template
          1. Using an Installed Web Template
          2. Instantiating a Custom Template
      2. 23. Using Business Connectivity Services
        1. Understanding Web Services
        2. Introducing Business Connectivity Services
        3. Using XML
          1. Exploring XML
          2. Well-Formed XML
        4. Working with BDC Model Definition Files
        5. Generating Entities
        6. Connecting Data Services in Access
    15. A. Installing Your Software
      1. Installing the Office System
        1. Choosing Options When You Have No Previous Version of the Office System
        2. Choosing Options to Upgrade a Previous Version of the Office System
      2. Converting from a Previous Version of Access
        1. Conversion Issues
      3. Installing the Office 64-Bit Version
      4. Installing the Sample Files
    16. B. About the Authors
    17. Index
    18. VIII. Automating an Access Application Using Visual Basic
      1. 24. Understanding Visual Basic Fundamentals
        1. The Visual Basic Development Environment
          1. Modules
            1. Module Objects
            2. Form and Report Modules
          2. The Visual Basic Editor Window
          3. Working with Visual Basic Debugging Tools
            1. Setting Breakpoints
            2. Using the Immediate Window
              1. Executing Visual Basic Commands in the Immediate Window
              2. Using Breakpoints
            3. Working with the Watch Window
            4. Examining the Procedure Call Sequence (Call Stack)
        2. Variables and Constants
          1. Data Types
          2. Variable and Constant Scope
        3. Declaring Constants and Variables
          1. Const Statement
            1. Syntax
            2. Notes
            3. Example
          2. Dim Statement
            1. Syntax
            2. Notes
            3. Examples
          3. Enum Statement
            1. Syntax
            2. Notes
            3. Example
          4. Event Statement
            1. Syntax
            2. Notes
            3. Example
          5. Private Statement
            1. Syntax
            2. Notes
            3. Example
          6. Public Statement
            1. Syntax
            2. Notes
            3. Example
          7. ReDim Statement
            1. Syntax
            2. Notes
            3. Example
          8. Static Statement
            1. Syntax
            2. Notes
            3. Examples
          9. Type Statement
            1. Syntax
            2. Notes
            3. Example
        4. Collections, Objects, Properties, and Methods
          1. The Access Application Architecture
          2. The DAO Architecture
          3. The ADO Architecture
          4. Referencing Collections, Objects, and Properties
            1. When to Use “!” and “.”
          5. Assigning an Object Variable—Set Statement
            1. Syntax
            2. Notes
            3. Examples
          6. Object Methods
            1. Working with DAO Recordsets
            2. Manipulating Complex Data Types Using DAO
            3. Working with ADO Recordsets
            4. Other Uses for Object Methods
        5. Functions and Subroutines
          1. Function Statement
            1. Syntax
            2. Notes
            3. Example
          2. Sub Statement
            1. Syntax
            2. Notes
            3. Example
        6. Understanding Class Modules
          1. Property Get
            1. Syntax
            2. Notes
            3. Examples
          2. Property Let
            1. Syntax
            2. Notes
            3. Examples
          3. Property Set
            1. Syntax
            2. Notes
            3. Examples
        7. Controlling the Flow of Statements
          1. Call Statement
            1. Syntax
            2. Notes
            3. Examples
          2. Do...Loop Statement
            1. Syntax
            2. Notes
            3. Example
          3. For...Next Statement
            1. Syntax
            2. Notes
            3. Example
          4. For Each...Next Statement
            1. Syntax
            2. Notes
            3. Example
          5. GoTo Statement
            1. Syntax
            2. Notes
            3. Example
          6. If...Then...Else Statement
            1. Syntax
            2. Notes
            3. Example
          7. RaiseEvent Statement
            1. Syntax
            2. Notes
            3. Example
          8. Select Case Statement
            1. Syntax
            2. Notes
            3. Example
          9. Stop Statement
            1. Syntax
            2. Notes
          10. While...Wend Statement
            1. Syntax
            2. Notes
            3. Example
          11. With...End Statement
            1. Syntax
            2. Example
        8. Running Macro Actions and Menu Commands
          1. DoCmd Object
            1. Syntax
            2. Notes
            3. Examples
          2. Executing an Access Command
            1. Syntax
            2. Notes
            3. Examples
          3. Actions with Visual Basic Equivalents
        9. Trapping Errors
          1. On Error Statement
            1. Syntax
            2. Notes
            3. Examples
        10. Some Complex Visual Basic Examples
          1. A Procedure to Randomly Load Data
          2. A Procedure to Examine All Error Codes
        11. Working with 64-Bit Access Visual Basic for Applications
          1. Using Declare Statements
          2. Using LongPtr Data Types
          3. Using PtrSafe Attributes
          4. Supporting Older Versions of Access
          5. Understanding Pointer Valued Functions and LongPtr Type Coercion
          6. Using LongLong Data Types
          7. Working with .MDE and .ACCDE files in 64-Bit Environments
      2. 25. Automating Your Application with Visual Basic
        1. Why Aren’t We Using Macros?
          1. When to Use Macros
          2. When to Use Visual Basic
        2. Assisting Data Entry
          1. Filling In Related Data
          2. Handling the NotInList Event
          3. Fixing an E-Mail Hyperlink
          4. Providing a Graphical Calendar
          5. Working with Linked Photos
            1. Deleting and Updating an Image Path
        3. Validating Complex Data
          1. Checking for Possible Duplicate Names
          2. Testing for Related Records When Deleting a Record
          3. Verifying a Prerequisite
          4. Maintaining a Special Unique Value
          5. Checking for Overlapping Data
        4. Controlling Tabbing on a Multiple-Page Form
        5. Automating Data Selection
          1. Working with a Multiple-Selection List Box
          2. Providing a Custom Query By Form
          3. Selecting from a Summary List
          4. Filtering One List with Another
        6. Linking to Related Data in Another Form or Report
          1. Linking Forms Using a Filter
          2. Linking to a Report Using a Filter
          3. Synchronizing Two Forms Using a Class Event
        7. Automating Complex Tasks
          1. Triggering a Data Task from a Related Form
          2. Linking to a Related Task
          3. Calculating a Stored Value
        8. Automating Reports
          1. Allowing for Used Mailing Labels
          2. Drawing on a Report
          3. Dynamically Filtering a Report When It Opens
        9. Calling Named Data Macros
    19. IX. After Completing Your Application
      1. 26. The Finishing Touches
        1. Creating Custom Ribbons with XML
          1. Creating a USysRibbons Table
          2. Creating a Test Form
          3. Building the Ribbon XML
            1. Hiding Existing Ribbon Elements
            2. Testing Your XML
            3. Creating Tabs
            4. Adding Built-In Groups to Tabs
        2. Loading Ribbon XML
          1. Syntax
          2. Notes
        3. Using Ribbon Attributes
          1. Creating VBA Callbacks
          2. Dynamically Updating Ribbon Elements
          3. Loading Images into Custom Controls
          4. Hiding Options on the Backstage View
          5. Adding Options to the Backstage View
          6. Creating a Custom Quick Access Toolbar
          7. Setting Focus to a Tab
        4. Disabling Layout View
        5. Controlling How Your Application Starts and Runs
          1. Setting Startup Properties for Your Database
          2. Starting and Stopping Your Application
          3. Creating an AutoKeys Macro
        6. Performing a Final Visual Basic Compile
      2. 27. Distributing Your Application
        1. Using Linked Tables in a Desktop Database
          1. Taking Advantage of the Database Splitter Wizard
          2. Creating Startup Code to Verify and Correct Linked Table Connections
        2. Understanding Runtime Mode
        3. Creating a Database Template
        4. Creating Custom Data Type Parts
        5. Creating an Execute-Only Database
        6. Creating an Application Shortcut
        7. Encrypting Your Database
        8. Packaging and Signing Your Database
      3. 28. Article 1 Designing Your Database Application
        1. Application Design Fundamentals
          1. Step 1: Identifying Tasks
          2. Step 2: Charting Task Flow
          3. Step 3: Identifying Data Elements
          4. Step 4: Organizing the Data
          5. Step 5: Designing a Prototype and a User Interface
          6. Step 6: Constructing the Application
          7. Step 7: Testing, Reviewing, and Refining
        2. An Application Design Strategy
          1. Analyzing the Tasks
          2. Selecting the Data
          3. Organizing Tasks
        3. Data Analysis
          1. Choosing the Database Subjects
          2. Mapping Subjects to Your Database
        4. Database Design Concepts
          1. Waste Is the Problem
          2. Normalization Is the Solution
            1. Field Uniqueness
              1. Rule 1: Each field in a table should represent a unique type of information
            2. Primary Keys
              1. Rule 2: Each table must have a unique identifier, or primary key, that is made up of one or more fields in the table
            3. Functional Dependence
              1. Rule 3: For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table
            4. Field Independence
              1. Rule 4: You must be able to make a change to the data in any field (other than to a field in the primary key) without affecting the data in any other field
          3. Efficient Relationships Are the Result
            1. Foreign Keys
            2. One-to-Many and One-to-One Relationships
            3. Creating Table Links
        5. When to Break the Rules
          1. Improving Performance of Critical Tasks
          2. Capturing Point-in-Time Data
          3. Creating Report Snapshot Data
      4. 29. Article 2 Understanding SQL
        1. SQL SELECT Queries
          1. Aggregate Functions: AVG, CHECKSUM_AGG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR, and VARP
          2. BETWEEN Predicate
            1. Syntax
            2. Notes
            3. Example
          3. Column-Name
            1. Syntax
            2. Notes
            3. Examples
          4. Comparison Predicate
            1. Syntax
            2. Notes
            3. Examples
          5. EXISTS Predicate
            1. Syntax
            2. Notes
            3. Example
          6. Expression
            1. Syntax
            2. Notes
            3. Examples
          7. FROM Clause
          8. Syntax
            1. Notes
            2. Example
          9. GROUP BY Clause
            1. Syntax
            2. Notes
            3. Example
          10. HAVING Clause
            1. Syntax
            2. Notes
            3. Example
          11. IN Clause
            1. Syntax
            2. Notes
            3. Example
          12. IN Predicate
            1. Syntax
            2. Notes
            3. Examples
          13. LIKE Predicate
            1. Syntax
            2. Notes
            3. Examples
          14. NULL Predicate
            1. Syntax
            2. Example
          15. ORDER BY Clause
            1. Syntax
            2. Notes
            3. Examples
          16. PARAMETERS Declaration
            1. Syntax
            2. Notes
            3. Example
          17. Quantified Predicate
            1. Syntax
            2. Notes
            3. Examples
          18. Search Condition
            1. Syntax
            2. Notes
            3. Example
          19. SELECT Statement
            1. Syntax
            2. Notes
            3. Examples
          20. Subquery
            1. Syntax
            2. Notes
            3. Examples
          21. TRANSFORM Statement
            1. Syntax
            2. Notes
            3. Example
          22. UNION Query Operator
            1. Syntax
            2. Notes
            3. Example
          23. WHERE Clause
            1. Syntax
            2. Notes
            3. Examples
        2. SQL Action Queries
          1. DELETE Statement
            1. Syntax
            2. Notes
            3. Examples
          2. INSERT Statement (Append Query)
            1. Syntax
            2. Notes
            3. Examples
          3. SELECT . . . INTO Statement (Make-Table Query)
            1. Syntax
            2. Notes
            3. Example
          4. UPDATE Statement
            1. Syntax
            2. Notes
            3. Example
      5. 30. Article 3 Exporting Data
        1. Exporting to Another Access Database
        2. Exporting to a Spreadsheet or to a dBASE File
        3. Exporting to a Text File
        4. Exporting to a Mail Merge Document in Word
        5. Exporting to an ODBC Database
        6. Exporting Data to SharePoint
      6. 31. Article 4 Function Reference
      7. 32. Article 5 Color Names and Codes
      8. 33. Article 6 Macro Actions
    20. About the Authors
    21. Copyright

    Product information

    • Title: Microsoft® Access® 2010 Inside Out
    • Author(s): Jeff Conrad and John Viescas
    • Release date: August 2010
    • Publisher(s): Microsoft Press
    • ISBN: 9780735649132