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.