VBA class modules & factory design (5 Viewers)

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