Table of Contents
Part I The Basics of Access Development
1 Access as a Development Tool
What Types of Applications Can You Develop in Access?
Access as a Development Platform for Personal Applications
Access as a Development Platform for Small Business Applications
Access as a Development Platform for Departmental Applications
Access as a Development Platform for Corporationwide Applications
Access as a Front End for Enterprisewide Client/Server Applications
Access as a Development Platform for Intranet/Internet Applications
Getting to Know the Database Objects
Tables: A Repository for Your Data
Queries: Stored Questions or Actions You Apply to Your Data
Forms: A Means of Displaying, Modifying, and Adding Data
Reports: Turning Data into Information
Macros: A Means of Automating Your System
Modules: The Foundation to the Application Development Process
What Hardware Does Microsoft Office Access 2007 Require?
How Do I Get Started Developing an Access Application?
What’s New in the User Interface?
Getting to Know the Navigation Pane
The Exciting World of Pivot Tables and Pivot Charts
Other New Features Found in Access 2007
Advanced Navigation Pane Techniques
Practical Examples: The Application Design for a Computer Consulting Firm
2 What Every Developer Needs to Know About Databases and Tables
Creating a Database Using a Template
Creating a Database from Scratch
Designing a Table from Scratch
Selecting the Appropriate Field Type for Your Data
Text Fields: The Most Common Field Type
Memo Fields: For Those Long Notes and Comments
Number Fields: For When You Need to Calculate++
Date/Time Fields: Tracking When Things Happened
Currency Fields: Storing Money
AutoNumber Fields: For Unique Record Identifiers
Yes/No Fields: When One of Two Answers Is Correct
OLE Object Fields: The Place to Store Just About Anything
Attachment Fields: Storing Several Files in a Single Field
Hyperlink Fields: Your Link to the Internet
Field Size: Limiting What’s Entered into a Field
Format: Determining How Data Is Displayed
Input Mask: Determining What Data Goes into a Field
Default Value: Saving Data Entry Time
Validation Rule: Controlling What the User Enters in a Field
Validation Text: Providing Error Messages to the User
Required: Making the User Enter a Value
Allow Zero Length: Accommodating Situations with Nonexistent Data
Unicode Compression: Compressing Your Data
Using the All-Important Primary Key
Working with the Lookup Feature
Using Indexes to Improve Performance
Using Access Tables with the Internet
Examining Database Specifications and Limitations
Examining Table Specifications and Limitations
3 Relationships: Your Key to Data Integrity
Introduction to Relational Database Design
The History of Relational Database Design
Goals of Relational Database Design
Rules of Relational Database Design
Normalization and Normal Forms
Denormalization—Purposely Violating the Rules
Examining the Types of Relationships
Establishing Relationships in Access
Establishing a Relationship Between Two Tables
Looking at Guidelines for Establishing Relationships
Modifying an Existing Relationship
Establishing Referential Integrity
Cascade Delete Related Records
Looking at the Benefits of Relationships
Examining Indexes and Relationships
4 What Every Developer Needs to Know About Query Basics
What Is a Query, and When Should You Use One?
Everything You Need to Know About Query Basics
Removing a Field from the Query Design Grid
Inserting a Field After the Query Is Built
Moving a Field to a Different Location on the Query Design Grid
Sorting by More Than One Field
Refining Your Query with Criteria
Working with Dates in Criteria
Understanding How You Can Update Query Results
Building Queries Based on Multiple Tables
Pitfalls of Multitable Queries
Row Fix-Up in Multitable Queries
Getting Help from the Expression Builder
Summarizing Data with Totals Queries
Excluding Fields from the Output
Understanding Nulls and Query Results
Refining Your Queries with Field, Field List, and Query Properties
Field Properties: Changing the Behavior of a Field
Field List Properties: Changing the Properties of the Field List
Query Properties: Changing the Behavior of the Overall Query
Building Parameter Queries When You Don’t Know the Criteria at Design Time
Adding Smart Tags to Your Queries
Creating a Pivot Table or Pivot Chart from a Query
Creating the Query to Display in PivotTable or PivotChart View
Displaying the Query in PivotTable View
Understanding Query Specifications and Limitations
5 What Every Developer Needs to Know About Forms
Understanding the Uses of Forms
Examining the Anatomy of a Form
Creating a Form with the Form Wizard
Creating a Form from Design View
Working with the Form Design Window
Understanding and Working with the Form Design Tools
Selecting, Moving, Aligning, and Sizing Form Objects
Using Stacked and Tabular Layouts
Using Alternating Background Colors for a Form
Selecting the Correct Control for the Job
Morphing a Text Box into a Combo Box
Morphing a Combo Box into a List Box
Determining Which Form Properties Are Available and Why You Should Use Them
Working with the Property Sheet
Working with the Important Form Properties
Determining Which Control Properties Are Available and Why You Should Use Them
Format Properties of a Control
Understanding Bound, Unbound, and Calculated Controls
Using Expressions to Enhance Your Forms
Using the Command Button Wizards: Programming Without Typing
Building Forms Based on More Than One Table
Basing Forms on Queries: The Why and How
Embedding SQL Statements Versus Stored Queries
Connecting Access Forms and the Internet
Adding Smart Tags to Your Forms
Creating a Pivot Table or Pivot Chart from a Form
Creating the Form to Display in PivotTable or PivotChart View
Displaying the Form in PivotTable View
Examining Form Specifications and Limitations
Practical Examples: Designing Forms for Your Application
Adding a Command Button That Links the Clients and Projects Forms
6 What Every Developer Needs to Know About Reports
Examining Types of Reports Available
Reports with Graphics and Charts
Understanding the Anatomy of a Report
Creating a Report with the Report Wizard
Creating a Report from Design View
Working with the Report Design Window
Understanding the Report Design Tools
Selecting, Moving, Aligning, and Sizing Report Objects
Selecting the Correct Control for the Job
What Report Properties Are Available, and Why Should You Use Them?
Working with the Property Sheet
The Report’s Format Properties
What Control Properties Are Available, and Why Should ou Use Them?
The Control’s Format Properties
Using Unbound, Bound, and Calculated Controls
Using Expressions to Enhance Your Reports
Building Reports Based on More Than One Table
Working with Sorting and Grouping
What Are Group Header and Footer Properties, and Why Should You Use Them?
Improving Performance and Reusability by Basing Reports on Stored Queries or Embedded SQL Statements
Using Access Reports and the Internet
Adding a Hyperlink to a Report
Understanding Report Specifications and Limitations
Practical Examples: Building Reports Needed for Your Application
Designing the rptClientListing Report
Designing the rptTimeSheet Report
7 What Are Macros, and When Do You Need Them?
Learning the Basics of Creating and Running a Macro
Running a Macro from the Macro Design Window
Running a Macro from the Macros Group of the Navigation Pane
Triggering a Macro from a Form or Report Event
What New Features Are Available in Macros?
Documenting Your Macro: Adding Comments
Determining When You Should Use Macros and When You Shouldn’t
Converting a Macro to VBA Code
Practical Examples: Adding an AutoExec Macro to the Time and Billing Application
What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules?
Creating Functions and Subroutines
Calling Event and User-Defined Procedures
Scope and Lifetime of Procedures
Scope and Lifetime of Variables: Exposing Your Variablesas Little as Possible
Using the Line Continuation Character
Using the VBA Control Structures
The Conditional If: Conditional Compilation
Passing Parameters and Returning Values
Executing Procedures from the Module Window
The DoCmd Object: Performing Macro Actions
Working with Built-In Functions
Functions Made Easy with the Object Browser
Working with Symbolic Constants
Working with Intrinsic Constants
Working with the Visual Basic Editor Tools
Mysteries of the Coding Environment Solved
The View Microsoft Access Tool
Using Bookmarks to Save Your Place
Code Color, Fonts, and Sizes—The Editor Format Tab
General Options—The General Tab
Docking Options—The Docking Tab
Practical Examples: Using Event Routines, User-Defined Functions, and Subroutines
9 Objects, Properties, Methods, and Events Explained
Understanding Objects, Properties, Events, and Methods
Using the Object Browser to Learn About Access’s Objects
Pasting Code Templates into a Procedure
Working with Properties and Methods
Telling the Difference Between Properties and Methods
Declaring and Assigning Object Variables
Object Variables Versus Regular Variables
Generic Versus Specific Object Variables
Understanding the Differences Between Objects and Collections
Manipulating a Collection of Objects
Passing Objects to Subroutines and Functions
Determining the Type of a Control
Using Special Properties That Refer to Objects
Understanding Access’s Object Model
Taking Advantage of Additional Useful Properties
Practical Examples: Working with Objects
Enabling and Disabling Command Buttons
What Are the Form Events, and When Do You Use Them?
Understanding the Sequence of Form Events
What Are the Section and Control Events, and When Do You Use Them?
Understanding the Sequence of Control Events
What Types of Forms Can I Create, and When Are They Appropriate?
Single Forms: Viewing One Record at a Time
Continuous Forms: Viewing Multiple Records at a Time
Multipage Forms: Finding Solutions When Everything Doesn’t Fit on One Screen
Tabbed Forms: Conserving Screen Real Estate
Switchboard Forms: Controlling Your Application
Splash Screen Forms: Creating a Professional Opening to Your Application
Dialog Forms: Gathering Information
Taking Advantage of Built-In, Form-Filtering Features
Including Objects from Other Applications: Linking Versus Embedding
Switching a Form’s RecordSource
Learning Power Combo Box and List Box Techniques
Adding Items to a Combo Box or List Box at Runtime
Handling Multiple Selections in a List Box
Learning Power Subform Techniques
Using Automatic Error Checking
Synchronizing a Form with Its Underlying Recordset
Creating Custom Properties and Methods
Practical Examples: Applying Advanced Techniques to Your Application
Getting Things Going with a Startup Form
Events Available for Reports and When to Use Them
Events Available for Report Sections and When to Use Them
Programmatically Manipulating Report Sections
Taking Advantage of Special Report Properties
Interaction of MoveLayout, NextRecord, and PrintSection
Using Automatic Error Checking
Incorporating Practical Applications of Report Events and Properties
Changing a Report’s RecordSource
Working with the Report Sort Order
Using the Same Report to Display Summary, Detail, or Both
Determining Where a Label Prints
Building a Report from a Crosstab Query
Printing the First and Last Page Entries in the Page Header
Creating a Multifact Crosstab Report
Practical Examples: Practicing What You Learned
Using Action Queries Versus Processing Records with Code
Viewing Special Query Properties
Understanding the Query Compilation Process
Analyzing a Query’s Performance
Steps You Can Take to Improve a Query’s Performance
Creating a Crosstab Query with the Crosstab Query Wizard
Creating a Crosstab Query Without the Crosstab Query Wizard
Creating Fixed Column Headings
Important Notes About Crosstab Queries
What Is SQL, and Where Did It Come From?
What Do You Need to Know About SQL?
Applying What You Have Learned
Using the Graphical QBE to Create a Union Query
Important Notes about Union Queries
Examining the Propagation of Nulls and Query Results
Using the Result of a Function as the Criteria for a Query
Passing Parameter Query Values from a Form
Understanding Jet 4.0 ANSI-92 Extensions
View and Stored Procedures Extensions
Practical Examples: Applying These Techniques in Your Application
What Are User-Defined Types, and Why Would You Use Them?
Storing Information from a Record in a Form into a Type Variable
Retrieving Information from the Elements of a Type Variable
Working with Intrinsic Constants
Declaring and Working with Fixed Arrays
Declaring and Working with Dynamic Arrays
Understanding Advanced Function Techniques
Passing by Reference Versus Passing by Value
Optional Parameters: Building Flexibility into Functions
Named Parameters: Eliminate the Need to Count Commas
Creating and Working with Custom Collections
Adding Items to a Custom Collection
Accessing an Item in a Custom Collection
Removing Items from a Custom Collection
Iterating Through the Elements of a Custom Collection
Handling Files with Low-Level File Handling
Understanding and Effectively Using Compilation Options
Importing and Exporting Code Modules
Working with Project Properties
Practical Examples: Putting Advanced Techniques to Use
Examples of Null, the DoCmd Object, and Intrinsic Constants
An Example of Using a Type Structure
14 Exploiting the Power of Class Modules
Object Orientation—An Introduction
Creating and Using a Class Module
Instantiating and Using the Class
Property Let and Get—Adding Properties the Right Way
Setting Values with Property Set
Creating Multiple Class Instances
Adding Code to the Initialize and Terminate Events
Building Hierarchies of Classes
Adding a Parent Property to Classes
Working with Custom Collections
Adding Items to a Custom Collection
Looping Through the Elements of a Custom Collection
Referencing Items in a Custom Collection
Removing Items from a Custom Collection
Practical Examples: Using Class Modules
15 What Are ActiveX Data Objects, and Why Are They Important?
Understanding ADO Recordset Types
Consistent Versus Inconsistent Updates
Working with the Supports Method
Working with ADO Recordset Properties and Methods
Examining Record-Movement Methods
Detecting the Limits of a Recordset
Counting the Number of Records in a Recordset
Sorting, Filtering, and Finding Records
Using the AbsolutePosition Property
Working with Persisting Recordsets
Modifying Table Data Using ADO Code
Changing Record Data One Record at a Time
Creating and Modifying Database Objects Using ADO Code
Establishing Relationships Using Code
Practical Examples: Applying These Techniques to Your Application
Using Recordset Methods on a Data-Entry Form
Part II What to Do When Things Don’t Go as Planned
16 Debugging: Your Key to Successful Development
Harnessing the Power of the Immediate Window
Testing Values of Variables and Properties
Setting Values of Variables and Properties
Practicing with the Built-In Functions
Executing Subroutines, Functions, and Methods
Printing to the Immediate Window at Runtime
Using Breakpoints to Troubleshoot
Executing Until You Reach the Next Breakpoint
Setting the Next Statement to Execute
Working with the Locals Window
Working with Watch Expressions
Breaking When an Expression Is True
Breaking When an Expression Changes
Continuing Execution After a Runtime Error
Looking at Gotchas with the Immediate Window
Practical Examples: Debugging Real Applications
17 Error Handling: Preparing for the Inevitable
The On Error Resume Next Statement
The Resume <LineLabel> Statement
Examining the Cascading Error Effect
Generating an Error on Purpose
Creating a Generic Error Handler
Determining the Appropriate Response to an Error
Preventing Your Own Error Handling from Being Invoked
Building a Custom Error Handler Class
Creating a List of Error Codes and Descriptions
Practical Examples: Incorporating Error Handling
18 Optimizing Your Application
Modifying Hardware and Software Configurations
Hardware, Hardware, More Hardware, Please!
Change Access’s Software Settings
What Is the Access Database Engine?
Letting the Performance Analyzer Determine Problem Areas
Designing Tables to Optimize Performance
I Thought You Just Told Me to Normalize
Designing Database Objects to Improve Performance
Optimizing the Performance of Your Queries
Making Coding Changes to Improve Performance
Designing Forms and Reports to Improve Performance
Practical Examples: Improving the Performance of Your Applications
Part III Developing Multiuser and Enterprise Applications
19 A Strategy to Developing Access Applications
Splitting Databases into Tables and Other Objects
Basing Forms and Reports on Queries or Embedded SQL Statements
Preparing an Application for Distribution
Basing Your Application Around Forms
Adding Startup Options to Your Database
Building Error Handling into Your Application
Factors You Need to Worry About When Converting to Client/Server
Benefits and Costs of Client/Server Technology
Your Options When Using Access as a Front End
What Are the Considerations for Migrating to a Client/Server Environment?
Practical Examples: Applying the Strategy to the Computer Consulting Firm Application
Importing, Linking, and Opening Files: When and Why
Looking at Supported File Formats
Creating a Link to External Data
Providing Connection Information
Understanding Windows Registry Settings
Using the Jet OLEDB:Link Provider String
Updating Links That Have Moved
Making a Local Table from a Linked Table
Looking at Special Considerations
Looking at Performance Considerations and Links
Practical Examples: Working with External Data from Within Your Application
Splitting the Database Using the Database Splitter
Why This Chapter Is Important?
Exporting Selected Data to a SharePoint Site
Moving an Entire Database to a SharePoint Site
Opening Access Forms and Reports from a SharePoint Site
Creating Databases from SharePoint Lists
Taking SharePoint Lists Offline with Access
Summary of Benefits of Working with SharePoint
The Access 2007 (accdb) File Format and SharePoint
Exporting Data to a SharePoint Site
Why Export Data to a SharePoint Site?
How to Export Data to a SharePoint Site
Publishing Data to a SharePoint Site
How the Wizard Moves Data to the SharePoint Site
Opening Access Forms and Reports from a SharePoint Site
Linking to and Importing from SharePoint Lists
Taking SharePoint Lists Offline with Access
Synchronizing Your Changes with SharePoint
Reestablishing Links When a SharePoint Site Has Been Moved
22 Developing Multiuser and Enterprise Applications
Designing Your Application with Multiuser Issues in Mind
The Basics of Linking to External Data
Understanding Access’s Locking Mechanisms
Understanding the Client/Server Model
Deciding Whether to Use the Client/Server Model
Dealing with a Large Volume of Data
Dealing with a Large Number of Concurrent Users
Handling Increased Network Traffic
Implementing Backup and Recovery
Sharing Data Among Multiple Front-End Tools
Understanding What It All Means
Understanding the Roles That Access Plays in the Application Design Model
The Front End and Back End as Access ACCDB Files
The Front End as an ACCDB File Using Links to Communicate to a Back End
The Front End Using SQL Pass-Through to Communicate to a Back End
The Front End Executing Procedures Stored on a Back End
The Front End as a Microsoft Access Data Project Communicating Directly to a Back End
Learning the Client/Server Buzzwords
Proactively Preparing for Upsizing
Understanding the Benefits of Transaction Processing
Modifying the Default Behavior of Transaction Processing
Implementing Explicit Transaction Processing
Practical Examples: Getting Your Application Ready for an Enterprise Environment
Part IV Black Belt Programming
23 Working with and Customizing Ribbons
Customizing the Ribbon: An Overview
Enabling the Display of System Errors
Creating the USysRibbons Table
Adding Data to the USysRibbons Table
Applying a Custom Ribbon to the Entire Database
Applying a Custom Ribbon to a Form or Report
Restoring the Ribbon to Its Default Settings
Adding Additional Groups and Controls
Executing a Macro from the Ribbon
Practical Examples: Securing an Access 2007 Database
24 Automation: Communicating with Other Applications
Defining Some Automation Terms
Declaring an Object Variable to Reference Your Application
Manipulating an Automation Object
Setting and Retrieving Properties
Using Early Binding Versus Late Binding
Closing an Excel Automation Object
Using Word to Generate a Mass Mailing
Using Word to Overcome the Limitations of Access as a Report Writer
Controlling PowerPoint from Access
Automating Outlook from Access
Controlling Access from Other Applications
Practical Examples: Using Automation to Extend the Functionality of Your Applications
25 Exploiting the Powerof the Windows API
Declaring an External Function to the Compiler
Passing Parameters to DLL Functions
Working with Constants and Types
Calling DLL Functions: Important Issues
Manipulating the Windows Registry
Getting Information About the Operating Environment
Determining Drive Types and Available Drive Space
Practical Examples: Using Windows API Functions in Your Applications
26 Creating Your Own Libraries
Preparing a Database to Be a Library
Structuring Code Modules for Optimal Performance
Writing Library Code That Runs
Creating an Explicit Reference
Creating a Reference Using VBA Code
Practical Examples: Building a Library for Your Application
Getting the Wizard Ready to Go
Practical Examples: Designing Your Own Add-Ins
28 An Introduction to Access and the Internet/Intranet
Saving Database Objects as HTML
Saving Database Objects as XML
Part V Adding Polish to Your Application
29 Documenting Your Application
Preparing Your Application to Be Self-Documenting
Using Database Properties to Document the Overall Database
Producing Documentation in Other Formats
Using the Object Dependency Feature
Writing Code to Create Your Own Documentation
Practical Examples: Applying What You Learned
30 Maintaining Your Application
Compacting Whenever a Database Closes
Using the CompactDatabase Method of the JetEngine Object
Using the CompactRepair Method of the Application Object
Practical Examples: Maintaining Your Application
What’s New in Access 2007 Security?
What Happened to User-Level Security?
Trusting a Database for the Current Session
Trusting a Database Permanently
Using a Database Password to Encrypt an Office Access 2007 Database
Removing a Password from a Database
Packaging, Signing, and Distributing an Access Database
Creating a Self-Signed Certificate
Extracting and Using a Signed Package
Working with Access Macros and VBA Code
Working with Trusted Locations
Working with Trusted Publishers
Understanding How Databases Behave When Trusted and Untrusted
Enabling or Disabling ActiveX Controls
Practical Examples: Securing an Access 2007 Database