By Ken Getz and Mike Gilbert
In this month's installment of "Class Act," we'll tackle the task of creating a database class, i.e. a class module that encapsulates database functionality. One of the best uses for class modules is to abstract something complex and make it simpler. This idea fits in well to the database development environment because data access methods can be difficult to understand. What developers building business solutions want is a way to program against business objects (like customers, invoices, etc.), not database objects. In this article we'll show you one way to create a simple business object that's based on data in a database.
Note: The sample code for this month's column is contained in an Excel 97 workbook (DCLASS.XLS) and uses Microsoft's new version 1.5 of ActiveX Data Objects (ADO). If you don't have ADO 1.5 installed on your computer, you can download it for free at http://www.microsoft.com/data/.
The purpose of a database class is to wrap the processes involved in database access (opening a connection, reading from a table, saving changes, etc.) into a class and expose them as properties (the fields) and methods (the operations). As an example, we've created a class that encapsulates data from the Shippers table in Northwind.mdb — the sample database that ships with Microsoft Access. This is a simple table with three fields: SupplierID (an AutoNumber field), CompanyName, and Phone. Each of the fields is represented by a set of property procedures in the class module.
The class also features several methods, including Find, Save, Delete, Refresh, and MakeNew. These map directly to the database operations we mentioned earlier. Later, we'll explain how the code for these works.
The Shipper class module declares a number of private variables in its Declarations section (see FIGURE 1). The most important declaration is for the ADO Recordset object. The class uses this object to communicate with and control the database. While we've used ADO, you could have just as easily used another technology such as DAO, RDO, or the ODBC API.
FIGURE 1: The private variables of Shipper's Declaration section.
Option Explicit
' ADO objects.
Private prst As Recordset
' Base SQL statement for query.
Private Const conSQL = "SELECT * FROM Shippers"
' Connect string.
Private pstrConnect As String
' Private variables for field values.
Private plngID As Long
Private pstrCompany As String
Private pstrPhone As String
' Internal state variables.
Private pfModified As Boolean
Private pfAdding As Boolean
We've used a constant to store the base SQL query for creating the Recordset. Methods defined in the class will modify this, for example, to locate a particular shipper's record. Note that we've selected all the fields from the Shippers table using the SQL * operator. This is fine, because the table only has three fields. For performance reasons, you should only select those fields you're actually going to use in the class.
We declared the ADO connect string variable, pstrConnect, at the module level because we'll be setting it in the class' Initialize event and using it later on.
Three variables, plngID, pstrCompany, and pstrPhone, represent field values from the Shippers table. When the class reads data from the table it will store it in these variables. Likewise, as programs modify data, the changes will be stored in these variables until written back to the database.
Lastly, we declared two Boolean variables to hold state information, such as whether changes being saved apply to an existing record or a new one.
The Life Span of a Database Class
We designed the simple class described in this article to manipulate a single record in a database. As such, you might use several instances of the class in an application. As with any other class, start by creating a new instance:
' Declare the Shipper object.
Dim objShpr As Shipper
' Create a new instance of the class.
Set objShpr = New Shipper
When an instance of the class is created, several things must happen. First, the class instance must connect to a database. Second, it must create a blank Recordset so new records can be immediately added. Code in the Class_Initialize procedure takes care of this (see FIGURE 2).
FIGURE 2: Connecting to a database and creating a blank Recordset.
Private Sub Class_Initialize()
' Get the connect string from the Windows Registry.
pstrConnect = GetSetting(gconAppName, gconKeyName, _
gconConnect)
If Len(pstrConnect) Then
' Create a new ADO Recordset.
Set prst = New Recordset
' Execute a query to open recordset with no records.
prst.Open conSQL & " WHERE False", pstrConnect, _
adOpenKeyset, adLockPessimistic
Else
' No connect string info.
Err.Raise vbObjectError + 1001, _
"Shipper::Initialize", _
"Connect string information not found."
End If
End Sub
You'll notice that we've stored information about the connection in the Windows registry and retrieved it using the GetSetting function. You could also store information in global variables or objects. Another approach would be to pass it to the class instance as a property setting. The downside to this, however, is that you can't do it until after the instance is initialized.
Note: For the sample code to work on your computer, you'll need to provide connection information, either in the registry, or by modifying the source code, to use another method. Provided that you installed Microsoft Access in the default directory, you can use the DCLASS.REG file included with the sample code to create the required registry entry.
When a program is finished using the class instance, the Terminate event cleans up by dropping the connection:
Private Sub Class_Terminate()
' Drop the connection.
If Not prst Is Nothing Then
On Error Resume Next
prst.Close
Set prst = Nothing
End If
End Sub
To use the class effectively, you must be able to locate a particular record based on some criteria. In the case of the Shipper class, this is done by specifying the shipper's ID number or name in the class' Find method.
The code that locates the record is shown in FIGURE 3. Notice that both arguments, ID and Company, are declared as Optional. This lets the programmer choose how to locate a given shipper.
FIGURE 3: Locating a particular record based on the shipper's ID number or name.
Public Function Find(Optional ID As Long = -1, _
Optional Company As String = "") As Boolean
Dim strWhere As String
' Determine criteria to use.
If ID = -1 Then
strWhere = "CompanyName = '" & Company & "'"
Else
strWhere = "ShipperID = " & ID
End If
' Requery database based on supplied criteria.
With prst
.Close
.Open conSQL & " WHERE " & strWhere, pstrConnect, _
adOpenKeyset, adLockPessimistic
' Refresh and set return value.
If .EOF Then
Find = False
Else
Me.Refresh
Find = True
End If
End With
End Function
When a record is found, the Find method calls the class' Refresh method. This method, shown in FIGURE 4, moves data from the current record in the Recordset to private variables in the class module. The IsValid property called by the Refresh method simply checks the Recordset's EOF and BOF properties to make sure a valid record exists in the Recordset.
FIGURE 4: Moving data from the current record in the Recordset to private variables in the class module.
Public Sub Refresh()
' If there is a current record, copy
' the field values to private variables.
If Me.IsValid Then
' Put field values into variables.
plngID = prst("ShipperID")
pstrCompany = prst("CompanyName")
pstrPhone = prst("Phone")
' Set state variables,
pfAdding = False
pfModified = False
End If
End Sub
Once data is stored in the private variables, it can be accessed using the class' property procedures. Here's an example of the property procedures for the Company property:
Property Get Company() As String
Company = pstrCompany
End Property
Property Let Company(strCompany As String)
pstrCompany = strCompany
pfModified = True
End Property
The pfModified flag is set to indicate that data has been changed by the Property Let procedure.
An example of how you would use the class to locate a record and print a property value (the telephone number in this case) to the Immediate window is shown in FIGURE 5. Note that we used a named parameter in the Find method to search on company name rather than the default, ShipperID.
FIGURE 5: Locating a record and printing a property value.
Sub FindShipper()
Dim objShpr As Shipper
' Create a new class instance.
Set objShpr = New Shipper
With objShpr
' Find Federal Shipping.
If .Find(Company:="Federal Shipping") Then
' Print the phone number.
Debug.Print "Phone: " & .Phone
Else
' Print error message.
Debug.Print "Didn't find record."
End If
End With
Set objShpr = Nothing
End Sub
Once you've located a record, and perhaps inspected the existing property values, you may want to update them and commit the changes back to the database. Changing the property values is accomplished by using property procedures to modify the private variables where the data is stored. As you saw earlier, we use a Boolean variable to denote when a property value is changed. If the program using the class wants to tell if a record has been changed, it can call the IsDirty property:
Property Get IsDirty() As Boolean
' Return value of modified flag.
IsDirty = pfModified
End Property
You can use property procedures to validate changes to data, based on business rules. In the case of the Shippers table, the ShipperID field is an AutoNumber field and thus non-updateable. Therefore the Property Let procedure returns an error when called:
Property Let ID(lngID As Long)
' This is an AutoNumber field.
Err.Raise vbObjectError + 1002, "Shipper::ID (Let)", _
"Property is read-only."
End Property
After making changes to property values, a programmer can call the Save method to commit those changes to the database (see FIGURE 6). In essence, the Save method is just the converse of the Refresh method. Instead of copying data from the Recordset to private variables, it copies data from the variables to the Recordset. The only difference is the methods required to commit the changes to the database.
FIGURE 6: Calling the Save method to commit the changes to a database.
Public Sub Save()
' Only do this if values have changed.
If pfModified Then
With prst
' Call appropriate method.
If pfAdding Then
.AddNew
Else
' ADO doesn't require an Edit method.
End If
' Copy variables to field values.
.Fields("CompanyName") = pstrCompany
.Fields("Phone") = pstrPhone
' Update record.
.Update
' If adding, get new AutoNumber value.
If pfAdding Then
.MoveLast
plngID = .Fields("ShipperID")
End If
End With
End If
End Sub
Finally, the Save method checks a Boolean variable, pfAdding, to see if the method is being called in response to a new record being created. This variable is set when the MakeNew method is called (see FIGURE 7). Then, after the newly created record is saved, the Save method calls the Recordset's MoveLast method to pick up the newly created AutoNumber value. Obviously, this would not be necessary if you didn't use AutoNumber fields in your tables.
FIGURE 7: Calling the MakeNew method.
Public Sub MakeNew()
' Clear private variables.
plngID = 0
pstrCompany = ""
pstrPhone = ""
' Set state variables.
pfAdding = True
pfModified = False
End Sub
FIGURE 8 is an example of how you find use the Save method to modify a record. This example locates a shipper, changes the area code of the phone number, and saves the change.
FIGURE 8: Locating a shipper, changing the area code of the phone number, and saving the changes.
Sub ModifyShipper()
Dim objShpr As Shipper
' Create new class instance.
Set objShpr = New Shipper
With objShpr
' Locate shipper.
If .Find(ID:=3) Then
' Change area code.
.Phone = "(425)" & Mid(.Phone, 6)
.Save
End If
End With
Set objShpr = Nothing
End Sub
We've created a very simple class to demonstrate the concept of using class modules to encapsulate database data. We are the first to admit that it's not terribly sophisticated, but we hope you can see the value in doing this. Now a developer can write code to control a business object (e.g. a shipper), rather than worry about how the program interacts with a database.
In future installments of "Class Act," we'll explore this concept further, showing you how to deal with multiple records and relationships between tables in a database.
Download source code for this article here.
Ken Getz is a Senior Consultant with MCW Technologies, a Microsoft Solution Provider focusing on Visual Basic and the Office and BackOffice suites. Mike Gilbert is Technical Product Manager for Office Developer Edition and VBA Licensing at Microsoft. They are also co-authors of VBA Developer's Handbook and Access 97 Developer's Handbook (with Paul Litwin), both for SYBEX.