You are previewing Access 2002 Programming by Example.
O'Reilly logo
Access 2002 Programming by Example

Book Description

This book addresses an unmet need in this topic area. It is a complete beginner's tutorial resource to tips, troubleshooting, techniques, and program development for Access 2002. The book covers fundamentals such as database normalization, query design, object manipulation, and optimization and custom programming for forms and reports. It makes the reader more efficient, productive, and knowledgeable in Access VBA. Speed techniques are mentioned. Shortcuts, tips, and techniques increase productivity. This book deals with getting data from outside sources and using VBA to ensure data integrity and consistency, which no other Access VBA book does. It also tackles problems in a variety of day-to-day situations that programmers often face.

Bob Villareal is a contributor and a resident expert for the "Inside Microsoft Access" Web journal and an Access instructor at a Tulsa Community College. He also does freelance programming and instruction. Bob has been developing databases in a large insurance firm for more than 15 years. He has more than seven years' experience of VBA programming in both Access and Excel and has written many tracking and management applications.

Table of Contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. Tell Us What You Think!
  5. Introduction
  6. Tables and Queries
    1. Planning and Designing Your Access Database
      1. Consider Your Data Before Designing Your Database
      2. Why Relational Tables?
      3. Database Design and Normalization
      4. Normalizing in Access
      5. What’s Next
    2. Joins and Cascades
      1. Referential Integrity
      2. Cascades
      3. What’s Next
    3. The Table—The Heart of Any Database
      1. Fields—The Building Blocks of Tables
      2. Access 2002 Data Types
      3. Testing and Using Custom Formats
      4. Building an Access 2002 Table
      5. Entering Data
      6. Explanation of Limitations
      7. The Table Is the Center
      8. What’s Next
    4. Unleashing the Power of Queries
      1. Queries Ask Questions
      2. Queries Access Records
      3. The Query Grid
      4. Understanding Query Types
      5. Introduction to SQL
      6. Understanding “Ands” and “Ors” in the Query Grid
      7. Understanding Wildcards
      8. Understanding Grouping and Sorting
      9. Understanding Dynasets and Their Underlying Tables
      10. Dynasets Versus Snapshots for Query Optimization
      11. What’s Next
  7. Forms for Input and Reports for Output
    1. Exploring Forms and Controls
      1. Forms Versus Reports
      2. Defining Events
      3. Using a Wizard to Help Design Your Form
      4. Examining Your Form’s Properties
      5. Changing the Form’s Color
      6. Introduction to Controls
      7. How to Control a Control
      8. Creating a Calculated Control
      9. Another Way to Create Calculated Controls
      10. Creating a Command Button
      11. Dealing with the Underlying Records
      12. Designing a Relational Form
      13. What’s Next
    2. Exploring Reports
      1. Reports Versus Spreadsheets
      2. Report Basics
      3. Creating a Report from Scratch
      4. Taking Charge of Report Controls
      5. Setting Up Spreadsheet-like Calculations in Report Controls
      6. Taking Charge of Grouping and Sorting
      7. Understanding Sections
      8. The On Open Event
      9. Working with the Record Source Property
      10. Designing a Relational Report Using a Wizard
      11. Conditional Formatting
      12. Creating a Group of Controls
      13. What’s Next
  8. Automate Your Access Database Using Code
    1. Exploring Objects
      1. Defining Objects
      2. Defining Object Properties
      3. Object Models
      4. Setting a Reference in the Reference Library
      5. The Power of Controls Revisited
      6. Three Sources
      7. Lookup List
      8. What’s Next
    2. Writing Your Own Visual Basic for Applications Code
      1. Function Procedures, Sub Procedures, and Modules
      2. Access String Functions
      3. The Access Immediate Window
      4. Using Arrays with String Functions
      5. What’s Next
    3. Macros, Modules, and Messages
      1. Visual Basic Versus Macros
      2. Printing the Current Record Using a Macro
      3. What’s Next
    4. Using Procedures to Customize Your Database
      1. Exploring Procedures
      2. Functions Versus Subs
      3. What Is Scope?
      4. Creating Procedures
      5. More Than One Way to Loop
      6. More than One Way to Handle Conditions
      7. Using Functions to Fulfill Your Wish List of Features Access Forgot
      8. Using Functions to Customize Your Applications
      9. Using the Immediate Window to Test Your Functions
      10. Using Functions for Pattern Matching in Queries
      11. What’s Next?
    5. Handling Access Error Codes
      1. What To Do When Errors Occur
      2. Determining which Errors Occur
      3. Error-Checking Sub Routines
      4. Fixing Errors After Trapping Them
      5. Resume Statements
      6. The Err.Raise Method
      7. What’s Next
    6. Access Visual Basic Tools, Tips, and Techniques
      1. Comparing DAO and ADO
      2. Using VBA to Run Queries
      3. Manipulating Recordsets Using Collections Versus Manipulating Recordsets Using Queries
      4. DAO Collections and Objects
      5. ADO Collections and Objects
      6. Cursors and Cursor Types
      7. How to Use DAO and ADO to Manipulate Data
      8. How to Use DAO and ADO to Enumerate Objects
      9. Creating a List of Objects
      10. Creating a List of Fields
      11. What’s Next
  9. Taking Advantage of the Latest Access Features
    1. Publishing Your Access Database
      1. Converting Objects to Web Pages
      2. Introduction to Data Access Pages
      3. Creating a Data Access Page
      4. Active Server Pages
      5. Hyperlinks in Access Objects
      6. What’s Next
    2. SQL Server Integration
      1. MSDE Upgraded
      2. Comparing MSSQL Server 2000 Desktop Engine to Jet
      3. Installing and Starting the Server
      4. Installing a Sample Northwind Application
      5. Exploring Your Northwind Project
      6. What’s Next
    3. User-Friendly Enhancements
      1. The Paradox of Databases
      2. Compact Improvements
      3. Converting from Previous Versions of Access
      4. Sharing and Integrating with Other Applications
      5. What’s Next
  10. Overcoming Access Development Obstacles
    1. Overcoming the Limitations of Queries
      1. Query Challenges
      2. Using Query by Table Example for If Then Scenarios
      3. Using Subqueries to Unleash the Power Of Queries
      4. Using Functions with Queries, Revisited
      5. What’s Next?
    2. Getting the Most from Your Queries
      1. Making Your Database More Efficient
      2. Query Optimization
      3. Queries Versus Filters
      4. Special SQL Specific Queries
      5. What’s Next
    3. Working with Data from External Sources
      1. Using Automation to Meet Performance Demands
      2. Importing and Exporting Data
      3. Scalability
      4. What’s Next
    4. Bringing Together and Separating Data
      1. The Art of Concatenation
      2. The Art of Parsing
      3. What’s Next
    5. Conquering Big Problems that Come In Small Packages
      1. Using Quotes and Pounds
      2. Testing Quotes and Pounds
      3. Controlling Nulls
      4. Conquering Dates
      5. What’s Next
    6. Taking Charge of Forms
      1. Using a Parameterized Query As a Form’s Record Source
      2. Pop-Up Forms Go Beyond the Message Box
      3. How to Tell Whether a Form Is Open
      4. Go Multidimensional with PivotTables
      5. Using Forms to Control Editing and Save Records
      6. Using Forms to Optimize Large Tables
      7. Tab Control Benefits
      8. What’s Next
    7. Taking Charge of Reports
      1. Handling Grouping and Sorting Programmatically
      2. Using Table Relationships to Include or Preclude Entire Sections of Report Data
      3. What to Do with Pesky Blank Lines
      4. Multicolumn Reports
      5. Print Extra Copies
      6. What’s Next
    8. Using Replication
      1. Database Replication
      2. How Replication Works
      3. What’s Next