Understanding a Database Application

We have already established that a database is a structured container that holds data. We use SQL (Structured Query Language) within the VBA code to retrieve and sort the data we want to work on. You may want to use the stored data to perform calculations and make logical decisions. But before any of this can happen with any degree of efficiency, the application must be structured properly.

Most database applications, even at an enterprise level, have three main components:

  • User interface
  • Business logic
  • Database

This is sometimes called a three-tier system. Each part has a specialized function. At one end, the user has the ability to interact with the database, which is located at the other end of the system. The business logic is the VBA code that decides how the user interacts with the data. Figure 3-1 shows the interaction.


Figure 3-1: Block diagram of a three-tier system

The user interface consists of the input forms. We will be creating them using text fields, buttons, menu options, and dialog boxes. The user interface must be designed well to avoid confusion and to ensure that data is input properly. During design, it is helpful if you get feedback from the people who will be working with the system.

On the other end of the system is the database engine—called the Jet engine in Access. This is the part of Access that does the work. If you are using SQL Server, it is called the SQL Server engine. As I have already stated, Access is compatible with both the Jet and SQL Server engines. You would use the latter in large enterprise applications.

Connecting the user interface and database engine is the business logic. This is where VBA comes into play. This part of the application decides how the database engine will be accessed and what procedures will be followed in handling the data.

Now for the bad news! In addition to learning VBA, you are going to need to look at two other languages: ADO and SQL.VBA, on its own, has nothing to do with Access or its Jet engine. It has no capability of looking at, or manipulating, data. It just serves as a vehicle to a library of programs that can connect with the data. This library is known as ADO (ActiveX Data Objects).

ADO was introduced with Access 2000 as a library of programs capable of connecting with a data source. It is meant to replace an older library called DAO (Data Access Object). As a matter of fact, by the end of 2005, Microsoft has indicated that it will stop supporting DAO. As of this writing, ADO is evolving into ADO.NET. We will be discussing ADO in greater. However, since it is being phased out, we will not be treating DAO in this book.

VBA can perform the basic programming operations of executing a sequence of commands, using loops to execute a task for a certain number of times, and making decisions with If…Else structures. However, ADO has no such capability. Because of that, the two languages work together, with ADO riding the programming structures of VBA.

If all of that does not seem like enough, don’t forget the third player, SQL. Please do not get this confused with SQL Server, which is a database engine. SQL is an easy-to-use language that is used to ask the database questions or queries.

In the course of this book, we will first be learning VBA, then SQL, then ADO. You will need all three skills to program in Access, and together, they make up the business logic component.

But the main purpose of this chapter is to see what we can do before we need to call in the programming.