Creating a customized Access ribbon and Network Database

Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.
Published:
Updated:
Edited by: Andrew Leniart
I wrote this article because I was unable to find an article on this here on EE.  Read on for step-by-step instructions on how to create a custom command ribbon in your office applications that not only make them look more professional but also protects against the ID10T error.

History

I wrote a database in MS Access (I believe I first wrote it in a version that ran on Windows 98 or XP). I made a custom Switchboard, which was the best I could get at the time. I updated my version of Office as it updated in my institution. The version I had started with was MS Office on 16 3.5 inch disks and the current version (Office 365) is now only available to us as a download from our institutional account.

When I asked for help here on Experts Exchange with a problem I was having with the database, then in version 2016 of Access, I was asked if I was "married" to that structure (it had changed through the years, but I am by no means a database expert).  I answered that I was not married to it, but I had been building it for over a decade, and it certainly had extra stuff in it, and I didn’t know what would break if I changed things.

I made changes to the look and feel and changed the underlying structure in bits and pieces. This helped me through a bad spot and made the database workable and usable, at least for me.  Then about a year ago, I thought I would make the database available to the rest of the administrative staff, but I realized that I would need to limit their ability to change it if I wanted it to be useful.  

Making it more accessible

At that point, I split the database into a front-end and a back-end (easy to do with MS Access’s built-in tool).  This was great, so now they would only get the database's front-end on their machines, and I could manipulate/backup/etc. the back-end, where all the data lives.  
I made a listing form of all members (part of an MS Access DB template I downloaded off the internet).  It seemed to work best when I put some controls on the form itself, so I went ahead and did that, and it was a mistake.  Long story short, the form displayed differently on different machines, and hence the controls didn’t look the same.  It wasn’t something I particularly wanted to be associated with.

The Custom Ribbon

I once again asked for help here on EE.  I received a lot of help from Access experts, and eventually, they pointed me in the right direction.  They almost all suggested I make a custom ribbon instead of writing code to auto-resize the form for each screen.  I searched the internet on how to make a custom ribbon, but the results were not very satisfying.  One of the EE experts showed me some code for customizing the ribbon, along with the code I had previously seen, and what I was able to glean from opening the MS Access built-in customization file in an XML editor, I was able to get a decent customized ribbon.
 
Before you get started with the attachments below (which should be everything you need), you will need to:

  1. Be able to show the system and hidden tables and to create a new one.  
      1. So open your database, right-click on the top of the navigation bar (mine says "All Access Objects"), and select Navigation Options.
      2. Check the boxes that say Show hidden Objects and Show system objects
  2. Some settings you should turn on if you want to make this work
    1. Go to File -> Options -> client Settings and tick "Show add-in user interface errors"

Now that you have done that, you will need to create two objects in your Access database to make your custom ribbon usable. First, you will need to create a new table called USysRibbons. This table must have, at least, the following three fields:

Field Name Field Type Description
ID Autonumber Let Access make this the unique identifier
RibbonName Text/Short Text Name of the Ribbon
RibbonXML Memo/Long Text Contains all XML of the ribbon

You can add more fields if you wish, but that is the minimum.
 
Besides the new USysRibbons table, you will need a new form based on the USysRibbons table (I called mine Ribbons), where the only absolute requirement is to make the RibbonXML field as large as possible - you'll need it.
 
After creating the table, create the form, highlight it, and click the create menu and the form button - right-click on the top of the document and select design to change the layout.
 
Although creating the USysRibbons table and the form based on it will make your life a lot easier, they are not ESSENTIAL to creating a custom ribbon.  They are essential to making the process a whole lot easier.

Attachments:

  • A sample of my custom ribbon XMLribbon from ccdb.xml
  • A sample of the Microsoft Ribbon customization file which can be used as guidance Access Customizations.exportedUI ribbon.xml
  • The steps to implement it require a lot of opening and closing of the database, but it is worth it in the end.  
    • In the Access options seen below, click Current Database on the side.
    • If you don't want the navigation bar to show, make sure it is not selected.
    • Select the ribbon of your choice from the drop-down and close the DB and reopen it.
  • The various “controls” used in the XML code[MS-CUSTOMUI2].pdf from this website
  • A link to the XML editor I use (free from Microsoft)
  • How the custom ribbon looks when implemented
(NOTE: to prevent unauthorized changes, go to the file menu and select "Options."  Select "Current Database" and be sure to uncheck "Display Navigation Pane," select the ribbon you wish to display under "Ribbon Name:," and untick "Allow Full Menus."  When you close and reopen the database the file menu will now only display "Print," Privacy Options," and "Exit."  Privacy options is the same Options.)

Making the ribbon functional requires creating macros that do whatever you need doing, calling the Access built-in routines, or using VBA script.
To create a macro in MS Access:
  • Click the create menu
  • Click macro
  • Select show all controls
  • Select open form
  • Select your form
  • Click the X to close
  • Say Yes to save
  • Name the macro
  •  Check that the macro is listed.

In the ribbon code, enter the name of the macro in the onAction section.
 e.g., the code to open a form in one of my databases is:
<button id="Contactlist" label="Contact Listing" imageMso="CondolatoryEvent" size=”large” onAction=”opencontactlist” visible=”true” />
Take particular notice of the onAction setting, it should be exactly the same as the name of the macro.

I should also mention that I use software called Auto FE Updater by Tony Toews.  Since I am not an Access expert, I need all the help I can get, and this software was very helpful.   All it does is create a script that puts the front end of your database on your end-users desktops with an icon of your choice and generates an email that helps them install said database. The software is named for the fact that it automatically updates the end user's version of the front-end of the database depending on the version of the server. There are a few more bells and whistles in this software, but that is for another article.

Some links at EE that might be helpful:
The question that led me to write this article. and the people who helped solve the question:
John Tsioumpris 
Natchiket

Other Questions:

Link to Microsoft explanation: https://support.microsoft.com/en-us/office/create-a-custom-ribbon-in-access-45e110b9-531c-46ed-ab3a-4e25bc9413de?ocmsassetid=ha010211415&correlationid=ef6cd992-50cc-4b09-b706-e999d12fa06f&ui=en-us&rs=en-us&ad=us

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.
   
 Note: If you need further "Support" about this topic, please consider using the "Ask a Question" feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts...
  
 Please do not forget to press the "Thumb's Up" button if you think this article was helpful and valuable for EE members. It also provides me with positive feedback. Thank you!
 


2
2,625 Views
Thomas Zucker-ScharffSenior Data Analyst
CERTIFIED EXPERT
Veteran in computer systems, malware removal and ransomware topics.  I have been working in the field since 1985.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.