Access Database Design & Programming. Creating Programmable Database Applications with Access 97, 2000, 2002 & 2003. 3rd Edition PhD Steven Roman

Spis treści książki

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

pokaż cały spis treści

ukryj recenzje