Access 2007 VBA Programmer's Reference

Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein

• Publisher: Wrox
• Year: 2011
• ISBN:
  9781118058527
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Access 2007 VBA Programmer's Reference covers a wide spectrum of programming topics relevant to Access. Although it assumes the reader has some familiarity with VBA programming language, it begins with a brief introduction to VBA. And to help you leverage the tools that Access provides, a chapter highlights the new features in Microsoft Office Access 2007 — including new wizards and GUI (graphical user interface) elements that previously required VBA code, as well as new VBA features.

The book also discusses 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. Key new objects such as using Macros and the Ribbon are explored, too, as are forms and reports, the two most powerful tools for working with and displaying data. Working with other applications is covered extensively both in a general nature and for working specifically with Microsoft Office applications, Windows SharePoint Services, and SQL Server. Of course, this book wouldn't be complete without discussing security issues and the Developer Extensions.

Chapter 1

Introduction to Microsoft Access 2007

What is Microsoft Office Access 2007? Simply put, it’s the newest version of Access, a well-known and widely used relational database management system (RDBMS) for Microsoft Windows designed for building small- to medium-scale database applications. Access 2007 provides a rich set of features and tools for designing, creating, storing, analyzing, and viewing data, as well as the capability to connect to a large variety of other data sources. Access combines ease-of-use features with software development capabilities to support a wide range of user skill sets. Access also provides a Primary Interop Assembly (PIA) to allow other development platforms, such as Microsoft Visual Studio .NET 2005, to manage data using an Access database or even incorporate Access functionality into an external application.

If you’re reading this book, you probably already know a good deal about Microsoft Office Access 2007 or a previous version. While this book presents the various aspects of programming Access applications using VBA code, this chapter provides an overview of Access and discusses some of the basics. Although it’s possible to create and administer a database application using only code, there are also many tools for creating, designing, and editing database objects. Some of the more common tools are briefly covered in this chapter. If you’ve used Access before and are familiar with the visual designers and other Access tools, you can easily skip ahead to Chapter 3 to learn about the new features included in Access 2007.

A Brief History of Access

Microsoft Access has been around for nearly 15 years. The first version of Access, Microsoft Access 1.0, was released in November of 1992. Built on top of the Jet Database Engine, Access was designed to enable users to create and manipulate Jet-compatible database applications through a variety of visual designers and a scripting language called Access Basic. Access quickly became one of the most popular database development systems for Windows and the user base started growing rapidly.

With Microsoft Access 95, the fourth release, Access was adopted as a new member of the Microsoft Office product line. This was the perfect move for the product because it allowed Access to integrate and leverage many great features shared among other Office applications, such as Spell Checking or the Format Painter. Access Basic was replaced with the integration of Visual Basic for Applications (VBA) across the Office applications to provide a common programming language for creating solutions using the core Office products.

By the time Access 97 was released, millions of people were using Access routinely to build applications to store and manage their personal and business data. Access 97 is still in use today by many individual and business users and it is widely regarded as one of the best releases of Access ever. Some of the key features for that release were increased Web support, the hyperlink data type, and many new wizards. For developers, the release showcased the introduction of the Object Browser, VBA class modules, source code control, conditional compilations, and programmable command bars. That’s a truly compelling set of features for users developing advanced applications.

Access 2003 VBA Programmer’s Reference, the predecessor to this book, focused on the Microsoft Office Access 2003 product, the eighth release of Access. By 2003, everyone from individual users to the United States government was using Access. Access 2003 included a number of feature enhancements, as well as new additions. XML support, Data Import, and Data Export were improved in a number of ways, and signed database projects and disabled mode were introduced for added security.

Fast-forward to the present, and you have Microsoft Office Access 2007, the ninth full release of Access. Now shipping in 38 languages, Access is used throughout the world on Windows systems everywhere. For this release, there is a large focus on ease of use, and you’ll notice major changes from previous versions as soon as you boot the program. Access 2007 probably has as many new elements and enhancements as the last four releases combined, and there are a number of developer-oriented features as well. One of the largest features is a new database engine called the Access Connectivity Engine (ACE), which supports several new data types, such as Attachment fields and Complex Data. Additionally, there are a number of new form and report designers, which make building Access database solutions even faster than before. After trying out Access 2007, I’m sure you’ll see that Microsoft Office Access 2007 is the absolute best release of Access ever.

Is Access the Only Database System?

Some may ask the question, is Access the end-all to database systems? The simple answer is, “No.” Access is not the only database product on the market, nor is it the only database product available from Microsoft or for Windows. There are times you might want to use a different type of database system such as SQL Server or SQL Server Express. If you’ve only used Microsoft Access for your database needs, you might be wondering why you’d ever need another database system. It could be argued that Access can connect to so many different types of data sources that there’s no need for other front-end products. Moreover, developers could make a case that an Access database is a perfect solution for data storage for an application developed outside of the Access client, such as a .NET application that stores data in a back-end Access database. Still, there may be several reasons to use other database products, and the following sections discuss Access features, as well as other database system features, to help you choose what is right for your scenario.

Microsoft Office Access 2007

Microsoft Access is the perfect solution for single-user applications. Access provides many built-in features for quickly and easily building forms, reports, charts, and queries to view data. The user interface (UI) is designed to be simple and intuitive so that even novice users can accomplish their tasks. Developers have the ability to create Macros and write VBA code to support application development. Another key feature of an Access database that is often overlooked is the storage of all database objects in a single file, which makes the database easy to distribute to others. The maximum supported database size is 2GB of data, which provides ample space for almost any personal database.

Multiple-user applications are supported by Access, although there are a number of considerations of which you should be aware. There are record-locking options that affect how data is accessed, and some operations require the database to be opened in exclusive mode, thus locking other users out of the application. The recommendation for multi-user Access applications is to create a distributable front-end database (for each user) that connects to a backend database that stores the data. For example, a front-end application written in Visual Basic can take advantage of DAO or ADO to make calls to retrieve and modify data in the back-end Access database. This type of application works well in a single- or multi-user environment, because the data is only manipulated when DAO or ADO code manipulates the back-end database. Even then, applications that have large numbers of data transactions may encounter performance limitations in the ACE database engine.

SQL Server 2005 Express Edition

The Microsoft SQL Server 2005 Express edition is a scaled-down version of SQL Server 2005. Microsoft provides this product for free and it can be distributed for free as one of many ways to integrate data with .NET applications. It is ideal as an embedded database for small desktop applications that call for a fully functional SQL Server database, but do not require a large number of users. Some of the features in SQL Server Express include new reporting tools and many feature enhancements for data views. SQL Server supports database triggers and stored procedures, which are database features not supported by the ACE database engine, although they can be used by Access in an Access project (ADP) file.

However, database development using SQL Server Express requires fair knowledge and there is no built-in forms package. You would not be able to build a complete Windows database application using only SQL Server Express in the same way you could using Access. Probably the most common scenario for using SQL Server Express is when developing a front-end application using Microsoft .NET Framework technology, in a programming language such as C#, which connects to the SQL Server database engine to manage data. It is worth noting that a fully functioning front-end database application (complete with forms, reports, and charts) easily could be created in Access 2007 and connected to a back-end SQL database on a machine running any version of SQL Server 2005 to enjoy many of the benefits of the SQL Server database engine.

SQL Server 2005

Microsoft SQL Server 2005 is the perfect solution for large-scale database applications. Typically, applications that require a large number of users, many concurrent connections, great amounts of data storage, data transactions, direct data security, or that need routine database backups are ideal for SQL Server. SQL Server is one of the most robust and scalable databases systems available for Windows. But, as with SQL Server Express, SQL Server requires a front-end application to be developed to allow users to access the data stored in the SQL database. All of this power comes with an associated cost. SQL Server is not free, so there is a monetary factor to consider when using it. Additionally, creating database applications with SQL Server also requires rather in-depth knowledge of database design and how to work with SQL Server. Although not the best choice for a small, end-user database solution, Microsoft SQL Server is ideal for very large databases in enterprise systems used for storing critical and sensitive business data.

How Do You Choose?

If you’re not sure which type of database to create for your application, ask yourself the following questions:

• Will your database grow beyond 2GB?
• Are there security concerns for the data stored and used by your application?
• Is the data in your application critical or irreplaceable?
• Does your application require a large number of transactions at any given time?
• Does your database need to be accessed by a large number of users simultaneously?
• How will users work with the data from the database in the application?

Even answering these questions won’t provide a definitive solution as to which type o...

Table of contents

• Cover
• Title Page
• Copyright
• Dedications
• About the Authors
• Credits
• Acknowledgments
• Foreword
• Introduction
• Chapter 1: Introduction to Microsoft Access 2007
• Chapter 2: Access, VBA, and Macros
• Chapter 3: New Features in Access 2007
• Chapter 4: Using the VBA Editor
• Chapter 5: VBA Basics
• Chapter 6: Using DAO to Access Data
• Chapter 7: Using ADO to Access Data
• Chapter 8: Executing VBA
• Chapter 9: VBA Error Handling
• Chapter 10: Using VBA to Enhance Forms
• Chapter 11: Enhancing Reports with VBA
• Chapter 12: Customizing the Ribbon
• Chapter 13: Creating Classes in VBA
• Chapter 14: Extending VBA with APIs
• Chapter 15: SQL and VBA
• Chapter 16: Working with Office Applications
• Chapter 17: Working with SharePoint
• Chapter 18: Database Security
• Chapter 19: Understanding Client-Server Development with VBA
• Chapter 20: Working with the Win32 Registry
• Chapter 21: Using the ADE Tools
• Chapter 22: Protecting Yourself with Access 2007 Security
• Appendix A: Upgrading to Access 2007
• Appendix B: References for Projects
• Appendix C: Calling Managed Code
• Appendix D: DAO Object Method and Property Descriptions
• Appendix E: ADO Object Model Reference
• Appendix F: ADO Object Argument Enumeration Information
• Appendix G: The Access Object Model
• Appendix H: Windows API Reference Information
• Appendix I: Windows Registry Information
• Appendix J: Access Wizards, Builders, and Managers
• Appendix K: Reserved Words and Special Characters
• Appendix L: Naming Conventions
• Appendix M: Tips and Tricks
• Index
• Advertisements