Access Database Design and Programming, Second Edition

Access Database Design and Programming, Second Edition

by Steven Roman PhD
Released July 1999
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565926264

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
Start your free trial

Book description

When using software products with graphical interfaces, we frequently focus so much on the details of how to use the interface that we forget about the more general concepts that allow us to understand and put the software to effective use. This is particularly true of a powerful database product like Microsoft Access. Novice, and sometimes even experienced, programmers are so concerned with how something is done in Access that they often lose sight of the general principles that underlie their database applications. Access Database Design & Programming, 2nd Edition, an update to the bestselling first edition, features:

  • A discussion of Access' new VBA Integrated Development Environment, which, for the first time, is the one used by Word, Excel, and PowerPoint

  • An expansion of the discussion of the VBA language itself, in response to reader requests

  • A discussion of Microsoft's latest data access technology, called Active Data Objects (or ADO), along with a discussion of Open Database Connectivity(ODBC), which is intimately connected with ADO

    • Unlike other Access books that take the long, detailed approach to every topic of concern to Access programmers, this book focuses instead on the core concepts, enabling programmers to develop solid, effective database applications. As a result, important topics such as designing forms and reports, database security, database replication, and programming for multiuser applications are simply not discussed. This book is a kind of "second course" in Access that provides a relatively experienced Access user who is new to programming with the frequently overlooked techniques necessary to successfully develop in the Microsoft Access environment. Anyone interested in learning Access in-depth, rather than just scraping the surface, will enjoy and immensely benefit from reading this book. Although this book is really an introduction directed to intermediate Microsoft Access users who are novice programmers, it should appeal to all levels of Access developers. For novice programmers, it focuses on a key body of knowledge that is typically neglected, but is nevertheless essential for developing effective database applications. For intermediate and advanced developers, its treatment of database design and queries provides a handy treatment that otherwise has to be gleaned from relatively uninteresting textbooks, while its programming chapters constitute a handy reference to some basic operations that can be performed using DAO or the Access object model.

    Table of contents

    1. Access Database Design & Programming, 2nd Edition
      1. Dedication
      2. Preface
        1. The Book’s Audience
        2. Organization of This Book
          1. Part I, Database Design
          2. Part II, Database Queries
          3. Part III, Database Architecture
          4. Part IV, Visual Basic for Applications
          5. Part V, Data Access Objects
          6. Part VI, ActiveX Data Objects
          7. Appendixes
        3. Conventions in This Book
        4. Obtaining Updated Information
        5. Request for Comments
        6. Acknowledgments
      3. I. Database Design
        1. 1. Introduction
          1. Database Design
            1. Why Use a Relational Database Design?
              1. Redundancy
              2. Multiple-value problems
              3. Update anomalies
              4. Insertion anomalies
              5. Deletion anomalies
              6. Avoiding data loss
              7. Maintaining relational integrity
              8. Creating views
            2. Summary
          2. Database Programming
        2. 2. The Entity-Relationship Model of a Database
          1. What Is a Database?
          2. Entities and Their Attributes
          3. Keys and Superkeys
          4. Relationships Between Entities
            1. Types of Relationships
        3. 3. Implementing Entity-Relationship Models: Relational Databases
          1. Implementing Entities
            1. Implementing Entity Classes—Table Schemes
            2. Implementing Entity Sets—Tables
          2. A Short Glossary
          3. Implementing the Relationships in a Relational Database
            1. Implementing a One-to-Many Relationship—Foreign Keys
            2. Implementing a One-to-One Relationship
            3. Implementing a Many-to-Many Relationship—New Entity Classes
            4. Referential Integrity
            5. Cascading Updates and Cascading Deletions
          4. The LIBRARY Relational Database
            1. Setting Up the Relationships in Access
          5. Index Files
            1. Example
          6. NULL Values
        4. 4. Database Design Principles
          1. Redundancy
          2. Normal Forms
          3. First Normal Form
          4. Functional Dependencies
          5. Second Normal Form
          6. Third Normal Form
          7. Boyce-Codd Normal Form
          8. Normalization
            1. Decomposition
      4. II. Database Queries
        1. 5. Query Languages and the Relational Algebra
          1. Query Languages
          2. Relational Algebra and Relational Calculus
          3. Details of the Relational Algebra
            1. Renaming
            2. Union
              1. Example
            3. Intersection
            4. Difference
            5. Cartesian Product
            6. Projection
            7. Selection
            8. Joins
              1. Equi-join
              2. Natural join
              3. θ-Join
            9. Outer Joins
            10. Implementing Joins in Microsoft Access
            11. Semi-Joins
            12. Other Relational Algebra Operations
            13. Optimization
        2. 6. Access Structured Query Language (SQL)
          1. Introduction to Access SQL
          2. Access Query Design
          3. Access Query Types
          4. Why Use SQL?
          5. Access SQL
            1. Syntax Conventions
              1. Notes
          6. The DDL Component of Access SQL
            1. The CREATE TABLE Statement
              1. Column definition
              2. Constraints
              3. Notes
            2. The ALTER TABLE Statement
              1. Notes
            3. The CREATE INDEX Statement
              1. Note
            4. The DROP Statement
              1. Note
          7. The DML Component of Access SQL
            1. Updatable Queries
            2. Joins
              1. Inner joins
              2. Outer joins
              3. Nested joins
              4. Self-joins
              5. Notes
            3. The SELECT Statement
              1. Predicate
              2. ReturnColumnDescription
              3. FROM TableExpression
              4. WHERE RowCondition
              5. GROUP BY GroupByCriteria
              6. HAVING GroupCriteria
              7. ORDER BY OrderByCriteria
            4. The UNION Statement
              1. Example
              2. Notes
            5. The UPDATE Statement
              1. Example
            6. The DELETE Statement
            7. The INSERT INTO Statement
              1. Note
            8. The SELECT... INTO Statement
              1. Notes
            9. TRANSFORM
            10. Subqueries
              1. Syntax 1
              2. Syntax 2
              3. Syntax 3
              4. Notes
            11. Parameters
      5. III. Database Architecture
        1. 7. Database System Architecture
          1. Why Program?
          2. Database Systems
          3. Database Management Systems
          4. The Jet DBMS
          5. Data Definition Languages
            1. The Jet Data Definition Language
          6. Data Manipulation Languages
            1. The Jet Data Manipulation Language
          7. Host Languages
          8. The Client/Server Architecture
      6. IV. Visual Basic for Applications
        1. 8. The Visual Basic Editor, Part I
          1. The Project Window
            1. Project Names
            2. Project Contents
              1. Standard modules
              2. Class modules
          2. The Properties Window
          3. The Code Window
            1. Procedure and Full-Module Views
            2. The Object and Procedure List Boxes
          4. The Immediate Window
          5. Arranging Windows
            1. Docking
        2. 9. The Visual Basic Editor, Part II
          1. Navigating the IDE
            1. General Navigation
              1. Navigating the code window at design time
              2. Tracing code
              3. Bookmarks
          2. Getting Help
          3. Creating a Procedure
          4. Run Time, Design Time, and Break Mode
          5. Errors
            1. Design-Time and Compile-Time Errors
            2. Run-Time Errors
            3. Logical Errors
          6. Debugging
            1. Tracing
              1. Step Into (F8 or choose Step Into from the Debug menu)
              2. Step Over (Shift+F8 or choose Step Over from the Debug menu)
              3. Step Out (Ctrl+Shift+F8 or choose Step Out from the Debug menu)
              4. Run to Cursor (Ctrl+F8 or choose Run To Cursor from the Debug menu)
              5. Set Next Statement (Ctrl+F9 or choose Set Next Statement from the Debug menu)
              6. Breaking out of Debug mode
        3. 10. Variables, Data Types, and Constants
          1. Comments
          2. Line Continuation
          3. Constants
            1. Enums
          4. Variables and Data Types
            1. Variable Declaration
            2. The Importance of Explicit Variable Declaration
              1. Option Explicit
            3. Numeric Data Types
            4. Boolean Data Type
            5. String Data Type
            6. Date Data Type
            7. Variant Data Type
            8. Access Object Data Types
              1. The generic As Object declaration
              2. The Set statement
            9. Arrays
              1. The dimension of an array
              2. Dynamic arrays
              3. The UBound function
            10. Variable Naming Conventions
            11. Variable Scope
              1. Procedure-level (local) variables
              2. Module-level variables
            12. Variable Lifetime
              1. Static variables
            13. Variable Initialization
          5. VBA Operators
        4. 11. Functions and Subroutines
          1. Calling Functions
          2. Calling Subroutines
          3. Parameters and Arguments
            1. Optional Arguments
            2. Named Arguments
            3. ByRef Versus ByVal Parameters
          4. Exiting a Procedure
          5. Public and Private Procedures
          6. Fully Qualified Procedure Names
        5. 12. Built-in Functions and Statements
          1. The MsgBox Function
          2. The InputBox Function
          3. VBA String Functions
          4. Miscellaneous Functions and Statements
            1. The Is Functions
              1. The IsDate function
              2. The IsEmpty function
              3. The IsNull function
              4. The IsNumeric function
            2. The Immediate If Function
            3. The Switch Function
            4. The Beep Statement
          5. Handling Errors in Code
            1. The On Error Goto Label Statement
            2. Handling Errors in the Calling Procedure
            3. The Calls Stack
            4. The Error Object
            5. The On Error GoTo 0 Statement
            6. The On Error Resume Next Statement
            7. The Resume Statement
        6. 13. Control Statements
          1. The If...Then Statement
          2. The For Loop
          3. Exit For
          4. The For Each Loop
          5. The Do Loop
          6. The Select Case Statement
          7. A Final Note on VBA
            1. File-Related Functions
            2. Date and Time-Related Functions
            3. The Format Function
      7. V. Data Access Objects
        1. 14. Programming DAO: Overview
          1. Objects
            1. Object Variables
            2. Object Variable Naming Conventions
            3. Referencing the Properties and Methods of an Object
          2. The DAO Object Model
          3. The Microsoft Access Object Model
          4. Referencing Objects
            1. Fully Qualified Object Names
            2. Using Object Variables to Advantage
            3. Default Collections
          5. Collections Are Objects Too
            1. Properties and Methods of Access Collections
            2. Properties and Methods of DAO Collections
            3. Properties and Methods of User-Defined Collections
            4. Say It Again
            5. Refreshing Certain Collections
          6. The Properties Collection
            1. The Virtues of Properties Collections
            2. Types of Properties
              1. Property: Inherited
              2. Property: Name
              3. Property: Type
              4. Property: Value
            3. User-Defined Properties
          7. Closing DAO Objects
          8. A Look at the DAO Objects
            1. DBEngine Object
            2. Errors
            3. Workspaces
            4. Users
            5. Groups
            6. Databases
            7. TableDefs
            8. QueryDefs
            9. Recordsets
            10. Relations
            11. Containers
            12. Documents
            13. Fields
            14. Parameters
            15. Indexes
          9. The CurrentDb Function
        2. 15. Programming DAO: Data Definition Language
          1. Creating a Database
            1. Notes
          2. Opening a Database
          3. Creating a Table and Its Fields
            1. Notes
              1. Note
            2. Changing the Properties of an Existing Table or Field
          4. Creating an Index
          5. Creating a Relation
            1. Notes
          6. Creating a QueryDef
            1. Notes
            2. Running a Query
            3. Properties of a QueryDef Object
        3. 16. Programming DAO: Data Manipulation Language
          1. Recordset Objects
          2. Opening a Recordset
            1. Note
            2. Default Recordset Types
          3. Moving Through a Recordset
            1. BOF and EOF
              1. Notes on the BOF and EOF properties
              2. Notes on the Move methods
          4. Finding Records in a Recordset
            1. Finding Records in a Table-Type Recordset
              1. Notes
            2. Finding Records in a Dynaset-Type or Shapshot-Type Recordset
          5. Editing Data Using a Recordset
            1. Editing an Existing Record
            2. Deleting an Existing Record
              1. Notes
            3. Adding a New Record
              1. Notes
      8. VI. ActiveX Data Objects
        1. 17. ADO and OLE DB
          1. What Is ADO?
          2. Installing ADO
          3. ADO and OLE DB
            1. Data Stores
            2. Data Providers
            3. Data Consumers
            4. Service Providers
          4. The ADO Object Model
            1. The Three-Pronged Approach to Data Manipulation
            2. The Connection Object
              1. Properties of the Connection object
              2. Methods of the Connection object
            3. The Recordset Object
              1. Cursors
              2. LockType
              3. Properties of the Recordset object
              4. Methods of the Recordset object
            4. The Command Object
              1. Command objects and connections
              2. Properties of the Command object
              3. Methods of the Command object
            5. The Property Object and Dynamic Properties
            6. The Field Object
              1. Properties of the Field object
          5. Finding OLE DB Providers
          6. A Closer Look at Connection Strings
            1. The Microsoft Jet 3.51 OLE DB Provider
            2. The Microsoft OLE DB Provider for ODBC Drivers
              1. Connecting to an Excel workbook
              2. Connecting to a text file
              3. ODBC support
      9. VII. Appendixes
        1. A. DAO 3.0/3.5 Collections, Properties, and Methods
          1. DAO Classes
          2. A Collection Object
            1. Methods
            2. Properties
            3. Methods
          3. Connection Object (DAO 3.5 Only)
            1. Collections
            2. Methods
            3. Properties
          4. Container Object
            1. Collections
            2. Properties
          5. Database Object
            1. Collections
            2. Methods
            3. Properties
          6. DBEngine Object
            1. Collections
            2. Methods
            3. Properties
          7. Document Object
            1. Methods
            2. Properties
          8. Error Object
            1. Properties
          9. Field Object
            1. Collections
            2. Methods
            3. Properties
          10. Group Object
            1. Collections
            2. Methods
            3. Properties
          11. Index Object
            1. Collections
            2. Methods
            3. Properties
          12. Parameter Object
            1. Properties
          13. Property Object
            1. Properties
          14. QueryDef Object
            1. Collections
            2. Methods
            3. Properties
          15. Recordset Object
            1. Collections
            2. Methods
            3. Properties
          16. Relation Object
            1. Collections
            2. Methods
            3. Properties
          17. TableDef Object
            1. Collections
            2. Methods
            3. Properties
          18. User Object
            1. Collections
            2. Methods
            3. Properties
          19. Workspace Object
            1. Collections
            2. Methods
            3. Properties
        2. B. The Quotient: An Additional Operation of the Relational Algebra
        3. C. Open Database Connectivity (ODBC)
          1. Introduction
          2. The ODBC Driver Manager
          3. The ODBC Driver
            1. Driver Types
          4. Data Sources
            1. DSNs and Data Source Types
              1. Machine data sources
              2. File data sources
            2. Creating DSNs: The ODBC Administrator
            3. Example DSNs
              1. Excel system data source
              2. Excel file data source
              3. Text system data source
            4. Connecting to a Data Source
            5. The SQLConnect Function
            6. Connection Strings
            7. SQLDriverConnect
          5. Getting ODBC Driver Help
          6. Getting ODBC Information Using Visual Basic
            1. Preliminaries
            2. Getting Driver Information
            3. Getting Data Sources
        4. D. Obtaining or Creating the Sample Database
          1. Creating the Database
          2. Creating the BOOKS Table
          3. Creating the AUTHORS Table
          4. Creating the PUBLISHERS Table
          5. Creating the BOOK/AUTHOR Table
          6. Backing Up the Database
          7. Entering and Running the Sample Programs
        5. E. Suggestions for Further Reading
      10. Index
      11. Colophon

    Product information

    • Title: Access Database Design and Programming, Second Edition
    • Author(s): Steven Roman PhD
    • Release date: July 1999
    • Publisher(s): O'Reilly Media, Inc.
    • ISBN: 9781565926264