Application for Access 2003 and 2007+: Substitute missing class in reference with custom MDE.

This trick I learned from Graham Mandeno, Access MVP. Here we talk about Ribbon interface, but same trick can be used for other libraries.

In Access 2007 command bars were replaced with Ribbon. You can develop application which can run on 2003 and 2007/2010, but if you build custom menu bar or command bar for Access 2003, when running in A2007+ the menu bar gets buried in the "Add-Ins" ribbon. So you would like to create a proper ribbon UI which would appear if the MDB/E is opened in A2007+, and still have the old-style CommandBar UI appear if opened in A2003.

You can create the ribbon for 2007/10, but the callbacks fail to compile in A2003 because class objects such as IRibbonControl do not exist in the Office11 object library. You can try to declare all instances "As Object" and that keeps A2003 happy, but then the ribbons don't work in A2007+. Let us give word to Graham:

What I have done is create a very small MDE containing nothing but class modules for IRibbonControl and IRibbonUI, each with the necessary properties and methods declared as empty procedures. The class modules need to have instancing set to “PublicNotCreatable”. Then I added a reference to the MDE to my project, ensuring that it is last in the list – BELOW the reference to MSO.DLL.

Now, when running on A2007+, the code picks up the reference from the Office 12/14 object library and works fine. On A2003 it finds it in the dummy MDE and compiles OK (of course, the actual code is never called in A2003).

In case anyone else is interested, here are the three class modules (I don’t actually use IRibbonExtensibility, but I included it for completeness):

' Class: IRibbonControl

Option Explicit

Public Property Get Context() As Object

End Property

Public Property Get Id() As String

End Property

Public Property Get Tag() As String

End Property

================================

' Class: IRibbonUI

Option Explicit

Sub Invalidate()

End Sub

Sub InvalidateControl(ControlID As String)

End Sub

================================

' Class: IRibbonExtensibility

Option Explicit

Public Function GetCustomUI(RibbonID As String) As String

End Function

================================

Access 2010: Interactive menu to ribbon guide

The most popular post in this blog is Compact database in Access 2007, people really get lost in Ribbon. Access 2010 has better organized Ribbon, but still - transition from Access 2003 to 2010 is not so easy due to the Ribbon. Microsoft just posted new program to help new Access 2010 users - Interactive menu to ribbon guide. Look how it shows where Compact database menu item in Access 2010:

Fortunately in Access 2010 Compact and Repair Database item was moved in more intuitive place: