Microsoft Access has a handy wizard for customizing the import of text files into local tables, as I wrote about here:
One major downside to this feature is that making minor changes to a saved specification is really awkward:
Unfortunately, there is no way to edit a saved spec through the Access interface. The only option is to start a new import process using the wizard as we demonstrated earlier.
One way to work around that limitation is to make direct changes to the local system tables where these import (and export) specifications are saved, as I covered here:
Of course, directly editing hidden system tables is a dicey proposition:
Editing these system tables directly is well outside the bounds of what Microsoft intends for you to do.
In other words, you're on your own if you screw something up.
With this in mind, I recommend using functions, subroutines, or–better yet–class modules to add a layer of safety and reliability to your interactions with these system tables.
Class modules, you say? How would that work?
Glad you asked!
On Wednesday, December 6, 2023, at 1 PM Eastern (10 AM Pacific; 6 PM London/UTC), I will be answering that very question in a presentation for the Access Europe User Group, hosted by Colin Riddington.
On the surface, the presentation is about import and export specifications. While that in itself is useful, we'll be using that example to learn about the benefits of class modules in VBA, an underused technique among the VBA development community.
This will also be a great warmup in advance of A.P.R. Pillai's January presentation where he will dive even deeper into the world of VBA class modules.
For the Zoom invite link and more information about the Access Europe User Group, check out the event page here. For your convenience, I've reproduced the full write-up for the event below. I hope to see you there!
Streamline Your Import/Export Spec Workflow with VBA Classes
Learn how to programmatically update import/export specifications in Access with a custom VBA class. In this presentation, Mike will demonstrate his clsImExSpecs class. This simplifies working with import and export specifications by writing and modifying simple VBA, rather than fighting with the unintuitive Access wizards.
- How Access works with specifications behind the scenes
- Why changing system tables directly can cause problems
- Encapsulation, organization, and reusability benefits of classes
- Using clsImExSpecs to document existing specifications in VBA
- Streamlining workflows by avoiding context switching between code and UI
- Demonstration updating a real-world import specification
By attending, you will gain practical tips for managing specification changes in code rather than through awkward and inconvenient Access wizards. You’ll also learn how VBA classes can be used to solve real-world problems, rather than the typical “Cat is a type of Animal” nonsense that you often find in online tutorials. Knowing when and how to effectively use class modules will take your VBA to the next level.
You should attend this presentation if you fall into one or both of the following categories:
- You are frustrated with the Import/Export wizards in Access and want a better way to manage import/export specifications.
- You keep hearing about how great class modules are, but you have no idea why or how you would use them.