Add ribbon controls

[A9G-Data-Droid]

I would prefer a ribbon control over the current add-in menu.

I have a ribbon branch I am working on. It works when looking at the version control add-in itself, loads automatically. I haven't figured out how to get it to load when you open other projects.

I believe to get this to work we will need to have a proper COM add-in that functions as a wrapper for the accda project. That is the only way I have found to load a ribbon at the application layer.

BRANCH: https://github.com/A9G-Data-Droid/msaccess-vcs-integration/tree/VCSRibbon

EXAMPLE:

[joyfullservice]

Thanks for the feedback! I could see how a ribbon would give greater visibility to the tool, and the ability to add icons is definitely a plus.

Just a clarification on the install... If an admin installs it for you (on their profile) it will install the registry keys under HKLM, but the add-in in their %AppData% folder. If you subsequently run the installer under your non-admin account, it should see that the registry keys already exist, and just add the add-in to your own AppData folder without requiring elevation. It should then be available to you through the add-in menu. Upgrades would not require the admin installer (unless the registry keys changed) since it is only updating the local file in your AppData folder.

The above is how it was intended to work, but if it's not working for non-admin users or not upgrading without elevation, we should open an issue for that. 😄

Regarding the ribbon, I briefly looked at how to get the ribbon available at the application level, and I think you are probably correct that this would require a COM add-in. (At least as a wrapper.) If you or someone would like to put something together for that, we can certainly test it out and get further feedback.

[A9G-Data-Droid]

I had found some code about making COM addins for Access but I couldn't get my test to load. If anyone knows how to make an Access addin using C# then the code to load the ribbon is quite simple.

[hecon5]

See my fork (which is based on hangie's fork of the head), it has a ribbon UI you can use; I'm also working on an API function that could expose these functions.

Depending on whether you install the addin via admin or to your application, you may need to use different tools. For me, I'm unable to install the addin at the application level, and so I have to use the API. I like the ribbon; it makes life real easy for 90% of my needed work. It's easy without a COM addin, and any user can add it.

The only issue that may need to be addressed is "how" you installed it (it would be nice to have a single ribbon that figures out if it's loaded via application or database level) to consistently operate.

I've pasted an excerpt of the UI ribbon xml here for reference; the icons/text/calling functions will need to be updated to the API when I'm done.

<mso:cmd app="Access" dt="0" />
<mso:customUI
    xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
  <mso:ribbon>
    <mso:qat/>
    <mso:tabs>
      <mso:tab id="mso_c2.2E5FF7F0" label="VCS">
    <mso:group id="mso_c3.2E5FF7F0" label="All" autoScale="true">
      <mso:button id="VCS_Import" label="Import Source" screentip="Import all tables, data, modules, etc. from source" size="large" imageMso="ImportSavedImports" tag="vcs.import" onAction="VCS_ImportAllSource" visible="true"/>
      <mso:button id="VCS_Export" label="Export Source" screentip="Export all tables, data, modules, etc. to source" size="large" imageMso="SaveAll" tag="vcs.export" onAction="VCS_ExportAllSource" visible="true"/>
      <mso:separator id="separator" />
      <mso:button id="VCS_Load" label="Load VCS Source" screentip="Load the VCS source files" size="large" imageMso="SourceControlCheckIn" tag="vcs.load" onAction="loadVCS" visible="true"/>
    </mso:group>
    </mso:tabs>
  </mso:ribbon>
</mso:customUI>

[hecon5]

Note also that the ribbon (for me) is fairly easy to add to my instance; Access will sometimes forget what happened, and lose it, but re-adding it is pretty straightforward. I wrote some instructions for how to do it manually, but was unsuccessful in getting VBA to do it for me due to development environment limitations.

[A9G-Data-Droid]

Thanks @hecon5, my test branch already includes the ribbon XML. The problem I was facing is that the ribbon doesn't appear when loading any other project. It only appears when you load the VCS project directly which you would only do when working on the VCS add-in itself.

Have you tried to add a ribbon to the latest version of this add-in? If you can get it to load automatically when opening arbitrary projects then we can continue implementing this feature.

[hecon5]

I added it via File>Options>Customize Ribbon>Customizations>Import/Export and then Import Customization file.

This works a treat for me. It does sometimes break and I have to re-add it (especially if I used it on a database that doesn't have any VCS modules inside (mine was based off Hangie's), but it could be adapted to this VCS.

[A9G-Data-Droid]

@hecon5 Can you find a way to do that programmatically? So it can be installed this way? I have code to load a custom UI programmatically that I was testing but it only affects CurrentDatabase. This is why the ribbon appears when you open the addin file but not when you open any other project.

You can see my module here: https://github.com/A9G-Data-Droid/msaccess-vcs-integration/blob/VCSRibbon/Version%20Control.accda.src/modules/modRibbon.bas

and the corresponding XML: https://github.com/A9G-Data-Droid/msaccess-vcs-integration/blob/VCSRibbon/VCSRibbon.xml

As you can see, the ribbon XML cites methods in the modRibbon that then call the underlying command in the add-in.

[hecon5]

I've not, or at least, not spent any serious time doing so.

A while back, I halfheartedly tried to implement something like https://stackoverflow.com/questions/35926784/access-load-and-apply-custom-ribbon-via-vba

Since it's the same code effectively as yours, it only worked on the current DB; also that required users to manually go to options anyway, and since the ribbon stays there (mostly) when I added it manually, I thought the amount of effort (for me) was not worth it to pursue automating that bit; for the fork I've been using, I just have the devs load the ribbon manually while they set up the VCS, and I've had few issues. If it does get corrupted/removed (usually due to trying to load VCS when it doesn't exist), adding it back in only takes a few moments.

[hecon5]

All that to say: no, I don't know how to get it to load to the application (I have other issues with loading to the application level due to my development environment, too) via VBA; I do think if we had a ribbon UI to load, and the "receiver" module you built. it would be massively nice to have.

[A9G-Data-Droid]

Ok, so the Ribbon file needs to be included in the release with instructions for how to add it. Not ideal but not terrible. I will test this when I get a chance. I have been on C# projects lately and I need to get back to updating my Access DB. I think the codebase has change alot since my fork was relevant so I need to make sure the plumbing of modRibbon goes to sane methods.

Even if the add-in menu had an "Add Ribbon" button, that would be cool...

[hecon5]

Actually, I've noticed, that once you load the addon, it tends to stay loaded until your instance is reset; this could allow an "on open" option to fire and setup your ribbon for that instance. This would be a sort of "in-between" option, since loading to the file is the best option. (As in loaded, stays loaded in the IDE)

[A9G-Data-Droid]

@hecon5 , so I made the ribbon branch again based on the latest version of VCS. It still doesn't work in other projects. Give it try:

https://github.com/A9G-Data-Droid/msaccess-vcs-integration/tree/AddRibbon

This new version leverages existing functions in the main VCS so no additional module is required. You simply import the XML file found here:

https://github.com/A9G-Data-Droid/msaccess-vcs-integration/blob/AddRibbon/VCSRibbon.exportedUI

When I tested it I found that the ribbon is visible across different projects but the buttons don't work. The error clearly states that it is looking for the methods in the currently open database and can't find them. I'm not sure how to make the ribbon work with the add-in. Please try it out and see what you can do. I would like to get this working.

[hecon5]

Hmm: I've not been able to touch this yet (dealing with SHA at the moment), but this may be a case where we would need to add the addin as a reference.

Do the ribbon buttons work after you open the addin via Database Tools>Add-ins>Version control?

[A9G-Data-Droid]

No, I tried that because I considered that maybe the code had to be loaded first. Still doesn't work. Same error.

[hecon5]

Dang; hail mary version: does referencing in the addin and then calling "MSAccessVCS.whatever" work?

[A9G-Data-Droid]

That's a good idea but it results in a different error:

I have tried many things now and the only thing that works is when you manually add the reference in the VBE.

I found code to automatically add it but you can't add a reference to a project that is already loaded. So it's a catch-22, the add-in can't add a reference to itself.

Here is the code to automatically add it, that doesn't work:

Private Function GetCurrentProject() As VBProject
    Dim objVBProject As Object
    For Each objVBProject In Application.VBE.VBProjects
        If Dir(objVBProject.FileName) = Dir(CurrentDb.Name) Then
            Set GetCurrentProject = objVBProject
            Exit For
        End If
    Next objVBProject 
End Function

Public Sub AddVCSReference()
    With GetCurrentProject.References
        .AddFromFile GetAddinFileName
    End With
End Sub

Conclusion: You have to manually load the ribbon XML, then you have to manually add a reference to each project. Seems like more trouble than the existing add-in menu solution.

[hecon5]

Wellllll. Outside of integrating it from each of the projects (like the legacy version, which also defeats the purpose of having an addon), I think you're right. Drat daggone. If I learn of a way to do it, I'll update.

[joyfullservice]

I still think it may be possible to implement a ribbon tab in the main application ribbon using a VSTO COM add-in as a wrapper to the Access add-in. See: https://stackoverflow.com/questions/2517843/programmatic-access-to-ribbon-controls-in-vsto

[A9G-Data-Droid]

@joyfullservice This is the way. We would need an installer that installs the Access Add-in AND the VSTO add-in. I started down this path and couldn't get the VSTO add-in working at all. It has been done before so we know it's possible.

[hecon5]

The fork I originated off @hangie turned his version into a VSTO based on C#; though I think they actually rolled the whole thing into a VSTO vice a wrapper.

[A9G-Data-Droid]

Do you have the C# project?

[hecon5]

I do, but because it's a private project, and I didn't write it, I'd rather @hangie verify they're willing to open up their ported work.

[hangie]

Hi @hecon5. I had always intended to open up the port to VSTO for the community to share and contribute to, I just hadn't completed the work to make sure it recognised the foundations it sprang from and didn't violate anything in doing so. Happy to have a conversation about how to complete this bit of work and open it up for contribution and community access.

[hecon5]

@hangie, What all would be involved in that? I do not have much familiarity with C# outside your project and research on VBA bugs, but would be happy to contribute where/how I can. Feel free to PM me if you'd rather take this offline.

[joyfullservice]

This is great news! I think between all of us, we have all the pieces we need to move forward with the VSTO wrapper for this add-in. 👍 😄

By keeping the majority of this codebase in VBA as an Access add-in, I see several long-term advantages:

• It functions just like the built-in wizards in Microsoft Access.
• If bugs or errors are encountered during an export or build operation, we can debug and step through the code live within the VBA IDE, even making and testing code changes within the add-in.
• The majority of the developers using this tool are already very familiar with VBA and the Microsoft Access Object Model, so they are in a familiar environment to read and understand the code used in the project.
• Thanks to the build from source functionality, enhancements can be added, tested, and deployed without needing additional development platforms or .Net language experience. This greatly simplifies the development cycle which translates to getting more done with less time (and cost).
• Since the target audience for this add-in predominantly consists of Access VBA developers, we have the potential for greater community involvement in the ongoing development and maintenance of the project, which benefits everyone.
• Based on my personal experience, I believe we will probably have much better performance in our interactions with the Microsoft Access object model using native in-process VBA vs. out-of-process COM automation.

Here is what I would suggest as we move forward. Let's add the VSTO wrapper project in a new folder in this project. This can be done in C#, VB.Net or whatever would be easiest for those working on it. Perhaps if @A9G-Data-Droid could upload his initial attempt, then @hecon5 and/or @hangie could help put the missing pieces together, we can get a working VSTO wrapper that would load the ribbon tab and launch the Access add-in using the technique I discovered that loads it at a persistent application-level, just like the built-in wizards.

If we can maintain the ribbon XML outside the compiled wrapper, that will allow us to customize and adjust the ribbon without needing to compile and reinstall the VSTO add-in each time. This will also provide a future path for developers to maintain their own customized versions of the ribbon while making calls to the add-in API.

With the VSTO add-in on the horizon, I think we are maturing to the point where we want to go ahead and create our own dedicated MSAccessVCS folder in AppData and install the COM add-in, the Access add-in, settings file, ribbon xml, etc... in that folder. This will also reduce the potential security risks of adding a trusted location to the Microsoft add-ins folder, which may contain other add-ins from unknown sources outside the scope of this project.

For an installer, I would be leaning towards NSIS. I have used it for several projects in the past and it seems to be a good balance of simplicity, automation, and widespread usage. This installer would handle the initial full installation of the add-in, but we would still retain the functionality in modAddIn to easily update the installed Version Control.accda file after building from source or downloading an update. We probably also want to leave the current installation and usage available for those that don't want to install the COM add-in, or are limited by corporate policy or security settings from using the full installer.

I will go ahead and create the VSTO Wrapper and Installer folders in the dev branch so we can begin some collaborative development on the COM add-in wrapper.

Feel free to share any additional perspectives or ideas as we move forward! 😄 I am excited to see the interest in bringing the ribbon integration to reality!

[A9G-Data-Droid]

I don't have any VSTO code to upload. I was given permission to view a private Git project that is called "AccessAddinManager" that was written by @bclothier from the RubberDuck group. They said that their project was not ready to be shared, and indeed I was unable to get it to work. So don't wait on me to push any VSTO code. If @hangie has something that loads in to Access then I can help with that project but this is the step I couldn't get to work.

[hangie]

@joyfullservice, the version I have written is a full C# port, not a VSTO wrapper around the VBA code.

[A9G-Data-Droid]

Wow! That's cool. As long as it has the ability to install in to Access the rest could be gutted. I would personally love to have a C# version of this plugin but I know @joyfullservice really wants to keep it all VBA.

Can you share just the Access Plugin part? Just a plugin that installs as ribbon with boilerplate events? Then we could wire it up to the rest of the VBA addin we have.

[joyfullservice]

@joyfullservice, the version I have written is a full C# port, not a VSTO wrapper around the VBA code.

@hangie - Yes, I did recognize that part. The C# version sounds like a neat direction to take this concept, but for purposes of this particular branch, I am personally more focused on the VBA side for the integrated interaction with the Access object model. Perhaps some of the ideas and techniques in this project may even help with your ported version as well...

As @A9G-Data-Droid mentioned above, if there is any way you might be willing to share a basic VSTO add-in that connects with Access and loads a ribbon XML file on startup, we can take and run with it from there. I even tried converting a simple Word VSTO add-in to Access on my own, following this article and some others, but having very little C# experience, I got in over my head with the designer factory code and an issue described here.

If you were willing to give some pointers on how we can connect the dots to get a working VSTO for Access, I think several of us would be very happy to flesh out the details and integrate that long-requested ribbon. 😄

Thank you again for taking the time to chime in!!

[A9G-Data-Droid]

Note: The author of twinBasic, Wayne, has given us promise that tB will allow us to easily create 64-bit Access Plugins. This will be one of my first tB efforts: to make a lightweight wrapper that passes off to our existing ribbon code. Allowing us to load a ribbon from any project. This generic ribbon wrapper could then be used for anything. So I plan on making it a standalone GH repo.

[hecon5]

I saw that! The demonstration ribbon was very neat indeed; I think it's the piece we're missing, for sure! I eagerly await it. Plus, from the sounds of it, it will include vbWatchdog ...natively!

[joyfullservice]

Some exciting news on this front! TwinBasic has matured enough for me to build out a basic proof of concept implementation of a ribbon interface! This will allow us to have a more user-friendly launching point for the add-in and should work in both 32 and 64 bit versions of Office, which was a major limitation on the VSTO approach.

My plan is to have the ribbon add-in dll located in the same folder as the Access add-in. The actual ribbon content will come from xml files so that they can be easily changed and developed without needing to recompile/rebuild the COM add-in. Different flavors of ribbons will be in a Ribbons subfolder, with an Images folder under that to store any custom images. In the (Access) Add-in UI, you will be able to select your desired ribbon and this preference will be saved across upgrades.

I am wanting to keep the actual TB project fairly small and light, and keep most of the logic in the Access add-in. For example, rather than hard-coding each button operation in TB, I am wanting to expose a single generic function that acts as a pass-through to the Add-in API. This way we can easily build and extend the ribbons using the XML and Access add-in project. I am looking at doing images the same way, where a generic pass-through function can dynamically load the images based on the file name used in the ribbon xml without having to rebuild the COM add-in each time.

For installation, I am looking at embedding the COM add-in files in a resource table in the Access add-in. This way the ribbon can be installed or removed using the existing framework. We can automate the loading of the compiled dlls and any custom images during the build process.

Again, this is a very simple proof-of-concept, but I think it establishes the foundation for us to pretty easily build this out for the upcoming 4.0 release. 😄

If you have recommendations on controls (buttons) you would like to see on the ribbon, or some suggested layouts, feel free to post screenshots or share further input.

[joyfullservice]

Here is what I am initially proposing for a ribbon layout:

Under Advanced Tools, we can nest some of the more custom things that are not used as frequently:

This layout uses only built-in MSO images, so we don't have to worry about custom images at this point.

[hecon5]

I like using the built in images; we don't need to distribute them, license them, and we're (pretty sure) they'll be there for users.

[hecon5]

One thing that this will enable (far easier direct access to the GitHub page), and that has been on my mind is the community component. We (you, @joyfullservice, actually...) might consider making this a project - team build vs. you managing it all. The upstream (https://github.com/msaccess-vcs-integration/msaccess-vcs-integration) did this a while ago, but I think did it too late (after many of the PRs were ignored for too long or issues stuck there), and the community engagement basically died (or your fork is so far ahead in ease of use and the like and they all came here). You've been carrying a HUGE amount of the water (coding, managing PRs, tickets, troubleshooting, etc.), and I'd like to volunteer my services if you're interested to help with triage/wiki docs/etc. Though, this would entail you "giving up" a little bit of control, done right, I think this could help make sure the project is much more resilient in the long term. No worries either way for me, just don't want you to feel like you've got to shoulder it all: I'm sure others feel similarly. If this should go in a discussion page, we can move it there, too, but it came to mind seeing the "GitHub Community" link.

Thoughts?

[hecon5]

Separately: I was thinking a "Check Version" button might be nice: Either a direct link to the releases page or a routine to call home and check the version then download it.

[A9G-Data-Droid]

This is great. I agree with the native icons. The one thing I'm worried about is a VBA routine that does the install. VBA macros are a common threat vector and we may not want to rely on a VBA installer. It might be time to move to a proper installer package. That can be done later in a separate step for ease of development. Mainly, we want a signed installer that can be run with Admin mode. This avoids running Access as admin and also any Microsoft security features that will block VBA from installing a DLL.

[hecon5]

What kind of installer are you thinking? If we want a valid signed cert, we'll need to raise money; that has alllllll kinds of other sticky wickets, unless you can think of another path? Does TB allow you to sign the compiled output? If so, that may be a path.

The other thing, and it's a big one: Access does not allow you to SIGN macros in the first place; so the signature would only be valid for distribution.

[A9G-Data-Droid]

I like InnoSetup because the configuration is a text file that can be checked in to source control.

The issue of a code signing cert is a bigger problem that can be solved later. I have only found one place the offers a discounted cert for opensource projects:

https://shop.certum.eu/open-source-code-signing.html

I wish there was an opensource certificate authority...

[joyfullservice]

Regarding the installer, I have a hunch that I might be able to do this in the HKCU without requiring admin privileges... Let me see how that goes as we flesh out the actual installation... I am all for keeping things simple if we can. 😄

[joyfullservice]

I have finished integrating the ribbon into version 4.0. Feel free to test it out and let me know if you encounter any issues. I would be especially interested in someone testing out the 64-bit version, since I don't have a 64-bit version of Access on hand.

To summarize the approach I took on this, I created a COM add-in using TwinBasic, and embedded the compiled DLLs and Ribbon.xml in a resource table in the Access add-in. These automatically update from the GitHub project source folders when the add-in is opened.

During the installation, the Access add-in runs regsvr32 on the COM dll, which generates the necessary COM registrations in the registry. The TwinBasic project also includes some (VBA) code to add the registry entries that Microsoft Access uses to show and activate the COM add-in.

Known Issues in v4.0.1

• When working with the add-in file itself, (such as building this add-in from source) the ribbon commands run in the local VBProject first. When exporting, this causes the export to begin with the local form, then switch over to the add-in form to complete the export. The build has to be run from the add-in menu, not the ribbon, since I haven't figured out a good way to invoke the Application.Run call on the add-in vs. the current project when they both have the same name. (Again, this only applies to the add-in project itself.)
• Version 4 includes functionality to help prevent accidentally overwriting changed source files or modified database objects, but this is still in development. If you see a dialog prompting you to confirm a list of changes, you can probably just select Overwrite All and continue. (That's what previous versions would do anyway, without prompting.)

You can build v4.0.1 from source, or I have attached a copy here if you want to try installing it and testing out the ribbon. Version_Control_v4.0.1.zip

[hecon5]

We're in the middle of a humongous push right now, so it'll be a week or so before I can break off to test, but I can run through 64Bit. I'm really excited to try this out!

[A9G-Data-Droid]

I have loaded this in a 64-bit environment and I'm seeing two things that are odd.

1. What's up with the Twin Basic splash screen? It happens on install but then it also happens every time I open any Access project. This is not ideal.

2. The Resolve Conflicts window. I don't know what to do with this. Is this some sort of git integration? I have good tools for git compare and I don't want this screen to ever come up. Is there a setting to turn it off? I don't know what I'm looking at or why. The "Show Diff" links don't do anything so I can't even make an informed decision.

The version of the conflict window shows 3.3.37:

[joyfullservice]

I have loaded this in a 64-bit environment and I'm seeing two things that are odd.

1. What's up with the Twin Basic splash screen? It happens on install but then it also happens every time I open any Access project. This is not ideal.

Yes, I was afraid that might be the case. That is the nag screen when you compile a 64-bit dll with the free version of TwinBasic. The 32-bit version loads very fast, no noticeable drag, so I think this will be resolved when someone with a licensed version of TwinBasic can compile the 64-bit version for us.

But the good news is that very little of the logic actually happens within the TwinBasic project. We can customize the ribbon, add new tools and adjust the ribbon functionality without needing to update the COM dll.

2. The Resolve Conflicts window. I don't know what to do with this. Is this some sort of git integration? I have good tools for git compare and I don't want this screen to ever come up. Is there a setting to turn it off? I don't know what I'm looking at or why. The "Show Diff" links don't do anything so I can't even make an informed decision.

As I mentioned in the known issues, this is still a work in progress. The main idea is that if you are about to overwrite a file that is different from the last one you exported, this will prompt you so you don't inadvertently overwrite unsaved work. (This can happen when switching between branches or checked out versions of a project.) On the export it is not as big of a deal. As you pointed out, there are great tools in place for reviewing changes in git before committing them. But when merging source files into an existing database application (a new feature I am working on), those kinds of changes are not as obvious because the current database objects may contain new changes that are not yet reflected in the source files. This is especially prone to create issues when multiple developers are working on the same database.

All that being said, your suggestion of adding an option to turn off the overwrite warning sounds like it would probably be a good idea. We can circle back around to that when the feature is closer to release and has some of the bugs and kinks worked out. 😄

The version of the conflict window shows 3.3.37:

Nice catch! Looks like I had that hard coded in there for some reason. I will take care of that. 😄

Thanks again for taking the time to test this out! It sounds like the ribbon must have loaded up just fine for you, which is encouraging for the future of 64-bit support.

[A9G-Data-Droid]

I have a license for twinBasic. I have compiled the 64-bit DLL for you. It removes the Splash Screen and Access opens much faster!

MSAccessVCSLib_win64.zip

[joyfullservice]

I have a license for twinBasic. I have compiled the 64-bit DLL for you. It removes the Splash Screen and Access opens much faster!

MSAccessVCSLib_win64.zip

Awesome!! Thanks!! I am reviewing your pull request now...

[A9G-Data-Droid]

Great! Let me know when you are ready for release and I can compile it again.

[hecon5]

Since this is implemented in the latest beta, should this be closed?

[A9G-Data-Droid]

Generally, issues are closed automatically by merging a PR in to the main branch. You do this by tagging the PR with Closes #34. This is a feature of GitHub. In a DevOps workflow, the act of merging to main generates a release. So by this mechanism you can see that an issue is not "closed" until a release containing a fix is made.

This is incentive to get your release out of beta.

[hecon5]

I more meant since there was no specific PR for this, but yes, I do think that the beta would be good to launch soon :)