Introduction

Welcome to the Access 2003 VBA Programmer's Reference. We wrote this book for Access users and programmers who want to increase the power of Access by adding the VBA (Microsoft Visual Basic for Applications) language. Access is Microsoft's leading consumer relational database management system for desktop applications. It's so popular because it's relatively easy to learn and very powerful. With wizards and detailed help files, users can easily create tables, queries, forms, and reports after only a brief introduction.

To utilize the power of Microsoft Access more effectively, you can add VBA code to your Access databases. By using VBA code, you can respond to application-level events, display forms and reports, manipulate toolbars, and even launch external applications or control certain aspects of Windows.

The Evolution of Access and VBA

Microsoft Access has had a rich history. Version 1.0 was the initial version of Access that ran on Windows 3.1. It was very quickly replaced by Version 1.1, which added a few new features and fixed many of the bugs introduced in the initial version. At this point in the history of Access, no one really took Access seriously as a database; it was buggy, there were a number of limitations in its feature set, and the database community just hadn't accepted that Microsoft could produce a quality database product.

In 1994, the first real version of Access was released: Access 2.0. Many database programmers using other software, such as FoxPro and dBase, decided to give Microsoft Access 2.0 a chance. Access 2.0 worked very well on both Windows 95 and Windows NT; however, it was missing much of the 32-bit API (application programming interface) and couldn't work with long filenames. Microsoft Access went through several more versions (95, 97, 2000, and 2002) before the current release, Access 2003.

Access 2003, released in October 2003, includes some additional enhancements, including the ability to open an Access 97 database without converting it to an updated format. Users of Access 2000 and Access 2002 were prompted to convert an Access 97 database to Access 2000 format before they could use the database. This often caused problems in corporate installations where often, multiple versions of the Microsoft Office software suite were installed on different computers or in different departments. Access 2003 can open certain Access 97 databases without converting them, thus allowing multiple versions to access the same database.

There have not been a large number of changes to VBA in Access 2003. However, the changes that have been made offer developers some distinct advantages. We've included an entire chapter (Chapter 3) about new features in Access 2003.

What Is VBA?

Microsoft Visual Basic for Applications (VBA) allows programmers to develop highly customized desktop applications that integrate with a variety of Microsoft and non-Microsoft programs. For example, all of the Microsoft Office System products support VBA. In addition, many third-party programs, such as drafting programs as well as WordPerfect, also support VBA.

VBA is actually a subset of the Visual Basic programming language and is a superset of VBScript (another in the Visual Basic family of development programs). VBA includes a robust suite of programming tools based on the Visual Basic development, arguably the world's most popular rapid application development system. Developers can add code to tailor any VBA-enabled application to their specific business processes. A manufacturing company can use VBA within Microsoft Access to develop sophisticated inventory control and management systems with custom toolbars, a back-end database, management reports, and security. Rather than purchasing an off-the-shelf Inventory Control product, usually at a great cost and with a very limited ability to customize, developers can take a product they already have installed (Access as part of the Microsoft Office 2003 System) and build a robust application with no additional expense other than time. Once the application is in place, the developer can respond to customization requests quickly and effectively, rather than waiting for another company to work the customization into their development cycle.

You might be wondering why you should develop in VBA rather than the more robust Visual Basic 6.0 or Visual Basic .NET. Both are robust, popular, and capable programming languages. However, using VBA within Access gives you some key benefits: First, you can take advantage of a built-in Access object library. This means you can take full advantage of a wide variety of Access commands, including executing any command from any toolbar in Access. Second, VBA is included in all Microsoft Office System applications. To develop in Visual Basic, you'll need to purchase Visual Basic 6.0 or Visual Basic .NET either alone or as part of the Visual Studio or Visual Studio .NET suite. It could get very expensive if multiple developers in your organization need access to the Visual Basic development tools.

Despite the advantages of VBA, there are definitely circumstances in which you'll want to use Visual Basic. If you need to deploy an application to a wide variety of computers, especially those without a full installation of Microsoft Access, Visual Basic might be your best bet. We will examine the three languages in the Visual Basic family and why you might want to use each of them.

Access 2003 VBA Programmer's Reference

This book is separated into two sections. The chapters provide tutorial information and the numerous appendices provide the reference material you'll need to write VBA code within Access. While the chapters are designed to build upon one another to give you a detailed guide to VBA in Access, each chapter can be read and applied separately from the rest of the book.

What Does This Book Cover?

This Programmer's Reference book covers a wide variety of programming topics. A brief introduction to VBA is included, although this book assumes the reader has at least some basic familiarity with the VBA programming language. Likewise, an entire chapter is devoted to changes in Microsoft Office Access 2003, covering both new wizards and GUI (graphical user interface) features that previously required VBA code, as well as the new VBA features included with Access 2003. You'll learn how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error handling techniques, and advanced functions such as creating classes and using APIs. There are two important chapters on Security and Macro Security as well as a chapter on the Access Developer Extensions (ADE). Finally, we'll explore a bit of the relationship between Access and SQL (Structured Query Language) Server, as well as how you can use VBA in Access to control and enhance other Office applications.

How to Use This Book

The initial chapters are written in a tutorial format with detailed examples. True to the Wrox Programmer's Reference standard format, we've included numerous reference appendices with details on the various object models you might use when writing VBA code in Access. We've also included a detailed primer on the Windows Registry and a listing of common API functions you might want to use in your code.

Real world examples will be given for many, if not most, of the programming topics covered in this book. Some typical topics include the following:

• How to hide fields on a form based on database login information.

• How to show or hide entire sections of reports based on information entered on a form.

• How to use VBA to transfer information between Access and other Office programs such as Outlook, Word, and Excel.

• How to configure custom menus for your Access database applications.

Throughout the book we've also included tips and tricks we've discovered during our programming experiences.

Introductory and Background Material

Chapters 1 through 5 provide some background reference material you'll need if you're new to Access or VBA. After a detailed look at the new features in Access 2002 and 2003, we've provided information on the building blocks of VBA, such as objects, properties, methods, and events. An introduction to the VBA Editor and its various debugging tools follows.

Accessing Data

After the introductory material, Chapters 6 and 7 focus on accessing data by using VBA. Both DAO and ADO provide methods for accessing data in Microsoft Access and other external data sources such as Informix and SQL Server.

Executing and Debugging

Chapters 8 and 9 provide detailed information on executing and debugging VBA code. Every development project needs some debugging, even if you're an expert developer. We'll show you some easy ways to debug your code as well as provide some tips and tricks to make the tedious process of debugging a bit easier.

Working with Access Objects

Two Access objects in particular, Forms and Reports, can make heavy use of VBA (Chapters 10 and 11). You can write VBA code to respond to a variety of events from the controls on a form or even from the form itself. You can write code to show or hide certain sections of a form or report in response to information entered on the form or even the particular user logged on to Windows at the time.

Advanced VBA Programming

The next three chapters (12-14) provide information on creating classes in VBA, using APIs, and using SQL and VBA. They are designed to give you a thorough tutorial on these subjects so you can design your own classes, implement some common APIs in your code, and use SQL to access data.

Miscellaneous Material

Calling Chapters 15 through 20 miscellaneous is not really fair to the extremely thorough content presented. Chapter 15 shows you how to use VBA to transfer information between Access and the other Office programs. You'll learn how to create tasks and e-mail in Outlook, perform a mail merge in Word, and export data to an Excel spreadsheet. We'll even show you how to take information from Access, create a graph, and insert that graph into PowerPoint.

Chapter 16 provides a detailed study in security. It seems every week there's a new security hole in a computer program, which can expose your computer to malicious code. When developing a database, you can implement security in your database to prevent users from seeing the code, or you can even prevent access to certain tables or queries in your database.

Chapter 17 examines working with client/server development and Chapter 18 examines the Windows Registry. Next, we provide an in-depth look at a new set of tools, the Access Developer Extensions. These tools help you automate many common tasks in Access.

Chapter 20 focuses on macro security. Access 2003 introduces some new concerns related to macro security. We'll introduce you to Sandbox mode and let you know how to properly work with these new security features.

Appendices

Appendix A provides information on upgrading to Access 2003 from previous versions. Appendix B shows you how to create and use references within your VBA code. We've provided extensive information on the DAO, ADO, and Access Object Models in Appendices C, D, and E, respectively. Rounding out the mix are appendices on common API calls, proper naming conventions, VBA reserved words, and the Windows Registry. Finally, we've included a wonderful appendix full of tips and tricks you can use to develop professional applications.

Other Access/VBA Sources

Just as no man (or woman) is an island, no book can be all things to all readers. No matter how many times you read this book, it can't tell you the meaning of life any more than it can tell you everything you need to know about VBA within Microsoft Access. There are several other resources you'll want to utilize while writing your VBA code. Some of our favorites are:

• Microsoft Newsgroups—Microsoft maintains a news server (msnews.microsoft.com) and has a wide variety of Access and VBA newsgroups to choose from. Currently there are more than 25 Access newsgroups for you to choose from. They all begin with microsoft.public.access. You can access newsgroups through a newsreader such as Outlook Express or through the Web at: http://support.microsoft.com/newsgroups/default.aspx.

• MVPS.ORG (http://www.mvps.org/)—This is your jumping-off point to a number of interesting offerings being provided for you by a few folks associated with the Microsoft Most Valuable Professional (MVP) program.

• Microsoft Access Support Center (http://support.microsoft.com/default.aspx?scid=fh;en-us;acc&x=16&y=16)—This provides information about current issues, downloads, updates, and of course ways of obtaining product support.

• Microsoft on Google (http://www.google.com/microsoft)—This harnesses the power of Google and limits the searches to Microsoft-related sites.

• Microsoft Developer Network (http://msdn.microsoft.com)—The Developer Center for Access provides a myriad of articles and tutorials on key issues. You can also find links to usergroups, newsgroups, and other valuable resources.

• Microsoft TechNet (http://www.microsoft.com/technet)—This site allows you to access Microsoft Knowledge Base articles, security information, and many other technical articles and tips.

Conventions Used in This Book

We've used several different styles of text in this book to help you understand different types of information. Some of the styles we've used are listed here:

Advice, hints, and background information comes in this type of font.

Important words or phrases are in italic.

Words that appear on the screen, such as menu commands or toolbar buttons are in a font such as File.

Keys that you press on the keyboard, like Ctrl, are in italics.

Code within the text is styled like the following: For I = 1 to 10

Any new or important code is offset with shading similar to the following sample:

SELECT TeamID, TeamName, StadiumName FROM tblFootball;

Code you've seen before is in the same font, without the shading, as shown here:

Dim strText as String

Source Code

As you work through the examples in this book, you can choose either to type in all the code manually, or use the source code files that accompany this book. All of the source code used in this book is available for download at www.wrox.com. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

Because many books have similar titles, you may find it easiest to search by ISBN; for this book, the ISBN is 0-7645-590-6.

After you download the code, just decompress it with your favorite decompression tool.

Tell Us What You Think

We've tried to make this book as complete as possible. We're all active programmers and have included tips and tricks that we've used in our everyday lives. Programming books can often be dry and boring. We've tried to liven our book up a bit with some interesting examples. If you liked the book (or even if you didn't), we encourage you to send us your feedback. You can contact us via e-mail at [email protected] (be sure to include the book's title) or through the Wrox Web site.

Customer Support

We've done our best to make sure that every code sample is complete, debugged, and well commented. However, if there are samples or topics you just can't quite grasp or need a little more help with, there are places you can turn. You can e-mail your questions to Wrox at [email protected] (again, be sure to include the book's title) or visit the Microsoft Newsgroups. Most of us hang around the Access newsgroups and will attempt to answer your questions as quickly and completely as possible. You can also e-mail any of us directly ([email protected], [email protected], [email protected], and [email protected]). Thanks for reading!