I have a MDB database application developed in Access 2002. This is a split database with a front-end and back-end component and the back-end having been set up with User workgroup security.



The front-end includes a custom menu bar in which certain menu items are either enabled or disabled depending on the user name logged in. This is controlled under VBA code which is included in the Load event procedure for a splash start-up form which is displayed when the front-end is opened. The relevant section of this code is shown here:
Code:
Set ctlmenu1 = Application.CommandBars("AMContacts1").Controls("File")
Set ctlmenu2 = Application.CommandBars("AMContacts1").Controls("Contacts")
strCurUserName = Application.CurrentUser
If strCurUserName = "officeuser" Then
    ctlmenu1.Controls("Export Data").Enabled = False
    ctlmenu2.Controls("Edit Contact Details").Enabled = False
    ctlmenu2.Controls("Add New Contact").Enabled = False
Else
    ctlmenu1.Controls("Export Data").Enabled = True
    ctlmenu2.Controls("Edit Contact Details").Enabled = True
    ctlmenu2.Controls("Add New Contact").Enabled = True
End If
I have recently ported this MDB application to run in Access 2010 which supports workgroup security on legacy MDB files and also supports legacy toolbars and menus.

In setting this up to run in Access 2010 I have selected several current database startup options via the Backstage Options menu item. These are:
Menu Bar - select name of my custom menu bar (AMContacts1 as in code example above)
Allow Full Menus - False
Allow Built In Toolbars - False

I also disable the normal Access Navigation pane.

Using these settings in Access 2002 provides the user with a simplified interface with just the custom menu showing. The various menu items are enabled or disabled depending on the username logon as designed in VBA code shown above.

Trying to get these to work in Access 2010 I have the following two results:

1. Using these same settings in running the application under Access 2010, the menu bar is displayed in place of any ribbon, but irrespective of the user logon all menu items are enabled. This indicates that under this start-up option, the VBA code is being ignored or not being processed as it was in Access 2002.

2. If I change the start-up option “Allow Built In Toolbars” to True, then my custom toolbar appears in the “Add-Ins” tab on a basic ribbon displayed at the top of the main window, and the menu items are enabled or disabled correctly depending on the username, as they did in Access 2002.

Can anyone provide an explanation of why the menu bar is behaving differently with these alternative settings and why in situation 2 the VBA code is apparently being ignored? Is there a work around?

My preference at this stage is for users to be presented with the same interface they had when running the application under Access 2002 and not to have any ribbon displayed which requires them to click on the “Add-Ins” to display the menu. If I still want to provide the control over the menus via user logon then I may have to accept this as being the only method.

In due course I will probably redevelop the application as a full Access 2010 application in ACCDB or ACCDE , with possibly a custom built ribbon in place of the existing legacy menu bar.