Organization of This Book

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

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 be able to reconstitute that data in ways that are useful—that is, to piece data back together from their multiple tables. Hence, the need for query languages and programming, which are in many ways an integral part of designing a database.

Part I, Database Design

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

Chapter 2 introduces some of the basic concepts of relational database management, like 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 are broken up into discrete tables, it must, almost paradoxically, be pieced back together again in order 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, Database Queries

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 front end 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 that the database programmer can rely on 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). Part IV and Part V examine these two tools for application development.

Part III, Database Architecture

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, Visual Basic for Applications

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 its intrinsic functions (Chapter 12), and to statements that alter the flow of program execution (Chapter 13).

Part V, Data Access Objects

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, ActiveX Data Objects

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 will examine when and why you might want to use ADO, and show you how to take advantage of it in your code.

Appendixes

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 one 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.

Get Access Database Design and Programming, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.