Developing in Access

As stated in the introduction, Access has successfully combined a powerful database development environment with a relatively easy-to-use interface. It has a number of wizards that will walk you through a variety of actions such as building a table, a form, a report, or a query.

In this section, we take a brief look at the development environment. However, before we begin, we must distinguish between two terms: database and database project (note, the terms database project and database application mean the same thing and will be used interchangeably throughout this book).

In its simplest form, a database is a collection of related data held in a structured environment. The data is usually related in order to manipulate it to accomplish a task. Most databases are relational and have tools to easily relate data. A database application is programming to help the database manipulate and deliver information. This is where VBA comes into play.

The Database Window

When you first open Access, you are presented with the window shown in Figure 1-1.

Figure 1-1: The Database window

The components of a database environment are called its objects. The Database window places those objects into categories: Tables, Queries, Forms, Reports, Pages, Macros, and Modules. Since this is not a beginning VBA book, I assume that you are experienced in using many of the features in this window, but we’ll take a brief look at the mechanics of using it.

You select the category you want using the column on the left. Once in that category, you should see the list of available objects of that type—for instance, the tables associated with your database. Each category also presents options for creating an object. Many can be created either manually or with the use of a wizard. As an example, if you double-click on Create Table by Using Wizard, you are presented with the screen shown in Figure 1-2.

Figure 1-2: The Table Wizard

Here you select the fields you want by examining a number of prebuilt tables for various types of jobs. You can rename the fields to suit your purposes. Once you have selected the fields you want, the Next button takes you to the next step necessary to complete the table. Most wizards work this way.

Returning to the Database window, you can also choose to build the same table by hand by selecting the Create Table in Design View option. You are presented with a grid as shown in Figure 1-3.

Figure 1-3: Table in Design View

Here you can name the field, select a data type, and provide some brief comments about the field. In the Field Properties area in the bottom portion of the window, you can assign default values, data rules, and formatting, for example.

While the mechanics may be slightly different, forms and reports have similar features. You can build them either in Design View or by using the supplied wizard.

In case you have never had an occasion to use it, a Data Access Page, which was first introduced in Access 2000, allows you to display information in a form or report on the Web. It converts the form or report to HTML code while binding it to the data it is associated with.

The nice part about this feature is that, even though the form or report is being displayed in a web page, you have all the same tools available as if you were using it within the database environment. You can add, delete, or modify data as well as scroll through it using the buttons of the form or report.

Macros

Macros are simple code that replicates a sequence of steps within Access. Unlike full VBA programming, macros cannot make decisions or loop through a block of code a certain number of times. They do not use variables, nor can you customize their operations through the use of procedures. They simply mimic a sequence of operations that you could perform yourself manually.

When you select the Macros category in the Database window, you will notice that there are no wizards. In some cases, you are going to create macros that are associated with a particular object within your database environment, such as a form or a button on that form. In other cases, you will create a macro for a general scenario within the environment. When we get to Chapters 5 and 6, we will be creating a couple of simple macros and then converting them into full VBA code. For now, if you click on New (located at the top of the Database window in the Macros category), you will see a grid, as shown in Figure 1-4.

Figure 1-4: The Macro design window

The window has two sections. The top section allows you to design an action, while the bottom section will be used to set parameters. As an example, suppose you want to write a simple macro to close a form. You would first select Close from the Action list in the top part of the grid (shown in Figure 1-5).

Figure 1-5: The Action list for macros

Once you have done that, you need to select what to close. Go to the Action Arguments section of the window and select Form in the Object Type field, as shown here.

From there on, all you need to do is add the name of the form object you are assigning this action to and save the macro. Simple, but limited in ability!

Modules

Modules are where the VBA code is written and stored. Beginning in Chapter 5, we will be spending the bulk of our time working on our code and learning about the VBA environment. For the time being, let’s take a look at the VBA Editor (VBE).

Within the Modules category, select New. You should see the same screen as shown in Figure 1-6.

Figure 1-6: The Visual Basic Editor

The main window, taking up most of the screen, is the Code window. The upper- left window is the Project window, and the lower-left window is the Properties window.

Notice the Project window, which is where you will find all the code modules associated with your application. For instance, in sophisticated projects, the VBA code may be divided up over many modules. The VBA code associated with a particular form or report will go into a dedicated module that holds only the specialized code for that form or report; while code associated with general operations will go into an entirely different module again. We will be looking at this in greater detail beginning in Chapter 5.