Streamlining SaveAsText / LoadFromText
SaveAsText and LoadFromText are indispensable for Access version control. They're also annoying to type repeatedly. Let's fix that.
The undocumented SaveAsText and LoadFromText methods are invaluable if you want to roll your own version control solution in Microsoft Access.
I use SaveAsText extensively in my expanded version of the decompose.vbs script. Conversely, the compose.vbs script relies heavily on the LoadFromText method to rebuild an Access application from source files.
In practice, I rarely use the compose.vbs script. If I need to roll back a change in one of my Access applications, it's usually limited to one or two individual objects: a form, report, query, or code module. In those cases, I simply run the LoadFromText method from the immediate window of my application.
Automating the process
Typing out that whole statement gets annoying after awhile, though. Surely there's a better way.
There is a better way. And don't call me Shirley.
Prerequisite: a consistent naming convention
The key to making this automation work is to use a consistent naming convention.
First, we need to use custom file extensions that indicate what sort of Access object we are dealing with. I use the following file extensions in keeping with the original decompose.vbs script:
You can use different extensions if you want, but each type of Access object needs a unique extension for this approach to work.
Second, we need to use the name of the Access object as the file name.
What's in a name?
In this case, everything we need to build the LoadFromText or SaveAsText expressions is contained in the file name.
The two methods each take three arguments:
- Type of the Access object
- The Access object's name
- The full path to the source (LoadFromText) or destination (SaveAsText) file name
So, if we start with the full path to the source or destination file name, we can infer the other two arguments:
Before calling the AutoHotkey shortcut, we first need to copy the full path of our source or destination file to the clipboard.
After that, the AutoHotkey code is straightforward:
- Extract the contents of the clipboard into a string variable
- Split the path, extracting the file name and extension
- Build the expression based on the file extension
- Send the expression as a string to the VBA IDE window
At the top of this code is a call to the AutoHotkey #IfWinActive directive. This directive restricts this shortcut key so that it is only active while the VBA IDE window is the active window on the desktop.
I use the #IfWinActive directive whenever I can because it reduces the amount that my AutoHotkey scripts conflict with shortcut keys in other Windows applications.
Saving QueryDef objects to text files using the SaveAsText method is...less than ideal. My expanded decompose.vbs script includes a function–AddLineBreaks()–that does some very simple formatting of QueryDef SQL strings to optimize them for version control and then saves them as a separate .sql file.
The .sql file is saved in addition to the .qry file. That's because LoadFromText only works with the .qry file's format (i.e., the SaveAsText acQuery version).
To match the format of the AddLineBreaks routine, the AHK SaveAsText shortcut code above calls an equivalent routine named ExportSqlToHg. That routine lives in one of my standard code modules, so it is available in every one of my applications. If you don't want or need this functionality, you can comment it out in the AHK code above.
Here's the code for the routine if you do want to include it: