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
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
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)
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
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.
Choose "Customize" and you should see the following:.
Create a New toolbar and enter "Empty Menu" as shown:.
The newly created "Empty Menu" will appear somewhere on the screen, as shown:.
Drag the new toolbar to the top of the screen and "dock" it, as shown:.
Go back to where you went to "Customize" toolbars, select the new "Empty Menu" and choose properties:.
Under Properties, change the toolbar to a menubar, as shown:.
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
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
Else ' Unknown error.
ChangeProperty = False
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.
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
MsgBox "Error #" & Err.Number & ": " & Err.Description
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,
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:
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
Option Compare Database
Private Sub Report_Close()
DoCmd.ShowToolbar "Compliance Database", acToolbarNo
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records found"
Cancel = True
Private Sub Report_Open(Cancel As Integer)
DoCmd.ShowToolbar "Compliance Database", acToolbarYes
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
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
<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
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
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: