Introduction

Overview

Before the introduction of Microsoft Access, database management systems were synonymous with programming. Without programming, you were unable to tap into the full power of the software. Indeed, some database management systems, like Nantucket Corporation’s Clipper, consisted only of a development environment and a compiler. The database developer was responsible for designing the databases and then writing all of the code that constituted his or her application.

The introduction of Microsoft Access in 1991 changed all that. Access was distinguished by its graphical approach to database management, which allowed users to take advantage of some powerful features of the DBMS without needing to do any programming. By using wizards and graphical designers, Access allowed users to design databases, create forms and user interfaces, write queries, and generate reports. It was this power and flexibility combined with its ease of use that quickly made Access the leading desktop database management system.

Although programming is not required to use Access, nevertheless, Access has attracted a huge programming audience. In fact, more programmers are working with Access than with any other database management system. There are two major reasons for this apparent contradiction:

• By providing such an effective rapid application development environment for database applications, Access prompts users to want to learn more about using the software more powerfully, effectively, and efficiently. In fact, many professional Access programmers began as Access users who developed a passion for the product and experienced a frustration over the things they wanted to do but didn’t know how to do.

• Despite its ease of use, users at some point run into the limitations of the Access user interface and must turn to programming. Although you can do a great deal as an Access user, some things can only be done programmatically.

If you’ve picked up this book, you’re no doubt an Access user who has had both of these experiences. You enjoy using Access, enjoy the power that it places at your disposal, and want to learn more about using it effectively. At the same time, you find that you’re running into some of the limitations of the Access interface and suspect that if you’re to continue to deepen your skills in using Access, you need to turn to programming. And perhaps you’re even beginning to find yourself responsible for developing and maintaining Access applications, possibly in a small business setting, where most users have very little experience with the Access interface. In that case, this book is for you.

Access VBA Programming takes the Access power user to the next level, from using Access to programming with Access, by building on much of what you already know. Part I, “Understanding the Access Environment,” introduces some of the basic concepts of program design and architecture (like requirements analysis and naming conventions) at the same time that it reviews what you can do in Access without programming. It includes a refresher course on the major components of an Access application (including macros, modules, and events), as well as a discussion of some of the interface elements you can eventually use to allow yourself and other users access to the code you’ll eventually write. This includes using the Switchboard Manager as the menu system of an Access application and customizing Access menus and toolbars so that the user can execute routines by clicking a toolbar button or selecting a menu item.

Part II, “Understanding the Visual Basic for Applications Environment,” introduces you to Visual Basic for Applications (VBA), the programming language used by Microsoft Access (as well as by the other Office applications and by Visual Basic). Here you’ll learn about basic programming concepts, such as variables, arrays, objects, program structures (looping structures, decision-making structures), functions, and procedures. VBA, however, offers not only a programming language, but a complete integrated development environment (IDE) that allows you to run, test, and debug your code. This part of the book introduces you to the VBA Editor, VBA’s IDE. Finally, VBA itself has no language elements that support data access. Instead, data access is handled by a separate library, ActiveX Data Objects (ADO), which can be called from VBA code. Part II introduces you to the basics of ADO as well.

With the basics in place, Part III of the book, “Interacting with VBA,” begins to show you how you can put VBA to creative use in developing Access applications. It covers using VBA with forms and reports, creating menus and toolbars programmatically, and customizing the Access environment both from the Options dialog and programmatically. In this part of the book, you learn not only how to use VBA to get Access objects (like forms and reports) to run, but also to configure the Access user interface so that the user can run your application.

Part IV, “Advanced Access Programming Techniques,” introduces some of the more specialized areas of Access development. You’ll learn about security in Access, an increasingly important topic as Access becomes more widely used in networked and multiuser environments. You’ll also learn how to use Access to create Data Access Pages for the web and to import data from other Office applications, like Excel and Word. Chapter 19 covers upgrading to a more enterprise-level DBMS like Microsoft SQL Server while continuing to use Access as a front-end through a technology called Access Data Projects (ADP).

Part V, “Application Development,” consists of two chapters, one on multiuser programming and one on programming outside of the Access environment with the skills you’ve developed while programming with Access. As Access evolves from a single-user, desktop DBMS to a networked DBMS, the demands placed on programmers to write robust, efficient code increases. The chapter on multiuser programming covers some of the techniques you can use to make sure your application performs as expected when multiple users are accessing data. The final chapter, “Beyond Microsoft Access,” demonstrates that the skills you have learned in the course of the book are applicable not only to Access, but also to the other Office applications, to the retail edition of Visual Basic, and even to VBScript and Active Server Pages.