If you’ve ever felt that object-oriented development is something that Access developers just don’t do–well, you’re wrong. As Peter Vogel points out, Access developers are uniquely qualified to design objects: They have the attitude and they’re already writing the necessary code.
Many Access developers live a long and happy life without ever creating their own objects. While Access developers use objects frequently, they find that they can build perfectly good Access applications without creating classes of their own. This actually makes sense: One of the main reasons people build objects is to hide the database structure from the application that’s accessing it. Of course, Access developers don’t want to hide the database from their application; the ability to bind an Access form to a database item (like a table or a query) is what gives Access its power.
That doesn’t mean the typical Access developer isn’t going to want to create the odd object. Many developers have combined various utility functions into class modules that they can copy from one project to another. The best-known examples are the many useful routines delivered as part of the Access Developer’s Handbook (by Paul Litwn, Ken Getz, and Mike Gunderloy). However, it’s only a matter of time before you’ll need to get more involved with objects. Here at Smart Access, for instance, we have at least one article in the pipeline that’s going to depend on your ability to integrate objects into your applications. It’s also entirely possible that, like developers in other environments, you won’t want to access a remote database through linked tables but will want to use ADO code embedded within a class module.
But here’s the good news: As an Access developer, you already have most of the mindset that’s required to be an effective object developer. Think about it: As an Access developer you create forms (a user interface) that hide the ugly details of the database from your end users. Only a very naïve Access developer creates one form for each table. Instead, each form typically represents an abstract view of several tables. An object developer creates objects (a programming interface) that hide the ugly details of the database from the programmers who use the object. Only a very naïve object developer creates one object for each table. Instead, each object typically represents an abstract view of several tables.
More good news: If you’ve been building your applications using functions and subroutines, then you know everything you need to know to create a method in an object. As you’ll see throughout this article, there’s no code that you’ll use in creating an object that you haven’t written over and over again in creating Access applications. In fact, because of your experience in working with recordsets you have another advantage in creating objects over non-Access developers: years of experience in working with structures for holding data.
Starting a design
Let me prove it to you: Assume that you’re going to be extracting sales order data from a remote database. The database has an Orders table with order header information, an OrderDetails table, and several lookup tables for decoding codes in the other tables.
Where do you begin designing an object model to work with this application? The answer is that, as with developing an application, you can begin anywhere. Whether you start building an application with the menu system (or with the most important form or with the easiest-to-build form) is a matter of personal preference. For this object model, you might begin with the object that accesses the SalesOrders table.
If you were retrieving data from the Orders table in an Access application, you might write a subroutine or a function to retrieve a single row from that table. Assuming that you’re not going to use a stored procedure, the ADO code to retrieve the data would look like this (I could have used DAO just as easily, but I’m more comfortable with ADO):
Dim rec As ADODB.Recordset Dim strCon As String Dim strSQL As String Set rec = New ADODB.Recordset strCon = Application.CurrentProject. _ AccessConnection.ConnectionString strSQL = _ "Select * from SalesHeader Where OrderId = '" & _ OrderId & "' " rec.CursorLocation = adUseClient rec.LockType = adLockBatchOptimistic rec.Open strSQL, strCon rec.ActiveConnection.Close
In this example, I’ve set the recordset to use a client-side cursor causing ADO to retrieve all the data into the recordset object as soon as I call the Open method. I’ve also set the LockOption to use batch optimistic locking, which prevents updates made to the recordset from being sent back to the database. The combination of these two options effectively eliminates the need to remain connected to the database, creating a “disconnected recordset.” As a result, I can break my connection to the database after retrieving the data by closing the ActiveConnection. Now it’s just a matter of adding this code to a Class module (which I’ll call the SalesOrder class).
Which raises the question: Where in the object would you put this code? When you work with an object, you execute its code through two mechanisms: by calling a method or by setting/reading a property. Traditionally, a method has a name that includes a verb so if this code goes into a method, the method should be something like GetSalesOrders (I like long method names). However, it makes more sense–in my opinion–to put the code inside a property.
How do you decide? This isn’t a technical question–it’s a design decision. The question that you need to ask yourself is whether developers should use your object like this:
Dim so As SalesOrder Set so = New SalesOrder so.GetSalesOrders(Me.txtOrderId.Text) or whether they should use your object like this: Dim so As SalesOrder Set so = New SalesOrder so.OrderId = Me.txtOrderId.Text
As you’re making up your mind, there’s one more thing to consider: You’ll need to provide a way for developers using your object to get at the data in the SalesOrders row. For that, you’ll almost certainly want to use properties that allow developers to read and change the values of the fields. This means that you’ll want to have a property that allows developers to retrieve the OrderId field. That being the case, it makes sense to use that OrderId property to retrieve the row.
If you decide that you prefer the method-based approach, you’ll write a method to retrieve the data and then write a property to support developers’ retrieving the OrderId field. You create that property by writing a Property Get routine. When someone reads a property, your Property Get routine–which looks just like a function–will be run. The result would be this code:
Public Sub GetSalesOrders(OrderId As String)
…data retrieval code…
End Sub
Public Property Get OrderId() As String
OrderId = rec("OrderId")
End Property
If you prefer the property-based method, you’ll write a Property Get and a Property Let routine. When someone changes the value of your property, your Property Let routine–which looks just like a subroutine–is run. The value that the property is being set to is passed as a parameter to the Property Let routine:
Public Property Let OrderId(OrderId As String)
…data retrieval code…
End Property
Public Property Get OrderId() As String
OrderId = rec("OrderId")
End Property
In this article, you’ll see my preference: I’ve used the property-based version in all of the following examples.
Extending the class
Now it’s just a matter of writing more Property Let and Get routines for the other fields in the row:
Public Property Let CustomerId(CustomerId As String)
rec("CustomerId") = CustomerId
End Property
Public Property Get CustomerId() As String
CustomerId = rec("CustomerId")
End Property
Here’s where the Access developer’s mindset becomes useful. In an Access form, you wouldn’t display just the CustomerId on the form. Instead, you’d retrieve enough customer information to be useful to the user (for example, the full customer name). When building an object, you’d do the same: You wouldn’t just return the customer id, you’d return something useful. In this situation, it makes sense to return a complete Customer object. In other words, someone using your object would be able to write code like this:
Dim so As SalesOrder Set so = New SalesOrder so.OrderId = Me.txtOrderId.Text Dim cust As Customer Set Cust = so.Customer
Implementing this is easy to do. First, of course, you have to create a customer class whose code would look very much like the SalesOrder object:
Public Property Let CustomerId(CustomerId As String)
Dim strCon As String
Dim strSQL As String
Set rec = New ADODB.Recordset
strCon = Application.CurrentProject. _
AccessConnection.ConnectionString
strSQL = _
"Select * from Customers Where CustomerId = '" & _
CustomerId & "' "
rec.CursorLocation = adUseClient
rec.LockType = adLockBatchOptimistic
rec.Open strSQL, strCon, adOpenStatic
rec.ActiveConnection.CloseEnd Property
End Property
Public Property Get CustomerId()
CustomerId = rec("CustomerId")
End Property
…other property routines to return Customer data…
Back in the SalesOrder object, instead of the property routines for a CustomerId that return a string, you’ll write property routines that return a Customer object. Since, presumably, you’ve already created a Customer object, all you have to do in your property routine is take advantage of the properties that you’ve put in your object. In your Property Get routine, for instance, you’ll create a Customer object, set its CustomerId property from the data in the SalesOrder row, and return the resulting Customer object.
Public Property Get Customer() As Customer
Dim cust As Customer
Set cust = New Customer
cust.CustomerId = rec("CustomerId")
Customer = cust
End Property
You don’t, however, write a corresponding Property Let. When someone sets a property to an object, they expect to use a Set command. In other words, the code to pass a Customer object to your Customer property would look like this:
Set so.Customer = cust
When a Set command is used to set a property, a Property Set routine inside your object is run. So, for your Customer object you must write a Property Set to accept a Customer object. Of course, the only part of the object that you’re interested in is the CustomerId property, since that’s the only value that’s stored in the Orders row:
Public Property Set Customer(Customer As Customer)
rec("CustomerId") = Customer.CustomerId
End Property
What I find amazing is how the little code that you’ve written lets developers do so many things. For instance, developers using your SalesOrder and Customer objects can write code like this (assuming that you’ve added a CustomerName property to your Customer object):
Dim so As SalesOrder Set so = New SalesOrder so.OrderId = Me.txtOrderId.Text Me.txtCustomerName.Text = so.Customer.CustomerName
Handling multiple rows efficiently
If you were building a sales order form, you’d probably display the Orders information at the top of the form and a continuous form of OrderDetail rows at the bottom. Your SalesOrder object should do the same but, in object development terms, the continuous form is represented by a collection. You actually have two ways to create the collection. Both build on something that an Access developer knows all about: working with recordsets.
The version that I’ll show you first is the simplest to understand. The key value of this method is that it doesn’t consume much in the way of memory (the second method that I’ll show you will use more memory). However, this method won’t support some key features that developers expect to find in a collection.
With whichever method you choose, the developer using your object will be able to pass a product id to the OrderDetails property and get back an OrderDetail object:
Dim so As SalesOrder
Set so = New SalesOrder
so.OrderId = Me.txtOrderId.Text
Dim odt As OrderDetail
Set odt = so.OrderDetails("A123")
To begin with, regardless of which method you choose, you must retrieve the OrderDetail rows. If you’re not particularly worried about efficiency, you can retrieve the OrderDetail rows in the OrderId Property Let routine that you’ve already created:
Dim rec As ADODB.Recordset Dim recDetails As ADODB.Recordset Public Property Let OrderId(OrderId As String) Dim strCon As String Dim strSQL As String …Orders retrieval code… Set recDetails = New ADODB.Recordset strSQL = _ "Select * from [Order Details] Where OrderId = '" & _ OrderId & "' " recDetails.CursorLocation = adUseClient rec.LockType = adLockBatchOptimistic recDetails.Open strSQL, strCon recDetails.ActiveConnection.Close End Property
Now you’re reading to write the Property Get routine that returns the OrderDetail object. If you go back to the code that showed how a developer would use your OrderDetails property, you’ll see that the product id is passed as a parameter to the property. To create a property that accepts a parameter, you just write a Property Get routine that accepts a parameter:
Public Property Get OrderDetails( _ ProductId As String) As OrderDetail
Now it’s just a matter of creating the OrderDetail object that was requested. First you need to find the product id detail record (to make sure that the row really does exist)–the ADO Find method will do this. The OrderDetail object is created in the same way that you created your Customer and Orders objects, except that you need to set two properties: the OrderId and the ProductId. With the code to find the requested row, the OrderDetail object is created like this:
recDetails.Find "ProductId = '" & ProductId & "'"
If Not recDetails.EOF Then
Dim odt As OrderDetail
Set odt = New OrderDetail
odt.OrderId = rec("OrderId")
odt.ProductId = ProductId
End If
However, if you wanted to be really efficient, you’d do two things differently:
- Only retrieve the OrderDetail rows if someone asks for them.
- Only retrieve the key information for the OrderDetail rows. In this case, that means just retrieving the ProductId (which is unique for each row).
If you decide to implement this strategy, then you won’t bother to retrieve the order details until the OrderDetails property is actually read. If the recordset holding the details doesn’t exist, you create it. With that strategy in place, the start of the OrderDetails Property Get routine would look like this:
Dim rec As ADODB.Recordset Dim recDetails As ADODB.Recordset Public Property Get OrderDetails( _ ProductId As String) As String Dim strSQL As String Dim strCon As String If recDetails Is Nothing Then …data retrieval code… End If
A two-key object
Before moving on to the next step in building a complete collection, let me stop and look at the OrderDetail object. As the previous code shows, the OrderDetail object requires two values to specify how it’s to be created: OrderId and ProductId. In my example, I used two properties to accept those values. The OrderId property code just updates a module-level variable:
Dim strOrderId as String Public Property Let OrderId(OrderId As String) strOrderId = OrderId End Property Public Property Get OrderId() As String OrderId = strOrderId End Property
My ProductId property would actually retrieve the order detail record using code that you’ve written hundreds of times:
Public Property Let ProductId(ProductId As String)
Dim strCon As String
Dim strSQL As String
Set rec = New ADODB.Recordset
strCon = "…connection string…"
strSQL = _
"Select * from [Order Details] Where OrderId = '" & _
strOrderId & "' And ProductId = '" & _
ProductId & "' "
rec.CursorLocation = adUseClient
rec.LockType = adLockBatchOptimistic
rec.Open strSQL, strCon, adOpenStatic
rec.ActiveConnection.Close
End Property
Public Property Get ProductId() As String
ProductId = rec("ProductId")
End Property
You may already be thinking that this is a very complicated way of doing things. I actually create an opportunity for developers using my code to create problems: If a developer sets the ProductId property before setting the OrderId property, then the code will generate an error.
For this object, it might make more sense to have a GetOrderDetail method that accepts both an order id and a product id:
Public Sub GetOrderDetail(OrderId As String, _ ProductId As String) …data retrieval code… End Sub
On the other hand, I think that it’s easier for developers to use my object if all of the objects in the project work the same way. Since I used properties to define the SalesOrder object, I’ll use properties to define the OrderDetail object. And, with a little more code, I could support the developers using my object setting the ProductId and OrderId properties in any order.
What you should notice about this discussion is that it’s not a technical discussion–it’s a design discussion. You’re free to implement your object any way that you want.
A complete collection
However, there are some important features of accessing a collection that this very efficient but simplified mechanism omits. For instance, there’s no Count property available through this implementation. Furthermore, developers won’t be able to use a For Each…Next loop to work through all the OrderDetail objects. This example, which loops through the OrderDetails collection of a SalesOrder object, adding each OrderId to a ListBox, isn’t possible with the highly efficient implementation already described:
Dim odt As OrderDetail For Each odt In so.OrderDetails Me.lstOrderId.AddItem(odt.OrderId) Next
Before you read the rest of this section, let me issue a warning: Supporting the For Each…Next loop is something of a kludge in VBA (the support is much cleaner in Visual Basic). So, unless you have a compelling interest in supporting the For Each…Next loop, you may want to skip this section.
To support a For Each…Next loop, you need to create a separate OrderDetails (notice the “s”) object that handles the collection of OrderDetail rows. The OrderDetails property on the SalesOrder object will now return this OrderDetails object:
Public Property Set OrderDetails() As OrderDetails
Dim odts As OrderDetails
Set odts = New OrderDetails
odts.OrderId = rec("OrderId")
OrderDetails = odts
End Property
So what does the OrderDetails object look like? To begin with, it has an OrderId property very much like what you’ve seen before to retrieve all the order detail rows:
Dim recDetails As ADODB.Recordset Dim colDetails As Collection Public Property Let OrderId(OrderId As String) Dim strCon As String Dim strSQL As String Set recDetails = New ADODB.Recordset strCon = "..connection string.." strSQL = _ "Select * from OrderDetail Where OrderId = '" & _ OrderId & "' " recDetails.CursorLocation = adUseClient recDetails.LockType = adBatchOptimistic recDetails.Open strSQL, strCon recDetails.ActiveConnection.Close
As I said, this version of managing the order detail rows is less efficient than the previous implementation. That’s because the next step in the OrderId property is to loop through all of the order detail rows, creating an OrderDetail object for each row and storing those objects in a collection. This will consume more memory than just holding the data in a recordset. However, this method will let developers use a For Each…Next loop with your object.
Here’s the code that, as each OrderDetail object is created, adds the object to a Collection object called colDetails declared at the top of the class:
Dim odt As OrderDetail
Do Until recDetails.EOF
Set odt = New OrderDetail
odt.OrderId = rec("OrderId")
odt.ProductId = rec("ProductId")
colDetails.Add(odt)
recDetails.MoveNext
End
You now have a choice as to how you want to implement the Count property for the collection. You can have your Count property return either the Count property of the Recordset or the Count property of the collection that you just created:
Public Property Get Count() As Integer Count = colDetails.Count End Property Public Property Get Count() As Integer Count = recDetails.RecordCount End Property
Developers can now use the Count property of your OrderDetails object by accessing the OrderDetails property of your SalesOrder object:
intCount = so.OrderDetails.Count
But what about that For Each…Next loop? In order to support using a For Each…Next loop with your object, you need to write a function that returns IUnknown (by convention, this routine is called NewEnum). In this function, you just need to return the _NewEnum member that’s built into the Collection object that holds the OrderDetail objects. Since the _NewEnum member’s name begins with an underscore, the name must be enclosed in square brackets:
Public Function NewEnum() As IUnknown Set NewEnum = colDetails.[_NewEnum] End Function
Here’s where things get ugly. In order for VBA to find this routine when processing the For Each…Next loop, the routine must be assigned a UserMemId attribute of -4. Unfortunately, the only way to assign this value to the routine is to go through these five steps:
- Click on the Class in Solution Explorer, and from the File menu select File | Export File.
- In the Export File dialog (see Figure 1), click the Save button to create a file called classname.cls.
- Open the resulting classname.cls file in Notepad and alter the NewEnum routine to insert a line that sets the UserMemId attribute:
Public Function NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Set NewEnum = colDetails.[_NewEnum] End Function
- Back in Access, delete the version of the file by selecting File | Remove classname (click No when asked if you want to export the file).
- Import the altered version of the file by selecting File | Import File.
Figure 1
The good news here is that you don’t need to do this more than once–VBA will maintain the attribute setting as you modify and recompile your class.
Updating data
Of course, there’s more to creating objects than displaying data. You also want to give the developers using your object the ability to change the data in your object. At the very least, you’ll want to give the developers using your object the ability to update the database with the changes that you’ve made in your recordset. The easiest way to do this is to reopen the connection to the remote database and call the recordset’s UpdateBatch method. The question is, where do you put this code?
You have three options:
- Perform an update whenever the data in the object’s recordset is changed. This means that you’ll update the remote database whenever any property is set–not an efficient choice. On the other hand, this strategy does guarantee that no data will ever be lost.
- Perform an update when the object is released. Class modules have an event called Terminate that’s fired when the variable pointing to the object is set to Nothing and the object is destroyed. The difficulty here is providing some way for the developer using your object to not make any updates. Presumably, you’ll need to add a method (called something like DiscardChanges) that allows the developer to replace any changed data with the original data from the database.
- Have an explicit Update method that developers have to call for updates to be made to the remote database.
Generally speaking, I prefer the third option. Here, along with the method that supports deleting SalesOrder data, is my typical Update method:
Public Sub Delete rec.Delete End Sub Public Sub Update rec.ActiveConnection.Open rec.UpdateBatch rec.ActiveConnection.Close End Sub
In the sample database method in the accompanying download, I’ve included the bare-bones of the four objects (SalesOrder, Customer, OrderDetail, and OrderDetails) that I’ve discussed in this article. I’m sure you realize that all of these routines will require some error handling code. But looking at my simplified code should help you realize that, as an Access developer, you have all the skills to become an object developer.
