SQL (Structured Query Language)

Before you can move on in your study of VBA, you need to start learning a second language that will work along with VBA.

The power of any database is the ability to ask it questions. This is called querying the database. There is a good possibility that you have done this many times without knowing it. If you use an Internet search engine, such as Yahoo! or Google, you have queried a database when you asked it to search for some topic. The language behind the query is called SQL (pronounced “C-Quill”).

SQL stands for Structured Query Language, which is the universal syntax for asking database questions. Virtually every database program, with minor variations, uses it. Once you have learned the standard syntax of SQL, which is fairly simple, you can ask a broad range of questions and bring a lot of information together easily.

Covering every aspect of SQL would take a book in itself; instead, we will look at the main points. Let’s begin by using SQL in a nonprogramming environment.

SQL—the Basics

If you are an experienced Access user, odds are you have built query objects. Most likely, you have used the graphics tools built into Access to do it. You have dropped the fields you wanted into a grid, selected which fields you wanted the results sorted on, and maybe even added a search criterion. Again, you probably did it all graphically.

What you may not have realized is that Access was writing SQL in the background for you. Let’s see an example. Start a new query object in Access, and select the tblCustomer object for the table. If you add some columns, a sort, and a possible search criterion, your screen should look something like Figure 8-9.

Figure 8-9: The Query grid

Access allows you a peek behind the scenes if you select View | SQL View. You will see the SQL that was written behind the scenes of this query, as shown in Figure 8-10. Let’s break the query into sections and discuss them.

Figure 8-10: The query in SQL View

SELECT

By tradition, programmers usually put SQL keywords in all caps. SQL is not case sensitive, but using uppercase helps SQL keywords stand out better.

The SELECT keyword chooses the columns you wish to use. In the case of multiple columns, they are separated by commas. For example:

SELECT txtCustFirstName, txtCustLastName

In many cases, you may want to use all of the columns. In this case, you can use an asterisk (*) to indicate all columns:

SELECT * 

But where do the columns come from? Well, the answer is in the question. The SELECT clause needs to be completed with the keyword FROM and the name of the source. You would complete it with:

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer

If you want to be very correct with your syntax, write the SELECT clause as follows:

SELECT tblCustomer.txtCustFirstName, tblCustomer.txtCustLastName, 
tblCustomer.txtState

When you are using one data source, it is not necessary to precede each field name with a table reference. But in a few moments, this will be important when you work with multiple tables.

This would be considered the mandatory part of the SQL statement. Now you have some options you can add.

WHERE

The WHERE keyword allows you to add search criteria. For instance, you might enter

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = ‘NJ’

Notice that because the field txtState is a text field, the search string is surrounded by single quotation marks. This is going to be important when we return to VBA because the search string needs to be differentiated from the SQL string.

In many cases you may have multiple search criteria separated by the logical operators AND or OR. For example:

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = ‘NJ’ AND 
txtCustLastName = ‘Miller’

Let’s say you want to see all the records in which the last name begins with the letter M. You use the operator Like to do pattern matching with an asterisk. For example:

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtCustLastName 
Like ‘M*’

You use the asterisk to replace all characters from that point on, or use the question mark to replace one character.

ORDER BY

You use the ORDER BY clause to sort the recordset. This clause can be placed either before or after the WHERE clause. As an example:

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = ‘NJ’ 
ORDER BY txtCustLastName

Let’s assume you want to sort by the last name and then the first:

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = ‘NJ’ 
ORDER BY txtCustLastName, txtCustFirstName

By default, the sort is ascending. However, if you want it to be descending, you simply add the keyword DESC, as shown next:

SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = ‘NJ’ 
ORDER BY txtCustLastName DESC, txtCustFirstName

INNER JOIN

In many instances, you may want to join two or more tables to bring data from different tables together. This can be a bit tricky because before you can do this, there must be a relationship on a unique value. For instance, two tables could be joined by an employee number. In general, fields such as last name are not a good choice to link because there could be multiple occurrences of the name.

When you do an INNER JOIN, you need to specify the source of the field, as shown earlier with the SELECT clause. The syntax is a bit involved. As an example:

SELECT tblCustomer.txtCustNumber, tblCustomer.txtCustLastName, 
tblPurchases.txtPurchaseNumber FROM tblCustomer INNER JOIN tblPurchases ON 
tblCustomer.txtCustNumber = tblPurchases.txtCustNumber;

Beginning with the FROM clause, you name the first table and then add a second table with INNER JOIN. However, you need to specify how the two tables can be linked. In this case, the txtCustNumber field of the tblCustomer table is linked with the txtNumber field of tblPurchases. Notice that the two fields do not have to have the same name. Notice, also, that you had to specify the originating table of the fields.

It is interesting to note that if you use the SQL View of Access to type out a SQL string, the graphic builder will automatically conform to the view. For example, if you typed the preceding SQL statement in SQL View, the graphic builder would look like Figure 8-11.

Figure 8-11: Graphic query builder after typing the statement in SQL View

In a few moments, you will be adding SQL statements to your VBA code. It is a common practice to set up and test the query using the graphic builder. Then, if it works to your liking, go into SQL View to copy and paste the statement into your VBA code. This saves a lot of typing and helps prevent potential errors.