VBA class modules & factory design

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