Introduction

Many excellent books about Access are available, so why write another one? In talking to the many students I meet in my travels around the country, I have heard one common complaint. Instead of the several great books available for the user community or the host of wonderful books available to expert Access developers, my students yearn for a book targeted toward the intermediate-to-advanced developer. They yearn for a book that starts at the beginning, ensures that they have no gaps in their knowledge, and takes them through some of the most advanced aspects of Access development. Along the way, they want to acquire volumes of practical code that they can easily port into their own applications. In addition, developers of all levels need to transition to Access 2007, which is dramatically different from its predecessors. I wrote Alison Balter’s Mastering Microsoft Office Access 2007 Development with those requests and objectives in mind.

This book begins by providing you with an introduction to Access development. It alerts you to the types of applications that you can develop in Access and introduces you to the components of an Access application. After you understand what an Access application is and when it is appropriate to develop one, you will explore the steps involved in building an actual Access application. The book covers several strategies before you build the first application component. This ensures that you, as the developer of the application, are aware of the design issues that might affect you in your particular environment.

After you have discovered the overall picture, you will be ready to venture into the specific details of each object within an Access database. Chapters 2 through 6 cover the basics of tables, relationships, queries, forms, and reports. The intent of these chapters is to provide you with an approach to developing these database objects from a developer’s perspective. Although this text starts at the beginning, it provides many tips, tricks, and caveats not readily apparent from the documentation or from books targeted toward end users.

When you have a strong foundation of knowing how to build tables, queries, forms, and reports, you will be ready to plunge full-force into the process of building applications. Chapter 7 covers the process of using macros as part of the application-building process. Although macros were not a choice for serious developers in the past, the new embedded macros, error handling, and the inclusion of variables in macros make them a more viable solution for at least part of your applications. Chapters 8 and 9 provide you with an extremely strong grasp of the Visual Basic for Applications (VBA) language. Once again, starting with the basics, the book takes you gently through some of the most complex intricacies of the VBA language and Access object model. The text provides you with many practical examples to ensure that you thoroughly digest each topic.

Chapters 10 through 12 provide you with an advanced discussion of forms, reports, and queries. By the time you reach this point in the book, you should be familiar with all the basics of creating database objects. These chapters combine the basics of table, query, form, and report design with the VBA and object techniques covered in Chapters 8 and 9. The power techniques covered in Chapters 10 through 12 provide you with the expertise that you need to design the most complex types of forms, reports, and queries required by your applications.

After you cover the basics, you will be ready to delve into more advanced techniques. Chapter 13 covers advanced VBA techniques. It is followed by an in-depth discussion of class modules in Chapter 14. The chapter includes many practical examples of how and why to utilize class modules.

Before you ride through the frontier of the many intricacies of the Access development environment, one basic topic remains. Chapter 15 introduces you to ActiveX Data Objects. You will see how you can move away from bound objects, manipulating the data within your database using code.

Unfortunately, things don’t always go as planned. No matter what your level of expertise, you will often find yourself stumped over a piece of code and looking for answers. Chapter 16 shows you how to effectively employ the debugger to solve any coding problem you might run into. Even after your application has been thoroughly debugged, you still must provide a responsible means of handling errors within your applications. Chapter 17 shows you everything you must know to implement error handling. Included in the text and on the sample code website is a generic error handler that you can easily build into any of your own applications. The sample code is available for download at www.informit.com/title/0672329328.

Even the fanciest of applications will not please its users if it is sluggish. Chapter 18 covers optimization—that is, all the techniques you should incorporate into your programming code to ensure that your application runs as efficiently as possible.

With the foundation provided by the first 18 chapters, you will be ready to move into the richer and more complex aspects of the VBA language and the Access development environment. Chapters 19 through 22 cover the basics of developing applications for a multiuser or a client/server environment. You can explore locking strategies, ways to interact with non-native Access file formats, and the alternatives for designing client/server applications, including designing them with Microsoft SharePoint.

As an Access developer, you realize your world is not limited to just Access. To be effective and productive as an Access developer, you must know how to interact with other applications and how to use ActiveX controls, libraries, menu add-ins, wizards, and builders to assist you with the application development process. Chapters 23 through 28 cover ribbons, automation, the Windows API, and library and add-in techniques, and provide an introduction to Access and the Internet. After reading these chapters, you will understand how to employ the use of external objects and functionality to add richness to your applications without too much effort on your part.

Having reached the final part of the book, you will be ready to put the final polish on your application. Chapters 29 through 31 cover security, documentation, and database maintenance. You will learn how to properly secure your application so that you do not in any way compromise the investment you have put into the application development process. You will also learn how easy but necessary it is to properly document and maintain your application.

The Access development environment is robust and exciting. With the keys to deliver all that it offers, you can produce applications that provide much satisfaction as well as many financial rewards. After poring over this hands-on guide and keeping it nearby for handy reference, you too can become masterful at Access 2007 development. This book is dedicated to demonstrating how you can fulfill the promise of making Access 2007 perform up to its lofty capabilities. As you will see, you have the ability to really make Access 2007 shine in the everyday world!

Conventions Used in This Book

The people at Sams Publishing have spent many years developing and publishing computer books designed for ease of use and containing the most up-to-date information available. With that experience, we’ve learned what features help you the most. Look for these features throughout the book to help enhance your learning experience and get the most out of Access 2007.

• Screen messages, code listings, and command samples appear in monospace type.
• Terms that are defined in the text appear in italics. Italics are sometimes used for emphasis, too.
• In code lines, placeholders for variables are indicated by using italic monospace type.
• With VBA, Access 97 and higher, the line continuation character is an underscore.