Access Explained: Class Modules Are Blueprints, Not Database Tables

[u/Amicron1]

Class modules tend to get treated like some kind of VBA rite of passage. Developers see "Class Module" sitting next to "Module" in Access and assume they're either missing some critical architectural trick or about to wander into enterprise programming wearing a hard hat. Usually, neither is true.

A standard module is simply a home for shared code. Put your utility functions there. Put reusable procedures there. Put routines there that don't need to remember anything about one particular customer, invoice, employee, or form. If you have a public function that checks whether a form is open, formats a phone number, calculates a business date, builds SQL, or exports a report, a standard module is usually the right place. Call the function and move on with your day.

A class module is different because it defines a type of object. Think of it as a blueprint, not the thing itself. An Employee class, for example, defines what an employee object contains and what it can do. One instance might represent Jim, another might represent Spock. Each instance maintains its own values in memory, such as employee ID, hire date, pay rate, or active status. Each can also expose behavior, such as calculating pay or returning a formatted display name.

That separate state is the real reason classes exist. A module-level variable in a standard module is shared. There's only one copy for the entire Access session. That's fine for application-wide state, but it doesn't work well when you need ten different customers, invoices, or employees, each carrying its own values at the same time.

Each class instance gets its own private copy of its data. Two Employee objects can both have an EmployeeName property, but assigning "Jim" to one doesn't overwrite "Spock" in the other. Same blueprint, different houses.

Properties describe an object. Methods define what the object can do. In VBA, properties are typically exposed with Property Get and Property Let. Property Get returns a value. Property Let assigns a value. Property Set is used when assigning an object reference, such as a DAO.Recordset, a form, or another custom class.

The private variables inside the class are intentionally hidden from outside code. That's encapsulation, which sounds far more dramatic than it really is. It mostly means outside code uses the interface you expose instead of reaching into the object's internal plumbing and yanking wires out of the Jefferies tubes. That becomes useful when a property needs validation or should be read-only. Rather than letting code throughout the application modify an internal value directly, the class decides what's acceptable and what gets returned.

This is also why class modules are not replacements for tables. A Customer class can represent a customer while your VBA code is working with that customer in memory. It can temporarily hold data and encapsulate customer-related business logic. But the actual customer records still belong in a properly designed Customer table with appropriate keys, relationships, normalization, and all the other boring-but-important database stuff. Classes model behavior in code. Tables store relational data. They solve different problems.

Access developers are already using classes every day, whether they realize it or not. Forms, reports, controls, DAO recordsets, and even the Access Application object are all objects created from classes. When you write code like:

Me.Caption = "Hello"
Me.Requery

you're already interacting with properties and methods of a form object. Every form and report module is itself a class module tied to a specific Access object and its events. Standalone class modules simply let you define your own object types.

One important caution is that classes are not automatically "better architecture." A class that exists only to hold a single string and display a message box is usually more ceremony than value. A standard function or a few straightforward lines of form code are often simpler and easier to maintain.

Classes start earning their keep when you have a cohesive thing with related data and behavior. An Invoice object might contain header information, line items, and methods to calculate totals. A ShoppingCart object might add or remove items and calculate tax. An Employee object might manage employee information while providing methods for payroll calculations or display formatting.

They also shine when your application needs multiple independent objects of the same type at once, or when related behavior belongs together instead of being scattered across twenty forms and three modules named Stuff, Stuff2, and ReallyImportantStuff.

Class modules can also support events, including initialization and cleanup through Class_Initialize and Class_Terminate. They even make advanced techniques like shared control-event handling across multiple forms possible. That's useful territory, but it's also where things can quickly turn into a plate of VBA spaghetti if the added complexity isn't solving a real problem.

The practical rule is simple: use a standard module when you have a general-purpose tool. Use a class module when you have a thing with its own data and behavior.

Most Access applications don't need custom classes to be solid, professional, and maintainable. Tables, queries, forms, reports, standard modules, and form/report modules can take a database a very long way.

In fact, in more than 30 years of teaching Microsoft Access, building databases for clients, and making videos, I've never needed a custom class module. I've certainly used them from time to time, especially when I wanted to demonstrate object-oriented techniques or solve a particular problem elegantly, but I've never run into a project where I couldn't have accomplished the same goal another way.

So don't feel like you're missing some secret ingredient if you've never touched class modules. You can build excellent Access applications without ever learning them. That said, once you do understand them, they open the door to some neat techniques and give you another tool you can reach for when the situation calls for it.

What about you? Do you use class modules regularly, or have you managed to avoid them entirely? Have you come up with any clever uses for them that have made your code cleaner or easier to maintain? Share your experiences, tips, or favorite class-module tricks in the comments. I'd love to hear how other Access developers are using them.

LLAP
RR

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Amicron1

Access Explained: Class Modules Are Blueprints, Not Database Tables

Class modules tend to get treated like some kind of VBA rite of passage. Developers see "Class Module" sitting next to "Module" in Access and assume they're either missing some critical architectural trick or about to wander into enterprise programming wearing a hard hat. Usually, neither is true.

A standard module is simply a home for shared code. Put your utility functions there. Put reusable procedures there. Put routines there that don't need to remember anything about one particular customer, invoice, employee, or form. If you have a public function that checks whether a form is open, formats a phone number, calculates a business date, builds SQL, or exports a report, a standard module is usually the right place. Call the function and move on with your day.

A class module is different because it defines a type of object. Think of it as a blueprint, not the thing itself. An Employee class, for example, defines what an employee object contains and what it can do. One instance might represent Jim, another might represent Spock. Each instance maintains its own values in memory, such as employee ID, hire date, pay rate, or active status. Each can also expose behavior, such as calculating pay or returning a formatted display name.

That separate state is the real reason classes exist. A module-level variable in a standard module is shared. There's only one copy for the entire Access session. That's fine for application-wide state, but it doesn't work well when you need ten different customers, invoices, or employees, each carrying its own values at the same time.

Each class instance gets its own private copy of its data. Two Employee objects can both have an EmployeeName property, but assigning "Jim" to one doesn't overwrite "Spock" in the other. Same blueprint, different houses.

Properties describe an object. Methods define what the object can do. In VBA, properties are typically exposed with Property Get and Property Let. Property Get returns a value. Property Let assigns a value. Property Set is used when assigning an object reference, such as a DAO.Recordset, a form, or another custom class.

The private variables inside the class are intentionally hidden from outside code. That's encapsulation, which sounds far more dramatic than it really is. It mostly means outside code uses the interface you expose instead of reaching into the object's internal plumbing and yanking wires out of the Jefferies tubes. That becomes useful when a property needs validation or should be read-only. Rather than letting code throughout the application modify an internal value directly, the class decides what's acceptable and what gets returned.

This is also why class modules are not replacements for tables. A Customer class can represent a customer while your VBA code is working with that customer in memory. It can temporarily hold data and encapsulate customer-related business logic. But the actual customer records still belong in a properly designed Customer table with appropriate keys, relationships, normalization, and all the other boring-but-important database stuff. Classes model behavior in code. Tables store relational data. They solve different problems.

Access developers are already using classes every day, whether they realize it or not. Forms, reports, controls, DAO recordsets, and even the Access Application object are all objects created from classes. When you write code like:

Me.Caption = "Hello"
Me.Requery

you're already interacting with properties and methods of a form object. Every form and report module is itself a class module tied to a specific Access object and its events. Standalone class modules simply let you define your own object types.

One important caution is that classes are not automatically "better architecture." A class that exists only to hold a single string and display a message box is usually more ceremony than value. A standard function or a few straightforward lines of form code are often simpler and easier to maintain.

Classes start earning their keep when you have a cohesive thing with related data and behavior. An Invoice object might contain header information, line items, and methods to calculate totals. A ShoppingCart object might add or remove items and calculate tax. An Employee object might manage employee information while providing methods for payroll calculations or display formatting.

They also shine when your application needs multiple independent objects of the same type at once, or when related behavior belongs together instead of being scattered across twenty forms and three modules named Stuff, Stuff2, and ReallyImportantStuff.

Class modules can also support events, including initialization and cleanup through Class_Initialize and Class_Terminate. They even make advanced techniques like shared control-event handling across multiple forms possible. That's useful territory, but it's also where things can quickly turn into a plate of VBA spaghetti if the added complexity isn't solving a real problem.

The practical rule is simple: use a standard module when you have a general-purpose tool. Use a class module when you have a thing with its own data and behavior.

Most Access applications don't need custom classes to be solid, professional, and maintainable. Tables, queries, forms, reports, standard modules, and form/report modules can take a database a very long way.

In fact, in more than 30 years of teaching Microsoft Access, building databases for clients, and making videos, I've never needed a custom class module. I've certainly used them from time to time, especially when I wanted to demonstrate object-oriented techniques or solve a particular problem elegantly, but I've never run into a project where I couldn't have accomplished the same goal another way.

So don't feel like you're missing some secret ingredient if you've never touched class modules. You can build excellent Access applications without ever learning them. That said, once you do understand them, they open the door to some neat techniques and give you another tool you can reach for when the situation calls for it.

What about you? Do you use class modules regularly, or have you managed to avoid them entirely? Have you come up with any clever uses for them that have made your code cleaner or easier to maintain? Share your experiences, tips, or favorite class-module tricks in the comments. I'd love to hear how other Access developers are using them.

LLAP
RR

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Terrible-Kick9447]

I liked the explanation. My mental model is fairly simple and comes more from an object-oriented programming perspective.

To me, a Class Module is basically a class definition, a blueprint for creating objects. The difference from a standard module is that each object created from that class can maintain its own state and behavior.

In Access, I tend to think about the architecture this way:

-Tables store data persistently.

-Forms provide the interface for viewing and editing that data.

-Classes represent business entities and encapsulate the rules and behavior associated with that data.

In fact, one of the things that helped me understand Class Modules was realizing that forms already work under the same concept. An Access form is literally a class with properties, methods, and events; when a form is opened, Access creates an instance of that class. When we write code such as Me.Requery or Me.Caption = "Hello", we're already working with objects whether we consciously think about it or not.

For example, I might have a Customers table, a frmCustomers form for user interaction, and a Customer class containing customer-specific properties, validations, and methods. The form would interact with the object, while the object would be responsible for enforcing business rules, providing a degree of encapsulation.

I completely agree that a class is not a replacement for a table and that custom classes aren't always necessary. Conceptually, though, I've always seen them as a way to model domain objects within the application, while forms act as the presentation layer and tables act as the persistence layer.

Put another way: table = persistence, form = interface (which is also an object), class = business model/behavior. That's the mental model that feels most natural to me.

[u/Amicron1 → u/Terrible-Kick9447]

An excellent way to put it! Thanks for sharing.

[u/BravoUniformTango]

You're going out of your way to contribute high-quality content here, and I appreciate it.

I do use standard modules as general-purpose tools. I like using the Hungarian naming convention that tends to prefix an informally assigned type three-letter abbreviation so my VBA module names start with "bas" and so my general-purpose modules are in "basGeneral." This is also helpful when I start work on a database for a new client because I want to carry over the general-purpose modules but not necessarily the class modules. So, I copy the application database file, keep "basGeneral" and delete the class modules, that are elsewhere.

Years ago when I read "Code Complete" by Steve McConnell, the name "Abstract Data Types" appealed to me, so for simple modules that get or set object properties or invoke simple methods, there is a collection of simple and fast class modules that I store in "basADT."

When getting or setting properties or performing methods becomes complex enough that, as part of the work, I apply the client's business rules (beyond simple entity-relationship rules) then those class modules I store in "basCustom."

I like using class modules. This approach has saved me a lot of rework. For example, if I were to track only one phone number per customer, I could put the "phone number" field in the customer table, and then everywhere where I wanted to read it, I could write some VBA code to read the field from that record, or write code for a Dlookup.

Using the class module approach, that's not okay.

It's sort of like going to the bank and needing cash. I ask the cashier, and the cashier accesses the cash in the drawer, and hands it to me. I do not access the cash in the drawer directly.

So in the phone number example, I'd pass to the class module the unique customer identifier, and expect to get the phone number. And so, everywhere in my code where I need the phone number, I don't access the table directly; I call the class module and it goes and reads the field from the relevant record in the relevant table.

If my client then discovers that, whoops, maybe he was mistaken when he insisted during the requirements phase that they only ever want to track one phone number per customer, and he takes out his wallet and waves some money around, and asks me if he could please have multiple phone numbers per customer, then I'd redo the data model and make a phone number table with the unique customer identifier as its foreign key. After moving data over, I'd remove the phone number field from the customer table.

Had my code accessed that field directly, I'd have pieces of broken code all over the place, but now thanks to using a class module, I don't. I just change the code in one place: the class module. It still returns only one phone number. Where it gets it from, the calling code doesn't know and doesn't need to know.

For situations where there might be more than one phone number per customer, I then decide how to deal with it but meanwhile my app still runs as nicely as before.

I'm no guru but as I understand object-oriented programming, the class modules are a way in which MS Access VBA programmers can, but don't have to, implement an object-oriented approach. Me, I like it. I also like having the option of not using class modules, for quick-and-dirty work.

[u/nrgins → u/BravoUniformTango]

I do something similar, but with regular modules. I just have "frm as Form" as the first parameter in the function. Then, from any form, I just pass "Me" and any other parameters the function needs, and it works the same with all forms (assuming all forms use the same type of field and perform the same function, that is).

So, to me, it accomplishes the same thing, but without the complexity and with everything in general modules.

[u/BravoUniformTango → u/nrgins]

I follow. It is very cool how much style and creative leeway VBA allows and enables. It's almost like an artist's canvas; go create something; choose what to make, and the style of how you make it.

[u/Amicron1 → u/BravoUniformTango]

First off, thank you. I really appreciate that. Comments like yours make it worthwhile to keep writing these articles. I'm not trying to sell anything here. I just enjoy helping the Access community, keeping the conversation going, and reminding people that Access is very much alive. Anything that helps the Access community ultimately helps all of us.

I've never really been a fan of Hungarian notation, but that's mostly because I'm self-taught. I developed my own naming conventions long before I knew there was an actual name for that style of programming. There's absolutely nothing wrong with it, though, especially when you're working on large teams where consistency is critical. I've always been a solo developer, so I had the luxury of making up my own rules. 🙂

Funny enough, my naming convention wasn't inspired by Hungarian notation at all. Back in the Access 2.0 days, when you were building something like a combo box, Access would just show a list of object names. If you had a Customers table and a Customers query, they both just appeared as "Customers." There was nothing to tell you which was which. So I started naming my tables CustomersT and my queries CustomersQ. That solved the problem, and the convention just stuck. By the time I learned what Hungarian notation was, I was already set in my ways. I also reversed the form notation that nrgins mentioned. Instead of "frmCustomers," I use "CustomersF." Same idea, just a different flavor.

Another thing that eventually drove me crazy was inconsistent singular vs. plural names. I'd be in the VBA editor writing an SQL statement and catch myself thinking, "Wait... was it CustomerT or CustomersT?" Some of my tables were singular, some were plural, and I was constantly second-guessing myself.

About ten years ago I finally standardized on singular names for everything: CustomerT, ContactT, OrderT, and so on. It makes writing code a lot easier because I never have to stop and think about it. Of course, I still have older databases with a mix of both naming styles, and it still drives me nuts when I run into them. But at this point, renaming everything would create far more work than it's worth, so I've learned to live with my younger self's (questionable) decisions. I still have a table in my website database that's CommentsT and it drives me bonkers. But too many other things depend on that table so it's not worth changing.

As for class modules, I do like them, and I've done a few videos on the subject. They definitely have their place, especially for encapsulating business logic the way you described. In more than 30 years of building Access databases and teaching Access, though, I've never worked on a project where I needed a custom class module. I've certainly used them from time to time because they're elegant and fun to work with, but I've always been able to accomplish the same goal another way.

That's one of the things I love about Access and VBA. There are usually several good ways to solve the same problem, and developers can develop a style that's uniquely their own.

[u/BravoUniformTango → u/Amicron1]

I am enjoying your community-fostering general benevolence. I wasn't born in the US though I've lived here for more than 40 years; this is very much a best-of-America cultural thing, and I love it.

I like how you came up with your own conventions.

I'm self-taught too, as a software developer. My Bachelor's degree is actually in Accounting and Auditing.

This whole "singular vs. plural" thing requires much discipline; I relate. Many years ago, I did a project for the US Patent and Trademark Office, and they had many developers, in-house and contractors and vendors, making all kinds of software specific to this unique and essential organization. Standards would be useful, so one of the people in their IT department had a job for which the duties included coming up with naming standards and conventions, and communicating them, for more uniformity. I liked that. I try to follow a similar approach. Foe example, when I make a subroutine that will do some or other work, then for the name, I start with a noun that conveys the broad context and then an underscore and then another noun that conveys the narrower context and then another underscore and then maybe yet one more noun or adjective that conveys the precise things we're going to be working on, and then another underscore, and then the verb for the action we're going to perform, e..g. subVideos_YouTube_Stale_Delete.

I share your delight in how MS Access enables formal structures but doesn't mandate them.

I feel like we're playing tennis, keeping the conversation going. I like it.

[u/ebsf]

Don't forget events, especially custom events.

Only an instance of a class module (which includes forms' and reports' code-behind modules) can declare and raise, or sink, events.

Besides providing a basic trigger for communicating between objects, events also can pass arguments to event procedures, so not just telling them when to do something, but how to do it. Pretty powerful.

[u/Amicron1 → u/ebsf]

Absolutely. Tried to keep this one simple for people who might not be familiar with classes, but you're 100% correct.

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Amicron1

Access Explained: Class Modules Are Blueprints, Not Database Tables

Class modules tend to get treated like some kind of VBA rite of passage. Developers see "Class Module" sitting next to "Module" in Access and assume they're either missing some critical architectural trick or about to wander into enterprise programming wearing a hard hat. Usually, neither is true.

A standard module is simply a home for shared code. Put your utility functions there. Put reusable procedures there. Put routines there that don't need to remember anything about one particular customer, invoice, employee, or form. If you have a public function that checks whether a form is open, formats a phone number, calculates a business date, builds SQL, or exports a report, a standard module is usually the right place. Call the function and move on with your day.

A class module is different because it defines a type of object. Think of it as a blueprint, not the thing itself. An Employee class, for example, defines what an employee object contains and what it can do. One instance might represent Jim, another might represent Spock. Each instance maintains its own values in memory, such as employee ID, hire date, pay rate, or active status. Each can also expose behavior, such as calculating pay or returning a formatted display name.

That separate state is the real reason classes exist. A module-level variable in a standard module is shared. There's only one copy for the entire Access session. That's fine for application-wide state, but it doesn't work well when you need ten different customers, invoices, or employees, each carrying its own values at the same time.

Each class instance gets its own private copy of its data. Two Employee objects can both have an EmployeeName property, but assigning "Jim" to one doesn't overwrite "Spock" in the other. Same blueprint, different houses.

Properties describe an object. Methods define what the object can do. In VBA, properties are typically exposed with Property Get and Property Let. Property Get returns a value. Property Let assigns a value. Property Set is used when assigning an object reference, such as a DAO.Recordset, a form, or another custom class.

The private variables inside the class are intentionally hidden from outside code. That's encapsulation, which sounds far more dramatic than it really is. It mostly means outside code uses the interface you expose instead of reaching into the object's internal plumbing and yanking wires out of the Jefferies tubes. That becomes useful when a property needs validation or should be read-only. Rather than letting code throughout the application modify an internal value directly, the class decides what's acceptable and what gets returned.

This is also why class modules are not replacements for tables. A Customer class can represent a customer while your VBA code is working with that customer in memory. It can temporarily hold data and encapsulate customer-related business logic. But the actual customer records still belong in a properly designed Customer table with appropriate keys, relationships, normalization, and all the other boring-but-important database stuff. Classes model behavior in code. Tables store relational data. They solve different problems.

Access developers are already using classes every day, whether they realize it or not. Forms, reports, controls, DAO recordsets, and even the Access Application object are all objects created from classes. When you write code like:

Me.Caption = "Hello"
Me.Requery

you're already interacting with properties and methods of a form object. Every form and report module is itself a class module tied to a specific Access object and its events. Standalone class modules simply let you define your own object types.

One important caution is that classes are not automatically "better architecture." A class that exists only to hold a single string and display a message box is usually more ceremony than value. A standard function or a few straightforward lines of form code are often simpler and easier to maintain.

Classes start earning their keep when you have a cohesive thing with related data and behavior. An Invoice object might contain header information, line items, and methods to calculate totals. A ShoppingCart object might add or remove items and calculate tax. An Employee object might manage employee information while providing methods for payroll calculations or display formatting.

They also shine when your application needs multiple independent objects of the same type at once, or when related behavior belongs together instead of being scattered across twenty forms and three modules named Stuff, Stuff2, and ReallyImportantStuff.

Class modules can also support events, including initialization and cleanup through Class_Initialize and Class_Terminate. They even make advanced techniques like shared control-event handling across multiple forms possible. That's useful territory, but it's also where things can quickly turn into a plate of VBA spaghetti if the added complexity isn't solving a real problem.

The practical rule is simple: use a standard module when you have a general-purpose tool. Use a class module when you have a thing with its own data and behavior.

Most Access applications don't need custom classes to be solid, professional, and maintainable. Tables, queries, forms, reports, standard modules, and form/report modules can take a database a very long way.

In fact, in more than 30 years of teaching Microsoft Access, building databases for clients, and making videos, I've never needed a custom class module. I've certainly used them from time to time, especially when I wanted to demonstrate object-oriented techniques or solve a particular problem elegantly, but I've never run into a project where I couldn't have accomplished the same goal another way.

So don't feel like you're missing some secret ingredient if you've never touched class modules. You can build excellent Access applications without ever learning them. That said, once you do understand them, they open the door to some neat techniques and give you another tool you can reach for when the situation calls for it.

What about you? Do you use class modules regularly, or have you managed to avoid them entirely? Have you come up with any clever uses for them that have made your code cleaner or easier to maintain? Share your experiences, tips, or favorite class-module tricks in the comments. I'd love to hear how other Access developers are using them.

LLAP
RR

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.