You are previewing Sams Teach Yourself Microsoft Access 2002 in 21 Days.
O'Reilly logo
Sams Teach Yourself Microsoft Access 2002 in 21 Days

Book Description

This book is a hands-on tutorial for Access users who want to learn Access by working through solid examples. It will show the reader how to use Access and how to develop solid databases from start to finish. The focus of the book will be Access databases on the desktop but will have two chapters on implementing Access in a networked or client/server environment.

Key topics include understanding relational databases and the Access 2002 architecture; designing, building, and maintaining full-feature, robust database applications; implementing Data Access Pages; working with Visual Basic for Applications and the Visual Basic Editor; and publishing Access content to the WWW or a company's intranet.

Table of Contents

  1. Copyright
    1. Dedication
  2. About the Authors
  3. Acknowledgments
  4. Tell Us What You Think!
  5. Introduction
    1. Who Should Read This Book
    2. Conventions
  6. 1. At a Glance
    1. 1. Database Concepts
      1. Today You Will Learn
      2. Access in Theory and Practice
      3. Data Isn’t Information
        1. Changing Data into Information
        2. Discovering Information
        3. The Key to the Transformation
      4. The Relational Model and Access
      5. Database Structure in Theory
      6. Database Structure in Practice
        1. Choosing the Primary Key
        2. Choosing Relationship Types
        3. Understanding Normalization
      7. Informal Rules to Live By
      8. The Mission of Access
      9. Hardware Requirements
      10. Access Databases Maintenance
      11. Summary
      12. Q&A
      13. Workshop
        1. Quiz
    2. 2. Learning the Basics to Develop an Access Database Application
      1. Today You Will Learn
      2. The Access User Interface
        1. Task Pane
        2. Objects and Actions
        3. Groups
      3. Object Views
        1. Opening and Closing Object Views
        2. Managing Access Object Views and Windows
      4. Options—Customizing Access Globally
      5. The Objects of Access
        1. Tables
        2. Queries
        3. Forms
        4. Reports
        5. Pages
        6. Macros
        7. Modules
      6. Planning Your Database Application
        1. Don’t Be a Slave to Convention
        2. Don’t Worry Yet
      7. Getting Help—The Office Assistant
        1. Real Experts Shun Help—Ha!
        2. The Ways of Accessing Help
        3. What’s This?
      8. Toolbars
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
    3. 3. Automatic Access
      1. Today You Will Learn
      2. Why Automatic Access?
      3. Database Wizard or Template
        1. The Template
        2. Comments on Templates
      4. Table Wizard
      5. The Query Wizard
      6. Automatic Forms
        1. Creating a Form Using AutoForm
        2. Creating a Form Using the Form Wizard
      7. Automatic Reports
      8. Pages
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
    4. 4. The Data Foundation—The Table
      1. Today You Will Learn
      2. Understanding the Importance of Table Design
      3. Examining Tables
      4. Creating Tables
        1. Understanding Table Design Basics
        2. Using The Table Design Grid
        3. Using the Datasheet View to Make a Table
        4. Using the Table Wizard to Create a Table
      5. Analyzing the Wizard’s Table
        1. Violation of First Normal Form
        2. Possible Wrong Field Types
      6. Linking Fields—the Heart of the Relationship
        1. Adding Data to Linked Tables Within the Datasheet View
      7. Understanding the Benefits of a Relationship
        1. The Best Is Yet To Come
        2. Not Convinced?
        3. Seeing It Yourself
      8. Using Table and Field Properties
      9. Understanding Key Fields and Indexes
      10. Sorting and Filtering in Tables
        1. Sorting
        2. Filtering
      11. Finding Data
        1. Find Cautions
      12. Summary
      13. Q&A
      14. Workshop
        1. Quiz
        2. Exercises
    5. 5. Simple Forms
      1. Today You Will Learn
      2. Why This Chapter Now
      3. Bound and Unbound Forms and Fields
      4. Control and Record Source Properties
      5. Name Property—Standard Naming Conventions
      6. Creating a First Form
      7. Form Design View
        1. Option Group
        2. Form Control Alignment
      8. Form Headers and Footers
      9. Other Form Format Properties
      10. Tab Order
      11. Finding, Filtering, and Sorting in Forms
      12. Summary
      13. Q&A
      14. Workshop
        1. Quiz
        2. Exercises
    6. 6. Introducing Queries
      1. Today You Will Learn
      2. The Query in Access
      3. A First Query
      4. The Simple Query Wizard
      5. Filtering and Sorting in Queries
      6. Query Criteria
        1. Quick Query Facts
      7. Multi-table Queries
      8. Multi-table Queries Without Existing Links
      9. Intermediate Criteria
        1. Wildcards in Like and Between
      10. Summary
      11. Q&A
      12. Workshop
        1. Quiz
        2. Exercises
    7. 7. Basic Reports
      1. Today You Will Learn
      2. Reports in Access
      3. The AutoReport Wizard
      4. Report Wizard
        1. Report Preview Details
      5. The Report Design View
      6. Grouping in Reports
        1. The Group
        2. Properties
      7. Mailing Labels
        1. Expression Details Explained
      8. Page Layout Settings
      9. Sums, Subtotals, and Running Sums
      10. Summary
      11. Q&A
      12. Workshop
        1. Quiz
        2. Exercises
    8. 1. In Review
  7. 2. At a Glance
    1. 8. A Macro Primer
      1. Today You Will Learn
      2. Macros and Access
        1. And The Point Is?
      3. Elements of a Macro
        1. The Macro Design Grid
        2. A Very Simple Macro
        3. Deleting and Inserting Macro Actions
      4. Macros and Events
      5. Macro Names
        1. More About Macro Actions
      6. Conditional Macros
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    2. 9. Refining Your Tables
      1. Today You Will Learn
      2. Tables and Data Integrity
      3. Data Validation Using Table Expressions
      4. Data Lookup in Tables
      5. Input Masks
      6. Using OLE Objects and Hyperlinks in Tables
        1. Inserting OLE Objects
        2. Inserting Hyperlinks
        3. Working Around the Hyperlink Edit Catch-22
        4. Using Free Floating Hyperlinks
      7. Saving Tables as HTML
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    3. 10. Improving Your Forms
      1. Today You Will Learn
      2. Using Color to Enhance Forms
        1. Altering Object Color
        2. Adding Pictures to Form Objects
        3. Adding Pictures to Command Buttons
      3. Applying 3D Effects and Controlling Object Tab Order
      4. Formats and System Tools
      5. Adding Artwork
      6. Creating Multi-Source Forms
      7. Control Wizards
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    4. 11. Manipulating Queries
      1. Today You Will Learn
      2. The Query Design Grid
      3. A Quick Look at the SQL View
      4. General Math in Queries
      5. Date and Time Math in Queries
      6. Sorting Values That Are Not Alphabetically Sequential
      7. Joins
        1. Self Join
        2. Inner to Theta Joins
      8. Using Ranges in Queries
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
    5. 12. Getting Your Reports Right
      1. Today You Will Learn
      2. Creating Professional Reports
      3. Filtering Data for Reports
        1. Filtering Reports by Changing the Underlying Query
        2. Filtering Reports by Changing the Filter Properties
        3. Making Report Criteria Dynamic—Using Parameters
      4. Sorting and Grouping
        1. Sorting
        2. Grouping
      5. Understanding Crosstab Reports
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
    6. 13. Learning Structured Query Language or SQL
      1. Today You Will Learn
      2. What Is SQL?
      3. Using the SELECT Statement
        1. Selecting Multiple Columns or Fields
        2. Selecting Multiple Tables
      4. Adding the WHERE Clause
      5. Adding to SELECT
      6. Eliminating Duplicate Returns
      7. Getting Practical with SQL
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    7. 14. Special Query Uses
      1. Today You Will Learn
      2. Action Queries
        1. Adding Queries to Your Toolbar
        2. Update Query
        3. Append Query
        4. Delete Query
        5. Make Table Query
      3. Crosstab Queries
        1. Using the Crosstab Query Wizard
        2. Refining the Wizard’s Output
      4. SQL-Specific Queries
        1. Union Queries
        2. Data-definition Queries
        3. Pass-Through Queries
      5. Using External Criteria in Queries
        1. Parameterized Queries
        2. Query Criteria from Form Entries
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercises
    8. 2. In Review
  8. 3. At a Glance
    1. 15. Introduction to VBA Concepts
      1. Today You Will Learn
      2. What Is VBA and Why Should You Learn It?
      3. The BASIC Programming Language
        1. Historical Perspective
        2. Visual Basic for Applications Versus Visual Basic
      4. Introducing Event-Driven Programming
        1. Events in Access
        2. Exercise: The “Events” Explorer
      5. Introducing Objects
        1. Everything’s an Object
        2. Encapsulating Functionality
        3. Referencing Objects, Properties, and Methods
      6. Introducing Object Collections
        1. Collections of Objects
        2. Collection Objects In Access
      7. Introducing VBA Modules
        1. Standard Modules
        2. Class Modules
      8. Introducing the Access VBE (Visual Basic Editor)
        1. Adding Procedures, Functions, and Properties
        2. The Object Browser Window
        3. VBE’s Intellisense Feature
      9. Using Online Help
      10. Summary
      11. Q&A
      12. Workshop
        1. Quiz
        2. Exercise
    2. 16. VBA Language Elements—Part 1
      1. Today You Will Learn
      2. Understanding Modules
        1. Class Modules
        2. Standard Modules
        3. Structure of a Module
      3. Understanding VBA Procedures
        1. Creating Reusable Code
        2. Introducing Variables
        3. Understanding the Structure of a Procedure
        4. Built-in Functions and Statements
        5. Using Arguments in Procedures
        6. Looking at a Real-World Procedure Example
        7. Calling Functions from the Properties List
      4. Understanding Variables
        1. Variable Declarations
        2. Data Types
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    3. 17. VBA Programming—Part 2
      1. Today You Will Learn
      2. Understanding Variables
        1. Converting Variables to Other Data Types
        2. Understanding Arrays
        3. Dynamic Arrays
        4. Evaluating Expressions
          1. Arithmetic Operators
          2. String Operator
          3. Comparison Operators
          4. Standard Logical Operators
          5. Operator Precedence
        5. Constants
        6. Understanding Scope and Lifetime
          1. Global-Level Scope
          2. Module-Level Scope
          3. Procedure-Level Scope
          4. Lifetime
        7. Using a Standard Naming Convention
      3. Decision Structures
        1. If...Then...Else Statements
        2. IIf() Function
        3. Select...Case Statement
        4. Switch() and Choose() Functions
      4. Looping Structures
        1. For...Next
        2. For Each...Next
        3. While...Wend
        4. Do...Loops
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
    4. 18. Objects and Collections
      1. Today You Will Learn
      2. Object-Oriented Programming
        1. Understanding the Benefits of Using Objects
        2. Interfacing with Other Objects
        3. Using the Object Browser
      3. Creating Your Own Objects
        1. Understanding Classes: Object Templates
        2. Understanding Instantiation
        3. Introducing Properties, Methods, and Events
          1. Properties
          2. Methods
          3. Events
        4. Defining Enumerated Types
      4. Creating Collections
        1. Understanding Collections
        2. Understanding Collection Properties and Methods
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    5. 19. Extending Access Using VBA
      1. Today You Will Learn
      2. ActiveX Data Objects (ADO)
        1. Avoiding Conflicts with DAO
        2. Using ADO Objects
          1. The Connection Object
          2. The Recordset Object
          3. The Fields Collection
          4. The Field Object
        3. Creating Connections
        4. Working with ADO Recordsets
        5. Using a Recordset Clone
        6. Persisting Recordsets
      3. Introduction to Office Command Bars
        1. Creating a New Toolbar
        2. Referring to Command Bar Objects
        3. Manipulating Command Bar Items
      4. Developer Considerations
        1. Multi-Developer Considerations
        2. Managing Library References
        3. Hiding the Database Window
        4. Distributing Finished Applications
      5. Suggested Resources for Access and VBA Information
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercise
    6. 20. Maintaining and Securing Access Databases
      1. Today You Will Learn
      2. The Importance of Database Maintenance
      3. Repairing a Corrupted Database
      4. Compacting a Bloated Database
      5. Converting the Database to a Different Format
        1. Using Different Versions of Access
        2. Using the Upsizing Wizard
      6. Security Options in Access
        1. Encrypting a Database
        2. Setting a Database Password
        3. Working With Users, Groups, and Permissions
        4. Using the Workgroup Security Wizard
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercise
    7. 21. Access on the Web
      1. Today You Will Learn
      2. Publishing Static Web Pages
        1. Creating the HTML Template File
        2. Performing the Export
      3. Publishing Dynamic Web Pages
        1. Creating an ODBC Data Source
        2. Exporting to Active Server Pages
      4. Exporting Data in XML Format
      5. Using Data Access Pages
        1. Creating a Data Access Page
        2. Editing a Data Access Page
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercise
    8. 3. In Review
  9. A. Answers to Quizzes
    1. Day 1
    2. Day 2
    3. Day 3
    4. Day 4
    5. Day 5
    6. Day 6
    7. Day 7
    8. Day 8
    9. Day 9
    10. Day 10
    11. Day 11
    12. Day 12
    13. Day 13
    14. Day 14
    15. Day 15
    16. Day 16
    17. Day 17
    18. Day 18
    19. Day 19
    20. Day 20
    21. Day 21
  10. Sams Teach Yourself Microsoft® Access 2002 in 21 Days