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, please click here

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

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

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 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:

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 accomplish the first goal, we describe the how and why of creating an efficient database system, explaining such concepts as

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.

Table of Contents


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, please click here
Return to Roman Press Home Page
Return to computer books' main page