Objects for the Database Developer (example uses ADO)

<< Click to Display Table of Contents >>

Navigation:  ADO >

Objects for the Database Developer (example uses ADO)

200508_pv1 Peter Vogel          

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:

1. Click on the Class in Solution Explorer, and from the File menu select File | Export File.

2. In the Export File dialog (see Figure 1), click the Save button to create a file called classname.cls.

3. 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

4. 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).

5. Import the altered version of the file by selecting File | Import File.

200508_pv1
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.

Your download file is called 508VOGEL.ZIP in the file SA2005-08down.zip

This can be purchased with all the other downloads on this page