Table of Contents
Part I:Access Building Blocks
Chapter 1:An Introduction to Database Development
The Database Terminology of Access
Databases
Tables
Records and fields
Values
Relational Databases
Access Database Objects
Datasheets
Queries
Data-entry and display forms
Reports
Database objects
A Five-Step Design Method
Step 1: The overall design — from concept to reality
Step 2: Report design
Step 3: Data design
Step 4: Table design
Step 5: Form design
Summary
Chapter 2:Creating Access Tables
Getting Started with Access
The Templates section
The Office Backstage View
Creating a Database
The Access 2010 Environment
The Navigation Pane
The ribbon
Other relevant features of the Access environment
Creating a New Table
Designing tables
Using the Design tab
Working with fields
Creating tblCustomers
Using AutoNumber fields
Completing tblCustomers
Changing a Table Design
Inserting a new field
Deleting a field
Changing a field location
Changing a field name
Changing a field size
Handling data conversion issues
Assigning field properties
Understanding tblCustomers Field Properties
Setting the Primary Key
Choosing a primary key
Creating the primary key
Creating composite primary keys
Indexing Access Tables
The importance of indexes
Multiple-field indexes
When to index tables
Printing a Table Design
Saving the Completed Table
Manipulating Tables
Renaming tables
Deleting tables
Copying tables in a database
Copying a table to another database
Adding Records to a Database Table
Understanding Attachment Fields
Summary
Chapter 3:Designing Bulletproof Databases
Building Bulletproof Databases
Data Normalization
First normal form
Second normal form
Third normal form
Denormalization
Table Relationships
Connecting the data
One-to-one
One-to-many
Many-to-many
Integrity Rules
No primary key can contain a null value
All foreign key values must be matched by corresponding primary keys
Keys
Deciding on a primary key
The benefits of a primary key
Designating a primary key
Creating relationships and enforcing referential integrity
Viewing all relationships
Deleting relationships
Application-specific integrity rules
Summary
Chapter 4:Selecting Data with Queries
Introducing Queries
What queries are
Types of queries
What queries can do
What queries return
Creating a Query
Adding Fields
Adding a single field
Adding multiple fields
Displaying the Recordset
Working with Fields
Selecting a field in the QBE grid
Changing field order
Resizing columns in the QBE grid
Removing a field
Inserting a field
Providing an alias for the field name
Showing a field
Changing the Sort Order
Displaying Only Selected Records
Understanding selection criteria
Entering simple string criteria
Entering other simple criteria
Printing a Query's Recordset
Saving a Query
Creating Multi-Table Queries
Viewing table names
Adding multiple fields
Multi-table query limitations
Overcoming query limitations
Working with the Table Pane
Looking at the join line
Manipulating Field Lists
Moving a table
Removing a table
Adding more tables
Creating and Working with Query Joins
Using ad hoc table joins
Specifying the type of join
Deleting joins
Understanding Table Join Types
Working with inner joins (equi-joins)
Understanding outer joins
Working with self-joins
Creating a Cartesian product
Summary
Chapter 5:Using Operators and Expressions in Access
Introducing Operators
Looking at the types of operators
Operator precedence
Using Operators and Expressions in Queries
Using query comparison operators
Understanding complex criteria
Using functions in select queries
Referencing fields in select queries
Entering Single-Value Field Criteria
Entering character (Text or Memo) criteria
The Like operator and wildcards
Specifying non-matching values
Entering numeric criteria
Entering true or false criteria
Entering OLE object criteria
Using Multiple Criteria in a Query
Understanding an Or operation
Specifying multiple values with the Or operator
Using the Or cell of the QBE pane
Using a list of values with the In operator
Using And to specify a range
Using the Between...And operator
Searching for Null data
Entering Criteria in Multiple Fields
Using And and Or across fields in a query
Specifying Or criteria across fields of a query
Using And and Or together in different fields
A complex query on different lines
Summary
Chapter 6:Working with Datasheet View
Understanding Datasheets
Looking at the Datasheet Window
Moving within a datasheet
Using the Navigation buttons
Examining the Datasheet ribbon
Opening a Datasheet
Entering New Data
Saving the record
Understanding automatic data-type validation
Knowing how properties affect data entry
Navigating Records in a Datasheet
Moving between records
Finding a specific value
Changing Values in a Datasheet
Manually replacing an existing value
Changing an existing value
Using the Undo Feature
Copying and Pasting Values
Replacing Values
Adding New Records
Deleting Records
Displaying Records
Changing the field order
Changing the field display width
Changing the record display height
Changing display fonts
Displaying cell gridlines and alternate row colors
Aligning data in columns
Hiding and unhiding columns
Freezing columns
Saving the changed layout
Saving a record
Sorting and Filtering Records in a Datasheet
Sorting your records with QuickSort
Filtering a selection
Filtering by form
Focusing on Special Features of Datasheets
Printing Records
Printing the datasheet
Using the Print Preview window
Summary
Chapter 7:Creating Basic Access Forms
Formulating Forms
Creating a new form
Looking at special types of forms
Resizing the form area
Saving your form
Working with Controls
Categorizing controls
Adding a control
Selecting and deselecting controls
Manipulating controls
Introducing Properties
Displaying the Property Sheet
Getting acquainted with the Property Sheet
Changing a control's property setting
Naming control labels and their captions
Summary
Chapter 8:Working with Data on Access Forms
Using Form View
Looking at the Home ribbon tab
Navigating among fields
Moving among records in a form
Changing Values in a Form
Knowing which controls you can't edit
Working with pictures and OLE objects
Entering data in the Memo field
Entering data in the Date field
Using option groups
Using combo boxes and list boxes
Switching to Datasheet view
Saving a record
Printing a Form
Working with Form Properties
Changing the title bar text with the Caption property
Creating a bound form
Specifying how to view the form
Removing the Record Selector
Looking at other form properties
Adding a Form Header or Footer
Changing the Layout
Changing a control's properties
Setting the Tab Order
Aligning controls
Modifying the format of text in a control
Using the Field List to add controls
Creating a Calculated Control
Converting a Form to a Report
Summary
Chapter 9:Presenting Data with Access Reports
Introducing Reports
Identifying the different types of reports
Distinguishing between reports and forms
Creating a Report, from Beginning to End
Defining the report layout
Assembling the data
Creating a report with the Report Wizard
Printing or viewing the report
Saving the report
Banded Report Design Concepts
The Report Header section
The Page Header section
The Group Header section
The Detail section
The Group Footer section
The Page Footer section
The Report Footer section
Creating a Report from Scratch
Creating a new report and binding it to a query
Defining the report page size and layout
Placing controls on the report
Resizing a section
Working with text boxes
Changing label and text-box control properties
Growing and shrinking text-box controls
Sorting and grouping data
Sorting data within groups
Adding page breaks
Improving the Report's Appearance
Adjusting the page header
Creating an expression in the group header
Enhancing the Detail section
Creating a report header
Summary
Part II:Programming Microsoft Access
Chapter 10:VBA Programming Fundamentals
Introducing Visual Basic for Applications
Understanding VBA Terminology
Starting with VBA Code Basics
Migrating from Macros to VBA
Knowing when to use macros and when to use VBA
Converting your existing macros to VBA
Using the Command Button Wizard
Creating VBA Programs
Events and event procedures
Modules
Understanding VBA Branching Constructs
Branching
Looping
Working with Objects and Collections
An object primer
The With statement
The For Each statement
Looking at Access Options for Developers
The Editor tab of the Options dialog box
The Project Properties dialog box
Command-line arguments
Summary
Chapter 11:Mastering VBA Data Types and Procedures
Introducing the Access VBA Editor
Using Variables
Naming variables
Declaring variables
Working with Data Types
Comparing implicit and explicit variables
Forcing explicit declaration
Using a naming convention
Understanding variable scope and lifetime
Understanding Subs and Functions
Understanding where to create a procedure
Calling VBA procedures
Creating subs
Creating Functions
Handling parameters
Calling a function and passing parameters
Creating a function to calculate sales tax
Simplifying Code with Named Arguments
Summary
Chapter 12:The Access Event Model
Programming Events
Understanding how events trigger VBA code
Creating event procedures
Identifying Common Events
Form event procedures
Control event procedures
Report event procedures
Report section event procedures
Paying Attention to Event Sequence
Looking at common event sequences
Writing simple form and control event procedures
Summary
Chapter 13:Accessing Data with VBA Code
Understanding SQL
Viewing SQL statements in queries
A SQL primer
Working with Data
Understanding ADO Objects
The ADO Connection object
The ADO Command object
The ADO Recordset object
Understanding DAO Objects
The DAO DBEngine object
The DAO Workspace object
The DAO Database object
The DAO TableDef object
The DAO QueryDef object
The DAO Recordset object
The DAO Field objects (recordsets)
Writing VBA Code to Update a Table
Updating fields in a record using ADO
Updating a calculated control
Adding a new record
Deleting a record
Deleting related records in multiple tables
Summary
Chapter 14:Debugging Your Access Applications
Testing Your Applications
Using the Module Options
Auto Syntax Check
Require Variable Declaration
Auto List Members
Auto Quick Info
Auto Data Tips
Break on All Errors
Compile on Demand
Organizing VBA Code
Compiling VBA Code
Traditional Debugging Techniques
Using MsgBox
Using Debug.Print
Using the Access Debugging Tools
Running code with the Immediate window
Suspending execution with breakpoints
Looking at variables with the Locals window
Setting watches with the Watches window
Using conditional watches
Using the Call Stack window
Summary
Chapter 15:Using Access Data Macros
Introducing Data Macros
Looking at How Data Macros Are Created
Using the Macro Designer
Using the Action Catalog
Discovering Table Events
Before events
After events
Building Macros
Adding macro items
Manipulating macro items
Moving macro items
Collapsing macro items
Saving a macro as XML
Recognizing the Limitations of Data Macros
Summary
Part III:More-Advanced Access Techniques
Chapter 16:Working with External Data
Looking at How Access Works with External Data
Types of external data
Ways of working with external data
Linking External Data
Linking to external database tables
Limitations of linked data
Linking to other Access database tables
Linking to ODBC data sources
Linking to xBase files
Linking to non-database data
Working with Linked Tables
Setting view properties
Setting relationships
Optimizing linked tables
Deleting a linked table reference
Viewing or changing information for linked tables
Using Code to Link Tables in Access
The Connect and SourceTableName properties
Checking links
Summary
Chapter 17:Importing and Exporting Data
Looking at Your Options for Importing and Exporting
Importing External Data
Importing from another Access database
Importing from an Excel spreadsheet
Importing a SharePoint list
Importing data from text files
Importing an XML document
Importing an HTML document
Importing Access objects other than tables
Importing an Outlook folder
Importing dBase tables
Troubleshooting import errors
Exporting to External Formats
Exporting objects to other Access databases
Exporting through ODBC drivers
Functionality exclusive to exports
Summary
Chapter 18:Advanced Access Query Techniques
Using Calculated Fields
Calculated Fields and the Expression Builder
Counting Records in a Table or Query
Finding the Top (n) Records in a Query
How Queries Save Field Selections
Hiding (not showing) fields
Renaming fields in queries
Query Design Options
Setting Query Properties
Creating Queries That Calculate Totals
Showing and hiding the Total row in the QBE pane
The Total row options
Performing totals on all records
Performing totals on groups of records
Specifying criteria for a total query
Creating expressions for totals
Creating Crosstab Queries
Understanding the crosstab query
Creating the crosstab query
Understanding Action Queries
Types of action queries
Creating action queries
Troubleshooting action queries
Summary
Chapter 19:Advanced Access Form Techniques
Setting Control Properties
Customizing default properties
Manipulating controls at runtime
Reading control properties
Working with Subforms
Designing Forms
Using the Tab Stop property
Tallying check boxes
Adding animation
Using SQL for a faster refresh
Selecting data for overtyping
Toggling properties with Not
Creating an auto-closing form
Setting up combo boxes and list boxes
Determining whether a form is open
Tackling Advanced Forms Techniques
Using the Page Number and Date/Time controls
Using the Image control
Morphing a control
Using the Format Painter
Offering more end-user help
Adding background pictures
Fine-tuning your form's behavior with form events
Using the Tab Control
Using Dialog Boxes to Collect Information
Composing the SQL statement
Adding a default button
Setting a Cancel button
Removing the control menu
Closing the form
Summary
Chapter 20:Advanced Access Report Techniques
Grouping and Sorting Data
Grouping data alphabetically
Grouping on date intervals
Hiding repeating information
Hiding a page header
Starting a new page number for each group
Formatting Data
Creating numbered lists
Adding bullet characters
Adding emphasis at runtime
Avoiding empty reports
Avoiding null values in a tabular report
Inserting vertical lines between columns
Adding a blank line every n records
Even-odd page printing
Using different formats in the same text box
Centering the title
Easily aligning control labels
Micro-adjusting controls
Adding Data
Adding more information to a report
Adding the user's name to a bound report
Trying More Techniques
Displaying all reports in a combo box
Fast printing from queried data
Hiding forms during Print Preview
Using snaking columns in a report
Exploiting two-pass report processing
Assigning unique names to controls
Summary
Chapter 21:Building Multiuser Applications
Working on a Network
Network performance
File location
Data sources
Considering the Options for Opening a Database
Splitting a Database for Network Access
Detailing the benefits of splitting a database
Knowing where to put which objects
Using the Database Splitter add-in
Finding the Key to Locking Issues
Access's built-in record-locking features
Record-lock error handling
Reducing Multiuser Errors with Unbound Forms
Creating an unbound form
Making an unbound form work
Summary
Chapter 22:Integrating Access with Other Applications
Using Automation in Access
Understanding how Automation works
Creating Automation references
Binding your VBA object variables to objects in the Automation interface
Creating an instance of an Automation object
Getting an existing object instance
Working with Automation objects
Closing an instance of an Automation object
Looking at an Automation Example Using Word
Creating an instance of a Word object
Making the instance of Word visible
Creating a new document based on an existing template
Inserting data
Activating the instance of Word
Moving the cursor in Word
Discarding the Word object instance
Inserting pictures by using bookmarks
Using Office's macro recorder
Collecting Data with Microsoft Outlook
Creating an e-mail
Managing replies
Summary
Chapter 23:Handling Errors and Exceptions
Dealing with Errors
Logical errors
Runtime errors
Identifying Which Errors Can Be Detected
What an error handler is
How to set a basic error trap
Trapping Errors with VBA
The Err object
VBA error-handling statements
The Error event
The ADO Errors collection
Summary
Part IV:Professional Database Development
Chapter 24:Optimizing Access Applications
Understanding Module Load on Demand
Organizing your modules
Pruning the call tree
Using the .accdb Database File Format
Distributing .accde Files
Understanding the Compiled State
Putting your application's code into a compiled state
Losing the compiled state
Distributing applications in a compiled or uncompiled state
Improving Absolute Speed
Tuning your system
Getting the most from your tables
Getting the most from your queries
Getting the most from your forms and reports
Getting the most from your modules
Increasing network performance
Improving Perceived Speed
Using a splash screen
Loading and keeping forms hidden
Using the hourglass
Using the built-in progress meter
Creating a progress meter as a pop-up form
Speeding up the progress meter display
Working with Large Access Databases
Understanding how databases grow in size
Recognizing that compiling and compacting may not be enough
Using the decompile option
Detecting an uncompiled database and automatically recompiling
Making small changes to large databases
Summary
Chapter 25:Advanced Data Access with VBA
Adding an Unbound Combo Box to a Form to Find Data
Using the FindRecord method
Using a bookmark
Filtering a Form
With code
With a query
Summary
Chapter 26:Bulletproofing Access Applications
Introducing Bulletproofing
Looking at the Characteristics of Bulletproofed Applications
Identifying the Principles of Bulletproofing
Building to a specification
Becoming one with documentation
Considering your users
Getting the application to the users
Enabling the users to actually use the application
Controlling the flow of information
Keeping the user informed
Tracking down problems
Securing the environment
Protecting your database
Continuing to improve the product
Summary
Chapter 27:Using the Windows Application Programming Interface
What the Windows API Is
Reasons to Use the Windows API
Common code base
Tested and proven code
Cross-platform compatibility
Smaller application footprint
Application consistency
DLL Documentation
Finding the documentation
Making sense of the documentation
Recognizing what you can't do with the API
How to Use the Windows API
The Declare statement
Wrapper functions
API Examples
Retrieving system information
Going over general-purpose Windows API functions
Manipulating application settings with the Windows API
Summary
Chapter 28:Object-Oriented Programming with VBA
Introducing Object-Oriented Programming
Getting to know objects
Defining objects with class modules
Looking at a simple class module
Adding a class module to a database
Creating simple product properties
Creating methods
Using the product object
Creating bulletproof property procedures
Recognizing the Benefits of Object-Oriented Programming
Encapsulating functionality
Simplifying programming tasks
Managing a class's interface
Using Property Procedures
Looking at the types of property procedures
Exploring property-value persistence
Heeding property procedure rules
Modifying the Product Class
Retrieving product details
Looking at the new ProductID property
Adding a new property to provide extra information
Adding a new method to the product class
Learning about Class Events
The Class_Initialize event procedure
The Class_Terminate event procedure
Adding Events to Class Modules
Learning about events in Access
Recognizing the need for events
Creating custom events
Raising events
Trapping custom events
Passing data through events
Exploiting Access class module events
Summary
Chapter 29:Customizing Access Ribbons
Why Replace Toolbars and Menus?
New controls for Access ribbons
SplitButton
DropDown
Gallery
SuperTips
Working with the Access Ribbon
Tabs
Groups
Controls
Managing the ribbon
Working with the Quick Access Toolbar
Editing the Default Access Ribbon
Developing Custom Access Ribbons
The ribbon creation process
Using VBA callbacks
The Ribbon Hierarchy
Getting Started with Access Ribbons
Step 1: Design the ribbon and build the XML
Step 2: Write the callback routines
Step 3: Create the USysRibbons table
Step 4: Add XML to USysRibbons
Step 5: Specify the custom ribbon property
The Basic Ribbon XML
Adding Ribbon Controls
Specifying imageMso
The Label control
The Button control
Separators
Check boxes
The DropDown control
The SplitButton Control
Using Visual Web Developer
Managing Ribbons
Completely Removing the Access Ribbon
Summary
Chapter 30:Using Access Macros
An Introduction to Macros
Creating a macro
Assigning a macro to an event
Multi-Action Macros
Submacros
Conditions
Opening reports using conditions
Multiple actions in conditions
Temporary Variables
Enhancing a macro you've already created
Using temporary variables to simplify macros
Using temporary variables in VBA
Error Handling and Macro Debugging
The OnError action
The MacroError object
Debugging macros
Embedded Macros
Macros versus VBA Statements
Choosing between macros and VBA
Converting existing macros to VBA
Summary
Chapter 31:Distributing Access Applications
Defining the Current Database Options
Application options
Navigation options
Ribbon and toolbar options
Name AutoCorrect Options
Testing the Application before Distribution
Polishing Your Application
Giving your application a consistent look and feel
Adding common professional components
Bulletproofing an Application
Using error trapping on all Visual Basic procedures
Separating tables from the rest of the application
Documenting the application
Summary
Part V:Access and Windows SharePoint Services
Chapter 32:Understanding Windows SharePoint Services
Introducing SharePoint
Reviewing Various Types of SharePoint Sites
Working with SharePoint Lists
Looking at a SharePoint Web Site
Editing SharePoint list items
Creating SharePoint lists
Summary
Chapter 33:Integrating Access with SharePoint
Introducing SharePoint as a Data Source
Sharing Access Data with SharePoint
Linking to SharePoint lists
Exporting Access tables to SharePoint
Moving Access tables to SharePoint
Using SharePoint Templates
Summary
Chapter 34:Understanding Access Services
Explaining Managed Applications
Looking at Web Publishing in Access
Why SharePoint?
Leveraging SharePoint features
Publishing Access applications to SharePoint
Understanding Access Services
Examining Access Web Application Limits
Not public-facing
Fewer than 20,000 rows of data
Modest transactional requirements
Summary
Chapter 35:Deploying Access Applications to SharePoint
Looking at SharePoint Deployment Options
Enhanced table exporting option
Publishing to SharePoint option
Dealing with Compatibility Checker Problems
General errors
Relationship and lookup errors
Form and report errors
Query errors
Macro errors
Schema errors
Summary
Part VI:Access as an Enterprise Platform
Chapter 36:Client/Server Concepts
Looking at the Parts of Client/Server Architecture
Applications
The back office
Making Sense of Multi-Tier Architecture
Two-tier systems
Three-tier systems
Putting It All Together: Access, Client-Server, and Multiple Tiers
Access as a database repository
Access as an Internet database
Summary
Chapter 37:SQL Server as an Access Companion
Connecting to SQL Server
Introducing connection strings
Connecting to SQL Server from Access
Working with SQL Server Objects
Using SQL Server tables from Access
Views
Stored procedures
Triggers
Summary
Chapter 38:Upsizing Access Databases to SQL Server
Introducing SQL Server Express
Upsizing Access and the Upsizing Wizard
Before upsizing an application
Running the Upsizing Wizard
Working with an Access ADP file
Comparing Access to SQL Server data types
Summary
Part VII:Appendixes
Appendix A:Access 2010 Specifications
Appendix B:What's New in Access 2010
Appendix C:What's on the CD-ROM
Example files
eBook version of Access 2010 Bible
Download CD/DVD Content
