SQL and VBA

A SQL statement can be plugged into your code as a string and saved in a string variable. We will be creating a Recordset object. A recordset is the collection of records that are of specific interest to us. The following code does this:

Sub MyFirstConnection()
  Dim con1 As ADODB.Connection
  Dim recSet1 As ADODB.Recordset  
  Set con1 = CurrentProject.Connection
  
  Set recSet1 = New ADODB.Recordset
  recSet1.Open "tblCustomer", con1
  
  Do Until recSet1.EOF
  Debug.Print recSet1.Fields("txtCustFirstName"), _ 
  recSet1.Fields("txtCustLastName")
  recSet1.MoveNext
  Loop
  recSet1.Close
  con1.Close
  Set con1 = Nothing
  Set recSet1 = Nothing
End Sub 

This code employs a second object from the ADODB library called Recordset. As with the Connection object, you first declare the object reference—in this case recSet1—and then use the keyword Set to assign it.

You opened a specific table using the line:

recSet1.Open "tblCustomer", con1

The Recordset object has a method called Open that can be used to open the table containing the data. The con1 reference tells the Open method where the table can be found—in this case, in the current project.

The Recordset object also has a Boolean property called EOF. It can test whether the recordset is on the last record. This can be handy when setting up the loop in the preceding code. It also has a method called MoveNext, which can be used to advance the record pointer to the next record.

When you run the code, you are going to output the first and last names of each record. Again, the Recordset object has a property called Fields, so you can specify the fields from the recordset that you want to include in the output.

If you run the procedure, you will see the result shown here:

Look at the last four lines of the code:

  recSet1.Close
  con1.Close
  Set con1 = Nothing
  Set recSet1 = Nothing 

It is good form to use the Close method, in both the Recordset and Connection objects, to close down the connections. Once the connections are closed, you set the object references to Nothing. This will take the objects out of memory.

What if you want to sort the results on the last name? Let’s do a little adjustment to the code:

Sub MyFirstConnection()
  Dim con1 As ADODB.Connection
  Dim recSet1 As ADODB.Recordset
  Dim strSQL As String
  
  strSQL = "SELECT * FROM tblCustomer ORDER BY txtCustLastName"
  
  Set con1 = CurrentProject.Connection
  
  Set recSet1 = New ADODB.Recordset
  recSet1.Open strSQL, con1
  
  Do Until recSet1.EOF
  Debug.Print recSet1.Fields("txtCustFirstName"), _
  recSet1.Fields("txtCustLastName")
  recSet1.MoveNext
  Loop
  recSet1.Close
  con1.Close
  Set con1 = Nothing
  Set recSet1 = Nothing
End Sub

Notice that a string variable, strSQL, is declared, and then the SQL statement is assigned to it. Then, rather than having the recordset open the table, the variable containing the SQL statement is used to open the table and produce the recordset. The new results are as follows:

There may be times when you want to select the WHERE criteria at runtime, as opposed to hard-coding it. This is going to require some additional code as follows:

Sub MyFirstConnection()
  Dim con1 As ADODB.Connection
  Dim recSet1 As ADODB.Recordset
  Dim strSQL As String
  Dim strSearch As String
  
  strSearch = InputBox("Enter the last name to find", "Search Criterion")
  
  strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer" & _
  " WHERE txtCustLastName = " & " '" & strSearch & "'"
  
  Set con1 = CurrentProject.Connection
  
  Set recSet1 = New ADODB.Recordset
  recSet1.Open strSQL, con1
  
  Do Until recSet1.EOF
  Debug.Print recSet1.Fields("txtCustFirstName"), _
 recSet1.Fields("txtCustLastName")
  recSet1.MoveNext
  Loop
  recSet1.Close
  con1.Close
  Set con1 = Nothing
  Set recSet1 = Nothing
End Sub

As in the previous example, you set up a string variable and use an input box to obtain a value from the user. However, you need to do a bit of concatenation in the SQL string.

You will recall that the string you are searching for needs to be surrounded by single quotation marks. However, you cannot do that directly to the variable, or it will not be recognized. So you concatenate, as you have before, in the line:

strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE 
txtCustLastName = " & "'" & strSearch & "'"

If you run this code, you should be prompted for the last name that you want, with the results shown in the Immediate window.

Since there is only one record that meets this criteria, the loop encounters the end of the recordset after displaying information from the single record.