Microsoft Access VBA Programming for the Absolute Beginner - page 66

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:

click to expand

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.

Summary

We really covered a lot of territory here, and you just had your first taste of connecting VBA code to the database. We started with simple form examples and then saw the power of ADODB objects not only to connect to the database, but to save hundreds of lines of programming code. You also saw how to use these objects to help present formatted outputs of the resulting recordsets. In many ways, the rest of this book will explore the power of these objects.

We now turn our attention to building procedures and learning how they can interact with each other.

Chapter 9: Sub Procedures

Overview

In the previous chapter we put a lot of pieces together. You learned how to connect the code with the database by getting your first look at ADO. In addition, you got some experience in implementing objects.

Up to this point, we have used procedures. However, we have not taken a close look at the mechanics and options available with procedures. In this chapter we are going to concentrate on the mechanics of building a procedure. You will learn the various ways to declare and run a procedure, the various ways arguments can be passed, and the meaning of the word “scope.” You will also learn the differences between sub procedures and functions.

Finally, we will continue to build on the previous chapter by finding various ways to connect with the database.

Buy on amazon.com >>

Brown C. E., Petrusha R.

<< Previous page

Table of contents

Next page >>