CommandBars Import/Export

[AZDeveloper777]

I have an MS Access 2010 database UI that has CommandBars. When I do an "Export Source Files", the CommandBars are not exported.
I tried searching this repo's Issues but didn't see this addressed anywhere. Is it possible to do this or is it a limitation of MS Access ?

[joyfullservice]

It appears that command bars would be stored in a system table MSysCmdBars. If you include the table data for this system table, you should be able to export this data, and have it come in again when you build from source.

http://access.mvps.org/access/general/gen0035.htm

I haven't tried this myself, but let me know how this works! If this works successfully, I can probably add that table name as one to export by default (if it exists) so that it will work a little more "out-of-the-box" for most users without even having to know about the system table.

[mwolfe02]

According to this discussion, the MSysCmdBars table was dropped after Access 97. I just checked in an .accdb with known custom shortcut menu bars and there is no MSysCmdBars table, even when showing both hidden and system tables.

Looks like this would require iterating through the Application.CommandBars collection and writing to a text file (.json in keeping with this project's existing approach, I would presume). Of course, there would need to be a corresponding process to read the properties in from text and creating the command bars via VBA when rebuilding the database.

Each CommandBar has several properties; I'm not sure which would need to be exported/imported, it might require some trial and error to figure out:

Within each CommandBar are a variable number of CommandBarControl objects within the .Controls property, each with their own properties that may or may not need to be preserved during export/import:

There's also the question of saving and loading custom embedded button images.

This seems like a small side project on its own. Seeing as it would be serving a relatively small audience and require a fair amount of work to build, I don't think it's a great use of @joyfullservice's time to implement this. However, perhaps he could point us toward an existing part of the project that already does something similar to this. My first thought is the report Printer property, but maybe there is a better candidate.

[joyfullservice]

@mwolfe02 - Yes, that was exactly the approach I was contemplating... It didn't seem like it would be too hard to pull off (famous last words) so I actually got the basic concept working in a few hours. A couple of recursive functions handle the exporting and rebuilding of the menus from the json file, and the images can be saved and restored pretty easily using the stdole library.

To actually finish this out I would probably need to make a more complex example popup menu in the testing database to work through any remaining implementation issues. But I can see how these menus could be useful in some applications. Thanks again for taking time to share your thoughts on this!

[bclothier]

Just to point out Dale Fye, an Access MVP wrote an add-in that helps with managing commandbars which may also be useful. A possible path would be to take the export from the tool, and allow it to be imported.

EDIT: I think I may have been hasty - I saw import button and assumed import/export but it's more for importing from an existing database, rather than importing from a text file. However it does have ability to script out the commandbars, which may be sufficient since that can then become a part of the project's VBA modules and be under the source code control this way.

[joyfullservice]

Thanks for mentioning this add-in! The related articles were helpful as well in understanding how to work with the CommandBar object in VBA.

[DickyPe]

This was virtually the first thing I noticed when testing CVS as a new convert. Just a vote to say I would welcome this being implemented, if possible. Currently the work round once the new database has been imported via VCS is to import from a known database source of the toolbars and click the Import Option of 'Menus and Toolbars' and no objects.

[ronmcca]

I am attaching a module to export to version control and import to new command bars. CommandBarExport is run after export and CommandBarImport is run after build from source. The command bars exported are simple right click menus for forms and reports on a FE when executed by runtime. Only Type, ID, and BeginGroup are handled. The file is stored in the base of the version control folder. It should be relatively simple to extend for more complex command bars.

modVersionControl.zip
paiCommandBars.json