Sams Teach Yourself Microsoft

Sams Teach Yourself Microsoft

Released August 2001
Publisher(s): Sams
ISBN: None

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

Sams Teach Yourself Access 2002 Programming in 24 Hours will considerably improve the quality of the database applications that the reader can create with Microsoft Access. Concise tutorials that quickly bring the reader up to speed will be the goal of each chapter. Having completed this book, the readers will be able to understand any sample VBA code that they see, and will possess the skills to attack all of the most common Access programming tasks.

Topics covered in the book will include: ¨

  • Creating data aware web pages ¨

  • Creating views to organize data ¨

  • Building reusable code modules ¨

  • Programming reports ¨

  • Communicating results with graphing ¨

  • Automating contact and task management with Outlook ¨

  • Access programming for Internet Explorer

Table of contents

  1. Copyright
    1. Dedication
  2. Inside Front Cover
  3. About the Author
  4. Acknowledgments
  5. Bibliography
  6. Tell Us What You Think!
  7. Introduction
  8. I. Access Programming 101
    1. 1. What’s New in Access 2002
      1. New Side Panes for Easier Navigation
      2. Customizing Menus and Toolbars
        1. Using the More Menu Item
        2. Personalizing Menus and Toolbars
        3. Assigning Hyperlinks to Menus and Toolbars
      3. Spell-Checking Data
      4. Editing Options
        1. Multiple Undos and Redos
        2. Clippit Office Assistant
        3. Ask a Question
        4. Expanded Clipboard Functionality
      5. Speech Command and Control
      6. Collaborating Online
        1. Setting Up NetMeeting
        2. So You’re Connected. Now What?
      7. Network Places
      8. Remove Author Information
      9. Introducing ActiveX Data Objects
        1. Introducing a New Object for Database Security—ADOX
        2. Support for Stored Procedures
      10. Programming Objects
      11. Adding Data to Web Pages
      12. Access Projects
      13. Summary
      14. Q&A
      15. Workshop
        1. Quiz
        2. Exercises
    2. 2. Exploring VBA
      1. Understanding How Access VBA Works
      2. Learning Access VBA Keywords
      3. Access Operators and Operands
      4. Data: What a Program Knows
      5. Putting It All Together
        1. Arithmetic Operators
        2. Comparison Operators
        3. Logical Operators
        4. String Concatenation Operators
      6. Prelude to Advanced Topics
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    3. 3. Storing Your Program’s Data
      1. How Memory Is Used
      2. Declaring Variables
        1. Dim Variables
        2. ReDim Variables
        3. Const Variables
        4. Global Variables
      3. Data Assignment and Evaluation
      4. Be Explicit
        1. Variant Data Types
        2. Implicit Variable Declaration
      5. Using Pop-up Hints
        1. Setting Up Test Code
        2. Debugging and Using Pop-up Hints
      6. Using the Locals Window
      7. Using the Watches Window
        1. Adding a Watch Item
        2. Editing Watches
        3. Using Quick Watches
      8. Running Code in the Immediate Window
      9. Employing the Call Stack
      10. Summary
      11. Q&A
      12. Workshop
        1. Quiz
        2. Exercises
    4. 4. Controlling Program Flow and Performing Calculations
      1. What Goes into an Equation
        1. Equation Data
          1. Literal Values
          2. Variables
          3. Constants
          4. Globals
          5. Function Results
        2. Rules of the Road
      2. Understanding Operator Count
      3. Using Arithmetic Operators
      4. Performing Comparisons
      5. The Truth of Logical Operators
        1. And
        2. Eqv
        3. Imp
        4. Or
        5. Xor
        6. Not
        7. Bitwise Operations
      6. Concatenate It
      7. Who Has Precedence Here?
      8. Special Operators
        1. Using the Is Operator
        2. Pattern Matching with Like
        3. AddressOf Operations
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
  9. II. Writing Code to Manage Your Access Data
    1. 5. Learning to Write Conditional Code
      1. A Programmer’s Traffic Cop
        1. Using Else
        2. Nesting Conditional Statements
        3. Housekeeping
      2. Tidying Up Nested Conditional Statements
      3. Want to Go for a Spin?
      4. Take It Once Around the Block
      5. Iterating Through Data
        1. Iterating Forward and Backward
        2. Ubound and Lbound
      6. Iterating Collections of Data
      7. Short-circuiting Code
      8. Using the Switch Function
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
    2. 6. Managing Your Database
      1. Access 2002 Is Backward Compatible with Access 2000
        1. New File Format Provides Better Performance
          1. MDE Database File Extension
          2. ADE Database File Extension
        2. Improved Compact and Repair Feature
      2. Creating a Database
        1. Defining the Database
        2. Creating a Table with Access 2002
        3. Creating a Module in the Contacts Database
        4. Creating a Table with Code
      3. Managing a Table with Code
        1. Database Terms Refresher Course
          1. The Catalog
          2. The Connection
          3. The Recordset
          4. Rows, Columns, and Fields
        2. Accessing Table Information
      4. Iteratively Inputting User Data
      5. Using Conditional Code to Find Data
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercises
    3. 7. Using Advanced Data Types to Manage Data
      1. Understanding OLE Automation
        1. Welcome to the World of Objects
        2. Useful Objects
        3. Comparing ADO to DAO
      2. Using the ADODB Object
        1. Using a Connection
          1. Opening a Connection
          2. Closing a Connection
        2. Using a Recordset
          1. Opening a Recordset
          2. Closing a Recordset
          3. Modifying a Record
          4. Testing for the First and Last Record
          5. Changing Records
          6. Using Fields
      3. Using the ADOX Object
        1. Catalog
        2. The ActiveConnection Attribute
        3. Tables Collection
        4. Groups, Users, and Views
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  10. III. Using Access Resources to Get the Job Done
    1. 8. Solving Problems a Piece at a Time
      1. The Mechanics of Writing Subroutines
        1. Writing the First and Last Lines of Subroutines
        2. Naming Subroutines
        3. Naming Subroutine Arguments
        4. Defining Argument Types
          1. Using ByVal Arguments
          2. Using ByRef Arguments
          3. Using Optional Arguments
      2. The Mechanics of Writing Functions
      3. Programming Rules of Thumb
        1. Rule 1: Make Recurring Code into a Function
        2. Rule 2: Keep Functions Short
        3. Rule 3: Strictly Limit the Number of Arguments
        4. Rule 4: Use Argument Qualifiers to Inhibit Variable Misuse
        5. Rule 5: Use Contract Programming
        6. Rule 6: Don’t Be Afraid to Comment
      4. Tying It All Together
        1. Creating a Table with Code
        2. Importing a Comma-Delimited Text File
        3. Finding a Record
        4. Using the Windows Registry
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    2. 9. Using Macros As Learning Aids
      1. Creating a Macro 101
        1. Creating a Table with SQL
        2. Creating a Macro
        3. Defining Macro Names and Conditional Code
        4. Testing and Debugging a Macro
      2. Using CopyObject
      3. Using DeleteObject
      4. The Keys to the City
      5. Importing Data
        1. Transferring a Database
          1. Specifying the Database Type
          2. Specifying the Database Name
          3. Choosing an Object Type to Transfer
          4. Specifying a Transfer Source and Destination
          5. Replicating Structure Only
        2. Transferring Text Data
          1. Specifying a Text Transfer Type
          2. Creating a Transfer Specification
          3. Specifying a Table Name
          4. Specifying a Filename
          5. Does the Text Source Contain Field Names?
          6. Using HTML Table Data
          7. Indicating a Code Page
      6. Using Macro Resources in Code
      7. To Macro or Not to Macro
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    3. 10. Employing Access Predefined Solutions
      1. Using String-Handling Functions
        1. Converting Between String and Numeric Values
        2. Dual Type String Functions
        3. General Data Type Conversion Functions
        4. Searching Strings
        5. Dynamically Allocating Strings
      2. Formatting Data
      3. Using Date and Time Functions
      4. Managing File I/O
        1. Using Open and Close
        2. Reading and Writing Text Data
        3. Reading and Writing Binary Data
      5. Dynamic User Input Functions
      6. Using Function, Method, Property, and Statement References
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
  11. IV. Defining Data Types and Using Arrays and Collections
    1. 11. Making the Complex Simple: Creating Your Own Data Types
      1. Understanding Aggregation
      2. Using the Type Declaration
      3. What Kind of Data Can Go into My Type?
      4. Declaring Instances of User Types
      5. Defining Enumerated Types
      6. Putting It All Together
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    2. 12. Managing Varying Amounts of Data
      1. Understanding Arrays
      2. Declaring Arrays
        1. Declaring Fixed Arrays
        2. Declaring Dynamic Arrays
        3. Declaring Static Arrays
        4. Setting Array Base Indexes
      3. Using Arrays to Store Data
      4. Functions for Managing Arrays
        1. Testing Variables with IsArray
        2. Passing Arrays of Parameters to Procedures
      5. Returning Arrays from a Function
      6. Things You Should Do When Using Arrays
      7. Sorting Array Data
        1. Bubble-Sorting an Array of Strings
        2. Selection-Sorting Arrays
        3. Quick-Sorting Arrays
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    3. 13. Storing Information in a Collection
      1. Understanding Collections
        1. Uses for Collections
        2. The Terminology of Object-Oriented Programming
      2. Declaring Collection Variables
      3. Managing a Data Collection
        1. Adding Data to a Collection
        2. Removing Data from a Collection
        3. Using the Item Method and Count Property
        4. Iterating Through a Collection with For Each Loops
      4. Places You’ll Find Collections
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
  12. V. Database Programming in Access
    1. 14. Managing Your Code
      1. Naming Conventions
        1. Meaning Is Conveyed with Whole Words
        2. Pairing Verbs and Nouns Make Subroutines and Functions Clear
        3. Avoid Nonstandard Abbreviations
      2. Using Whitespace Consistently
      3. Managing Complexity
        1. Avoid Nesting Conditional Statements
        2. Use Procedures to Implement Conditional Code
        3. Keep Procedures Short and to the Point
      4. Commenting Guidelines
        1. Write Comments in Complete Sentences
        2. Comment Lengthy Code
        3. Comment Ambiguous Code or Code Copied from an External Source
      5. Understanding the Benefits of Code Reuse
      6. Testing and Debugging
      7. Managing Data
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    2. 15. Data Programming Made Easy with ADODB
      1. Connecting to a Database
        1. Open Method’s Connection Arguments
        2. Creating an ODBC Alias
        3. What Is a Provider?
        4. Specifying a Username and Password
        5. Selecting a Connection Mode
      2. Managing a Recordset
        1. Opening and Closing Recordsets
        2. The Ingredients in a Recordset
          1. Command Objects
          2. Stored Procedures
          3. Literal SQL Code
          4. Tables and Queries
        3. Adding and Updating Data
        4. Modifying Field Data
        5. Deleting Data
      3. Finding Records
        1. Using a Table
        2. Using a Query
        3. Using the Filter Property
      4. Copying Data to a Collection Object
      5. Using AddItem and RemoveItem
      6. Finding ActiveX Data Objects Resources Online
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    3. 16. Using Advanced SQL Techniques
      1. Using the SELECT Statement
        1. Writing a Simple SELECT Statement
        2. Filtering the Recordset with the WHERE Clause
        3. Operators for the WHERE Clause
          1. Specifying a Range with BETWEEN
          2. Performing Set Operations with IN
          3. Using Logic Operators
        4. Using Nested SELECT Statements in WHERE Clauses
        5. Sorting Queries
        6. Grouping Columns
        7. Using the HAVING Clause to Filter Result Sets
        8. Joining Tables
          1. Using the INNER JOIN
          2. Using the LEFT JOIN
          3. Using the RIGHT JOIN
        9. Creating a Union of Tables
        10. Defining a Column Alias
      2. Inserting New Data
        1. Inserting into Specified Fields Only
        2. Inserting into Implied Fields
        3. Inserting with Parameters
        4. Inserting from a Table with SELECT
      3. Updating Data
      4. Deleting Data
      5. Calling Functions in SQL Code
      6. Writing Stored Procedures
        1. Adding a Stored Procedure to a Catalog
        2. Executing a Stored Procedure
      7. Querying a SQL Server Database
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
  13. VI. Mastering Error Handling
    1. 17. Removing Bugs
      1. Scaffolding
        1. Why Scaffolding Early Is Important
        2. Understanding What to Look For
        3. How to Scaffold Access Code
          1. What to Do If Your Code Has Dependencies
          2. What to Do If the Code Doesn’t Solve the Problem
          3. What to Do in the Event of Unhandled Errors
      2. Trapping Code
        1. What Is a Trap?
        2. Using Traps
      3. Tracing Code
      4. Asserting Assumptions
        1. Contract Programming
        2. Reusing the Debugging Techniques
      5. Using Compiler Directives
      6. Making Debug Code Read-Only
      7. Development Versus Deployment Error Handling
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    2. 18. Adding Code to Handle Errors
      1. Comparing Error-Handling Styles
        1. Conditional Error Checking: The Old Way
        2. Exception Handling: A Better Way
      2. Writing Error Handlers
        1. Starting an Error Handler
          1. Naming Labels
          2. Writing Your Exit Statement
        2. General Rules for Error Handlers
      3. Clearing the Error Handler
      4. Writing Silent Error Handlers
        1. Using Resume in an Error-Handling Block
        2. Using Resume Next in an Error-Handling Block
      5. Using the Err Object
        1. Err Object Properties
        2. Err Object Methods
      6. Using Error Handlers to Validate User Input
      7. Creating the Resource-Protection Block Idiom
      8. Reviewing the Debug Object
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
  14. VII. Creating Access User Interfaces
    1. 19. Creating Custom Forms and Reports
      1. Using Form Wizards
        1. An Overview of Each Form Design Type
          1. Design View
          2. Form Wizard
          3. Columnar AutoForm
          4. Tabular AutoForm
          5. Datasheet AutoForm
          6. PivotTable AutoForm
          7. PivotChart AutoForm
          8. Chart Wizard
          9. PivotTable Wizard
        2. Creating a Form with the Form Wizard
        3. Using Data-Management Menu Items
        4. Filtering Form Records
          1. Using Filter by Form
          2. Using Filter by Selection
          3. Using Filter Excluding Selection
          4. Building Advanced Filters
      2. Customizing Wizard Forms
        1. Understanding the Three Facets of Visual Objects
          1. Assigning Data to Properties
          2. Calling Methods
          3. Programming Event Handlers
        2. Adding Controls to Your Form
          1. Selecting Additional Controls from the Toolbox
          2. Making Non-Component Objects Available to Your Programs
        3. Writing Code
        4. Enhanced Editing Capabilities
        5. Extended Capabilities for Forms and Reports
          1. New Properties for Forms and Reports
          2. New Form Events
          3. New Methods for Forms and Reports
        6. Live Subreport Form
      3. Testing Your Form
      4. Defining a Report
        1. Using the New Printer Object
        2. Adding a Menu Item to Access to Print the Report
      5. Setting the Startup Point for Your Program
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercises
    2. 20. Adding Data to Web Pages
      1. Comparing Intranets to Internets
        1. Installing and Running Internet Information Services
        2. Establishing a Web Site
      2. Understanding Web Pages
        1. What Is a Data Access Page?
        2. Advanced Web Page Topics
      3. Building the Database Used for the Demo
      4. Using Wizards to Generate Web Pages
        1. Defining a Multiple-Table Query
        2. Using the Page Wizard
      5. Using Web Page Design Tools
        1. Using the Data Access Page Designer
          1. Data Outline View and Server Filters
          2. Visual Control Sizing and Snap-to-Grid
          3. Intuitive Drop Zones
          4. AutoSum Feature and Group Filtering
          5. Designer Inherits Extended Properties from the Database
        2. Taking a Tour of Data Access Page Menus and Toolbars
        3. Adding a Control to a Data Access Page
        4. Using the Properties Dialog
        5. Binding a Field to a Control
        6. Sorting and Grouping Data
      6. Adding a PivotTable to Web Pages
        1. Uniform Naming Convention
        2. Office Data Connection
      7. XML’s Role in Access
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
  15. VIII. Object-Oriented Programming in Access
    1. 21. Class Programming Basics
      1. Understanding the Need for Classes
      2. Creating Your First Class
      3. Hiding Information to Make Code Easier to Use
      4. Defining Class Methods
      5. Defining Properties
        1. Using Object Properties
        2. Using Static Properties
      6. Writing Initialization and Termination Code
      7. Writing a New Class
        1. Testing Classes
        2. Extending Existing Classes
      8. Creating an Instance of a Class
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
    2. 22. Adding Capabilities to Your Data Types
      1. Defining the Objective
      2. Understanding When to Write a Class
      3. Applying Basic Principles of Object-Oriented Design
        1. Naming What the Class Must Do and Know
        2. Tips for Implementing Classes
      4. Extending Existing Classes
        1. Using a Collection to Store the Associative Array Data
        2. Implementing the File Streaming Capabilities
      5. Testing Your New Class
      6. Using the Strings Class
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    3. 23. Writing Access Add-Ins
      1. Introducing Add-Ins
      2. Defining an Error-Logging Database
        1. Adding Code to Create the Table Dynamically
        2. Logging Exceptions to the Database
      3. Defining a Viewer for the Error Log
      4. Testing the Error Logging Add-In Database
      5. Installing and Uninstalling Add-ins
        1. Creating the Registry Information Table
        2. Installing the Add-In
        3. Uninstalling and Modifying Add-Ins
      6. Going for a Test Drive
      7. Summarizing the Process
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    4. 24. Managing Outlook Contact Information
      1. Understanding Outlook 2002
      2. Introducing the Outlook Object Model
        1. Creating an Instance of Outlook
        2. Using Namespaces
        3. Using a Folder
      3. Viewing Outlook Contacts in Access
      4. Updating Outlook Information
      5. Searching the Body of a Mail Item
      6. Undeleting Mail Items
      7. Sending Mail to Contacts Automatically
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
  16. IX. Appendix
    1. A.  
      1. Hour 1
        1. Quiz
        2. Exercises
      2. Hour 2
        1. Quiz
        2. Exercises
      3. Hour 3
        1. Quiz
        2. Exercises
      4. Hour 4
        1. Quiz
        2. Exercises
      5. Hour 5
        1. Quiz
        2. Exercises
      6. Hour 6
        1. Quiz
        2. Exercises
      7. Hour 7
        1. Quiz
        2. Exercises
      8. Hour 8
        1. Quiz
        2. Exercise
      9. Hour 9
        1. Quiz
        2. Exercise
      10. Hour 10
        1. Quiz
        2. Exercises
      11. Hour 11
        1. Quiz
        2. Exercises
      12. Hour 12
        1. Quiz
        2. Exercises
      13. Hour 13
        1. Quiz
        2. Exercises
      14. Hour 14
        1. Quiz
        2. Exercises
      15. Hour 15
        1. Quiz
        2. Exercises
      16. Hour 16
        1. Quiz
        2. Exercises
      17. Hour 17
        1. Quiz
        2. Exercises
      18. Hour 18
        1. Quiz
        2. Exercises
      19. Hour 19
        1. Quiz
        2. Exercises
      20. Hour 20
        1. Quiz
        2. Exercises
      21. Hour 21
        1. Quiz
        2. Exercises
      22. Hour 22
        1. Quiz
        2. Exercises
      23. Hour 23
        1. Quiz
        2. Exercises
      24. Hour 24
        1. Quiz
        2. Exercises
  17. Quick Reference for DoCmd Methods

Product information

  • Title: Sams Teach Yourself Microsoft
  • Author(s):
  • Release date: August 2001
  • Publisher(s): Sams
  • ISBN: None