Output

You could make the output of the example look a little nicer with just a few simple programming techniques that you have already learned. Right now the output is in columns. By doing a simple concatenation, as follows, you can make it look like a list of names.

Sub MyFirstConnection()
  Dim con1 As ADODB.Connection
  Dim recSet1 As ADODB.Recordset
  Dim strSQL As String
  
  strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer"
  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 simple concatenation is performed, within the loop, of the results of the recordset. As a result, the output now looks like this:

You could send the output to a message box for an even better result. However, if you use the loop as you are using it now, you would end up with a new message box for each record in the recordset. Instead, you need to declare a new string variable and build the output within that variable. Then, you output the entire variable to the message box. Let’s take a look at the following code:

Sub MyFirstConnection()
  Dim con1 As ADODB.Connection
  Dim recSet1 As ADODB.Recordset
  Dim strSQL As String
  Dim strOutput As String
  
  strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer"
  
  Set con1 = CurrentProject.Connection
  
  Set recSet1 = New ADODB.Recordset
  recSet1.Open strSQL, con1
  
  Do Until recSet1.EOF
  strOutput = strOutput + recSet1.Fields("txtCustFirstName") & " " & _
 recSet1.Fields("txtCustLastName") & vbCrLf
  recSet1.MoveNext
  Loop
  recSet1.Close
  MsgBox strOutput
  con1.Close
  Set con1 = Nothing
  Set recSet1 = Nothing
End Sub

You assign to the variable strOutput whatever was previously in strOutput and then add a concatenation. At the completion of the concatenation, an intrinsic constant, vbCrLf, which represents a new line character, is used.

Now your output looks something like this:

For large quantities of data, this may not be practical. But for quick results involving a relatively small recordset, it does the job quite nicely.

Notice that in all of the preceding examples, using the objects contained in the ADODB library saved a tremendous amount of programming. Both the Connection and Recordset objects contain hundreds of lines of code. If you did not have these objects, you would need to write all of that code just to perform the simplest of tasks.

Chapter 15 contains a more complete discussion of these libraries.