VBA class modules & factory design (1 Viewer)

[bodders24]

I have developed a 2-tier application for my own use for budgeting. It was originally an Access front-end and an Access back-end, and I am currently upgrading the back-end to MS SQL Server.

As part of this upgrade I want to make as much use as possible of VBA class modules. To this end, I have created a separate stand-alone Access application which generates the class modules for a selected object by reading its table structure from SQL Server, and it then uses boilerplate text to create an interface class module, and an implementation class module. The motive behind this is that I only need to create the table in SQL Server, and the class generator code will take care of the rest at the click of a button.

Each class module contains 4 basic procedures which do the following:

• Load the object data from the SQL Server table to the class.
• Populate the object form in Access from the class.
• Populate the class from the object form in Access.
• Save the class to the SQL Server table.

The point of these procs is that they effectively decouple the front-end from the back-end.

I am now looking at building a design factory code module to create instances of the classes, and this is where I need some advice. I’ve read a few articles about this, but I am not sure which is the best way to go about it.

Thanks for any help and advice

Bodders

 

[RonPaii]

There was a discussion of class factories here at class-factories-parameterized-object-initialization based on a RubberDuck example.
I found that with VBA all classes with factories require the Pre-declare attribute to be true, causing all these classes to have 1 instance initialized when your application starts and remain so until it exits. I use a "factory" function that takes optional parameters, returning a new instance of the class. Most of the parameters inject other class instances used by the class.

I know there was a presentation that generated classes for each table in the DB to allow easer reference to those tables.

PS: I use Joyfullservice addon to export all objects to text files for source control and to build new front ends from those text files. The code is open and contains some really nice modules.

 

[MarkK]

I think you'd be more likely to use a factory pattern for composition, for instance, if you have a class that contains other classes. Then the factory might take responsibility for creating the parent instance, and injecting collections or related object instances specific to that parent.

Another thing you might look into, if you are reading data from a database and creating a DataModel layer, is a Repository pattern. Don't pollute the DataModel object itself with the specifics of database interaction, rather, delegate DataModel creation and persistence to a repository, which provides all your Db interaction, queries the database, creates instances, and caches them in a dictionary keyed by ID.

Then, when a consumer requests an instance by ID, the repository returns the cached instance--with blistering speed--from its dictionary.

So yeah, I would remove from the data model any knowledge of any database. The object is still reasonably tightly-coupled if it contains any hint of knowledge about how it is saved or how it is loaded. It is very convenient, for instance, if the user wants to add a new object, and your UI knows how to present that object, that you can present and validate a default instance that just contains data.

 

[arnelgp]

i think it is overkill.
you can use ado/dao recordset object.

 

[dalski]

you can use ado/dao recordset object.

How would he decouple - create a duplicate temporary record?

I agree @bodders24, there are not a lot of good tutorials on VBA Class Factory like functions. I'm not sure what you're gaining in decoupling the BE/ FE? Only to add several layers to the process; diminishing performance, maybe you have union queries & you're implementing a class interface to simplify the process where that makes perfect sense to me now.

Others here are for higher-calibre than me (you sound so too) so take what i say with a pinch of salt - it sounds like you only need a single instantiation; where predeclaring a class instance does not bother me much at my early stages of learning.

 

[bodders24]

Thanks for all the responses, and the helpful suggestions.

My main aim from this is to get as much code as possible for the class modules built by the Class Generator accdb using just the SQL Server table.

The secondary benefit is that I can add additional procs for saving to and loading from other types of database, eg Access back-end without having to rebuild the whole application.

@MarkK - I understand your point about the repository, but I think that - at this stage - it would be a bit overkill for what I am trying to achieve.

Bodders

 

[gemma-the-husky]

Will you be able to re-use these class modules in other apps?
If you need to budget for many companies, then I can understand perhaps.

If it's only one company then I struggle to see the point. In fact, budgeting is one area where I found it easier to use excel.
I had an excel template that evaluated profit and incorporated cash flow and interest calculations. (producing a P&L a/c. balance sheet, and cash flow projection). If you changed the profit/cash flow parameters (eg sales and GP%, Account receivables and Account payable projections) it would iterate the spreadsheet by adjusting the cash flow and interest repeatedly until a stable result was reached. I only had one (large) company to do each year, and this was sufficient.

I tend to concur with @arnelgp that your idea might be overkill.

 

[MarkK]

A potential problem with not retaining data model instances--if you are going to create them at all--is that if your user/consumer code requests one, and then requests the same one again--which is very common--now you have two distinct instances representing the same data within your own system. And if a user modifies one in ViewA, and modifies a different one differently in ViewB, when it comes time to save the data, one of those edits will overwrite the other one. This problem will be intermittent, and exceedingly difficult to find and/or prevent.
• If you are going to write a system that produces/consumes DataModel objects, there is a HUGE simplicity/reliability advantage to be gained from a Repository-As-Db-Gatekeeper, where, if ViewA requests an instance for ID=1234, and then 0.25 seconds later ViewB requests the same ID, that it is a fact--provided by your system architecture--that both views are working with the same instance.
• A Repository pattern guarantees this uniqueness.
Also, if you are accustomed to binding your UI directly to a row in a table, this uniqueness is enforced by the context--there is only one row--and so you may take this uniqueness for granted, and thereby undervalue its importance.

 

[Albert D. Kallal]

Thanks for all the responses, and the helpful suggestions.

My main aim from this is to get as much code as possible for the class modules built by the Class Generator accdb using just the SQL Server table.

The secondary benefit is that I can add additional procs for saving to and loading from other types of database, eg Access back-end without having to rebuild the whole application.

@MarkK - I understand your point about the repository, but I think that - at this stage - it would be a bit overkill for what I am trying to achieve.

Bodders

Ok, first up, there ARE reasons to start building some class objects to deal with business logic.
HOWEVER, and it is a big one, in 99% of cases?
Keep in mind that when you build a form + VBA in access, you ARE building a class object!

Why would I want to use a class object in ms-access?

The real trick to understanding class objects in ms-access is figure out WHEN to use them. You can’t normally sit down and say ok, lets use a class object. You need good reason to use one. So, when should you use an object in place of simply writing some code (and using the code in a form)?

Generally, I use and create a class object when the process, or "thing" I am dealing with starts to become very complex. The other big reason is when you need multiple instances of the same “thing”. Class objects allow you to “set” values of the object. Hence, objects can contain many settings that you the programmer can set in code. Hence, if the problem you are dealing with has MANY SETTINGS in code, then again a class object makes sense.

When I say settings, I don't mean things like height, weight and age of a person (that information can easily be placed in a table). When we say settings, we are talking about a bunch of variables and code and data that we are required to work with. So, when we have to work with a bunch of values that DON'T normally exist in the database, then a object starts to make sense. Further, in many cases, we might need things like height, weight, age. When data like height etc. IS in the database, but it is taking too much work to get information about the patient while writing code, then again a object makes sense. So, lots of code, lots of variables and lots of information from tables is a real winner. In fact, all of the classic reasons for using objects apply equally well to ms-access.

Lets assume we make a database to store information about doctors patients. You can easily build a data entry screen if the task at hand is simply entering a last visit date of that patient. If you need to print some reports on that patient, then again a button on the form with a bit of code will more suffice. Even if you are writing some billing software, then again a object will not help a lot. However, if you start having some very complex pricing procedures, then you certainly might start looking a creating pricing object.

Really, though, to work with these patients, you don't really need to create a patient “class” object. It just don’t make sense.

On the other hand, if you need to execute several "processes" on a patient, then a object starts to be come handy. When you find that you need a lot of code AND data to complete a certain task, then a class object starts to make sense. Hence, if for example you have a complex booking system that allocates doctors to that patient, or you have to schedule certain tests and procedures that requires you to book that patient to special exam room, then creating of a object now starts to make sense. Hence, creating some type of booking object will now start to be worth the effort. You might even consider making a patient object in this case also.

Often, regular coding techniques will suffice,. It is just that things start to get real messy after a certain amount of complexity. When you first start writing code, you are not writing very complex applications. Hence, if you are just starting to write some code, you will not see the need for creating objects yet.

However, there are some real good reasons to consider using class objects. One great reason is you can have more than one instance of the object. This is really critical for certain kinds of user interface. I have a tour booking system, and I created a tour booking object. If you take look at the following screen, you can see that I have an active tour on the left side, and also have an active tour on the right side.

The above screen allows you to move people from one tour booking to another. On the left side is one tour booking, and on the right side is another. Each tour object has a zillion things like

• The hotel name.​
• The type of rooms at his hotel.​
• The room rates for the particular season etc.​

In the above example we are actually moving people from one room in a tour to another room. In the above example the tour on the left side is the same as the one on the right side, but it does not have to be.

This cute simple screen is VERY complex. When I want to move that person, tons of things like room pricing has to be re-calculated for that ONE person. That pricing comes from a complex seasonal pricing table. I have to check for bus availability (the person likely has to be moved from one bus to another, and this is done automatically). However, before moving to that bus, availability of that bus must be checked. So, the problem becomes that I need two sets of a very complex system in memory (the tour on the left side, and the tour on the right side).

The point here is that for each tour, there is a TON of things need to have in memory:

• How many rooms available.
• How many tour buses available.
• How many seats on each tour bus.
• Name of the tour bus company.
• How many corporate bookings are holding seats..but not yet used.

The above list gets quite long. Each of the above "how many" questions is fact a bunch of code and queues to calculate the results. Note that if my design has STORED the above results, then probably I could get away NOT using a class object. However, EVERY single one of the above values requires some code to calculate. So, I don't store how many rooms are available anywhere in the database. I don't store how many seats are available where. All of these values are the result of on the fly calculations. So, it becomes really hard to write code. When we move a person from one booking to another, all of the above information will have to be dealt with (I need it at my finger tips while writing code). Further, if we need to display and have two bookings on the screen at the same time, that amount of information becomes VERY difficult when you now have to deal with two copies. Using class objects made the whole thing quite easy.

 

[Albert D. Kallal]

Lets try a bit of regular code here to ask a few questions about our tour. Lets assume we have a booking record for a customer. Ok, lets display the Tour name, and the hotel a user is booked into. Lets just assume that the user is booked to tour in the tours table (the tour table will define things like the hotel etc). Heck, lets just say the tour ID is 222. (this number is just the simple internal auto number for that tour record).

To get the hotel name for this booking, I would have to do the following.

dim lngTourId as longdim rstTour as recordsetdim rstHotel as recordsetlngTourId = 222

set rstTour = currentdb.openrecordset("select * from tblTours where tourid = " & lngTourId)

Ok...so now we have the tour, lets display the tour name

msgbox "tour name = " & rstTour!TourName

Ok, now display the hotel name. The hotel name of course is simply a hotelID in the tour.

lngHotelId = rstTour!Hotel

set rstHotel = currentdb.openrecordset("select * from tblHotels where hotelID = " & lngHotelId)

msgbox "hotel name = " & rstHotel!HotelName

You can see what pain it is to just get the tour name, and the hotel name. We have to deal with two related tables, build two queries, declare two recordsets. Man, what a pain! We could perhaps use the dlookup function above to reduce some code, but even dlookup becomes a real hassle here. We also could use a query that is a relational join to reduce the above code. However, there is a lot of additional things we need when working with the tour.

Now extend the above to include the room type description (a another table), the room rates (another table, and complex looking to a seasonal pricing system). Grab the bus? Heck...this is becoming a night mare to simply display a few things about the users booking. And, do you want to try and work with two of these tours in memory at the same time?

Now, lets do all of the above with a class object.

These properties of the object are actual ones that I use:

' declare an instance of our tour object.

dim myTour as New clsRidesTour

myTour.TourId = lngTourId

' at this point, I can retrieve, and ask virtually anything I want about his tour. So.

myTour.TourName returns the name of the tour

myTour.HotelName returns the name of the hotel

myTour.HotelSpace returns the space allocated in the hotel

myTour.HotelRooms returns the number of rooms in this tour

myTour.HotelRoomsUsed returns the number of rooms used

I will stop at this point, but to get the above simple answers with regular coding methods..it will requite at lest 50 lines or more of code? Yikes!! Is not the above incredibly easy? Hey, lets get the space remaining in the tour:

myTour.HotelSpaceRemain

You could just write a bunch of regular subroutines for each of the above questions, but you can see that many of the questions like TourName, hotel name etc will requite you to load a SET OF related records.

How nice and easy it is to get the hotel space remaining. Note only that, but inteli-sense works for all of the above..so I don't even have to remember all the names of subroutines/code as I type!

And to get number of people on the bus for this tour..?

myTour.InBusTotal

Here is the actual class code for above example in the class object for InBusTotal:

Public Property Get InBusTotal() As Integer
   InBusTotal = 0
   If m_rstBusList.RecordCount > 0 Then
      m_rstBusList.MoveFirst
      Do While m_rstBusList.EOF = False
         InBusTotal = InBusTotal + Nz(m_rstBusList!InBus)
         m_rstBusList.MoveNext
      Loop
   End If
End Property

Now, if you look at the above property of the object, you will notice that a recordset m_rstBusList is already loaded. In other words, when I create and set the myTour = "tour id", then all of the recordsets for bus company, hotels, rates etc gets loaded into the object. Hence, the above code as a result is MUCH BETTER then just writing a stand alone subroutine called "in bus total". Since, a stand alone routine would have to still LOAD up a very complex query into the recordset. I DON'T have to do that since the class object is a collection of those reocrdsets and routines to give me the "things" I need to know about a tour. The more you add code to the class object...the more it simplifies the application. When you don't use a object the more code you write the worse your application will get! (ask your self, where would I put those recordsets? and how would I SHARE THEM between each routine? I can't use global vars, since then I would be restricted to working with ONE TOUR in memory at a time).


Anyway...you can see that the whole thing just becomes such a pain to code simple questions about a tour. Create a tour object, and virtually anything you need about that tour is ready made at your finger tips.

However, JUST a class to do some query against SQL server? Nope, I don't think that effort yields anything in terms of code saving, and yields anything in terms of performance.....

So, keep in mind that introduction of SQL server changes ZERO in the above advice.....

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta, Canada

 

[Albert D. Kallal]

So, after my long post above?
The question then becomes this:

You have to shuffle data from SQL server to the object.
You then have to shuffle data from the objec to the form.
You then after editing, have to shuffle data back from the form to the object.
Then shuffle data from the object back to SQL server.

The problem?
While some systems have provisions for above, and allow binding of a class to a form?
(.net Maui (multi platform system, allows windows, Android, and iOS code with ONE project/build)

In Access, we don't have ANY provisions for moving data from the object to the form.
And worse yet, there are NO provisions for having nested class objects for related data - of which you surely going to have.

If you want a SINGLE object holds the parent data, and the child data? And you want to attach code and business logic to that object?

Why of course, that's called a Access form! - remember, each and every form in Access is a object. And you can even create multiple instances of that object if you wish.

The only real downside of using the Access form object is that tight coupling between the UI and data. But, for some, this is a bonus feature.

So, even for VERY complex Access applications? Most of the data code and editing can remain as a form, and having code inside of that form? Again, it still ok to have/place such code in that form object.

Now, for code that you CAN move out of the Form? (such as some payroll or invoice processing routine(s)? Sure that should be either module code, or even a class module. And thus you can pass the ONE form object to that routine, and that gives that external class or routine full use of all of the data - including sub forms. So, yes, it's a great idea to move out code from forms WHEN you can, but such code often should and stay in the form - and the parent data, and child data is thus already "modeled" for you by having designed that form with sub forms anyway...

However, for any complex recordset processing, and business logic routines? sure, I do suggest placing such code in external module(s), and using class modules. In fact we are moving boatloads of code out of our Access application, and moving such code into .net (which is then called/consumed from VBA).

The reason for above is:
We have some command line processing routines (.net), that works on the same data, and things like create a project, approve a project etc.?

Well, such code used to be 1000's of lines of VBA code. But, then our command line routines (that process incoming web orders) also needed to do many of the SAME things that the VBA code did.

And we have a live web site - again connected to the same database as our access application. So, now we only have ONE copy of the routine(s) that say approve a project. So, while I just stated that it's fine and ok to place VBA code in the correct form with the data you working on?

When such routines and expensive code needs to be shared amoung multiple platforms? Then by all means, move as much code and logic outside of the Access forms. And if code sharing is required, then move the code right out of VBA, and into say vb.net - which are are doing......


R
Albert

 

[MajP]

Are you set on doing this in Access? I think this is one of those cases where it would be far easier to code in something like .Net. It sounds like you are basically building strongly typed dataset objects which is a robust feature in ado.net. Especially if pulling in multiple data sources. Far easier to truly decouple. Then you can completely decouple and bind to anything supporing iBindable.

The real power of Access is a Rapid Application Development and especially bound forms. Once you start going beyond that Access becomes a hinderance. I am all for using Acccess for what it is really good at, but it provides few features for what you are doing.

 

[Imb]

The point of these procs is that they effectively decouple the front-end from the back-end.

"By accident" I read the above line.
I am working on a project where I have completely decoupled front-end from back-end.
I only work with local applications, no SQL-server. That could make a difference.

The problem with the coupling of Access is that Access starts from the Fields.
You than have to add User-ctl specific code or definitions to tune the behaviour of the Forms controls.
And this makes the forms very static, and hard to generalize.

In my project I start from the opposite site, form the User-ctl.
All information how the form-control should behave is stored in a metadata-table in FE. So the information is available in the application, not in the form and not "hidden" in BE-tables (e.g. referential integrity, or obliged field).
This metadata-table also contains where and how the value of the User-ctl will be stored.

When you open a User-form, an anonymous form is tuned according to the description in the metadata-table.
The anonymous form is just a form with hidden anonymous controls.
The linking is done through "dynamic Userctl-linking", that is, on the moment you enter a formcontrol, the corresponding record in the metadata-table is connected to the control.

For generalization, instead of Form-classes and all that kind, I use code to tune an anonymous form to the wanted layout.
This code is very generic, and shared by all applications. Compare the generic FormView in standard Access, but now included with Userctl-specifics thanks to "dynamic Userctl-linking".
Many, many underlying processes in the database can now be automated, and thus shared.

In the metadata-table I use a Userctl-type. It has far more possibilities then the underlying fieldtype. For instance any PK-field is of the "PK"-type. This makes it possible to zoom in in its primary record, jump from relation to relation etc.
In the running application all controls on all forms pass the same control events. It is a simple case statements that steers the further processing in the right direction depending on the Userctl-type.

Because of the unbound nature of the forms and the "dynamic Userctl-linking" it is "easy" to make all kind of bound/unbound combinations, using the same forms. Think of calenders, scheduling, internet-extracts, ...

 

[MajP]

@bodders24
I am interested in your approach to creating a class what appears to be similar to a "strong type dataset" in ado.net. I do not know how it is done in entity framework now a days.

I have created a separate stand-alone Access application which generates the class modules for a selected object by reading its table structure from SQL Server

I wanted to see if I could do it and if this is similar to your approach. I did a very rudimentary class generator, but seems pretty doable. My thought was that the class holds an ADODB disconnected recordset and then all the properties for each field. Then all the methods for loading, writing back to the real table, and manipulating the recordset. It is a little tedious at first to write a class to write a class but doable.
Obviously it needs lots of other methods but it seems doable.
Here is my generated class from the code by passing in a table name.

Option Compare Database
Option Explicit

Private m_Recordset As ADODB.Recordset
Public Property Get Recordset() As ADODB.Recordset
    Set Recordset = m_Recordset
End Property
Public Property Set Recordset(ByVal Newvalue As ADODB.Recordset)
    Set m_Recordset = Newvalue
End Property
Public Property Get ProductID() As ADODB.Field
    Set ProductID = Me.Recordset.Fields("ProductID")
End Property
Public Property Get ProductName() As ADODB.Field
    Set ProductName = Me.Recordset.Fields("ProductName")
End Property
Public Property Get supplierName() As ADODB.Field
    Set supplierName = Me.Recordset.Fields("supplierName")
End Property
Public Property Get CategoryID() As ADODB.Field
    Set CategoryID = Me.Recordset.Fields("CategoryID")
End Property
Public Property Get QuantityPerUnit() As ADODB.Field
    Set QuantityPerUnit = Me.Recordset.Fields("QuantityPerUnit")
End Property
Public Property Get UnitPrice() As ADODB.Field
    Set UnitPrice = Me.Recordset.Fields("UnitPrice")
End Property
Public Property Get UnitsInStock() As ADODB.Field
    Set UnitsInStock = Me.Recordset.Fields("UnitsInStock")
End Property
Public Property Get UnitsOnOrder() As ADODB.Field
    Set UnitsOnOrder = Me.Recordset.Fields("UnitsOnOrder")
End Property
Public Property Get ReorderLevel() As ADODB.Field
    Set ReorderLevel = Me.Recordset.Fields("ReorderLevel")
End Property
Public Property Get Discontinued() As ADODB.Field
    Set Discontinued = Me.Recordset.Fields("Discontinued")
End Property
Public Sub LoadRecordset()
   Dim conn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Set conn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   rs.CursorLocation = adUseClient
   rs.Open "SELECT * FROM Products", conn, adOpenStatic, adLockBatchOptimistic
   Set rs.ActiveConnection = Nothing
   Set Me.Recordset = rs
End Sub

People may wonder why do something like this. If you were doing this a lot it could make writing code a lot easier. All the specific properties would be available in intellisense and you could wrap a lot of functionality making it simpler or at least more dedicated to what you are doing.

attached is the class generator code. If you have not done extensibility this will show how to write code with code in vba.