Access Book, Third Edition

Access Database Design & Programming, Third Edition

ISBN: 1-596-00273-4, 425 pp., $34.95

Return to Roman Press Home Page
Return to computer books' main page

To order this book through Amazon.com, please click here

Access Database Design & Programming, Third Edition focuses on three areas:

Database design. Access makes it so easy to create databases that users are tempted to create the tables of a database without designing them beforehand. Moreover, existing books and documentation focus on the details of using the interface, and neglect the general principles of database design. The consequences of this become painfully evident at a later date, when problems in database design result in a loss of information, or when the retrieval of information from the database becomes too slow to be worthwhile. This book provides an informative overview of database design that carefully shows the reader how to normalize tables to eliminate data redundancy without losing data.

Queries. The attractive query design dialog that Access provides tends to obscure the fact that the Access interface can be used for some kinds of queries but does not support others. In particular, 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 provides an introduction to the Data Access Object (DAO) and Microsoft Access object models, which allow a developer to place a database under program control. Rather than covering these object models in an encyclopedic fashion, the section serves as a handy introduction and primer for basic database operations, like: modifying a table under program control, dynamically adding and deleting a record, or repositioning a record pointer.

Unlike other Access books that take the long, detailed approach to every topic of concern to Access programmers, this book focuses instead on the core concepts, enabling programmers to develop solid, effective database applications. As a result, important topics such as designing forms and reports, database security, database replication, and programming for multi-user applications are simply not discussed. This book is a kind of "second course" in Access that provides a relatively experienced Access user who is new to programming with the frequently overlooked techniques necessary to successfully develop in the Microsoft Access environment. Anyone interested in learning Access in-depth, rather than just scraping the surface, will enjoy and immensely benefit from reading this book.

Although this book is really an introduction targeted at intermediate Microsoft Access users who are novice programmers, it should appeal to all levels of Access developers. For novice programmers, it focuses on a key body of knowledge that is typically neglected, but is nevertheless essential for developing effective database applications. For intermediate and advanced developers, its treatment of database design and queries provides a handy treatment that otherwise has to be gleaned from relatively uninteresting textbooks, while its programming chapters constitute a handy reference to some basic operations that can be performed using DAO or the Access object model.

New for the Third Edition

Added to the end of Chapter 17: Using ADO Over the Web
A new chapter entitled ADOX: Jet Data Definition In ADO
A new chapter entitled Some Common Data Manipulation Problems

Preface to the Third Edition

As with the second edition, let me begin by thanking all of those readers who have helped to make this book so successful.

The third edition of the book includes two new chapters. Chapter 18 is entitled ADOX: Jet Data Definition In ADO. With the sad, and in my opinion highly unfortunate demise of DAO at Microsoft's hands, it seemed necessary to bring the book up to speed on that aspect of ADO that gives the programmer most of the functionality of the Data Definition Language (DDL) portion of DAO.

ADOX is an acronym for ADO Extensions for Data Definition and Security. When making comparisons between ADO and DAO, proponents of DAO will point out that ADO does not include features for data definition, that is, features that can be used to create and alter databases and their components (tables, columns, indices, etc.). This is precisely the purpose of ADOX. (Our concern here is with ADOX as it relates to Jet.)

Unfortunately, ADOX is not a complete substitute for DAO's data definition features. For example, query creation in ADOX has a serious wrinkle. Namely, a query created using ADOX will not appear in the Access user interface! We will elaborate on this in Chapter 18.

The other new chapter for the third edition is Chapter 19: Some Common Data Manipulation Problems. In this chapter, I present a number of problems that are commonly encountered when dealing with data, along with their solutions couched in terms of SQL. I hope that this chapter will provide some good food for thought, as well as useful examples for your own applications.

Preface to the Second Edition

Let me begin by thanking all of those readers who have helped to make the first edition of this book so very successsful. Also, my sincere thanks go to the many readers who have written some very flattering reviews of the first edition on amazon.com and on O'Reilly's own web site. Keep them coming.

With the recent release of Office 2000, and in view of the many suggestions I have received concerning the first edition of the book, it seemed like an appropriate time to do a second edition.

Actually, Access has undergone only relatively minor changes in its latest release, at least with respect to the subject matter of this book. Changes for the Second Edition are:

A discussion (Chapters 8 and 9) of Access' new VBA Integrated Development Environment. At last Access shares the same IDE as Word, Excel and PowerPoint!
In response to reader requests, I have significantly expanded the discussion of the VBA language itself, which now occupies Chapters 10-13.
Chapter 17, which is new for this edition, provides a fairly complete discussion of Active Data Objects (ADO). This is also accompanied by an appendix on Open Database Connectivity (ODBC), which is still intimately connected with ADO.

As you may know, ADO is a successor to DAO and is intended to eventually replace DAO, although I suspect that this will take some considerable time. While the DAO model is the programming interface for the Jet database engine, ADO has a much more ambitious goal-it is a programming model for a universal data access interface called OLE DB. Simply put, OLE DB is a technology that is intended to be used to connect to any type of data-traditional database data, spreadsheet data, Web based data, text data, e-mail and so on.

Frankly, while the ADO object model is smaller than that of DAO, the documentation is much less complete and, as a result, ADO seems far more confusing than DAO, especially when it comes to issues such as how to create the infamous connection strings. Accordingly, I have spent considerable time discussing this and other difficult issues, illustrating how to use ADO to connect to Jet databases, Excel spreadsheets and text files.

I should also mention that while the Access object model has undergone significant changes, as you can see by looking at Figure 14-7, the DAO object model has changed only in one respect. In particular, DAO has been upgraded from version 3.5 to version 3.6. Here is what Microsoft itself says about this new release:

DAO 3.6 has been updated to use the Microsoft® Jet 4.0 database engine. This includes enabling all interfaces for Unicode. Data is now provided in unicode (internationally enabled) format rather than ANSI. No other new features were implemented.

Thus, DAO 3.6 does not include any new objects, properties or methods.

I hope that readers will find the second edition of the book to be even more useful than the first edition. Please keep the suggestions and comments coming in.

Preface to the First Edition

This book appears to be about two separate topics - database design and database programming. It is.

It would be misleading to claim that database design and database programming are intimately related. So why are they in the same book?

The answer is that while these two subjects are not related, in the sense that knowledge of one leads directly to knowledge of the other, they are definitiely linked, by the simple fact that a power database user needs to know something about both of these subjects in order to effectively create, use and maintain a database.

In fact, it might be said that creating and maintaining a database application in Microsoft Access is done in three broad steps - designing the database, creating the basic graphical interface (that is, setting up the tables, queries, forms and reports) and then getting the application to perform in the desired way.

The second of these three steps is fairly straightforward, for it is mostly a matter of becoming familiar with the relatively easy to use Access graphical interface. Help is available for this through Access' own on-line help system, as well as through the literally dozens of overblown 1000-page-plus tombs devoted to Microsoft Access. Unfortunately, none of the books that I have seen does any real justice to the other two steps. Hence this book.

To be a bit more specific, the book has two goals:

To discuss the basic concepts of relational database theory and design.
To discuss how to extract the full power of Microsoft Access, through programming in the Access structured query language (SQL) and the Data Access Object (DAO) component of the Microsoft Jet database engine.

To accomplish the first goal, we describe the how and why of creating an efficient database system, explaining such concepts as

entities and entity classes
keys, superkeys and primary keys
one-to-one, one-to-many and many-to-many relationships
referential integrity
joins of various types (inner joins, outer joins, equi-joins, semi-joins, q-joins and so on)
operations of the relational algebra (selection, projection, join, union, intersection and so on)
normal forms and their importance

Of course, once you have a basic understanding of how to create an effective relational database, you will want to take full advantage of that database, which can only be done through programming. In addition, many of the programming techniques we discuss in this book can be used to create and maintain a database from within other applications, such as Microsoft Visual Basic, Microsoft Excel and Microsoft Word.

We should hasten to add that this book is not a traditional cookbook for learning Microsoft Access. For instance, we do not discuss forms and reports, nor do we discuss such issues as database security, database replication and multi-user issues. This is why we have been able to keep the book to a (hopefully) readable few hundred pages.

This book is for Access users at all levels. It applies equally well to Access 2.0, Access 7.0 and Access 8.0 (which is a component of Microsoft Office 97). We will assume that you have a passing acquaintance with the Access development environment, however. For instance, we assume that you already know how to create a table or a query.

Throughout the book, we will use a specific modest-sized example to illustrate the concepts that we discuss. The example consists of a database called LIBRARY that is designed to hold data about the books in a certain library. Of course, the amount of data we will use will be kept artificially small - just enough to illustrate the concepts.

Preface 

I. Database Design
1. Introduction 
   Database Design 
   Database Programming 
2. The Entity-Relationship Model of a Database 
   What Is a Database? 
   Entities and Their Attributes 
   Keys and Superkeys 
   Relationships Between Entities 
3. Implementing Entity-Relationship Models: Relational Databases 
   Implementing Entities 
   A Short Glossary 
   Implementing the Relationships in a Relational Database 
   The LIBRARY Relational Database 
   Index Files 
   NULL Values 
4. Database Design Principles 
   Redundancy 
   Normal Forms 
   First Normal Form 
   Functional Dependencies 
   Second Normal Form 
   Third Normal Form 
   Boyce-Codd Normal Form 
   Normalization 

II. Database Queries
5. Query Languages and the Relational Algebra 
   Query Languages 
   Relational Algebra and Relational Calculus 
   Details of the Relational Algebra 
6. Access Structured Query Language (SQL) 
   Introduction to Access SQL 
   Access Query Design 
   Access Query Types 
   Why Use SQL? 
   Access SQL 
   The DDL Component of Access SQL 
   The DML Component of Access SQL 

III. Database Architecture
7. Database System Architecture 
   Why Program? 
   Database Systems 
   Database Management Systems 
   The Jet DBMS 
   Data Definition Languages 
   Data Manipulation Languages 
   Host Languages 
   The Client/Server Architecture 

IV. Visual Basic For Applications
8. The Visual Basic Editor, Part 1
	The Project Window
	The Properties Window
	The Code Window
	The Immediate Window
	Arranging Windows
9. The Visual Basic Editor, Part II
	Navigating the IDE
	Getting Help
	Creating a Procedure
	Run Time, Design Time, and Break Mode
	Errors
	Debugging
10. Variables, Data Types, and Constants
	Comments
	Line Continuation
	Constants
	Variables and Data Types
	VBA Operators
11. Functions and Subroutines
	Calling Functions
	Calling Subroutines
	Parameters and Arguments
	Exiting a Procedure
	Public and Private Procedures
	Fully Qualified Procedure Names
12. Built-In Functions and Statements
	The MsgBox Function
	The InputBox Function
	VBA String Functions
	Miscellaneous Functions and Statements
	Handling Errors in Code
13. Control Statements
	The If … Then Statement
	The For Loop
	A Final Note on VBA

V. Data Access Objects
14. Programming DAO: Overview 
   Objects 
   The DAO Object Model 
   The Microsoft Access Object Model 
   Referencing Objects 
   Collections Are Objects Too 
   The Properties Collection 
   Closing DAO Objects 
   A Look at the DAO Objects 
   The CurrentDb Function 
15. Programming DAO: Data Definition Language 
   Creating a Database 
   Opening a Database 
   Creating a Table and Its Fields 
   Creating an Index 
   Creating a Relation 
   Creating a QueryDef 
16. Programming DAO: Data Manipulation Language 
   Recordset Objects 
   Opening a Recordset 
   Moving Through a Recordset 
   Finding Records in a Recordset 
   Editing Data Using a Recordset 

VI. ActiveX Data Objects
17. ADO and OLE DB
	What is ADO?
	Installing ADO
	ADO and OLE DB
	The ADO Object Model
	Finding OLE DB Providers
	A Closer Look at Connection Strings
    An Example: Using ADO over the Web 

18. ADOX: Jet Data Definition in ADO
    The ADOX Object Model 

VII. Programming Problems

19. Some Common Data Manipulation Problems
    Running Sums 
    Overlapping Intervals I 
    Overlapping Intervals II 
    Making Assignments with Default 
    Time to Completion I 
    Time to Completion II 
    Time to Completion III-A MaxMin Problem 
    Vertical to Horizontal 
    A Matching Problem 
    Equality of Sets 

VIII. Appendices
A. DAO 3.0/3.5 Collections, Properties, and Methods 
B. The Quotient: An Additional Operation of the Relational Algebra 
C. Open Database Connectivity (OBDC) 
	Introduction
	The ODBC Driver Manager
	The ODBC Driver
	Data Sources
	Creating DSNs - The ODBC Administrator
	Example DSNs
	Connecting to a Data Source
	Getting ODBC Driver Help
	Getting ODBC Information Using Visual Basic
D. Obtaining or Creating the Sample Database 
E. Suggestions for Further Reading 
Index

To order this book through Amazon.com, please click here
Return to Roman Press Home Page
Return to computer books' main page

Access Database Design & Programming, Third Edition

New for the Third Edition

Preface to the Third Edition

Preface to the Second Edition

Preface to the First Edition

Table of Contents

I. Database Design

II. Database Queries

III. Database Architecture

IV. Visual Basic For Applications

V. Data Access Objects

VI. ActiveX Data Objects

VII. Programming Problems

VIII. Appendices