Microsoft Access VBA Programming for the Absolute Beginner

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 = NothingEnd 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 = NothingEnd 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.