Home Page
Tips And Tricks
Helpful Links
e-mail me

Picture of building from outside Tips And Tricks

I seem to run into things from time to time that I want to share with the world, usually things that I had to find out the hard way. Sometimes it will just be something that I had to dig for, and want to make more readily accessible. This page is intended to make life a little easier for other programmers. If it meets your need, then that's what it's here for.

Hide or Eliminate The Ribbon Programmatically in MS Access 2007

Recently, this subject has come up for at least three of my customers.  When I looked for help on the internet about how to do it, some of the answers are there, but they are about as clear as mud.  Everyone seemed to have a different answer, and not all of them worked or applied to my situation at the time.

First of all, one very important question to answer before you start in on this task is, "Which Access database file format is my front end database in?"  Because how you hide or eliminate the ribbon is affected in a critical way by the answer to that question.  If your program resides in a database which was written using Access 2003 and is still stored in the Access 2003 file format, then the task is actually simpler.  If you converted your program to Access 2007 file format, things get more complicated.  Leave it to Microsoft to make life more complicated as we go.

If you have already converted your database to an Access 2007 file format, for instance, AccDB, then skip directly to:

Native Access 2007 Solution using Menubars

Solution For An Access 2003 Database Running Under Access 2007

Let's start with how you do this when you are running a front end stored in Access 2003 file format using Access 2007.  A quick search for:

 access 2007 minimize ribbon on startup

yields the following Microsoft reference:


That page discusses the behavior of Access 2007 running a 2003 database. While this resource tells you how to set up your database to present things the way you want them to be for your users, it does not explain how to do it programmatically. 

Why do this programmatically, some might ask?  Well, if you don't do it that way as you publish the database for public consumption, then you will either have to hold the shift key to get a menu every time you bring up the database in your development area, or else you will have to set these properties manually (by hand) just after you copy the database to the area on your network where your users will pull down a copy to run on their own workstation.

I don't know about you, but I like things easy. I want my development copy of the database to be set up for me to do development in, and it is often required by my customers that the version my users run be restricted so that users can't accidentally cause problems.

So, when I'm ready to publish a new version, I use a batch file to copy the MDB or ADP to the area where it will be picked up by the users, and to also set things up the way my users will see them, (i.e. a more restricted environment)

Technically, two of the items discussed in that article could be set by some simple property code, shown below:

currentdb.Properties("AllowFullMenus") = False
currentdb.Properties("AllowBuiltinToolbars") = False
'currentdb.Properties("StartupMenuBar") = "Empty Menu" 'this line doesn't work!

The last line will NOT operate as it is shown here, but instead produces a message saying that the property does not exist or is not found. And in addition, these are properties that do not take effect until you close and restart Access. (See picture)

Image of Access 2007 screen after changing AllowBuilt-in property

Now, enough explanation.  Let's get started actually doing things.  First create a new MenuBar called "Empty Menu". 

This step normally should be done in Access 2003. Why, you ask? Because Microsoft has not included the same facilities in Access 2007 for editing and changing toolbars and menubars that were included in Access 2003  If you already have a menubar for your application, you might possibly skip this step, and just use the name of your menubar in the code below. 

You will have to have your database open. This step requires that you do it in a database, since that is where the new menubar will be stored.

Right click near the top of the page as shown in the picture below.

Image of MS Access Toolbar customization screen

Choose "Customize" and you should see the following:. 

Image of MS Access Toolbar customization screen

Create a New toolbar and enter "Empty Menu" as shown:. 

Image of MS Access Toolbar customization screen

The newly created "Empty Menu" will appear somewhere on the screen, as shown:. 

Image of MS Access Toolbar customization screen

Drag the new toolbar to the top of the screen and "dock" it, as shown:. 

Image of MS Access Toolbar customization screen

Go back to where you went to "Customize" toolbars, select the new "Empty Menu" and choose properties:. 

Image of MS Access Toolbar customization screen

Under Properties, change the toolbar to a menubar, as shown:. 

Image of MS Access Toolbar customization screen

Now that we've established a menubar called "Empty Menu", we need to set up the code to actually hide things.  I usually place the following code in a module. This function is necessary to change the startup properties of an Access database. 

Function ChangeProperty(strPropName As String, varPropType As Variant,  _
  varPropValue As Variant) As Integer
 On Error GoTo Change_Err
 Dim dbs As Object, prp As Variant
 Const conPropNotFoundError = 3270
 Set dbs = CurrentDb 
 dbs.Properties(strPropName) = varPropValue 
 ChangeProperty = True
 Exit Function
 If Err = conPropNotFoundError Then    ' Property not found.
  Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)  
  dbs.Properties.Append prp  
  Resume Next 
 Else  ' Unknown error.  
  ChangeProperty = False  
  Resume Change_Bye 
 End If
End Function

The following code hides the ribbon programmatically by running code in Access 2003 or Access 2007, and will affect the way things look when the program is run later in either one. 

Function DisableStdOption()
 On Error GoTo Err_DisableStdOption
 ChangeProperty "StartupShowDBWindow", DB_Boolean, False
 ChangeProperty "AllowFullMenus", DB_Boolean, False
 ChangeProperty "AllowBuiltinToolbars", DB_Boolean, False
 ChangeProperty "StartupMenuBar", DB_TEXT, "Empty Menu"
 'turn off the database window in normal use
 DoCmd.SelectObject acTable, , True
 DoCmd.RunCommand acCmdWindowHide
 Exit Function
 MsgBox "Error #" & Err.Number & ": " & Err.Description
 Resume Exit_DisableStdOption
End Function

I then call the above code from a batch file which publishes the new version for the users.  Within the batch file, I have a line calling a macro, for example:

copy "c:\AccDev\MyApp.mdb" "n:\LiveNetworkCode\MyApp.mdb"
call c:\windows\runacces "n:\LiveNetworkCode\MyApp.mdb" /x Macro2DisableStdOption

Voilą!  Now you get to use your database in the development area with all the options turned on, and after you run your batch file to publish it, your users will not have that pesky Access 2007 ribbon to contend with, and will not have options that they could get in trouble with.

The result of running the Access 2003 database under Access 2007 looks something like the following:

Image of MS Access 2007 With Menubars Instead Of Ribbon

Another thing that you will also notice, now that you can run your application without a visible toolbar, is that there is now no toolbar for reports, as well.  That becomes a bit of a problem, as there is no obvious way for the user to print the report.

There are a couple of ways to go about making sure that users have a visible option to print the report.  One easy way is to just assign your favorite toolbar to the toolbar or menubar property of the report.

My favorite way, as I am a code writing kind of guy, is to use the following code:

Option Compare Database
Option Explicit

Private Sub Report_Close()
 DoCmd.ShowToolbar "Compliance Database", acToolbarNo
End Sub

Private Sub Report_NoData(Cancel As Integer)
 MsgBox "No records found"
 Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)
 DoCmd.ShowToolbar "Compliance Database", acToolbarYes
End Sub

Native Access 2007 Solution using Menubars

If you have already converted your database to Access 2007 format, perhaps because you are not running any earlier versions, then the water has already gotten deep. 

First of all, (grumble, grumble) Access 2007 does not have a facility to visually edit (drag and drop) toolbars.  It seems that in Access 2007, if you choose to change anything on toolbars, you are expected to write Visual Basic code to do it (or else use that old copy of Access 2000 that you still have lying around)

Secondly, (grumble some more) toolbars are handled in a different way in Access 2007 when you are running a database which has been converted to the Access 2007 format (in other words, a database with an AccDB extension)  In that case, the toolbars that you wrote using Access 2003 or earlier show up under a tab called "Add Ins"

Well, I didn't want my users to have to choose an "Add Ins" tab every time that they used my menu.  After much searching and a lot of hair pulling, I finally figured out how to make a custom ribbon and change the label of the "Add Ins" tab so that it would be called something else, whatever name I wanted it to be called.

The task, unfortunately, is a little daunting, even after having already done it.  The way that one person described it is as follows: "To remove the ribbon, you create a USysRibbons table and in the one row, add the following XML, which will remove all other std Access 2007 ribbon tabs and rename add-ins"  There are several steps in this process, and a good tutorial of how to get started is at the following link:


Remember how you could view Hidden files and System files in in Access 2003?  Well to be able to create a new ribbon, you have to create a new system table. So, in order to do that, you have to use the same option in Access 2007 that is like the check box in Access 2003 under "Tools" "Options" "View" "System Object".  That is to say, you have to be viewing system objects.  The link above has pictures of where to go in Access 2007 to view system tables. 

Then you have to create a system table called USysRibbons.   Basically, you add three fields to it, a standard ID field of the type that Access will sometimes make for you (an Autonumber Primary Key type field), a text field called RibbonName, and a memo field called RibbonXML.  By the time you finish, this new system table should have a single record in it.  The contents of the RibbonXML field define the custom ribbon.  In order to do this, you'll have to be familiar with changing the size of memo fields in datasheet view to be able to view larger amounts of text.

The example at the web site listed above, however, is of doing something more complicated than what I wanted to do.   I mean, I just wanted to get rid of the doggone thing so that I could clean up my user interface and also keep my users out of trouble, ya know?

So for my example, the code that you paste into the single record in the newly created USysRibbons system table is as follows: 

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="true">
        <tab idMso="TabAddIns" visible="true" label="Projects" />

The label tag in the above XML changes the name of the "Add-Ins" tab to "Projects". 

Having that XML define the contents of the ribbon, setting the "AllowFullMenus" property and the "AllowBuiltinToolbars" property to "False" (which could be done using the ChangeProperty function described above) and in addition setting the "StartupMenuBar" property to my custom menubar that I'd already developed in Access 2003 which had options for "File", "Edit", "Reports", and "Maintenance" did the trick. 

So far, at the time that I am writing this, I haven't done enough development under Access 2007 to need to do the Access 2007 method programmatically yet.  The way that I approached the development that I did under native Access 2007 so far was to just use the shift key to bypass the special menu in the development folder.

The result of running the native Access 2007 database under Access 2007 came out looking something like the following:

Image of MS Access 2007 without ribbon

So there you have it. That's my summation of the options that I currently know of to eliminate the ribbon in Microsoft Access 2007.  If you like my explanation, then by all means, send money!

And if you should have some extra time on your hands and be interested in a little "light" (or maybe not so light) reading, here's a link to a web page with some of Microsoft's explanation of how to write custom ribbons:


Happy ribbon cutting!

If you found this information to be useful, and would like to donate via PayPal to help offset the cost of maintaining this web site, use the following button:

|Home Page| |Custom Software| |Policies| |About| |Tips And Tricks| |Helpful Links|