Streamlining SaveAsText / LoadFromText

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.

LoadFromText acReport, "01_TaxCert", "M:\Repos\TaxColl2k\01_TaxCert.report"

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:

• Modules: .bas
• Forms: .form
• Reports: .report
• QueryDefs: .qry
• Macros: .mac

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:

1. Type of the Access object
2. The Access object's name
3. 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:

AutoHotkey algorithm

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:

1. Extract the contents of the clipboard into a string variable
2. Split the path, extracting the file name and extension
3. Build the expression based on the file extension
4. Send the expression as a string to the VBA IDE window

AutoHotkey Code

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.

;***  ;VBA IDE
#IfWinActive ahk_class wndclass_desked_gsk

; Ctl + Shift + S
;   SaveAsText from a copied path
^+s::  
    StringReplace FullPath, Clipboard, ", , All
    SplitPath FullPath, , , ObjType, ObjName
    If (ObjType="bas") {
        AccCmd = SaveAsText acModule, "%ObjName%", "%FullPath%"
    } Else If (ObjType="form") {
        AccCmd = SaveAsText acForm, "%ObjName%", "%FullPath%"
    } Else If (ObjType="report") {
        AccCmd = SaveAsText acReport, "%ObjName%", "%FullPath%"
    } Else If (ObjType="qry") {
        StringReplace SqlPath, FullPath, .qry, .sql
        SqlCmd = ExportSqlToHg "%ObjName%", "%SqlPath%"
        Send %SqlCmd%
        PasteText("`r`n")
        AccCmd = SaveAsText acQuery, "%ObjName%", "%FullPath%"
    }
    Send %AccCmd%
    Return

; Ctl + Shift + L
;  LoadFromText from a copied path
^+l::  
    StringReplace FullPath, Clipboard, ", , All
    SplitPath FullPath, , , ObjType, ObjName
    If (ObjType="bas") {
        AccCmd = LoadFromText acModule, "%ObjName%", "%FullPath%"
    } Else If (ObjType="form") {
        AccCmd = LoadFromText acForm, "%ObjName%", "%FullPath%"
    } Else If (ObjType="report") {
        AccCmd = LoadFromText acReport, "%ObjName%", "%FullPath%"
    } Else If (ObjType="qry") {
        AccCmd = LoadFromText acQuery, "%ObjName%", "%FullPath%"
    }
    Send %AccCmd%
    Return

Other Requirements

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:

'Use this routine to export query sql to source
'   - the string replacement code must be kept in sync with the Decompose.vbs function AddLineBreaks()
Public Sub ExportSqlToHg(QryName As String, FName As String)
    Dim s As String
    s = CurrentDb.QueryDefs(QryName).SQL
    s = Replace(s, " INNER JOIN ", vbCrLf & " INNER JOIN ")
    s = Replace(s, " LEFT JOIN ", vbCrLf & " LEFT JOIN ")
    s = Replace(s, " ON ", vbCrLf & " ON ")
    s = Replace(s, " AND ", vbCrLf & "  AND ")
    s = Replace(s, " OR ", vbCrLf & " OR ")
    s = Replace(s, ", ", "," & vbCrLf & " ")
    
    'https://nolongerset.com/text-files-read-write-append/
    FileWrite FName, s
End Function