Using VBA to Create a Class Based on a Table with Adolph Dupré

Tired of manually writing hundreds of lines of class module code every time you need to work with a table?

In this Access Europe presentation, Adolph Dupré demonstrates a powerful technique for automatically generating class modules based on existing tables. His solution creates all the necessary properties, methods, and boilerplate code to handle basic CRUD operations (Create, Read, Update, Delete) while maintaining clean separation between the UI and data layers.

Whether you're working with small tables or complex data structures with hundreds of fields, this approach can save hours of development time while reducing errors and improving maintainability.

Core Concepts

What is a Class Module?

• Similar to a User Defined Type (UDT) but with enhanced capabilities
• Allows encapsulation of data and related functions
• Supports data validation and business logic
• Can make properties read-only or write-only
• Enables cleanup code when object terminates

Benefits of Using Classes

• Business logic can be compiled and protected
• IntelliSense support for public properties
• Code is more modular and easier to understand
• Simplifies transfer between different backends
• Works well with object-oriented programming principles
• Supports Model-View-Controller (MVC) architecture

When to Consider Using Classes

Ideal Scenarios

• Working with arrays, dictionaries, or collections
• Unstable backend connections
• Unbound forms
• Multiple developers on a project
• Potential future backend migration
• Possible frontend language changes

Implementation Details

Auto-Generation Features

• Creates private variables for each field
• Generates Let/Get property procedures
• Implements standard CRUD operations
• Handles record navigation (Next/Previous)
• Includes basic error handling
• Provides debug/print capabilities

Key Components

• Table field analysis and mapping
• Code template generation
• Automatic naming conventions
• Property and method creation
• Basic validation framework

Practical Example

Demo Application Features

• Person table with basic demographic data
• Unbound form implementation
• Record navigation and editing
• Search functionality
• Automatic BMI calculation
• Connection management

Conclusion

Adolph's code generator dramatically simplifies the creation of class modules in Access, potentially saving hours of development time while promoting best practices in code organization. The solution is particularly valuable for larger tables or when working with multiple backend platforms.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

• Interact directly with presenters during Q&A sessions
• Network with other Access developers
• Share your own experiences and challenges
• Get immediate answers to your specific questions
• Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

• Base cover image generated by FLUX-schnell
• Initial draft generated by Claude-3.5-Sonnet