Automating Microsoft Access with VBA

2017-07-07 02:10:07

< Day Day Up >

After you've settled on Access as the place where you'll store your information, you still have decisions to make. One of these is which programming language to use with Access. VBA is not the only means available to automate your Access solutions; depending on the situation, you might also want to make use of Access macros or Structured Query Language (SQL) for some or all of your work.

Using Macros

You may already be familiar with the Access macro programming language, which provides a limited (though still useful) set of tools for automating database actions. For example, Figure 1.3 shows a simple macro that opens a single form. You can run this macro directly from the database container, or attach it to a button on another form.

Figure 1.3. Access macros provide a limited means of automating database actions.

Macros are useful but limited. We've seen some databases that do amazing things with macros, but if you're a power user, you'll soon hit their limits. In particular, macros have only a limited ability to respond to errors or other conditions out of the ordinary.

TIP

There's no need to remove working macros from a database if you've decided to switch to VBA. Access is perfectly able to mix the two automation languages in a single database.

Using SQL

You might also have heard of Structured Query Language, more commonly called SQL. SQL is the language that Access uses to store database queries. For example, you might want to find all the Web-related projects in a database of tasks. Figure 1.4 shows an Access query to perform this task.

Figure 1.4. The design view of an Access query is just a pretty face atop the query's SQL.

Although most users prefer to work with queries in design view, this isn't how Access saves your queries. Instead, it uses SQL for this purpose. Here's the SQL statement that corresponds to the query shown in Figure 1.4:

SELECT Projects.* FROM Projects WHERE (((Projects.ProjectName) Like "*Web*"));

Although SQL isn't a general-purpose programming language, you'll need to understand the basics to work with Access. That's because many automation tasks in Access involve retrieving particular data, and SQL is the way to do that.

If youre not familiar with SQL, you'll find a primer in Appendix A, "Review of Access SQL," page 347.

Using VBA

Finally, there's VBA, the focus of this book. VBA can automate just about any operation that you can perform in an Access database. Here are some of the things that we'll teach you to do throughout this book with VBA:

Streamline data entry on Access forms

Add new items to list boxes

Customize the data that appears on reports

Work with data without even opening a form

Automate other applications from within Access

Import and export XML files

That's just a small sample; the possibilities are nearly limitless. To demonstrate the power of VBA, we'll start with a simple database and gradually make it more complex (and useful).

CASE STUDY: Using the TimeTrack Sample Database

Of course, you'll eventually want to use the techniques that you learn from this book in your own databases. But to demonstrate them, we're going to use a sample database named TimeTrack.mdb. At this point, there's no VBA code at all in the TimeTrack database. It handles the basic activities involved in keeping track of billable time for a small software development company, but it's pretty bare bones.

When you open the database, you'll see the Switchboard form, shown in Figure 1.5. This form provides an interface to the other four forms and the single report that make up the application.

Figure 1.5. The Switchboard form is the starting point for the TimeTrack application.

Clicking the Clients button will show you the list of the firm's clients as well as the projects that are underway for each client. Figure 1.6 shows the form that displays this information.

Figure 1.6. You can work with both clients and projects from the clients form.

Figure 1.7 shows the Employees form, which tracks basic information for each employee who can be assigned to work on a task.

Figure 1.7. The Employees form displays basic information on each employee.

The Projects form is central to working with projects. For each project, you can choose the appropriate client. You can also enter the various tasks that make up the project and the billing rate associated with each one, as shown in Figure 1.8.

Figure 1.8. Projects contain tasks, which can be billed at different rates.

The last form in the application, shown in Figure 1.9, tracks timeslip information. After choosing an employee and a task, you can enter the date and the number of hours worked. This ensures that those hours are properly billed to the customer.

Figure 1.9. Timeslips record billable time.

Finally, Figure 1.10 shows the Billing Report, which is used to calculate charges for a client. When you run the report, it will prompt you to select a client as well as a starting date and an ending date.

Figure 1.10. The Billing Report calculates the amount due from a client for a period of time.

Right now, all the automation in this database is done with macros. But it won't stay that way!