Testing the new command bar export / build in Dev Branch.

[ronmcca]

I tested the new command bar features of export and build from source.

On the 1st test with simple right click menu for forms and reports, it work OK except the resulting db crashed on compact / repair. It work ok on the 2nd attempt compiling before compact/repair.

The 2nd test on a FE with multiple command bars failed to import the command bars, logging an error on the 1st menu. I am uploading a clean db with only those command bars and the exported repository.

MenuTest.zip
MenuTest.accdb.src.zip

[bclothier]

So the issue appears to be related to the fact that we have several controls that are declared to be "built-in" but that's not entirely true.

This returns True:
?Application.CommandBars("Engineering").Controls(1).BuiltIn

But it's a button that opens an Access form. The OnAction is empty but Parameter is not and refers a name of Access form to be opened by this button. It also has Id that doesn't map to any existing control (e.g. Application.CommandBars.FindControl(Id:=1837) returns Nothing).

I've attempted to add a "built-in" control but I get errors if I try to specify the Id (whether 1837 or some other nonexisting numbers and my addition result in BuiltIn being false, so we need to understand how this menu got built with BuiltIn set to true when it's clearly not the case.

[bclothier]

Addendum - it appears that all built-in controls will not have either Parameter nor OnAction set. Those "built-in" controls have duplicate Id and have Parameter set, so it's more like custom than a built-in. So we could expand the test to check whether a control has BuiltIn set to true and ALSO that both Parameter and OnAction are both empty. For those that don't have empty OnAction or Parameter, we'd treat them like custom control. One downside is that the round-trip will no longer yield an equivalent build since the new build will have those controls' BuiltIn set to false which would be correct but it's still a difference.

[bclothier]

Ok, I was able to reproduce the BuiltIn = true. In Office 2003, if one adds a new command bar via Customize..., one can choose from an existing form. In this case, it generated a Id 1837 but it had BuiltIn set to True while the Parameter was populated with the name of the form.

The Id 1837 does not exist as a built-in commandbar and adding it with that Id will yield an error.

I suspect there will be other Ids like this (e.g. All Reports and other similar options here could be used).

I'm not sure if we can execute the exact same command that is performed interactively via the Customize in Office in order to replicate the built-in control with the Id 1837. At least, we'd have to ignore the BuiltIn property and generate a new custom control that does the equivalent thing which is still a difference.

[ronmcca]

I also don't know how this toolbar was built.
The program was done by some no longer with the company using Access 2 or 97. He wasn't a programmer and built the application using macros. I would have to assume the toolbars were built using the standard Access tools. When I was handed the application all the macros were converted to VBA and I have been cleaning up and enhancing the program but never changed the tool bars.

Do you think editing the output file and re-trying the build would solve the problem?

[bclothier]

I do suspect that if the file was edited to set BuiltIn to false, it might work but would have to test. However, we should not have to rely on hand-tweaking like this; the tool should do the right thing. Thanks for the background - it might be the macro that's the key. I'll look at it.

[bclothier]

The test database provided by @ronmcca was very helpful! Thank you!

With PR #588, we will be able to handle most of the command bars, with 2 exceptions. There are 2 controls with Id of 937, and 2 other controls with Id of 32768. Those will not import correctly currently.

With the Id 32768, this is a strange one. Both are a popup menu that is "built-in". They do have built-in controls within the popup but I'm not convinced it's not customized by someone (as opposed to just using an existing popup menu as-is). I kind of want to just ignore the Id/built-in and treat it like a normal popup. The numeric value 32768 seems not so random-y but I can't see how that gets built and how it would be different from a normal pop-up menu.

With the Id 937, this invokes Access help. There are other Ids that does the equivalent (I believe 984 will do it). I can only assume it was built with a command bar control that now no longer exists and its functionality re-mapped somehow. Access 2003 does not have this particular Id.

So, there are three things I have observed:

1. If the built-in Id doesn't exist, it will probably fail. So far, that has been the case for all the custom built-in controls. In the cases of the controls that opens Access object, those Id don't exist as part of Access' default commandbars set but they definitely get used when the user interactively customizes the menu.

2. However with Id like 937, it does not appear to be a part of Access specific actions (e.g. opening a particular form). It opens an Access help pane and there are other Ids that does the same thing. Based on this, I have to think the command was deprecated and removed from the application's default command bars. In order to be able to build those commands exactly as-is, we would have to somehow add them to the template command bar, which opens up a set of problems.

3. Maddeningly, command bars are attached to the application object, yet a database can have a specific set of command bars available. I am fairly sure it is stored in one of the internal tables. But to make it even more insane, they also stuff it in the registry. I was horrified to find that for every custom menus made/use, there's a registry entry for it. For Office 365, that can be found under HKCU\SOFTWARE\Microsoft\Office\16.0\Access\Settings\CommandBars. I feel like I'm handling Schrödinger's cat here; it's either an application thing or it's a database thing. That is especially true when we have multiple database loaded; the Application.CommandBars will end up with an union of all custom commandbars from all loaded databases but we know that it doesn't mean one database suddenly now has other database's .

This leads us to the following options:

• When the PR Improve handling around commandbars #588 is merged, the new status quo will be to raise an error during the import notifying the user that the command bar cannot be imported as-is. The user can then tweak it and use a commandbar that is still available.
• We provide the user the option of building a binary file just to save the command bars and add it to the source code repository. That would be more generic and would work with any funky command bar, not a subset of not-too-funky command bars. I'm not very thrilled with that option because the whole point of source code control is to have it in human-readable text that can be reviewed and dissected.
• We look for a different way to hack the black voodoo magic that is being done with the import wizard's support for importing the command bars.
• ???

[joyfullservice]

@bclothier - I would imagine that you have already seen this, but there is a WizHook function to copy command bars to or from a specified database... I don't know if this would be helpful in fleshing this out... Colin Riddington has a brief note on this function.

[bclothier]

Thanks for pointing this out. I looked at it, but it's pretty simple; it only takes a file as a parameter and operates on the WizHook, which is part of Application. I assume it can only run on the current database. Colin mentions it can be used to export but doesn't explain how one would export it. I assume probably by opening the target database, and then running the method against the source database, which is still technically an import.

But the bigger concern here is that I can't specify if it'll be the current database or the add-in database. I need to be able to catch Schrödinger's cat here. This is the method I'm currently using:

Private Function ImportCommandBarTemplate() As Boolean
    Dim strXML As String

    strXML = _
        "<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbNewLine & _
        "<ImportExportSpecification Path = """ & GetAddInFileName & """ xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & vbNewLine & _
        "  <ImportAccess ImportExportSpecs=""false"" MenusAndToolbars=""true"" Relationships=""false"" NavPane=""false"" StructureAndData=""true"" QueriesAsTables=""false"" Resources=""false"" />" & vbNewLine & _
        "</ImportExportSpecification>"

    Const strTempName As String = "TempSpec"

    On Error Resume Next
    CurrentProject.ImportExportSpecifications(strTempName).Delete
    On Error GoTo 0

    With CurrentProject.ImportExportSpecifications.Add(strTempName, strXML)
        .Execute
        .Delete
    End With

    ' Verify we have the command bar now
    On Error Resume Next
    ImportCommandBarTemplate = Not (Application.CommandBars(strTemplateCommandBarName) Is Nothing)
    If Err.Number Then
        Log.Error eelCritical, "Template command bar was not imported!"
        ImportCommandBarTemplate = False
    End If
    On Error GoTo 0
End Function

Note that I can specify CurrentProject, and could use CodeProject instead, so there's more control with this method and it works.

I originally planned to run this only for the add-in database but given the issue identified in the #588, I'm wondering if it needs to be generalized for the build process. If we were to use the copy methods generally rather than special casing it for a subset, we might run into less problems with funky commandbars such as the weird built-in popup menus and missing Ids that still works. We would use the generated JSON files & exported pictures as a way to document the structure of the commandbar but use the binary contents as the copy source, then edit the commandbars to match the JSON file. Again, not 100% thrilled with that but the alternative is far more special cases or requiring the users to fix up their funky commandbars to be less funky which seems less user-friendly. If this is handled in the build process for any database, that would also work for the add-in development as well.

Thoughts?

[joyfullservice]

Please forgive my ignorance if I am missing something important here, but as I step back and consider this from a broader perspective, there is another angle that I am considering for the command bars. Getting the source files to match the original database is good, but what is even more important, (IMHO) is having a consistently repeatable build cycle. The very first time a database is built from source using this add-in, there are a few non-essential changes. But subsequent builds are pretty consistent going forward from there. I am okay with the idea of a one-time change if it makes the process simpler and more robust going forward.

Reading through all the dialog on the command bars, it seems that you have three categories of controls. You have the custom ones, the (normal) built-in ones, and the ones that think they are built-in, even though they are not. It is that last category that creates the biggest challenge because you cannot recreate these controls using straight VBA automation. Copying the controls back and forth between databases works, but something has to manage a growing collection of weird exceptions most likely inherited from previous versions of Access or other mysterious sources. We could continue down the path of trying to support them in all their weirdness, but I am wondering if it would be better to just treat these as custom controls.

If we treat these as custom controls, then we simply have to check the built-in flag, if it says it is built-in, then verify this by attempting to access a control with this ID. If we can't confirm that this is a valid built-in control, it would be treated as a custom control. This would "repair" those invalid references and provide a more robust command bar going forward, especially if Microsoft Access ever changes (adds or removes) built-in controls.

Taking this approach, we don't have to worry about the chicken and the egg scenario, since we aren't trying to collect the whole set to deliver them through the add-in binary. Menus would be built purely through VBA and could be built on any version of Access. (Copy from built-in where possible, and add as custom where the built-in controls are not available.) The built-in flag would have a one-time change for those special ones, and then they would be consistently treated as custom controls going forward.

Again, I might be missing something here, and this also hinges on the assumption that we can simply treat these as custom controls from an export and build standpoint, but I wanted to offer this as a suggestion. Neither approach is perfectly ideal, but this seem to give the advantages of a simpler code base, and repairs/upgrades the command bars to a more robust state going forward.

[bclothier]

Unfortunately, this isn't possible. The BuiltIn property is read-only and is governed by how we execute the Add method (e.g. if we specify a Id that's not 1, then it becomes a "built-in" control, otherwise it's a custom control).

If we then ignore the Id and create a custom control, then we'd be now responsible for defining what it should do. In other words, we'd have to figure out what the built-in control was doing and replicate the equivalent action in OnAction property which is not populated when we use a built-in control. This also has the unfortunate possibility that the custom control won't behave exactly the same way the original "custom built-in" control does. Thus, creating a custom control in its place might end up being non-functional because we don't know what to put in OnAction property to do the same action.

I was OK with building for Access' custom built-in since those were "known quantity" since that was something we can do easily via the old Customize menu action. But with the other buttons I mentioned, those do not indicate anything that would be Access-specific, and I am not even sure how they managed to achieve this via the Customize button. Using the copy method will work for those buttons but that does entail working with binary source somewhere which is not great.

So logically, we have two choices:

• We proceed with the limited support for Access "custom built-in" controls and provide documentation that if they can't build the commandbars because it's a custom built-in (and not one of Access') to update their commandbars to use proper custom controls for better source code compatibility.
• We use the binary file to provide the source of the copy and document the structure of the binary file in a JSON file. This allows them to use commandbars without modifying and adding it to the source but with the caveat that if they make any future changes to the commandbars, they'd need to update the binary file, which makes the commit history less clean.

With the example of "custom built-in" popup control and help button, I'm inclined to say that they need to be revised as per the first option because there is nothing "built-in" about those two controls. But if others come and report more problems with their "custom built-in" command bars not importing/exporting, then the 2nd option would look better in terms of user-friendliness.

[ronmcca]

When the command bar option is released, I would like to see it as optional like exporting and importing table data. That way if I have a problem command bar, It can be imported from a template using the after build function. The template could be stored in the repository to keep the source complete.

[bclothier]

Actually that could be a good compromise. We could expose the import method and make it more generic so that it can be used in the AfterBuild method, and provide instructions for the users in the similar situations where they do not want to modify existing command bars. The only one thing I would not want to do is disable the export of the command bars because even though you are ultimately using the template to import, having it exported would provide you with a way to document when the command bars are modified, if ever. We would just skip the import of the command bars in this case.

[mwolfe02]

I like Ben's compromise. There's a ton of value in using "one-way" version control to document changes to a database even if you never build from source. In fact, that's mainly how I've used version control with Access for the past 15 years.

[joyfullservice]

Yes, I am liking the approach of keeping things simple with the generic export/import with standard built-in commands and custom commands, and let users extend this functionality if needed to support unusual cases. I can also see the value of providing a low-visibility option to skip the export or import of specific component categories. (Configured in vcs-options.json but not exposed in the GUI.) This gives ones like @ronmcca the ability to replace the default build with a custom process for that component type.

[ronmcca]

I installed the new VCS 4.1.0 release and found a a bug in the the cbDbCommandBar.SaveImages function. One of my right click menus had file wild card characters "???" in the name which caused an error in SavePicture. The attached code allowed the export to complete.

clsDbCommandBar.txt

I am changing my command bars to remove those characters.

[joyfullservice]

I just added a commit to ensure that the file names are converted to legal Windows file names. In this case it will map the question mark characters to HTML entity codes, and back to question marks when the item is restored. 2c40068

[ronmcca]

These are the lines that created the problem controls

    ' FilterBeforeSelection
    .Controls.Add Type:=paiMsoControlType.paimsoControlButton, _
                    ID:=AccessControlsEnum.FilterBeforeSelection

    ' FilterAfterSelection
    .Controls.Add Type:=paiMsoControlType.paimsoControlButton, _
                    ID:=AccessControlsEnum.FilterAfterSelection

Private Enum paiMsoControlType
paimsoControlButton = 1
End Enum

Private Enum AccessControlsEnum
FilterBeforeSelection = 10092 ' button
FilterAfterSelection = 10093 ' button
End Enum