March 1998

Return to Article Index

Database Programming with Class! By Jeffrey Hasan

Introduction

Class modules make Visual Basic programming more elegant. Through the technique of encapsulation they isolate complex code from the rest of the program, and communicate by exposing their methods and properties, and in some cases, events. By exposing their functionality instead of their entire code, they allow users to use the code but not get bogged down in its details. Class modules provide the foundation for ActiveX components and allow you to create reusable components. In object-oriented terms, you can also think of a class module as the blueprint from which an object is created. After all, Visual Basic works with objects, and class modules simply describe how those objects will look, act and feel.

Class modules are naturally suited to database programming. Anyone who has written long, messy SQL statements can appreciate how you might only wish to do this once, and how, if you are providing code to someone else, you might wish to shield them as much as possible from its complexities, while still exposing its full functionality. Class modules provide the encapsulation and powerful functionality that is required.

This article shows you how to design a class module from which you can create reusable "SQL Query" objects for accessing that veritable workhorse, the Northwind database, which ships with Microsoft Access. The sample code will also provide you with an example of using Data Access Objects for accessing a database.

Project Scope

Our goal in this project is to extract information on Beverage suppliers from the Northwind database. Specifically, our demanding CEO is anxious to have a listing of which beverages are supplied by different suppliers. "Bigfoot Breweries", for example, supplies "Sasquatch Ale", "Steeleye Stout", and "Laughing Lumberjack Lager". This query may sound simple, but it is based on 3 linked tables (Suppliers, Categories and Products), and therefore generates a long SQL string which you’d only want to write once. Let’s put it in a class module.

Designing the Class Module

Open up a new Standard EXE object and before starting, make sure that your project references the Microsoft DAO 3.5 Object Library. Next, insert a new class module from the Project - Add Class Module menu. Rename the class module to ‘ClsSQL’ in its property sheet, then save your entire project (use any Save-As names you wish: it’s unimportant since the forms and modules are referenced by their Property names only).

Let’s start by establishing 2 public properties of the class, by placing the following at the top of the class module:

Public CompanyName As String  ‘Object property
Public CategoryName As String  ‘Object property

Let’s also add a public variable called strMsg to hold a message that the object will send back to the user:

Public CompanyName As String  ‘Object property
Public CategoryName As String  ‘Object property

Public strMsg As String 

Now let’s program the ‘RunQuery’ method for this class. A method is programmed simply by adding a procedure to the class:

Sub RunQuery()

Dim strSQL As String
Dim db As Database
Dim qdfTemp As QueryDef
Dim rsResults As Recordset

'Construct the SQL string
strSQL = "SELECT DISTINCTROW Suppliers.CompanyName, " & _
"Products.ProductName FROM Suppliers INNER JOIN " & _
"(Categories INNER JOIN Products ON Categories.CategoryID =  " & _
"Products.CategoryID) " & _
"ON Suppliers.SupplierID = Products.SupplierID " & _
"WHERE (((Suppliers.CompanyName)='" & CompanyName & "') AND " & _
"((Categories.CategoryName)='" & CategoryName & "'))"



Set db = OpenDatabase("C:\MSOffice\Access\Samples\Northwind.mdb")

Set qdfTemp = db.CreateQueryDef("")
qdfTemp.SQL = strSQL

Set rsResults = qdfTemp.OpenRecordset(dbOpenSnapshot)
rsResults.MoveFirst

'Enumerate the recordset
With rsResults
    Do While Not .EOF
        Debug.Print .Fields(0); "  "; .Fields(1)
        strMsg = strMsg & .Fields(1) & vbCrLf
        .MoveNext
    Loop
End With

rsResults.Close
qdfTemp.Close

End Sub

Let’s briefly analyze the code. We start by dimensioning several data access objects, including a Database object, a QueryDef object and a Recordset object. Note that the ‘db’ object variable explicitly references a path to the ‘NorthWind.mdb’ database. You may need to modify this path if it is different on your system.

The SQL code is set up separately as a string variable, strSQL. I wrote this code by executing the query in Microsoft Access, then copying and pasting from the View SQL Code window. This is an excellent approach which I recommend you use all the time, since you can work out the bugs in your SQL code visually in Access. After copying and pasting the code into my Visual Basic module, I replaced the explicit query fields "Bigfoot Breweries" and "Beverages" in the SQL Code with the public variables CompanyName and CategoryName. I’d recommend that you construct this query in Access before you proceed further with this VB project, so that you fully understand the query’s construction and what records it returns.

After defining my QueryDef the code proceeds to enumerate the recordset. The records are written both to the Debug (Immediate) window and to the public string variable strMsg.

We’re almost finished with the class module, but first we need to add code to the class Terminate event. Every class module has an Initialize and a Terminate event, which contains code that runs when you create or destroy on object based on that class module. Place the following event code at the end of the class module:

Private Sub Class_Terminate()

MsgBox strMsg, Title:="Beverage Results for " & CompanyName, _
    buttons:=vbExclamation

End Sub

And we’re done programming the class module! Now let’s switch to the Form module in Form View. Add a single command button to the center of the form, give it the default name ‘Command1’, and the caption ‘&Run Query’. Double click on the command button to view the Click event code for this button. We want to add the following code so that when the button is clicked, our query is executed:

Private Sub Command1_Click()

Dim objSQL As ClsSQL

'Set the object variable
Set objSQL = New ClsSQL

'Set properties of the new object
With objSQL
    .CompanyName = "Bigfoot Breweries"
    .CategoryName = "Beverages"
End With

'Retrieve the properties
Debug.Print objSQL.CompanyName
Debug.Print objSQL.CategoryName

'Call a method of the object
objSQL.RunQuery

'Destroy the object
Set objSQL = Nothing

End Sub

Let’s analyze this simple code. We start by dimensioning a new object variable based on our newly written ClsSQL class. Next we set the object variable, using the Set statement. Note that we can also accomplish this in one step using:

Dim objSQL As New ClsSQL

Next we set the 2 public properties of our new project. We set CompanyName = "Bigfoot Breweries", and CategoryName = "Beverages", then write these to the Immediate window for verification purposes. Now all we need to do to run our query is to call the ‘RunQuery’ method of our new object:

objSQL.RunQuery

This executes a query that selects the beverages which are supplied by "Bigfoot Breweries".

Finally, we destroy the object by setting the object variable, objSQL to Nothing. On doing this, we trigger the Terminate event of the object, which as you’ll recall contains the following code:

MsgBox strMsg, Title:="Beverage Results for " & CompanyName, _
    buttons:=vbExclamation

where strMsg contains the results of the query. Thus, before the object is destroyed, it provides us with a message box that displays the results of the query, then when you hit ‘OK’, the object vanishes in a puff of smoke, as if it never existed!

Running other queries

Of course, "Bigfoot Breweries" is not the only supplier in the Northwind database, nor are beverages the only product supplied. "Mayumi’s" for example, supplies seafood and condiments, as does "Pavlova, Ltd." Now that we’ve developed a class module, for every new query that you want to run, you need only create a new object based on ClsSQL, and set its public properties to match the new query parameters. The code example below shows the Form1 code rewritten to execute 2 queries:

Private Sub Command1_Click()

Dim objSQL As New ClsSQL

'Set properties of the FIRST object
With objSQL
    .CompanyName = "Exotic Liquids"
    .CategoryName = "Beverages"
End With

'Retrieve the properties
Debug.Print objSQL.CompanyName
Debug.Print objSQL.CategoryName

'Call a method of the object
objSQL.RunQuery

'Destroy the object
Set objSQL = Nothing


'Set properties of the SECOND object
With objSQL
    .CompanyName = "Mayumi’s"
    .CategoryName = "Condiments"
End With

'Retrieve the properties
Debug.Print objSQL.CompanyName
Debug.Print objSQL.CategoryName

'Call a method of the object
objSQL.RunQuery

'Destroy the object
Set objSQL = Nothing

End Sub

The first object/query produces a listing of all of the beverages supplied by "Exotic Liquids", and the second object/query produces a listing of all of the condiments supplied by "Mayumi’s".

Conclusions

By now you have hopefully developed an appreciation for how useful class modules are for database programming. You could if you wanted, encapsulate this SQL code in a standard module, but it would not be as elegant as using a class module. Using a class module you can initiate a new object for every query that you wish to run, and the complex SQL code is nicely encapsulated. Using a standard module you would be forced to pass control to the module with every query. A standard module is part of the procedural flow of your main program and thus can contribute equally to debugging problems. A class module however shields its code, which makes the debugging of your project easier. Finally, your class module is reusable, and could easily be incorporated into any future project where you need to query the Northwind database in this fashion.

References

There are many excellent references on programming with class modules, and I would especially recommend Deborah Kurata’s Doing Objects in Microsoft Visual Basic 5.0. This book covers all aspects of a programming project, from planning to object design to deployment. Microsoft Visual Basic 5.0 Developer’s Workshop by Microsoft Press also provides some excellent examples of class modules. Surprisingly, I found Visual Basic Books Online to be obscure in its explanation of class modules, and would not recommend it as a first reference. Several recent magazine articles have also appeared on class modules, including in the January 1998 issue of VB Tech Journal, and in early issues of this magazine, Visual Basic Online. Class modules are new for many of us, but the learning curve is well worth the payoff you will receive in terms of more streamlined code development, and the ability to create reusable objects.

Copyright 1995-1998 VB Online. All rights reserved.