Access Database Design & Programming. Creating Programmable Database Applications with Access 97, 2000, 2002 & 2003. 3rd Edition (e-book) Bieruń

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. Spis treści: 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