Access database design & programming: [what you really need to know to develop with access]

✍ Scribed by Steven Roman PhD

• Publisher: O'Reilly Media
• Year: 1999
• Tongue: English
• Leaves: 363
• Series: Nutshell Handbooks
• Edition: 2
• Category: Library

✦ Synopsis

While targeted at intermediate Microsoft Access users who are novice programmers, this second edition of the bestselling Access Database Design & Programming should appeal to all levels of Access developers. In particular, the new edition includes:

A discussion of Access' new VBA Integrated Development Environment used by Word, Excel, and PowerPointExpanded coverage of the VBA language itself, in response to reader requestsDiscussions of Microsoft's latest data access technology, called Active Data Objects (or ADO), and Open Database Connectivity (ODBC), which is intimately connected with ADO

For novice programmers, this book focuses on typically neglected, but nevertheless essential, knowledge for developing effective database applications. Its treatment of database design and queries gives intermediate and advanced developers a consolidated source of info, while its programming chapters constitute a handy reference to basic operations that can be performed using DAO or the Access object model.

✦ Table of Contents

Table of Contents......Page 1
Preface......Page 5
1.1 Database Design......Page 12
1.2 Database Programming......Page 18
2.1 What Is a Database......Page 19
2.2 Entities and Their Attributes......Page 20
2.3 Keys and Superkeys......Page 23
2.4 Relationships Between Entities......Page 24
3.1 Implementing Entities......Page 26
3.2 A Short Glossary......Page 28
3.3 Implementing the Relationships in a Relational Database......Page 30
3.4 The Library Relational Database......Page 35
3.5 Index Files......Page 39
3.6 Null Values......Page 41
4.1 Redundancy......Page 42
4.2 Normal Forms......Page 44
4.4 Functional Dependencies......Page 45
4.5 Second Normal Form......Page 47
4.6 Third Normal Form......Page 48
4.7 Boyco-Codd Normal Form......Page 50
4.8 Normalization......Page 51
5. Query Language and the Relational Algebra......Page 56
5.1 Query Languages......Page 58
5.2 Relational Algebra and Relational Calculus......Page 59
5.3 Details of the Relational Algebra......Page 61
6.1 Introduction to Access SQL......Page 85
6.2 Access Query Design......Page 86
6.3 Access Query Types......Page 87
6.4 Why Use SQL?......Page 88
6.5 Access SQL......Page 89
6.6 The DDL Component of Access SQL......Page 91
6.7 The DML Component of Access SQL......Page 95
7.1 Why Program?......Page 117
7.2 Database Systems......Page 119
7.3 Database Management Systems......Page 120
7.4 The Jet DBMS......Page 121
7.5 Data Definition Languages......Page 123
7.6 Data Manipulation Languages......Page 124
7.7 Host Languages......Page 125
7.8 The Client/Server Architecture......Page 126
8. The Visual Basic Editor Part I......Page 127
8.1 The Project Window......Page 128
8.2 The Properties Window......Page 130
8.3 The Code Window......Page 131
8.5 Arranging Windows......Page 133
9.1 Navigating the IDE......Page 135
9.3 Creating a Procedure......Page 136
9.4 Run Time, Design Time, and Break Mode......Page 137
9.5 Errors......Page 138
9.6 Debugging......Page 141
10. Variables, Data Types, and Constants......Page 143
10.3 Constants......Page 144
10.4 Variables and Data Types......Page 147
10.5 VBA Operators......Page 162
11.1 Calling Functions......Page 163
11.2 Calling Subroutines......Page 164
11.3 Parameters and Arguments......Page 165
11.5 Public and Private Procedures......Page 169
12. Built-in Functions and Statements......Page 170
12.1 The MsgBox Function......Page 172
12.3 VBA String Functions......Page 174
12.4 Miscellaneous Functions and Statements......Page 179
12.5 Handling Errors in Code......Page 182
13.1 The If--- Then Statement......Page 189
13.2 The For Loop......Page 190
13.3 Exit For......Page 191
13.4 The For Each Loop......Page 192
13.5 The Do Loop......Page 193
13.6 The Select Case Statement......Page 194
13.7 A Final Note on VBA......Page 195
14. Programming DAO : Overview......Page 196
14.1 Objects......Page 197
14.2 The DAO Object Model......Page 203
14.3 The Microsoft Access Object Model......Page 205
14.4 Referencing Objects......Page 207
14.5 Collections Are Objects Too......Page 211
14.6 The Properties Collection......Page 216
14.7 Closing DAO Objects......Page 222
14.8 A Look at the DAO Objects......Page 223
14.9 The CurrentDB Function......Page 231
15. Programming DAO : Data Definition Language......Page 236
15.1 Creating a Database......Page 237
15.3 Creating a Table and Its Fields......Page 238
15.4 Creating an Index......Page 241
15.5 Creating a Relation......Page 243
15.6 Creating a QueryDef......Page 245
16.1 Recordset Objects......Page 248
16.2 Opening a Recordset......Page 249
16.3 Moving Through a Recordset......Page 251
16.4 Finding Records in a Recordset......Page 254
16.5 Editing Data Using a Recordset......Page 256
17.1 What Is ADO?......Page 260
17.2 Installing ADO......Page 261
17.3 ADO and OLE DB......Page 262
17.4 The ADO Object Model......Page 265
17.5 Finding OLE DB Providers......Page 300
17.6 A Closer Loot at Connection Strings......Page 305
A. DAO 3.0/3.5 Collections, Properties, and Methods......Page 318
B. The Quotient: An Additional Operation of the Relational Algebra......Page 332
C. Open Database Connectivity(ODBC)......Page 335
D. Obtaining or Creating the Sample Database......Page 354
E. Suggestions for Future Reading......Page 362
Colophon......Page 363