vba - Hide Access Options

Question

I have noticed that even though when you disable the “Use Access Special Keys”, disable the “Display Navigation Pane”, and disable the ribbon menus, you can easily access the “Access Options” go to the current Database area, and re-enable all these options.

Is there a way to completely hide the “Current Database” option in Access 2007 and 2010?

Answer 1

In MS Access 2007 and MS Access 2010, rather than setting options to control a user's access to the application, it is possible to control the contents of the "Backstage". All images and instructions apply to Access 2010, but 2007 is not very different. Read Customize the Ribbon first.

Backstage

First, right-click the Navigation Bar at the top of the Navigation Pane, and then click Navigation Options on the shortcut menu. In the Navigation Options dialog box, under Display Options, select the Show System Objects check box, and then click OK. This will allow you to see the table you create. Note that is applies to all databases, so you may wish to switch it back off when you are finished.

Next, under Options, choose Client Settings and scroll down to General. You will see Show add-in user interface errors, make sure it is selected.

You will need a table called USysRibbons:

Create Table USysRibbons (ID Counter Primary Key, 
                          RibbonName Text(255),RibbonXml Memo)

You might like to add a unique index to RibbonName, otherwise you could end up with more than one ribbon with the same name.

You will need some XML, you can just cut and paste into the newly created table.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
       <!-- Ribbon XML -->
  </ribbon>
  <backstage>
   <button idMso="FileSave" visible="false"/>
   <button idMso="SaveObjectAs" visible="false"/>
   <button idMso="FileSaveAsCurrentFileFormat" visible="false"/>
   <button idMso="FileOpen" visible="false"/>
   <button idMso="FileCloseDatabase" visible="false"/>
   <tab idMso ="TabInfo" visible="false"/>
   <tab idMso ="TabRecent" visible="false"/>
   <tab idMso ="TabNew" visible="false"/>
   <tab idMso ="TabPrint" visible="false"/>
   <tab idMso ="TabShare" visible="false"/>
   <tab idMso ="TabHelp" visible="false"/>
   <button idMso="ApplicationOptionsDialog" visible="false"/>
   <button idMso="FileExit" visible="false"/>
  </backstage>
</customUI>

Set the Ribbon Name to say, "BackstageCustom". It should be fairly obvious which line relates to which part of the Backstage.

You now have to close and reopen the database (Compact & Repair is probably the quickest way to do this, but make sure you have a backup before you use this method). There will be a lot of opening and closing in this.

You can now go to Options->Current Database and scroll down to Ribbon and Toolbar Options, select the newly created ribbon under Ribbon Name. When you close Options, you will get a warning that you must close and open the database for the changes to take effect.

When you do, you will no longer be able to see Options on the backstage, nor will you see much except a list of recent databases. Look at the XML above, you can see that everything is set to false.

I reckon the easiest way out of the situation is to open the USysRibbons table and change this line:

<button idMso="ApplicationOptionsDialog" visible="false"/>

to

<button idMso="ApplicationOptionsDialog" visible="true"/>

Open and close again, and remove the ribbon from Ribbon Name, under Options. Open and close and you are back to where you started, more or less.