Access Database Design & Programming, 3rd Edition

Preface

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; the first of which is Chapter 18. 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, indexes, 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! I elaborate on this in Chapter 18.

The other new chapter for the third edition is Chapter 19. 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 successful. 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. I hope that readers will find the second edition of the book to be even more useful than the first 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 (Chapter 8 and Chapter 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 Chapter 10, Chapter 11, Chapter 12, and Chapter 13.

• Chapter 17, which is new for this edition, provides a fairly complete discussion of ActiveX 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 (Data Access Objects) and is intended to eventually replace DAO, although I suspect that this will take 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 to connect to any type of data—traditional database data, spreadsheet data, web-based data, text data, email, and so on.

  Frankly, while the ADO object model is smaller than that of DAO, the documentation is much less complete. 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.

This book appears to cover two separate topics—database design and database programming. It does. 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 definitely linked, by the simple fact that a power database user needs to know something about both of these subjects 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 (i.e., 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’ online help system, as well as through the dozens of overblown 1,000-plus-page tomes 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, I 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, θ-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 I 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.

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

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

Throughout the book, I will use a specific modest-sized example to illustrate the concepts discussed. 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 used will be kept artificially small—just enough to illustrate the concepts.

The Book’s Audience

Most books on Microsoft Access focus primarily on the Access interface and its components, giving little attention to the more important issue of database design. After all, once the database application is complete, the interface components play only a small role, whereas the design continues to affect the usefulness of the application.

In attempting to restore the focus on database design, this book aspires to be a kind of “second course” in Microsoft Access—a book for Access users who have mastered the basics of the interface, are familiar with such things as creating tables and designing queries, and now want to move beyond the interface to create programmable Access applications. This book provides a firm foundation on which you can begin to build your database-application development skills.

At the same time that this book is intended primarily as an introduction to Access for aspiring database-application developers, it also is of interest to more experienced Access programmers. For the most part, such topics as normal forms or the details of the relational algebra are almost exclusively the preserve of the academic world. By introducing these topics to the mainstream Access audience, Access Database Design and Programming offers a concise, succinct, readable guide that experienced Access developers can turn to whenever some of the details of database design or SQL statements escape them.

The Sample Code

To follow along with the sample code, you will need to set a reference in the Visual Basic Editor to the DAO object model and the ADO and ADOX object models. Once in the VB Editor, go to the Tools menu, choose References, and select the references entitled:

• Microsoft DAO 3.XX Object Model

• Microsoft ActiveX Data Objects 2.X Library

• Microsoft ADO Ext. 2.5 for DLL and Security

Organization of This Book

Access Database Design and Programming consists of 19 chapters that are divided into six parts. In addition, there are five appendixes.

Part I

The first part of the book focuses on designing a database—that is, on the process of decomposing data into multiple tables.

Chapter 1 examines the problems involved in using a flat database—a single table that holds all of an application’s data—and makes a case for using instead a relational-database design consisting of multiple tables. But because relational-database applications divide data into multiple tables, it is necessary to reconstitute that data in ways that are useful—that is, to piece data back together from their multiple tables. Hence, there is a need for query languages and programming, which are in many ways an integral part of designing a database.

Chapter 2 introduces some of the basic concepts of relational-database management, such as entities, entity classes, keys, superkeys, and one-to-many and many-to-many relationships.

Chapter 3 shows how these general concepts and principles are applied in designing a real-world database. In particular, the chapter shows how to decompose a sample flat database into a well-designed relational database.

Chapter 4 continues the discussion begun in Chapter 3 by focusing on the major problem of database design, that of eliminating data redundancy without losing the essential relationships between items of data. The chapter introduces the notion of functional dependencies and examines each of the major forms for database normalization.

Once a database is properly normalized or its data is broken up into discrete tables, it must, almost paradoxically, be pieced back together again to be of any value at all. The next part of the book focuses on the query languages that are responsible for doing this.

Part II

Chapter 5 introduces procedural query languages based on the relational algebra and nonprocedural query languages based on the relational calculus, then focuses on the major operations—like unions, intersections, and inner and outer joins—that are available using the relational algebra.

Chapter 6 shows how the relational algebra is implemented in Microsoft Access, both in the Access Query Design window and in Access SQL. Interestingly, the Access Query Design window is really a frontend that constructs Access SQL statements, which ordinarily are hidden from the user or developer. However, it does not offer a complete replacement for Access SQL—a number of operations can only be performed using SQL statements, and not through the Access graphical interface. This makes a basic knowledge of Access SQL important.

While SQL is a critical tool for getting at data in relational database management systems and returning recordsets that offer various views of their data, it is also an unfriendly tool. The Access Query Design window, for example, was developed primarily to hide the implementation of Access SQL from both the user and the programmer. But Access SQL, and the graphical query facilities that hide it, do not form an integrated environment on which the database programmer can rely to shield the user from the details of an application’s implementation. Instead, creating this integrated application environment is the responsibility of a programming language (Visual Basic for Applications or VBA) and an interface between the programming language and the database engine (DAO). Parts IV and V examine these two tools for application development.

Part III

Part III consists of a single chapter, Chapter 7, that describes the role of programming in database-application development and introduces the major tools and concepts needed to create an Access application.

Part IV

When programming in Access VBA, you use the VBA integrated development environment (or IDE) to write Access VBA code. The former topic is covered in Chapter 8, and Chapter 9, while the following three chapters are devoted to the latter. In particular, separate chapters are devoted to VBA variables, data types, and constants (Chapter 10), to VBA functions and subroutines (Chapter 11), to VBA statements and intrinsic functions (Chapter 12), and to statements that alter the flow of program execution (Chapter 13).

Part V

Chapter 14 introduces Data Access Objects, or DAO. DAO provides the interface between Visual Basic for Applications and the Jet database engine used by Access. The chapter provides an overview of working with objects in VBA before examining the DAO object model and the Microsoft Access object model.

Chapter 15 focuses on the subset of DAO that is used to define basic database objects. The chapter discusses operations such as creating tables, indexes, and query definitions under program control.

Chapter 16 focuses on working with recordset objects and on practical record-oriented operations. The chapter discusses such topics as recordset navigation, finding records, and editing data.

Part VI

Chapter 17 explores ActiveX Data Objects, Microsoft’s newest technology for data access, which offers the promise of a single programmatic interface to data in any format and in any location. The chapter examines when and why you might want to use ADO and shows you how to take advantage of it in your code.

Chapter 18 discusses the role of ADOX in various data-definition operations, such as creating a Jet database and creating and altering Jet database tables.

Part VII

Chapter 19 presents a number of problems commonly encountered when dealing with data, along with their solutions.

Part VIII

Appendix A is intended as a quick reference guide to DAO 3.0 (which is included with Access for Office 95) and DAO 3.5 (which is included with Access for Office 97).

Appendix B examines an additional, little-used query operation that was not discussed in Chapter 5.

Appendix C examines how to use ODBC to connect to a data source.

Appendix D contains instructions for either downloading a copy of the sample files from the book or creating them yourself.

Appendix E lists some of the major works that provide in-depth discussion of the issues of relational database design and normalization.

Conventions in This Book

Throughout this book, we’ve used the following typographic conventions:

UPPERCASE

Indicates a database name (e.g., LIBRARY) or the name of a table within a database (e.g., BOOKS). Keywords in SQL statements (e.g., SELECT) also appear in uppercase, as well as types of data (e.g., LONG), commands (e.g., CREATE VALUE), options (e.g., HAVING), etc.

Constant width

Indicates a language construct such as a language statement, a constant, or an expression. Lines of code also appear in constant width, as do function and method prototypes in body text.

Constant width italic

Indicates parameter and variable names in body text. In syntax statements or prototypes, constant width italic indicates replaceable parameters.

Italic

Is used in normal text to introduce a new term, to represent menu options, and to indicate object names (e.g., QueryDef ), collection names, the names of entity classes (e.g., the Books entity class), and VBA keywords.

Obtaining Updated Information

The sample tables in the LIBRARY database, as well as the sample programs presented in the book, are available online and can be freely downloaded. Alternately, if you don’t have access to the Internet by either a web browser or a file transfer protocol (FTP) client, and if you don’t use an email system that allows you to send and receive email from the Internet, you can create the database file and its tables yourself. For details, see Appendix D.

Updates to the material contained in the book, along with other Access-related developments, are available from the O’Reilly web site, http://www.oreilly.com/catalog/accessdata3/. Simply follow the links to the Windows section.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: Access Database Design & Programming, Third Edition, by Steven Roman. Copyright 2002 Steven Roman, ISBN 978-0-596-00273-2.

If you feel your use of code examples falls outside fair use or the permission given here, feel free to contact us at [e-mail].

Request for Comments

Please address comments and questions concerning this book to the publisher:

O’Reilly & Associates, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

[phone] (in the United States or Canada)

[phone] (international/local)

[phone] (fax)

There is a web page for this book, which lists errata, examples, or any additional information. You can access this page at:

http://www.oreilly.com/catalog/accessdata3/

To comment or ask technical questions about this book, send email to:

[e-mail]

For more information about books, conferences, Resource Centers, and the O’Reilly Network, see the O’Reilly web site at:

http://www.oreilly.com

Acknowledgments

My thanks to Ron Petrusha, editor at O’Reilly & Associates, for making many useful suggestions that improved this book.

Also thanks to the production staff at O’Reilly & Associates, including Jeffrey Holcomb, the production editor, Edie Freedman for the cover design, David Futato for interior design, Mihaela Maier for Tools support, Rob Romano and Jessamyn Read for the illustrations, Rachel Wheeler, Matt Hutchinson, and Claire Cloutier for quality and sanity control, and Brenda Miller for the index.