Beginning Access 2007 VBA

Beginning Access 2007 VBA

John Wiley & Sons

Chapter One

This chapter will provide an introduction to the world of Access 2007 VBA and programming in general. More specifically, this chapter will cover:

Introduction to Access 2007 VBA and new features

Explanation of the stages in the Systems Development Life Cycle of software development

Techniques for designing applications

Writing and testing VBA code using the Visual Basic Editor

What Is Access 2007 VBA?

VBA is an acronym that stands for Visual Basic for Applications. VBA is included as part of several Microsoft products, including Access, Word, and Excel. For example, Access 2007 VBA uses VBA version 6.0, which is the same version of VBA used by Word 2007 VBA and Excel 2007 VBA. VBA is a programming language that can be used to add additional features to your applications. You are no doubt already aware that Access is a powerful database application that also enables you to create applications that include user interfaces. You can use VBA instead of or in addition to Access macros to provide advanced functionality to those Access applications. For example, you might use an AutoExec macro to control which form loadswhen the application begins, and then write the business logic that controls how the application works using VBA.

VBA should not be confused with the Microsoft Visual Basic (VB) or Visual Basic .NET (VB.NET) programming products. VB and VB.NET have their own syntaxes for the Visual Basic programming language. The VB syntax, the VB.NET syntax, and the VBA syntax are very similar, but they have some differences. Unlike Access, the VB and VB.NET products do not have a built-in database. The Visual Basic and Visual Basic .NET programming products are typically used in building more complex enterprise applications that use Access, SQL Server, or Oracle as the database. Access 2007 VBA is typically used to build small and simple Access database applications designed for a few users. The experience you gain writing VBA code in Access or other Microsoft Office application products will certainly be helpful if you ever decide to use the Microsoft VB or VB.NET programming products. Microsoft has a development solution called Visual Studio Tools for Microsoft Office that allows Microsoft Office products, including Access 2007, to call code that is written in a .NET programming language.

What's New in Access 2007 VBA?

Access 2007, in general, has changed substantially from Access 2003, although Access 2007 VBA has not changed much from Access 2003 VBA. One significant change with Access 2007 is the introduction of a new ACCDB file format, instead of the MDB file format of the past. Another significant change to Access 2007 and all other Office 2007 programs in general is the new user interface design. The new user interface for Access 2007 uses ribbons on the toolbar that replace the layers of menus and toolbars found in earlier releases. Actions or options on the ribbons are grouped together in a logical fashion based on the task they accomplish. While the user interface for Access 2007 has changed significantly, the Visual Basic Editor for Access 2007 has remained mostly unchanged. The Visual Basic Editor opens in a separate window from Access 2007. In developing a VBA application, you will work with both Access 2007 and the Visual Basic Editor.

Some examples of the additional changes to Access 2007 in general (but not Visual Basic for Applications) include:

Templates - Many prebuilt templates are included to get you started quickly with certain common applications.

Rich text in memo fields - Text can now be formatted with options, such as bold, italic, colors, and the like.

Navigation pane - The navigation pane replaces the old database window and contains all of the database objects, such as tables, forms, and so on. What is interesting about the navigation pane is that it also allows you to create custom groupings of objects, such as tables, forms, reports and so on to a particular meaningful grouping, such as Products.

Embedded macros - Embedded macros support simple variables, some looping, and even some error handling. You can also embed a macro within a control's event property. For example, if you need a button to open a form, you can write a one-line embedded macro instead of using VBA code to accomplish this simple task. In other words, you can save VBA for the more complex coding tasks and can use macros for some of the simplest tasks.

Custom ribbons - You can create custom ribbons for the toolbar, since ribbons are built dynamically from XML contained in hidden system tables in the Access database.

Another change that is likely of interest to an Access VBA developer is the change in user-level security. With the Access 2007 ACCDB file format, user-level security is not supported. A database password can still be used to protect the database, but it is no longer stored in the Access data file. If more comprehensive security is needed beyond a database password, then the ACCDE file format (which replaces MDEs) can be used to protect your code, forms, and reports. The ACCDE format, however, does not provide any specific security on the data stored in the application. In addition to removed user-level security, replication has also been removed for ACCDB files. In other words, you cannot replicate data and design changes between databases using the ACCDB file format. A workaround for user-level security and replication is to use the Access 2000 or Access 2002-2003 MDB format when working in Access 2007.

Note that user-level security is not the same concept as the security model you create for your Access applications. You can store user names, passwords, and credentials in your database and implement a security model for your application that uses these stored values. The prior section on Access security discusses the intrinsic security features of Access.

Access 2007 VBA Programming 101

Many people first enter the world of programming by creating simple Access applications that become a success and must be expanded. These programmers typically have little to no formal experience in designing software applications and have taught themselves how to write basic Access applications. They now need to use VBA to extend those applications. You may very well fall into this category. The rest of this chapter will provide you with a basic overview of general programming concepts such as the phases of the Systems Development Life Cycle and will explain how Access VBA fits into each of these phases.

All applications should be written according to a development methodology that provides guidelines for developing the application. A commonly followed development methodology is a Systems Development Life Cycle (SDLC), which includes the following phases:

Requirements gathering

Designing

Developing

Testing

Implementing

These phases are discussed in detail below.

Various other types of application development methodologies can be used in addition to, or instead of, the Systems Development Life Cycle as appropriate, such as Rapid Application Design (RAD) techniques, Extreme Programming techniques, and so on.

Requirements-Gathering Phase

In the requirements-gathering phase, your goal is to identify the objectives for the new application. The final output of the requirements-gathering phase should be a document describing the purpose of and features requested for the new application and any other helpful details you gathered during this phase.

You should determine the purpose of the application overall, who will use the application, from what locations the application will be accessed, and exactly what features the application should provide. Interview end users and other company employees as appropriate to determine what electronic or paper systems this application will replace. Ask them questions about the problems with the current systems, so you can better understand what will make your application successful. You should also find out from the end users what features they would like to see included in the application, which features they feel are critical, and which are less so. Also obtain copies of any current documents or screens of current applications or processes that are being used. Reviewing the existing applications that your application will replace is a very helpful strategy.

Interviewing end users is a critical part of developing a software application because if you expect someone to use your application you have to first find out what he is looking for. If your application does not meet some need he has, he has no incentive to use it. You should also know, however, that end users often ask for many more features than you can or should include in the application. You have to learn how to prioritize the features and implement those that are required or helpful for meeting the needs of most users or that are required in order to comply with management orders.

For purposes of illustrating some concepts in the Systems Development Life Cycle, I use a hypothetical example. Suppose that you have the task of writing a Wrox Auto Sales Application that will be used by sales and other staff at the auto sales company. Applying these requirements-gathering techniques, you have determined that the application should allow the users to search inventory for available and sold cars, to view or edit details about a selected car, and to view or edit customer details. Currently this process is being implemented solely on paper and needs to be automated in an Access application that will be used by a few end users. You have written down extensive details about what data elements need to be tracked, the features that are desired, and other details that you learned in this process. You are ready to move into the design phase.

Design Phase

During the design phase, you analyze the requirements gathered in the prior phase and determine a system design. The final output of the design phase should be a written document describing the features your application will provide and, ideally, a prototype of each screen.

You can document your design for the hypothetical Wrox Auto Sales Application and any other application in various ways, but having some type of documentation on paper before writing the first line of code is very important. You will understand why momentarily.

Start the design phase by organizing the requirements into logical groupings and/or steps. They will help you determine the layout for screens and the code. Using the Wrox Auto Sales example, let's look at some ways you can use modeling to assist you with your design.

Model the Application Design

Modeling is best defined as the process of documenting one or more parts of an application on paper (or with an electronic tool). A variety of modeling techniques can be used to accomplish the end result: modeling the flow of activities through the system, modeling the way the code will be structured, and so on.

Regardless of the modeling techniques you decide to use, the objective is to come up with a complete roadmap for building the system before you write a single line of code. If you start coding a solution without a roadmap, you will find that it becomes extremely difficult and inefficient to make major adjustments. Think of the roadmap as a blueprint for building a house. You wouldn't want to build a house without a blueprint showing how it is supposed to be structured. Suppose that you have someone build a house for you without a blueprint, and you check up on the progress a few weeks later. When you walk into the house, you notice that it has no basement. The first and second floors have been framed, yet there is no basement (and you wanted one). Think of how much work is involved in having all of the framing for the first and second floors torn down just so the basement can be put in. The rework involved, the cost, and the delays are enormous.

This same concept applies to designing a computer application. If you plan up front how the application needs to look and what it will do, you have a detailed roadmap. You probably won't get deep into the process and discover some major unresolved issue. This is not to say that proper modeling will take away all risk of later problems, because that is impossible. However, you will, at least, get the major framework of the system (such as whether the house needs a basement) defined, and you can worry about finishing the very minor details (like paint colors) later. I hope you now see why documenting the system on paper before writing code is so important. Now you'll take a look at some of the most commonly used diagramming techniques - Use Case Diagrams and Activity Diagrams - and see how they can be used to help design your applications.

Use Case Diagrams

Use Case Diagrams show the services provided by the system to its users. I like to think of it as the actions a user can take in the system. For example, in the case of the Wrox Auto Sales hypothetical application, the auto sales staff will need some type of Search Inventory Screen to allow them to search the inventory of cars. From that screen, they need to be able to run searches, clear the search results, open a selected car detail record, open the car detail screen (empty), or open the customer screen (empty). Suppose that each of these is an action the user can take on the Search Inventory Screen in the system. Thus, each of these actions can map to an action in a Use Case Diagram.

First, let's look at how such a Use Case Diagram would appear on the screen (see Figure 1-1), and then you can study in more detail how it is structured.

Notice how the Use Case Diagram in Figure 1-1 lists the separate actions the Wrox Auto Sales Staff can take on the Search Inventory Screen in the system, as described previously. The stick figure representing a person is called an Actor. The actor represents the Wrox Auto Sales Staff member who is using the system. Lines are drawn to each of the actions the actor can perform. No special requirement exists for how you group Use Cases. In other words, whether you group Use Cases together by screens, major functionality, logically related concepts, and so on is up to you. In the preceding example, I have structured the Use Cases by a particular screen. This is the structure that I use most frequently, as it is typically easier to think of activities you can perform in an application from the perspective of the particular screens on which they can be invoked.

Notice how each of the Use Cases is numbered beginning with the Requirement Number followed by the Use Case Number (for example, 1.2 for representing the second Use Case in Requirement One). This is another technique that I myself follow because it makes numbering Use Cases much easier should you later insert or delete one in a particular section. For example, if you add a new action that the user is allowed to perform on the Search Inventory Screen, you don't have to renumber all the Use Cases for the entire system. You just add the next highest Use Case for that particular requirement (for example, the new one would be UC 1.6 for the preceding example).

This is the basic concept of the structure of Use Cases. So now, take a look at the Use Case Diagram for another screen in the system. Figure 1-2 shows the activities the user performs on some type of View/Manage Car Details Screen.

These two sample Use Case Diagrams cover several of the actions that our hypothetical Wrox Auto Sales Application will allow a user to take in the system. In Chapter 4, you will learn how the Use Case Diagrams can help create Class Diagrams to represent how source code should be structured into custom objects that you will create. Now, look at creating Activity Diagrams to portray the flow of actions in the system.

(Continues...)

---

Excerpted from Beginning Access 2007 VBA by Denise M. Gosnell Copyright © 2007 by John Wiley & Sons, Ltd. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---