1. How to develop a database
application
1.1 How to develop a database application - what this book
contains 1
1.1.1 The database application system life cycle 1
1.1.2 Entity modelling and normalization 3
1.1.3 Database design in Access 4
1.1.4 Query Design in Access 5
1.1.5 Access Forms, Macros and Reports 5
1.1.6 Database Programming in Visual Basic 6
1.2 Visual Basic and Access 8
1.3 Versions of Visual Basic and Access 9
1.4 Compatibility between versions of Visual Basic and Access 9
1.5 Exercises 10
2. Entity modelling and
normalization
2.1 Introduction 11
2.2 Entity modelling
2.2.1 Entity types and relationships 14
2.2.2 Replacing M:N relationships with new entity types 18
2.2.3 Replacing relationships involving more than two entity
types with a new entity type 22
2.2.4 Parallel relationships 23
2.2.5 Mutually exclusive relationships 23
2.2.6 Unnecessary relationships 23
2.3 The Relational model
2.3.1 Selecting an initial set of attributes 24
2.3.2 Terminology 25
2.3.3 Primary keys 27
2.3.4 Foreign keys 28
2.4 Normalisation
2.4.1 First normal form 30
2.4.2 Second normal form 32
2.4.3 Third normal form 34
2.4.4 Fourth normal form 35
2.4.5 Fifth normal form 37
2.5 Exercises 38
3. Access database design
3.1 Introduction 45
3.2 Organizing an application 45
3.3 The Facilities of Access 47
3.4 Creating a database 48
3.5 Creating tables 49
3.6 Creating relationships 58
3.7 Exercises 72
4. Access query design using
Query Design view
4.1 Introduction 73
4.2 Developing queries using the Access Query Design view 73
.3 Exercises 94
5. Access query design using SQL
- DDL and DML statements
5.1 Introduction to SQL 96
5.2 Versions of SQL: Jet and ANSI 97
5.3 Categories of SQL statement 98
5.4 How we describe the syntax of SQL statements 99
5.5 CREATE TABLE 101
5.6 ALTER TABLE 106
5.7 DROP TABLE 109
5.8 CREATE INDEX 113
5.9 DROP INDEX 120
5.10 INSERT INTO 121
5.11 UPDATE 134
5.12 DELETE 143
5.13 Exercises 152
6. Access query design using SQL
- the SELECT statement - single tables and joins
6.1 Introduction 154
6.2 SELECT with a single table
6.2.1 Selecting columns 155
6.2.2 DISTINCT 158
6.2.3 WHERE 161
6.2.4 LIKE 164
6.2.5 BETWEEN 166
6.2.6 IN 167
6.2.7 AND, OR and NOT 169
6.2.8 GROUP BY 175
6.2.9 HAVING 181
6.2.10 ORDER BY 184
6.2.11 TOP 188
6.3 SELECT with multiple tables
6.3.1 SELECT with joined tables 193
6.3.2 INNER JOIN 194
6.3.3 Greater-than join 197
6.3.4 Self join 199
6.3.5 Joining more than two tables 200
6.3.6 LEFT JOIN and RIGHT JOIN 203
6.3.7 Combining INNER JOINs and LEFT JOINs 209
6.3.8 Jet's join nesting rule 212
6.4 Exercises 215
7. Access query design using SQL
- the SELECT statement - further features
7.1 Introduction 217
7.2 SELECT with subqueries 217
7.2.1 Simple Subqueries and their Join equivalents 221
7.2.2 Subquery with Join in the Outer SELECT 226
7.2.3 Where a Join must be used instead of a subquery 227
7.2.4 Joins in the subquery 227
7.2.5 Returning more than one column from a subquery 229
7.2.6 ANY and ALL 230
7.2.7 Replacing ANY and ALL 233
7.2.8 Where a subquery seems essential 234
7.2.9 Correlated subqueries 235
7.2.10 EXISTS and NOT EXISTS 242
7.3 UNION queries 252
7.3.1 Simple UNION examples 252
7.3.2 Column homogeneity 256
7.3.3 Different field names 258
7.3.4 UNION of recordsets with different numbers of columns 259
7.3.5 Simulating an INTERSECT 263
7.3.6 Combinations of UNION and (simulated) INTERSECT 266
7.3.7 Simulating a MINUS 267
7.4 Queries built on queries - Views 271
7.4.1 Simple views 271
7.4.2 Summary views 277
7.4.3 Views based on joins 278
7.4.4 Joining views 280
7.4.5 Views may contain duplicates 282
7.4.6 Updating and views 282
7.4.7 Views as programmers' stepping-stones 287
7.5 SELECT INTO 290
7.6 CROSSTAB queries 293
7.7 Exercises 297
8. Access Forms, Macros and
Reports
8.1 Introduction 299
8.2 Access Forms 300
8.2.1 AutoForms 300
8.2.2 Filtering, Sorting and Finding 304
8.2.3 Form Wizard: Creating a 1:N form 306
8.2.4 Adding a Combo box to an Access form 310
8.2.5 Design view: Creating forms manually 315
8.3 Access Macros 318
8.3.1 Linking Access forms via Macro code 318
8.3.2 Using Conditions in Access Macros 322
8.4 Access Reports 330
8.4.1 Report showing data from two linked tables 331
8.4.2 Report containing a Graph 337
8.5 Exercises 347
9. Visual Basic database
programming using the Data Control
9.1 Introduction 349
9.2 Linking an Access database to Visual Basic using the Data
Control 351
9.3 Adding, Deleting and Changing records 353
9.4 Error handling 357
9.5 Error prevention 359
9.6 A FindFirst button 367
9.7 Adding a Menu 368
9.8 DBGrid with a single table 371
9.9 DBGrid with linked tables in a 1:N form 373
9.10 Using the ADO Data Control - single table updates 376
9.11 Using the ADO Data Control - linked tables in a 1:N form 378
9.12 Exercises 381
10. Visual Basic database
programming using Data Access Objects (DAO)
10.1 Introduction 383
10.2 DAO commands 384
10.3 Displaying data in a VB Grid 386
10.4 Editing data in a VB Grid 390
10.5 Editing data in a VB Grid: two forms 396
10.6 Displaying data from a 1:N relationship using a VB Grid 407
10.7 Producing Reports 413
10.8 Printing a report (using Print#) displaying data from one
table 416
10.9 Printing a report (using Print#) displaying data from three
related tables 418
10.10 Printing a report (using the Printer object) displaying
data from three related tables 422
10.11 Listing the printers available 425
10.12 Exercises 425
11. Visual Basic database
programming using ActiveX Data Objects (ADO)
11.1 The ADO approach 427
11.2 Displaying data in a List Box: ADO and DAO compared 427
11.3 Creating and using a Data Source Name (DSN) 431
11.4 Exercises 435
12. Visual Basic database
programming using Access Modules
12.1 Introduction 436
12.2 Access Modules 436
12.3 Form Modules and Standard Modules 438
12.4 Example: Database filtering on more than one criterion 442
12.5 Example: Transaction processing 450
12.6 Exercises 454
Appendix 1 - Example databases 457
Appendix 2 - Differences between database programming commands in VB versions 474
Appendix 3 - Definitions 477
References - Books, Websites, News Groups, Help files 482
Index 483