Access_Database_Design_and_Programming_3rd_Edition.(2000).Jan2002.OReilly.pdf
(
2273 KB
)
Pobierz
Access Database Design & Programming, 3rd Edition - Jan. 2002 - O'Reilly
Access Database Design & Programming, 3rd Edition
Steven Roman
Publisher: O’Reilly
Third Edition January 2002
ISBN: 0-596-00273-4, 448 pages
When using GUI-based software, we often focus so much on the interface
that we forget about the general concepts required to use the software
effectively.
Access Database Design & Programming
takes you behind the
details of the interface, focusing on the general knowledge necessary for
Access power users or developers to create effective database applications.
The main sections of this book include: database design, queries, and
programming.
Copyright .................................................................................................................... 5
Full Description .......................................................................................................... 6
Steven Roman ............................................................................................................. 7
O’Reilly Books ........................................................................................................... 7
O’Reilly Articles ......................................................................................................... 7
Preface............................................................................................................................. 8
Preface to the Third Edition ........................................................................................ 8
Preface to the Second Edition ..................................................................................... 8
The Book’s Audience ............................................................................................... 11
The Sample Code ...................................................................................................... 11
Organization of This Book........................................................................................ 11
Conventions in This Book ........................................................................................ 14
Obtaining Updated Information ................................................................................ 15
Request for Comments.............................................................................................. 15
Acknowledgments..................................................................................................... 16
Part I: Database Design..................................................................................................... 17
Chapter 1. Introduction ................................................................................................. 18
1.1 Database Design.................................................................................................. 18
1.2 Database Programming ....................................................................................... 24
Chapter 2. The Entity-Relationship Model of a Database ............................................ 25
2.1 What Is a Database?............................................................................................ 25
2.2 Entities and Their Attributes............................................................................... 25
2.3 Keys and Superkeys............................................................................................ 29
2.4 Relationships Between Entities........................................................................... 30
Chapter 3. Implementing Entity-Relationship Models: Relational Databases ............. 32
3.1 Implementing Entities ......................................................................................... 32
3.2 A Short Glossary................................................................................................. 34
3.3 Implementing the Relationships in a Relational Database ................................. 36
3.4 The LIBRARY Relational Database................................................................... 40
3.5 Index Files........................................................................................................... 44
3.6 NULL Values...................................................................................................... 46
Chapter 4. Database Design Principles ......................................................................... 48
4.1 Redundancy......................................................................................................... 48
4.2 Normal Forms ..................................................................................................... 50
4.3 First Normal Form .............................................................................................. 50
4.4 Functional Dependencies .................................................................................... 51
4.5 Second Normal Form.......................................................................................... 52
4.6 Third Normal Form............................................................................................. 53
4.7 Boyce-Codd Normal Form ................................................................................. 55
4.8 Normalization ..................................................................................................... 56
Part II: Database Queries .................................................................................................. 62
Chapter 5. Query Languages and the Relational Algebra............................................. 63
5.1 Query Languages ................................................................................................ 64
5.2 Relational Algebra and Relational Calculus ....................................................... 65
5.3 Details of the Relational Algebra........................................................................ 67
6. Access Structured Query Language (SQL)............................................................... 91
6.1 Introduction to Access SQL................................................................................ 91
6.2 Access Query Design .......................................................................................... 91
6.3 Access Query Types ........................................................................................... 92
6.4 Why Use SQL? ................................................................................................... 94
6.5 Access SQL......................................................................................................... 95
6.6 The DDL Component of Access SQL ................................................................ 96
6.7 The DML Component of Access SQL.............................................................. 100
Part III: Database Architecture ....................................................................................... 123
7. Database System Architecture ................................................................................ 124
7.1 Why Program? .................................................................................................. 124
7.2 Database Systems.............................................................................................. 125
7.3 Database Management Systems........................................................................ 127
7.4 The Jet DBMS................................................................................................... 127
7.5 Data Definition Languages ............................................................................... 129
7.6 Data Manipulation Languages .......................................................................... 130
7.7 Host Languages................................................................................................. 131
7.8 The Client/Server Architecture ......................................................................... 132
Part IV: Visual Basic for Applications ........................................................................... 134
Chapter 8. The Visual Basic Editor, Part I.................................................................. 135
8.1 The Project Window ......................................................................................... 136
8.2 The Properties Window .................................................................................... 138
8.3 The Code Window ............................................................................................ 138
8.4 The Immediate Window ................................................................................... 140
8.5 Arranging Windows.......................................................................................... 141
Chapter 9. The Visual Basic Editor, Part II ................................................................ 143
9.1 Navigating the IDE ........................................................................................... 143
9.2 Getting Help...................................................................................................... 144
9.3 Creating a Procedure......................................................................................... 144
9.4 Run Mode, Break Mode, and Design Mode ..................................................... 145
9.5 Errors................................................................................................................. 146
9.6 Debugging......................................................................................................... 149
Chapter 10. Variables, Data Types, and Constants..................................................... 152
10.1 Comments ....................................................................................................... 152
10.2 Line Continuation ........................................................................................... 152
10.3 Constants......................................................................................................... 152
10.4 Variables and Data Types ............................................................................... 155
10.5 VBA Operators ............................................................................................... 170
Chapter 11. Functions and Subroutines ...................................................................... 171
11.1 Calling Functions ............................................................................................ 171
11.2 Calling Subroutines......................................................................................... 172
11.3 Parameters and Arguments ............................................................................. 173
11.4 Exiting a Procedure......................................................................................... 177
11.5 Public and Private Procedures ........................................................................ 177
11.6 Fully Qualified Procedure Names................................................................... 178
Chapter 12. Built-in Functions and Statements .......................................................... 179
12.1 The MsgBox Function .................................................................................... 180
12.2 The InputBox Function ................................................................................... 181
12.3 VBA String Functions..................................................................................... 182
12.4 Miscellaneous Functions and Statements ....................................................... 187
12.5 Handling Errors in Code ................................................................................. 190
Chapter 13. Control Statements .................................................................................. 198
13.1 The If ...Then Statement ................................................................................. 198
13.2 The For Loop .................................................................................................. 198
13.3 The Exit For Statement ................................................................................... 199
13.4 The For Each Loop ......................................................................................... 200
13.5 The Do Loop ................................................................................................... 201
13.6 The Select Case Statement.............................................................................. 202
13.7 A Final Note on VBA ..................................................................................... 203
Part V: Data Access Objects ........................................................................................... 206
Chapter 14. Programming DAO: Overview ............................................................... 207
14.1 Objects ............................................................................................................ 207
14.2 The DAO Object Model.................................................................................. 213
14.3 The Microsoft Access Object Model .............................................................. 215
14.4 Referencing Objects........................................................................................ 216
14.5 Collections Are Objects Too........................................................................... 221
14.6 The Properties Collection ............................................................................... 226
14.7 Closing DAO Objects ..................................................................................... 231
14.8 A Look at the DAO Objects............................................................................ 232
14.9 The CurrentDb Function ................................................................................. 240
Running exaCurrentDb2 ......................................................................................... 244
Chapter 15. Programming DAO: Data Definition Language ..................................... 247
15.1 Creating a Database ........................................................................................ 247
15.2 Opening a Database ........................................................................................ 248
15.3 Creating a Table and Its Fields ....................................................................... 249
15.4 Creating an Index............................................................................................ 252
15.5 Creating a Relation ......................................................................................... 254
15.6 Creating a QueryDef ....................................................................................... 256
Chapter 16. Programming DAO: Data Manipulation Language ................................ 260
16.1 Recordset Objects ........................................................................................... 260
16.2 Opening a Recordset ....................................................................................... 261
16.3 Moving Through a Recordset ......................................................................... 262
16.4 Finding Records in a Recordset ...................................................................... 266
16.5 Editing Data Using a Recordset ...................................................................... 268
Part VI: ActiveX Data Objects ....................................................................................... 273
17. ADO and OLE DB................................................................................................ 274
17.1 What Is ADO?................................................................................................. 274
17.2 Installing ADO................................................................................................ 275
17.3 ADO and OLE DB.......................................................................................... 276
17.4 The ADO Object Model.................................................................................. 279
17.5 Finding OLE DB Providers ............................................................................ 314
17.6 A Closer Look at Connection Strings ............................................................. 319
17.7 An Example: Using ADO over the Web......................................................... 332
Chapter 18. ADOX: Jet Data Definition in ADO ....................................................... 337
18.1 The ADOX Object Model............................................................................... 337
Part VII: Programming Problems ................................................................................... 345
Chapter 19. Some Common Data Manipulation Problems......................................... 346
19.1 Running Sums ................................................................................................. 346
19.2 Overlapping Intervals I ................................................................................... 349
19.3 Overlapping Intervals II .................................................................................. 350
19.4 Making Assignments with Default ................................................................. 353
19.5 Time to Completion I...................................................................................... 355
19.6 Time to Completion II .................................................................................... 356
19.7 Time to Completion III—A MaxMin Problem............................................... 358
19.8 Vertical to Horizontal ..................................................................................... 361
19.9 A Matching Problem....................................................................................... 363
19.10 Equality of Sets ............................................................................................. 364
Part VIII: Appendixes ..................................................................................................... 367
Appendix A. DAO 3.0/3.5 Collections, Properties, and Methods .............................. 368
A.1 DAO Classes .................................................................................................... 369
A.2 A Collection Object ......................................................................................... 369
A.3 Connection Object (DAO 3.5 Only) ................................................................ 370
A.4 Container Object .............................................................................................. 371
A.5 Database Object ............................................................................................... 371
A.6 DBEngine Object ............................................................................................. 372
A.7 Document Object ............................................................................................. 374
A.8 Error Object...................................................................................................... 374
A.9 Field Object...................................................................................................... 374
A.10 Group Object.................................................................................................. 375
A.11 Index Object................................................................................................... 376
A.12 Parameter Object............................................................................................ 376
A.13 Property Object .............................................................................................. 376
A.14 QueryDef Object ............................................................................................ 377
A.15 Recordset Object ............................................................................................ 378
A.16 Relation Object .............................................................................................. 380
A.17 TableDef Object ............................................................................................. 380
A.18 User Object .................................................................................................... 381
A.19 Workspace Object .......................................................................................... 381
Appendix B. The Quotient: An Additional Operation of the Relational Algebra ...... 383
B.1 Step 1................................................................................................................ 384
B.2 Step 2................................................................................................................ 384
B.3 Step 3................................................................................................................ 385
Appendix C. Open Database Connectivity (ODBC) .................................................. 386
C.1 Introduction ...................................................................................................... 386
C.2 The ODBC Driver Manager............................................................................. 387
C.3 The ODBC Driver ............................................................................................ 388
C.4 Data Sources..................................................................................................... 389
C.5 Getting ODBC Driver Help.............................................................................. 397
C.6 Getting ODBC Information Using Visual Basic.............................................. 397
Appendix D. Obtaining or Creating the Sample Database ......................................... 406
D.1 Creating the Database ...................................................................................... 407
D.2 Creating the BOOKS Table ............................................................................. 408
D.3 Creating the AUTHORS Table ........................................................................ 409
D.4 Creating the PUBLISHERS Table ................................................................... 410
D.5 Creating the BOOK/AUTHOR Table.............................................................. 411
D.6 Backing Up the Database ................................................................................. 412
D.7 Entering and Running the Sample Programs ................................................... 413
Appendix E. Suggestions for Further Reading ........................................................... 415
Colophon..................................................................................................................... 416
Copyright
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved.
Printed in the United States of America.
Published by O’Reilly & Associates, Inc., 1005 Gravenstein Highway
North, Sebastopol, CA 95472.
O’Reilly & Associates books may be purchased for educational, business,
or sales promotional use. Online editions are also available for most titles
(http://safari.oreilly.com). For more information contact our
corporate/institutional sales department: 800-998-9938 or
corporate@oreilly.com.
Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo
are registered trademarks of O’Reilly & Associates, Inc. ActiveX,
Microsoft, Visual Basic, Windows, and Windows NT are registered
trademarks of Microsoft Corporation. Many of the designations used by
Plik z chomika:
pawlok87
Inne pliki z tego folderu:
!done - Access 2000 - Księga eksperta.rar
(16729 KB)
(Ebook - English) Hacking For Dummies - Access To Other Peoples Systems Made Simple.pdf
(1294 KB)
(eBook English) MS Access 2003 - The Complete Reference - McGraw - Hill - (2003).pdf
(7859 KB)
=ISO-8859-1Qprosz=EA= o =ISO-8859-1Qksi=B9=BFk=EA= na temat Accessa (by mirekmn mirekmn@neostrada.pl).txt
(0 KB)
Abc_Access_2003.rar
(17165 KB)
Inne foldery tego chomika:
Excel
Power Point
Word
Zgłoś jeśli
naruszono regulamin