Access Database Design & Programming, 3rd Edition

Access Database Design & Programming, 3rd Edition

by Steven Roman
Released January 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596002732

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Access Database Design & Programming takes you behind the details of the Access interface, focusing on the general knowledge necessary for Access power users or developers to create effective database applications. When using software products with graphical interfaces, we frequently focus so much on the interface that we forget about the general concepts that allow us to understand and use the software effectively. In particular, this book focuses on three areas:

  • Database design. The book provides an enjoyable, informative overview of database design that carefully shows you how to normalize tables to eliminate redundancy without losing data.
  • Queries. The book examines multi-table queries (i.e.,various types of joins) and shows how to implement them indirectly by using the Access interface or directly by using Access SQL.
  • Programming. The book examines the VBA integrated development environment (IDE). It then goes on to provide an excellent introduction to Data Access Objects (DAO), ActiveX Data Objects (ADO), and ADO Extensions for Data Definition and Security (ADOX). These sections serve as a handy introduction and primer for basic database operations,such as modifying a table under program control, dynamically adding and deleting a record, and repositioning a record pointer. The concluding chapter focuses on common programming problems, such as computing running sums and comparing two sets.
Unlike other Access books that take the long, detailed approach to every topic of concern to Access programmers, Access Database Design &Programming instead focuses on the core concepts, enabling programmers to develop solid, effective database applications. This book also serves as a 'second course' in Access that provides a relatively experienced Access user who is new to programming with the frequently overlooked techniques necessary to develop successfully in the Microsoft Access environment.Anyone interested in learning Access in depth, rather than just scraping the surface, will enjoy and benefit immensely from reading this book.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Preface to the Third Edition
    2. Preface to the Second Edition
    3. The Book’s Audience
    4. The Sample Code
    5. Organization of This Book
      1. Part I
      2. Part II
      3. Part III
      4. Part IV
      5. Part V
      6. Part VI
      7. Part VII
      8. Part VIII
    6. Conventions in This Book
    7. Obtaining Updated Information
    8. Using Code Examples
    9. Request for Comments
    10. Acknowledgments
  3. I. Database Design
    1. 1. Introduction
      1. 1.1. Database Design
        1. 1.1.1. Why Use a Relational-Database Design?
          1. 1.1.1.1. Redundancy
          2. 1.1.1.2. Multiple-value problems
          3. 1.1.1.3. Update anomalies
          4. 1.1.1.4. Insertion anomalies
          5. 1.1.1.5. Deletion anomalies
        2. 1.1.2. Complications of Relational-Database Design
          1. 1.1.2.1. Avoiding data loss
          2. 1.1.2.2. Maintaining relational integrity
          3. 1.1.2.3. Creating views
        3. 1.1.3. Summary
      2. 1.2. Database Programming
    2. 2. The Entity-Relationship Model of a Database
      1. 2.1. What Is a Database?
      2. 2.2. Entities and Their Attributes
      3. 2.3. Keys and Superkeys
      4. 2.4. Relationships Between Entities
        1. 2.4.1. Types of Relationships
    3. 3. Implementing Entity-Relationship Models: Relational Databases
      1. 3.1. Implementing Entities
        1. 3.1.1. Implementing Entity Classes—Table Schemes
        2. 3.1.2. Implementing Entity Sets—Tables
      2. 3.2. A Short Glossary
      3. 3.3. Implementing the Relationships in a Relational Database
        1. 3.3.1. Implementing a One-to-Many Relationship—Foreign Keys
        2. 3.3.2. Implementing a One-to-One Relationship
        3. 3.3.3. Implementing a Many-to-Many Relationship—New Entity Classes
        4. 3.3.4. Referential Integrity
        5. 3.3.5. Cascading Updates and Cascading Deletions
      4. 3.4. The LIBRARY Relational Database
        1. 3.4.1. Setting Up the Relationships in Access
      5. 3.5. Index Files
        1. 3.5.1. Example
      6. 3.6. NULL Values
    4. 4. Database Design Principles
      1. 4.1. Redundancy
      2. 4.2. Normal Forms
      3. 4.3. First Normal Form
      4. 4.4. Functional Dependencies
      5. 4.5. Second Normal Form
      6. 4.6. Third Normal Form
      7. 4.7. Boyce-Codd Normal Form
      8. 4.8. Normalization
        1. 4.8.1. Decomposition
  4. II. Database Queries
    1. 5. Query Languages and the Relational Algebra
      1. 5.1. Query Languages
      2. 5.2. Relational Algebra and Relational Calculus
      3. 5.3. Details of the Relational Algebra
        1. 5.3.1. Renaming
        2. 5.3.2. Union
        3. 5.3.3. Intersection
        4. 5.3.4. Difference
        5. 5.3.5. Cartesian Product
        6. 5.3.6. Projection
        7. 5.3.7. Selection
        8. 5.3.8. Joins
          1. 5.3.8.1. Equi-join
          2. 5.3.8.2. Natural join
          3. 5.3.8.3. θ-Join
        9. 5.3.9. Outer Joins
        10. 5.3.10. Implementing Joins in Microsoft Access
        11. 5.3.11. Semi-Joins
        12. 5.3.12. Other Relational Algebra Operations
        13. 5.3.13. Optimization
    2. 6. Access Structured Query Language (SQL)
      1. 6.1. Introduction to Access SQL
      2. 6.2. Access Query Design
      3. 6.3. Access Query Types
      4. 6.4. Why Use SQL?
      5. 6.5. Access SQL
        1. 6.5.1. Syntax Conventions
          1. 6.5.1.1. Notes
      6. 6.6. The DDL Component of Access SQL
        1. 6.6.1. The CREATE TABLE Statement
          1. 6.6.1.1. Column definition
          2. 6.6.1.2. Constraints
          3. 6.6.1.3. Notes
        2. 6.6.2. The ALTER TABLE Statement
          1. 6.6.2.1. Notes
        3. 6.6.3. The CREATE INDEX Statement
          1. 6.6.3.1. Note
        4. 6.6.4. The DROP Statement
          1. 6.6.4.1. Note
      7. 6.7. The DML Component of Access SQL
        1. 6.7.1. Updatable Queries
        2. 6.7.2. Joins
          1. 6.7.2.1. Inner joins
          2. 6.7.2.2. Outer joins
          3. 6.7.2.3. Nested joins
          4. 6.7.2.4. Self-joins
          5. 6.7.2.5. Notes
        3. 6.7.3. The SELECT Statement
          1. 6.7.3.1. Predicate
          2. 6.7.3.2. ReturnColumnDescription
          3. 6.7.3.3. FROM TableExpression
          4. 6.7.3.4. WHERE RowCondition
          5. 6.7.3.5. GROUP BY GroupByCriteria
          6. 6.7.3.6. HAVING GroupCriteria
          7. 6.7.3.7. ORDER BY OrderByCriteria
        4. 6.7.4. The UNION Statement
          1. 6.7.4.1. Example
          2. 6.7.4.2. Notes
        5. 6.7.5. The UPDATE Statement
          1. 6.7.5.1. Example
        6. 6.7.6. The DELETE Statement
        7. 6.7.7. The INSERT INTO Statement
          1. 6.7.7.1. Note
        8. 6.7.8. The SELECT...INTO Statement
          1. 6.7.8.1. Notes
        9. 6.7.9. TRANSFORM
        10. 6.7.10. Subqueries
          1. 6.7.10.1. Syntax 1
          2. 6.7.10.2. Syntax 2
          3. 6.7.10.3. Syntax 3
          4. 6.7.10.4. Notes
        11. 6.7.11. Parameters
  5. III. Database Architecture
    1. 7. Database System Architecture
      1. 7.1. Why Program?
      2. 7.2. Database Systems
      3. 7.3. Database Management Systems
      4. 7.4. The Jet DBMS
      5. 7.5. Data Definition Languages
        1. 7.5.1. The Jet Data Definition Language
      6. 7.6. Data Manipulation Languages
        1. 7.6.1. The Jet Data Manipulation Language
      7. 7.7. Host Languages
      8. 7.8. The Client/Server Architecture
  6. IV. Visual Basic for Applications
    1. 8. The Visual Basic Editor, Part I
      1. 8.1. The Project Window
        1. 8.1.1. Project Names
        2. 8.1.2. Project Contents
          1. 8.1.2.1. Standard modules
          2. 8.1.2.2. Class modules
      2. 8.2. The Properties Window
      3. 8.3. The Code Window
        1. 8.3.1. Procedure and Full-Module Views
        2. 8.3.2. The Object and Procedure Listboxes
      4. 8.4. The Immediate Window
      5. 8.5. Arranging Windows
        1. 8.5.1. Docking
    2. 9. The Visual Basic Editor, Part II
      1. 9.1. Navigating the IDE
        1. 9.1.1. General Navigation
          1. 9.1.1.1. Navigating the code window at design time
          2. 9.1.1.2. Tracing code
          3. 9.1.1.3. Bookmarks
      2. 9.2. Getting Help
      3. 9.3. Creating a Procedure
      4. 9.4. Run Mode, Break Mode, and Design Mode
      5. 9.5. Errors
        1. 9.5.1. Design-Time and Compile-Time Errors
        2. 9.5.2. Runtime Errors
        3. 9.5.3. Logical Errors
      6. 9.6. Debugging
        1. 9.6.1. Tracing
          1. 9.6.1.1. Step Into (F8 or choose Step Into from the Debug menu)
          2. 9.6.1.2. Step Over (Shift+F8 or choose Step Over from the Debug menu)
          3. 9.6.1.3. Step Out (Ctrl+Shift+F8 or choose Step Out from the Debug menu)
          4. 9.6.1.4. Run to Cursor (Ctrl+F8 or choose Run To Cursor from the Debug menu)
          5. 9.6.1.5. Set Next Statement (Ctrl+F9 or choose Set Next Statement from the Debug menu)
          6. 9.6.1.6. Breaking out of Debug mode
    3. 10. Variables, Data Types, and Constants
      1. 10.1. Comments
      2. 10.2. Line Continuation
      3. 10.3. Constants
        1. 10.3.1. Enums
      4. 10.4. Variables and Data Types
        1. 10.4.1. Variable Declaration
        2. 10.4.2. The Importance of Explicit Variable Declaration
          1. 10.4.2.1. Option Explicit
        3. 10.4.3. Numeric Data Types
        4. 10.4.4. Boolean Data Type
        5. 10.4.5. String Data Type
        6. 10.4.6. Date Data Type
        7. 10.4.7. Variant Data Type
        8. 10.4.8. Access Object Data Types
          1. 10.4.8.1. The generic As Object declaration
          2. 10.4.8.2. The Set statement
        9. 10.4.9. Arrays
          1. 10.4.9.1. The dimension of an array
          2. 10.4.9.2. Dynamic arrays
          3. 10.4.9.3. The UBound function
        10. 10.4.10. Variable Naming Conventions
        11. 10.4.11. Variable Scope
          1. 10.4.11.1. Procedure-level (local) variables
          2. 10.4.11.2. Module-level variables
        12. 10.4.12. Variable Lifetime
          1. 10.4.12.1. Static variables
        13. 10.4.13. Variable Initialization
      5. 10.5. VBA Operators
    4. 11. Functions and Subroutines
      1. 11.1. Calling Functions
      2. 11.2. Calling Subroutines
      3. 11.3. Parameters and Arguments
        1. 11.3.1. Optional Arguments
        2. 11.3.2. Named Arguments
        3. 11.3.3. ByRef Versus ByVal Parameters
      4. 11.4. Exiting a Procedure
      5. 11.5. Public and Private Procedures
      6. 11.6. Fully Qualified Procedure Names
    5. 12. Built-in Functions and Statements
      1. 12.1. The MsgBox Function
      2. 12.2. The InputBox Function
      3. 12.3. VBA String Functions
      4. 12.4. Miscellaneous Functions and Statements
        1. 12.4.1. The Is Functions
          1. 12.4.1.1. The IsDate function
          2. 12.4.1.2. The IsEmpty function
          3. 12.4.1.3. The IsNull function
          4. 12.4.1.4. The IsNumeric function
        2. 12.4.2. The Immediate If Function
        3. 12.4.3. The Switch Function
        4. 12.4.4. The Beep Statement
      5. 12.5. Handling Errors in Code
        1. 12.5.1. The On Error Goto Label Statement
        2. 12.5.2. Handling Errors in the Calling Procedure
        3. 12.5.3. The Calls Stack
        4. 12.5.4. The Error Object
        5. 12.5.5. The On Error GoTo 0 Statement
        6. 12.5.6. The On Error Resume Next Statement
        7. 12.5.7. The Resume Statement
    6. 13. Control Statements
      1. 13.1. The If ...Then Statement
      2. 13.2. The For Loop
      3. 13.3. The Exit For Statement
      4. 13.4. The For Each Loop
      5. 13.5. The Do Loop
      6. 13.6. The Select Case Statement
      7. 13.7. A Final Note on VBA
        1. 13.7.1. File-Related Functions
        2. 13.7.2. Date- and Time-Related Functions
        3. 13.7.3. The Format Function
  7. V. Data Access Objects
    1. 14. Programming DAO: Overview
      1. 14.1. Objects
        1. 14.1.1. Object Variables
        2. 14.1.2. Object-Variable Naming Conventions
        3. 14.1.3. Referencing the Properties and Methods of an Object
      2. 14.2. The DAO Object Model
      3. 14.3. The Microsoft Access Object Model
      4. 14.4. Referencing Objects
        1. 14.4.1. Fully Qualified Object Names
        2. 14.4.2. Using Object Variables to Your Advantage
        3. 14.4.3. Default Collections
      5. 14.5. Collections Are Objects Too
        1. 14.5.1. Properties and Methods of Access Collections
        2. 14.5.2. Properties and Methods of DAO Collections
        3. 14.5.3. Properties and Methods of User-Defined Collections
        4. 14.5.4. Say It Again
        5. 14.5.5. Refreshing Certain Collections
      6. 14.6. The Properties Collection
        1. 14.6.1. The Virtues of Properties Collections
        2. 14.6.2. Types of Properties
          1. 14.6.2.1. Property: Inherited
          2. 14.6.2.2. Property: Name
          3. 14.6.2.3. Property: Type
          4. 14.6.2.4. Property: Value
        3. 14.6.3. User-Defined Properties
      7. 14.7. Closing DAO Objects
      8. 14.8. A Look at the DAO Objects
        1. 14.8.1. DBEngine Object
        2. 14.8.2. Errors
        3. 14.8.3. Workspaces
        4. 14.8.4. Users
        5. 14.8.5. Groups
        6. 14.8.6. Databases
        7. 14.8.7. TableDefs
        8. 14.8.8. QueryDefs
        9. 14.8.9. Recordsets
        10. 14.8.10. Relations
        11. 14.8.11. Containers
        12. 14.8.12. Documents
        13. 14.8.13. Fields
        14. 14.8.14. Parameters
        15. 14.8.15. Indexes
      9. 14.9. The CurrentDb Function
    2. 15. Programming DAO: Data Definition Language
      1. 15.1. Creating a Database
        1. 15.1.1. Notes
      2. 15.2. Opening a Database
      3. 15.3. Creating a Table and Its Fields
        1. 15.3.1. Notes
          1. 15.3.1.1. Note
        2. 15.3.2. Changing the Properties of an Existing Table or Field
      4. 15.4. Creating an Index
      5. 15.5. Creating a Relation
        1. 15.5.1. Notes
      6. 15.6. Creating a QueryDef
        1. 15.6.1. Notes
        2. 15.6.2. Running a Query
        3. 15.6.3. Properties of a QueryDef Object
    3. 16. Programming DAO: Data Manipulation Language
      1. 16.1. Recordset Objects
      2. 16.2. Opening a Recordset
        1. 16.2.1. Note
        2. 16.2.2. Default Recordset Types
      3. 16.3. Moving Through a Recordset
        1. 16.3.1. BOF and EOF
          1. 16.3.1.1. Notes on the BOF and EOF properties
          2. 16.3.1.2. Notes on the Move methods
      4. 16.4. Finding Records in a Recordset
        1. 16.4.1. Finding Records in a Table-Type Recordset
          1. 16.4.1.1. Notes
        2. 16.4.2. Finding Records in a Dynaset-Type or Snapshot-Type Recordset
      5. 16.5. Editing Data Using a Recordset
        1. 16.5.1. Editing an Existing Record
        2. 16.5.2. Deleting an Existing Record
          1. 16.5.2.1. Notes
        3. 16.5.3. Adding a New Record
          1. 16.5.3.1. Notes
  8. VI. ActiveX Data Objects
    1. 17. ADO and OLE DB
      1. 17.1. What Is ADO?
      2. 17.2. Installing ADO
      3. 17.3. ADO and OLE DB
        1. 17.3.1. Data Stores
        2. 17.3.2. Data Providers
        3. 17.3.3. Data Consumers
        4. 17.3.4. Service Providers
      4. 17.4. The ADO Object Model
        1. 17.4.1. The Three-Pronged Approach to Data Manipulation
        2. 17.4.2. The Connection Object
          1. 17.4.2.1. Properties of the Connection object
          2. 17.4.2.2. Methods of the Connection object
        3. 17.4.3. The Recordset Object
          1. 17.4.3.1. Cursors
          2. 17.4.3.2. LockType
          3. 17.4.3.3. Properties of the Recordset object
          4. 17.4.3.4. Methods of the Recordset object
        4. 17.4.4. The Command Object
          1. 17.4.4.1. Command objects and connections
          2. 17.4.4.2. Properties of the Command object
          3. 17.4.4.3. Methods of the Command object
        5. 17.4.5. The Property Object and Dynamic Properties
        6. 17.4.6. The Field Object
          1. 17.4.6.1. Properties of the Field object
      5. 17.5. Finding OLE DB Providers
      6. 17.6. A Closer Look at Connection Strings
        1. 17.6.1. The Microsoft Jet 3.51 OLE DB Provider
        2. 17.6.2. The Microsoft OLE DB Provider for ODBC Drivers
          1. 17.6.2.1. Connecting to an Excel workbook
          2. 17.6.2.2. Connecting to a text file
          3. 17.6.2.3. ODBC support
      7. 17.7. An Example: Using ADO over the Web
    2. 18. ADOX: Jet Data Definition in ADO
      1. 18.1. The ADOX Object Model
        1. 18.1.1. Creating a Database
        2. 18.1.2. Creating Tables
        3. 18.1.3. The Tables Collection
        4. 18.1.4. Creating Indexes
        5. 18.1.5. Creating a Primary Key
        6. 18.1.6. Creating a Query
        7. 18.1.7. Conclusion
  9. VII. Programming Problems
    1. 19. Some Common Data Manipulation Problems
      1. 19.1. Running Sums
        1. 19.1.1. Solution
      2. 19.2. Overlapping Intervals I
        1. 19.2.1. Solution
      3. 19.3. Overlapping Intervals II
        1. 19.3.1. Solution
      4. 19.4. Making Assignments with Default
        1. 19.4.1. Solution
      5. 19.5. Time to Completion I
        1. 19.5.1. Solution
      6. 19.6. Time to Completion II
        1. 19.6.1. Solution
      7. 19.7. Time to Completion III—A MaxMin Problem
        1. 19.7.1. Solution 1
        2. 19.7.2. Solution 2
      8. 19.8. Vertical to Horizontal
        1. 19.8.1. Solution
      9. 19.9. A Matching Problem
        1. 19.9.1. Solution
      10. 19.10. Equality of Sets
        1. 19.10.1. Solution
  10. VIII. Appendixes
    1. A. DAO 3.0/3.5 Collections, Properties, and Methods
      1. A.1. DAO Classes
      2. A.2. A Collection Object
        1. A.2.1. Methods
        2. A.2.2. Properties
        3. A.2.3. Methods
      3. A.3. Connection Object (DAO 3.5 Only)
        1. A.3.1. Collections
        2. A.3.2. Methods
        3. A.3.3. Properties
      4. A.4. Container Object
        1. A.4.1. Collections
        2. A.4.2. Properties
      5. A.5. Database Object
        1. A.5.1. Collections
        2. A.5.2. Methods
        3. A.5.3. Properties
      6. A.6. DBEngine Object
        1. A.6.1. Collections
        2. A.6.2. Methods
        3. A.6.3. Properties
      7. A.7. Document Object
        1. A.7.1. Methods
        2. A.7.2. Properties
      8. A.8. Error Object
        1. A.8.1. Properties
      9. A.9. Field Object
        1. A.9.1. Collections
        2. A.9.2. Methods
        3. A.9.3. Properties
      10. A.10. Group Object
        1. A.10.1. Collections
        2. A.10.2. Methods
        3. A.10.3. Properties
      11. A.11. Index Object
        1. A.11.1. Collections
        2. A.11.2. Methods
        3. A.11.3. Properties
      12. A.12. Parameter Object
        1. A.12.1. Properties
      13. A.13. Property Object
        1. A.13.1. Properties
      14. A.14. QueryDef Object
        1. A.14.1. Collections
        2. A.14.2. Methods
        3. A.14.3. Properties
      15. A.15. Recordset Object
        1. A.15.1. Collections
        2. A.15.2. Methods
        3. A.15.3. Properties
      16. A.16. Relation Object
        1. A.16.1. Collections
        2. A.16.2. Methods
        3. A.16.3. Properties
      17. A.17. TableDef Object
        1. A.17.1. Collections
        2. A.17.2. Methods
        3. A.17.3. Properties
      18. A.18. User Object
        1. A.18.1. Collections
        2. A.18.2. Methods
        3. A.18.3. Properties
      19. A.19. Workspace Object
        1. A.19.1. Collections
        2. A.19.2. Methods
        3. A.19.3. Properties
    2. B. The Quotient: An Additional Operation of the Relational Algebra
      1. B.1. Step 1
      2. B.2. Step 2
      3. B.3. Step 3
    3. C. Open Database Connectivity (ODBC)
      1. C.1. Introduction
      2. C.2. The ODBC Driver Manager
      3. C.3. The ODBC Driver
        1. C.3.1. Driver Types
      4. C.4. Data Sources
        1. C.4.1. DSNs and Data Source Types
          1. C.4.1.1. Machine data sources
          2. C.4.1.2. File data sources
        2. C.4.2. Creating DSNs: The ODBC Administrator
        3. C.4.3. Example DSNs
          1. C.4.3.1. Excel system data source
          2. C.4.3.2. Excel file data source
          3. C.4.3.3. Text-system data source
        4. C.4.4. Connecting to a Data Source
        5. C.4.5. The SQLConnect Function
        6. C.4.6. Connection Strings
        7. C.4.7. SQLDriverConnect
      5. C.5. Getting ODBC Driver Help
      6. C.6. Getting ODBC Information Using Visual Basic
        1. C.6.1. Preliminaries
        2. C.6.2. Getting Driver Information
        3. C.6.3. Getting Data Sources
    4. D. Obtaining or Creating the Sample Database
      1. D.1. Creating the Database
      2. D.2. Creating the BOOKS Table
      3. D.3. Creating the AUTHORS Table
      4. D.4. Creating the PUBLISHERS Table
      5. D.5. Creating the BOOK/AUTHOR Table
      6. D.6. Backing Up the Database
      7. D.7. Entering and Running the Sample Programs
    5. E. Suggestions for Further Reading
  11. Index
  12. Colophon
  13. Copyright

Product information

  • Title: Access Database Design & Programming, 3rd Edition
  • Author(s): Steven Roman
  • Release date: January 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596002732