VBA class modules & factory design (2 Viewers)

bodders24

New member
Local time
Today, 22:32
Joined
Oct 3, 2016
Messages
27
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.

2025-10-12_17-43-29.jpg


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

Users who are viewing this thread

Back
Top Bottom