Introduction to Access VBA

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

□ Introduction to Access 2003 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 2003 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 2003 VBA uses VBA version 6.0, which is the same version of VBA used by Word 2003 VBA and Excel 2003 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 loads when 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 of 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 Visual Basic (VB) and Visual Basic .NET (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 2003 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.

What's New in Access 2003 VBA?

Access 2003, in general, has not changed substantially from Access 2002, although some helpful improvements have certainly been made. Access 2003 provides navigation and user interface improvements, the ability to create smart tags, as well as enhanced capabilities for working with XML data and interacting with applications such as Microsoft SharePoint or InfoPath. A new error-checking feature is included that flags common errors in forms and reports. Field properties for bound controls on forms and reports are now updated automatically. Access 2003 VBA has not changed much from Access 2002 and is still based on VBA version 6.0.

Access 2003 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) that includes the following phases:

Requirements gathering





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 it as a blueprint for building a house. You wouldn't want to build a house without a blueprint for 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 road map. 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 I'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 our 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 each of these is an action the user can take on the Search Inventory Screen in our 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 our screen (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

Requirement 2 - View/Manage Car Details Screen

Use Cases

Figure 1.2

objects that you will create. Now, look at creating Activity Diagrams to portray the flow of actions in the system.

Activity Diagrams

Activity Diagrams are diagrams very similar to process Flow Diagrams, showing the flow from activity to activity and action to action. They provide a detailed view of the way a user (an actor) can operate within the system. Here are some basic steps for creating an Activity Diagram:

1. Determine the scope you want your Activity Diagram to portray (that is, a single use case, a series of use cases, a business process, a screen, and so on).

2. Add the Starting Point for the diagram.

3. Add the activities for each step users can take. This includes the initial step (opening the screen, and so on) plus any steps describing what they do next.

4. Add the Connectors from each activity to the next.

5. Label alternative paths appropriately so you can tell that the user can take one or more paths. These are called Decision Points.

6. Add any Parallel Activities, or activities that can happen simultaneously and must both finish before another action can be taken.

7. Add the Ending Points, which are the one or more points in the flow that the actions may end.

Activity Diagrams are helpful for many reasons, including aiding in structuring your code for the system and aiding in the test cases for the functionality you must ensure the system provides.

You will now see how we can apply these concepts to creating an Activity Diagram for the Search Inventory Screen of the hypothetical Wrox Auto Sales application. Based on the requirements you have gathered and analyzed so far, you have enough information to describe the actions a user can take on this screen in plain English. First, the Search Inventory Screen opens. Next, the user can open the View/Manage Car Details Screen, open the View/Manage Customer Details Screen, or run a search against inventory. If she opens the View/Manage Car Details Screen, the flow on this Search screen ends. If she opens the View/Manage Customer Details Screen, the flow on this screen also ends. However, the user can also run a search, fill in the search criteria, click the Search button, and view the results. She can then either clear the results, run the search again, or open the detail screen for a selected car. After she finishes searching (or if she opens a car detail record), the flow on this screen ends. Figure 1.3 shows how these steps can be depicted in an Activity Diagram:

Notice how the alternative paths the user can take are depicted using OR. Further notice how each activity is connected with an arrow. The starting and ending circles depict the points where the flow starts and the points where the flow can end. That's really all there is to creating an Activity Diagram. You simply model the flow of the actions a user takes in the system electronically or on paper in the order they happen, indicating any alternative or parallel activities. I used Microsoft Visio to create these diagrams, but you can use various other tools or just diagrams written on paper. The tool you use is not important, just as long as you document the design in some fashion. Let's look at an Activity Diagram for another screen in our Wrox Auto Sales application.

Figure 1.4 demonstrates the actions a user can take in order on a View/Manage Car Details Screen. Notice how the user can either open a car detail record or add a new car detail record when the screen first opens. After a record is opened, it can be viewed, modified, or deleted.

Screen Prototypes

After creating Use Case and Activity Diagrams (or other appropriate design diagrams), you should also create screen prototypes that illustrate at a basic level how the user interface will look.

Turning now to the Wrox Auto Sales hypothetical, look at what the Search Inventory and Manage Cars screen prototypes might look like. From the Use Case diagram for the Search Inventory screen (shown in Figure 1.1), you know there are five requirements:

□ UC 1.1 Search Car Inventory

□ UC 1.2 Clear Search Results

Requirement 1 - Search Inventory Screen Activity Diagram

UC 1.3

UC 1.4

UC 1.5

Use Cases can translate directly into menu options, buttons, and other controls on corresponding form(s).

Requirement 2 - View/Manage Car Details Screen Activity Diagram

Figure 1.5 shows an example of how these Use Cases were mapped to user interface elements for the Search Inventory screen. Notice how the Use Cases are satisfied: Search button (UC 1.1), Clear button (UC 1.2), Manage Cars Screen option in View menu (UC 1.3 and UC 1.4), and Manage Customers Screen option in View menu (UC 1.5). Depending on whether a particular car is selected in the search results list, the Manage Cars Screen will open empty or containing data for the selected car. The other controls you

Figure 1.5 shows an example of how these Use Cases were mapped to user interface elements for the Search Inventory screen. Notice how the Use Cases are satisfied: Search button (UC 1.1), Clear button (UC 1.2), Manage Cars Screen option in View menu (UC 1.3 and UC 1.4), and Manage Customers Screen option in View menu (UC 1.5). Depending on whether a particular car is selected in the search results list, the Manage Cars Screen will open empty or containing data for the selected car. The other controls you

Figure 1.5

see in Figure 1.5 are the data elements that the user can specify to run a search. These data elements include Vehicle ID Number, Year, Make, Color, Model, and Status. These data elements were originally determined in the requirements gathering phase.

The same concepts apply to creating the View/Manage Car Details screen prototype for the hypothetical Wrox Auto Sales application. Recall that the Use Case diagram in Figure 1.2 listed four requirements: UC 2.1—Open Existing Car Detail Record, UC 2.2—Updated Existing Car Detail Record, UC 2.3—Add New Car Detail Record, and UC 2.4—Delete Existing Car Detail Record. Figure 1.6 shows how these requirements can map to control buttons and other options on a screen. Notice how these Use Cases are satisfied in Figure 1.6: the Lookup button (UC 2.1), the Save button (UC 2.2), the Add New button (UC 2.3), and the Delete button (UC 2.4). Again, the data elements are also listed, such as Vehicle ID Number, Make, Model, and Year—all determined during the requirements gathering phase.

Determine Application Architecture

During the design phase, you should have determined the architecture of your application. Let's now take a brief look at what application architecture actually is. Application architecture refers to the way you split up an application into smaller pieces. Just about all applications, including Access 2003 VBA

Figure 1.6

applications, have the following elements:

□ User Interface: the screens the user sees and interacts with

□ Business Rules: the processing that takes place in response to the user's action, such as calculating some result, looking up information, and so on

□ Database: a physical data store for certain information used by the application

The architecture is the manner in which you put all the pieces together to form an application. Often, the architecture of an application is referred to by the number of tiers it contains. In this context, tiers refers to the number of machines that an application actually runs on. If any part of an application runs on a particular machine, that machine is counted in the tiers. This concept will be described in much more detail as you look at the different types of architecture: single-tier, two-tier, and three/n-tier.

Most of the examples in this book will be based on a single-tier standalone architecture for the sake of simplicity, although some two-tier client-server examples will also be covered.

Single-Tier (Standalone)

Single-tier applications became common at the beginning of the PC era, but they have actually been around longer than that if you consider mainframes. A single-tier application is one where the entire application is contained and runs on a single computer. This is also referred to as a standalone application. An example of a standalone application is, of course, a Microsoft Access application that has the user interface, business logic, and data all within the same file on one computer.

An application need not be completely contained in a single file to be considered standalone. Take, for instance, a loan amortization program installed on your computer that has an .EXE file that runs and reads information from one or more files on your hard drive. The entire application runs on a single computer even though more than one file is involved. This application is also a standalone application. Figure 1.7 illustrates a diagram of a typical standalone application.

Standalone Architecture -^


User Interface


Entire application runs on a single computer

Business Logic


Figure 1.7

With a standalone application, everything is nicely self-contained. If you are the only person who will use the application and data, a standalone architecture is fine. In the case of Access applications, you may also put the single Access file containing both the user interface and the database on the network to allow multiple people to access the application. This also works fine in many situations and also qualifies as a standalone architecture. As the number of users for a standalone Access application grows, the standalone architecture can become problematic because each user is accessing the same file on the network for the user interface and database. Many of the Access 2003 VBA examples covered in this book are based on a standalone architecture.

As applications grew and had to be shared more often, a client-server architecture was introduced to facilitate sharing. You will see how the client-server architecture can help address some of the limitations of the standalone architecture.

Two-Tier (Client-Server)

Client-server applications (applications that span two-tiers: a client machine and a server machine) solved the problem introduced by standalone applications and allowed multiple people to share the same data more easily. Client-server applications require a central server to hold the data store in a central location. The database server then processes requests for data from each of the client machines. Client-server applications first appeared in the early 1990s on the PC platform. A diagram of this architecture is depicted in Figure 1.8.

Client-Server Architecture -



Application (User Interface, Business Logic)


Database (and possibly some business logic)

Figure 1.8

In this architecture, the data store is abstracted from the client. The client machine makes a request from the server to add, insert, update, or view data and the server processes the request and returns the results to the client. A typical database server in such a scenario might be an Access, SQL Server, or Oracle database. In the case of an Access client-server application, the user interface may reside in one Access file, and the database may reside in a separate file or database application on a server, such as an Access or SQL Server database. Creating client-server applications using Access or SQL Server databases will be discussed in Chapters 7, 9, and 13.

In a client-server application, the main application is loaded on each client computer. The application includes the user interface and probably most or all of the business logic elements. In other words, the application contains code to create the user interface, process the user actions, perform any validations or calculations, and look up or modify data in the database. It is possible, however, to have some of the business logic on the database server, such as in stored procedures that are described in Chapters 8 and 12. For example, if you require a zip code to be five characters in length, you could put that logic in the insert or update stored procedure to raise an error instead of the code on the client. The bottom line with business logic in client-server applications is that it can be entirely on the client, entirely on the server, or you can use a combination of the two.

The advantages of client-server applications are that you can share data across multiple computers more easily than before and also separate the user interface from the database. Each client always updates the central repository.

At some point, however, the central database server is limited in terms of how many users it can handle. This might mean that a database server must scale up to a bigger server or beef up the resources of the existing server. To address some of these problems with growth and maintenance, the three-tier/n-tier architecture was introduced.


The three-tiered architecture, also called n-tier, solves the limitations imposed by the client-server architecture and allows you to architect solutions that can grow easily as your needs grow. You can simply add additional servers to handle the processing. Microsoft Access is not typically used to create three-tier applications, although with some complex coding it can be used as part of a three-tier application design. Access should not be used for three-tier applications because for such a large-scale application, Access is not the best user interface or database to use. It contains serious limitations.

Even though you will not use Access to develop three-tier applications, it is still worthwhile to understand what a three-tier application includes. I discuss this because, at some point, you may need to migrate your client-server Access application to a three-tier architecture.

So how does a three-tier architecture create incredible growth potential? The idea is that application elements are physically compiled as separate elements, and they run on different computers. For example, the user interface elements are separate from the middle-layer business logic, which is separate from the database layer. In this case, the application is running on three different computers: the client, the middle tier server, and the database server (hence the name three-tier). Figure 1.9 diagrams this:

- 3-Tier Architecture -

0 0

Post a comment