Install Add-In as compiled version (accde)

[josef-poetzl]

I like to install add-ins as accde so that the built-in error handling runs stably, since I usually develop with option "Break on All Errors".

This feature is easy to install:

Public Function InstallVCSAddin(Optional ByVal CreateCompiledVersion As Boolean = False) As Boolean
...

    If CreateCompiledVersion Then
        CreateAccde strSource, strDest
    Else
        FSO.CopyFile strSource, strDest, True
    End If
    
....

Private Sub CreateAccde(ByVal SourceFilePath As String, ByVal DestFilePath As String)
 
    Dim FileToCompile As String
    Dim AccessApp As Access.Application
 
    FileToCompile = DestFilePath & ".accdb"
   
    FSO.CopyFile SourceFilePath, FileToCompile, True
   
    Set AccessApp = New Access.Application
    AccessApp.Visible = True
    'create accde
    AccessApp.SysCmd 603, (FileToCompile), (DestFilePath)

    FSO.DeleteFile FileToCompile, True

End Sub

see: https://github.com/josef-poetzl/msaccess-vcs-addin/tree/Create-compiled-add-in-file
Note: In the form frmVCSInstall I added a checkbox for the selection if an accde should be created.

[joyfullservice]

Interesting... Personally, I prefer the ability to debug and step through code in the add-in project. You are certainly welcome to run the add-in as an accde, but I believe the greater number of users prefer the accda format. (Others can feel free to chime in here and we can see what the response is like from the developer community.)

[mwolfe02]

I have to admit, I find it jarring when I switch to the code editor and the version control addin project is selected instead of the project I'm working on. That might be because I've never worked with an .accda before. I like the idea of being able to jump into the add-in code if there was a problem, but 99% of the time I would prefer if it stayed out of the way entirely. I'm still in the experimentation phase with the add-in, as I'm continuing to use my in-house version control script in production. That said, I've already made the decision to switch to the add-in, so I will likely be working with it much more in the near future.

I may adapt to having it available in VBA as I work with it more...or I may come back to Josef's solution and compile it to an accde to keep it out of sight and out of mind (assuming that compiling to an accde would accomplish that).

[hecon5]

In our environment, we have had to release compiled versions because any error causes Access to crash out entirely, and we also don't really want our users to be able to change code while running the installed files.

For green devs especially, when there's an issue with the Addin, and they're sent to the addin code (and not our application), they often haven't realized they're even looking at the Addin, and then send me upset messages when it turns out their code all got wiped when they changed it and closed.

This doesn't happen often, and for the most part, they all now know to check if they're troubleshooting our code or the addin.

Where I think it'd be REALLY nice is when we're working on the addin itself; because I cannot count the number of times I've been happily working and saving only to close and realized I'd been working on the installed addin, not the one I meant to work on, and then had to get up and get some coffee or my computer'd be used as a frisbee golf discuss into the sun.

Anyway, I am a huge proponent of open-source, but I do think it'd be nice to install as a compiled file, but provide all the code/regular version.

[josef-poetzl]

Since the add-in function is now called with Application.Run using the full name, a compiled add-in also works without problems.
=> I created the pull request #594.
If it is accepted, please note that I exported the form with Access 2016/365. I have cleaned up the changes a bit, but there may still be lines that only exist in Access 16.0 but not in 14.0.

[joyfullservice]

Just adding a further note here for future reference... It appears that there are some limitations that affect add-ins compiled as *.accde.

• You cannot use the c:\full\path\VBProject.SubName syntax to call a sub in the current project if the current project (working database) is a *.accdb. (It works fine with *.accda and *.accde. You have to use a fallback syntax of [VBProject].SubName instead. I have updated this add-in to support the fallback syntax if the add-in is run as an mde.
• Some commands, such as acCmdCompileAndSaveAllModules will not be able to run if they are initiated from a compiled add-in. This means that if you use this command (or any other affected command) in a hook such as RunSubAfterBuild, it will not be able to complete.
• Initializing forms will not run correctly when building the add-in from a compiled version of the add-in. I have bypassed this in the code for now if the installed add-in is a compiled version.

Due to these limitations, I would generally recommend installing the add-in as a non-compiled version, unless there is a specific reason or requirement to utilize the compiled version. That being said, it does support the compiled option for now, but we may review that in the future if we encounter a lot of issues affecting end users.

Update

@josef-poetzl actually helped me work through the last two issues, so this may not be as big of a problem after all. 😄

[josef-poetzl]

If execution from the compiled add-in causes problems, an attempt is made to change something in the add-in.
You do not receive an error in the non-compiled add-in, but it is still executed in the wrong place.
(See #644).

Trick: activate the appropriate VBProject.

ActivateCurrentVbProject
Application.RunCommand acCmdCompileAndSaveAllModules

Private Sub ActivateCurrentProject()

    Dim Activated As Boolean
    Dim vbc As VBComponent

    If CurrentVBProject Is VBE.ActiveVBProject Then
    If Not VBE.ActiveCodePane Is Nothing Then
    If VBE.ActiveCodePane.Collection.Count > 0 Then
        'nothing to do
        Exit Sub
    End If
    End If
    End If
    
    If CurrentVBProject.VBComponents.Count > 0 Then
        'first use only code modules, otherwise forms/reports will be opened in the draft
        For Each vbc In CurrentVBProject.VBComponents
            If vbc.Type <> vbext_ct_Document Then
                vbc.Activate
                Activated = True
                vbc.CodeModule.CodePane.Window.Close
                Exit For
            End If
        Next
        'if there are only vbext_ct_Document, use the first one
        If Not Activated Then
            With CurrentVBProject.VBComponents(1)
                .Activate
                .CodeModule.CodePane.Window.Close
            End With
        End If
    End If

End Sub

/edit: upd. ActivateCurrentProject

[joyfullservice]

You are exactly right!! After setting the current VBProject, my AfterBuild hook works just fine in the testing database. Nice catch! 🚀

The VBA help documentation states that VBE.ActiveVBProject is read only, but in practice it seems to work fine to simply use this:

    Set VBE.ActiveVBProject = CurrentVBProject

[josef-poetzl]

You cannot use the c:\full\path\VBProject.SubName syntax to call a sub in the current project if the current project (working database) is a *.accdb. (It works fine with *.accda and *.accde. You have to use a fallback syntax of [VBProject].SubName instead.#

Why would you even want to start a procedure in the current application using the full path?
The current application will usually be an accdb.
The exception is if the main application has the same project name as the add-in.

if Extension = "accdb" then 
    use "[ProjectName].Procedure"
else 
   ...

or

if ProjectName = "MSAccessVCS" then 
      full path
else 
     "[ProjectName].Procedure"

[joyfullservice]

My reasoning was that if you use the full path, you can be 100% confident that it is running in the correct database. However, for most actual use cases outside of the add-in itself, you are right that most files would use the *.accdb or *.mdb file extensions. This means that the majority of calls would fail the first call with the full path, then use the project name syntax.

Generally I like to handle errors as the exception rather than the normal flow, but in this case it seems like it may be difficult to determine whether the file supports the path syntax unless it is purely by the extension. (Which may well be the case.) I will think on it a bit more... 🤔