Class module for Object-Relational use 1

[GKChesterton]

Access 2003, DAO not ADO. I want to use a class module to address a record in a field. The instance is a record and its properties are fields. For example, if the table WeatherHistory has 365 records that summarize weather for the last twelve months, and I want to update the high temperature for a certain date, then in the past I'd have done something like

sngReading = 90	‘Fahrenheit, mind you … 	
datDateNeedsChange = #6/1/2009#
DoCmd.RunSQL “UPDATE WeatherHistory SET HiTemp =” & sngReading _
    & “ WHERE HistoryDate=” & datDateNeedsChange

Instead of running that, I want to instantiate a class module:

objWeatherHistory.HiTemp = sngHiTemperature

I can see how to make a Sub and go take care of it using FindFirst:

objWeatherHistory.HiTemp(datDateNeedsChange) = sngHiTemperature
. . . rsWH.FindFirst “HistoryDate=” & datDateNeedsChange
      rsWH.Edit
      rsWH!HiTemp = 90
      rsWH.Update

But that obviously isn’t exploiting OOP; and I have a lot of manipulations ahead, thus a lot of FindFirst routines. How is this done?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[MajP]

What you are proposing does not make much sense as presented. All you would be doing is duplicating the methods of the recordset object. It would be redundant and inefficient. Just make some procedures in a standard module that encapsulates your record manipulations. Something simple like.

public sub changeTemp(dtmDate as date, sngReading as single)
DoCmd.RunSQL "UPDATE WeatherHistory SET HiTemp =" & sngReading _
& " WHERE HistoryDate= " & dtmDate
end sub

then simply call this method

changeTemp(#1/1/2009#,90)

If you do what you are proposing you would have to do it one of two ways.
1) Instantiate a recordset, find the record, instantiate your class. Do the update on that record object and then update the recordset. That is just redundant. At least work directly with the recordset

or
2)Instantiate your recordset, and read all of your weatherHistory objects into a collection. Then do your modifications on the collection, and then update the recordset with the record objects in the collection. Again redundant.

If you really want to build a class then I would think the class would have a recordset as a property. You could then shield the user from methods. So when you create a weather history object it has a recordset property. Then you could simply do something like

public wxHist as objWeatherHistory

public some procedure
set wxHist as new objWeatherHistory
wxHist.RS(currentdb.openrecordset(....)
end sub

public some otherProc
wxHist.updateTemp(#1/1/2009#,90)
end sub

 

[GKChesterton]

My impression of ORM is that some environments like Ruby on Rails would support my proposed method. In researching class modules in VBA (Internet, Wrox books circa 2004), there are enough hints to have urged me on. And I haven't found a posting called "What not to use VBA classes for" that would help me as you have, MajP. So thank you for the review.

My original purpose for building class modules is handling hierarchical data (nested BOMs that describe manufactured items). I think that may be a good technique.

It won't take me long to dismantle my other work. Or as an alternative, I can get rich and famous as a pioneer of the Rube Goldberg Object-Relational Model (RGORM).

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[MajP]

I am a huge fan of building custom classes and OOP. Most of my forms, and controls are wrapped in custom classes that shield the user from complex code and allow for a lot of reuse. However, in the example you proposed I just do not see any savings in reuse, flexibility, or efficiency. There may be more to it, but you just talked about updating field values.

However, something more complex like handling updates to Hierarchical data may warrant a good class. You can do hierarchical queries, but they are very complicated. I find that doing a lot of recordset manipulation is easier. If you want to pursue that topic there may be a good reason for a class module to handle many recordset manipulations. I have done a lot of work with tree views and self referencing tables that support hierarchical data.

 

[GKChesterton]

If you're trying to horn in on [highlight #FF99FF]RGORM[/highlight], forget it -- I've already called my agent.

Seriously, thanks for the additional insights. My plan is to work up a new version that removes classes except where I need them for recursion, and then benchmark that against my existing code. Should be fun.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Time to check in. I cleaned up up my code. What a mess. I improved the location of misplaced procedures, removed orphaned declarations, and revised declarations for appropriately-limited scope.

As for the class modules, a few were changed over to standard. Three are left and they run in succession through a fair mountain of hierarchical data. I did profit by moving some code out to other modules; it's good to have them thinner. (It's a bit like normalizing data ... or polishing my great-aunt's silver service for that matter. I mean, though, that the ad-hoc methodology is interesting.)

My 'compile VBA' button is a bit sore but it feels good. The routine actually may be running a bit slower, but nothing's broken which is all I need for now.

Then I found this article, which seems to be the answer to my original question.

The ORM approach I once read proposed presenting a table/query/view as a class object. Each instance is a single record and the relevant fields are properties. I believe that will be elegant and efficient. The key is tracking your instances:

   ' Create a unique identifier string and set it to the upper index
   ' of the Public col Collection plus one.
   Dim varClassId As String
   varClassId = "Key_" & CStr(col.Count + 1)

   ' Set the clsTest class module's ClassID property to the value of
   ' varClassId, which in turn sets the frmTest.ClassId property to
   ' the same value. This is so the form has a method to track its
   ' relationship to the collection.
   cls.ClassID = varClassId

I recreated the example. (To my surprise it doesn't use any Set function.) Hopefully I can leverage it. If I can't, I'll claim victory anyway.

 

[MajP]

You are using the term ORM way out of context.

An object-relational database (ORD), or object-relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. ... Some (e.g. Microsoft SQL Server) allow such functions to be written in object-oriented programming languages, but this by itself doesn't make them object-oriented databases; in an object-oriented database, object orientation is a feature of the data model

All you have are a couple of custom classes to hold information, but there is no way you can use this as part of the data model or in the query language. An ORM is something far different

I am not sure how the article has anything to do with your original question. The example is a simple and standard use of a collection to manage form instances.

I told you that yes you could in fact do it that way.

Each instance is a single record and the relevant fields are properties. I believe that will be elegant and efficient. The key is tracking your instances:

by simply

2)Instantiate your recordset, and read all of your weatherHistory objects into a collection. Then do your modifications on the collection, and then update the recordset with the record objects in the collection.

But you can not convince me that in anyway this is elegant or efficient. As I said you have a recordset object at your fingertips. There is absolutely no way you are going to build classes that provide you more flexibility and efficiency than the native recordset.

Bottom line you are stuck with a RDMS. You can do what you propose, but in the end you are only going to push the data back into a relational data structure.

You have not described your hierarchical data, the original post was about temperature observations. People originally argured that RDMS could not handle such data, but clearly a self referencing table can.

 

[GKChesterton]

You are telling me I can serve up a table in different ways, for example I can press it into an array -- but what do I get? Something that is less efficient than a recordset.

About the two blocks you set off in quotes: Your statement proposes instancing a recordset, and I proposed instancing a record. I didn't connect these statements because they seemed different, but you are saying the two ideas amount to the same thing because the latter is illusory.

Thank you for the explanations.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[MajP]

No I said you could create individual record objects and keep them in a collection just like you were suggesting. But in order to do that the data will have to persist somewhere. So you would open a recordset, read the records, instantiate a weather record object, and put each object in a collection. Then once done performing your updates you could read the data from the collection back in to the recordset so that the data persists. What you are suggesting could be done no problem, I just do not see the need. There could be, I just do not see it. If I work with the recordset instead of rolling my own, I have all kinds of methods and properties at my fingertips.

So there could be utility in this, but with what you have suggested so far I do not see how it helps. Regardless, this is in no way object relational database structure. If that was the case I could use my weatherobject as basically a field in a table and use it directly in queries. Your objects would be part of the datastructure. Not currently possible in Access or Sql. Maybe in the future.

Here is an example where I do something like this and it has utility. I use treeview controls especially with hierarchical data. You read the recordset, then turn each record into a Node object of the Nodes collection of a treeview. Now you can edit, drag and drop to sort without dealing with an underlying table. When you are all done you read the nodes collection and update the table.

 

[GKChesterton]

ORM is object relational mapping. It's not the same as ORD et al.
en.wikipedia.org/wiki/Object-relational_mapping

I'd say you used ORM in the tree-view utility. This is not a topic I'm interested in debating any further, and thanks again for the information.

 

[MajP]

Got it. I was not even familiar with the term ORM. So I guess by definition I in fact implement ORM everytime I use a recordset object, and or push records into a listview or treeview. And I was not trying to debate, I agreed that you could do exactly what you proposed. I was just cautioning that the amount of work to pay-off.