Wednesday, February 13, 2008

Using Access Menu Add-Ins Without The Windows Registry

Access menu add-ins require a registry entry to be available from the Access Add-Ins Menubar menu item.

In many corporate environments Access developers and users are unable to use 3rd-party Access add-ins, as users are not given permission to add entries to the Windows registry.

There is a way round this limitation, by referencing the add-in file in the database you want the add-in to be available in.

To illustrate this technique, I will use my free add-in Access SideBar Add-In for Access 2000/2002/2003, which provides an easy way to navigate objects in your Access MDB.

These are the steps to get the tool working as a library database in Access 97 or later, without the need for any changes to the Windows registry:

  1. Copy the file sidebr2k.mda to a folder on your PC.

  2. Open your MDB in Access and hit F11 to open the VBA Editor (VBE) window.

  3. In the VBE, from the Menubar select Tools|References...

  4. In the References dialog, click the Browse... button, and in the Add Reference dialog, select Add-ins(*.mda) in the Files of type drop-list, locate and select the file sidebr2k.mda, and click Open.

  5. The MDA file will now appear as a reference in the References dialog:

  6. Note the name of the library file in the Available references listbox, as you need the library name to call functions from the library. In my example the library name is: The Access SideBar.

  7. Create a new VBA module and insert this public function:

    Option Compare Database
    Option Explicit

    Public Function pfnRunSIDEBAR()

    Call [The Access SideBar].aad_OpenLibraryDB

    End Function

    This code calls the function in the library database that invokes the add-in from the Access Add-Ins menu item: the syntax is [LibraryName].[FunctionName].

    To find out the name of the invoking function, open the add-in file in Access using the SHIFT key. From the Access Menubar select Tools|Options..., and in the Show section of the View tab of the Options dialog, ensure that the check-boxes for System objects and Hidden objects are both checked. In the Access Database Window open the USysRegInfo table. The invoking function will be the text after the = sign in the Value column for the record where the value "Expression" appears in the ValName column.

  8. Save the module and call the function pfnRunSIDEBAR from anywhere in your MDB. In my work I create a custom toolbar for my development coding functions, and add a custom button to this toolbar to call a function.