databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Ad-Ins

Creating a Simple Microsoft Access Add-In

Add-ins extends the functionality of applications by adding additional features over and above the functions that is already available. Sometimes they are called plug-ins. You can download ready made Microsoft Office add-ins from the Microsoft Web site, purchase add-ins from third-party vendors, download freeware or shareware add-ins from various Web sites, or create your own add-ins for personal or corporate use.

Microsoft Access offers us two types of Add-ins:

  • Application specific Add-ins
  • COM add-ins

The difference between the two is that Application specific add-ins can only be used in one application, while COM add-ins can be used in more than one application. As you can imagine the application specific add-ins will therefore be considerably easier to create and use compared to the COM add-ins. In this article we are going to focus on the application specific add-ins. There are three types of functional add-ins that we can create:

  • Wizards - A series of forms that guide you through creating a new object. A wizard is invoked when a new object of that type is created. You can write table wizards, query wizards, form wizards, report wizards, and control wizards.
  • Builders - A single form or dialog box that lets you set properties in Design view of forms and reports. You can write Property Builders and Expression Builders. (Builders are listed in the registry as wizards and are sometimes referred to as wizards.)
  • Menu add-ins - Called from the Tools menu's Add-Ins submenu; not context-specific.

We are going to be looking at how to create Menu add-ins as it is the easiest way to start learning about how to create add-ins for the Microsoft environment. But before we delve into creating this add-in, it would only be fair of me to walk you through the general steps that is required to build a add-in (incidentally, these steps are applicable to all of the above named add-in types).

Application-specific Access add-ins are simply Microsoft Access library databases, that is, Access databases saved with the .mda extension. The very first thing you need to determine is where exactly your add-ins are saved.

Usually, for Office XP users, it will be stored in C:\Documents and Settings\user name\Application Data\Microsoft\AddIns.

If you cannot see the ‘Application Data’ folder, then go to Tools » Folder Options » View in the Explorer Window and uncheck the ‘Do not show hidden files and folders’ option. Once you’ve determined the path to where Microsoft Office stores its add-ins then make sure that you also store your newly created add-ins there.

The next step is to actually create the add-in, so create a database called addin2 and save it with the .mda extension in the add-in path as discussed above. You actually have to type in the .mda extension when saving the database:

Save the add-in with the .mda file extension
Figure 1. Save the add-in with the .mda file extension

Make sure that you don’t save the database with the default mdb extension, ensure that the ‘Save as type’ is ‘All Files (*.*)’ otherwise your database will be saved as .mdb.mda which will not work as an add-in.

You should now have an add-in database or library in the Access window. So we need to make the system tables visible, in particular the all important USysRegInfo system table. To do this go to Tools » Options, you should see a dialog that looks something like this:

Setting options to view the System Tables
Figure 2. Setting options to view the System Tables

Make sure that the System Objects option is selected and click on apply and then OK. You should now see all the system objects listed, but for most of you, the USysRegInfo system table will not be listed:

Displaying the System Tables.
Figure 3. Displaying the System Tables

The USysRegInfo system table is at the heart of the entire add-in creation scenario, because it is responsible for writing information about the add-in to the windows registry when you install the add-in. So, create a table called USysRegInfo with the following fields:

USysRegInfo Table
Field Data Type What Does It Do?
Subkey Text (255) The name of the registry subkey where a specific Registry setting is stored. Can be either HKEY_CURRENT_ACCESS_PROFILE or HKEY_LOCAL_MACHINE (plus the path to Office 10).
Type Number(Long Integer) The type of entry to create. Can be key (0), string (1), or DWORD (4).
ValName Text (255) The name of the registry value.
Value Text (255) The value of the registry value.

The entire table design should look something like this:

The USysRegInfo Table
Figure 4. The USysRegInfo Table

The USysRegInfo table is designed to write information about a given add-in to the Windows registry. For this reason it is important that you write the right values for the fields listed above.

Since we intent to create a Menu add-in, lets discuss the USysRegInfo table from that perspective. Three records must be added to the USysRegInfo table for every Menu item that we want to show on a menu:

Subkey

The subkey field takes one of two registry keys:

  • HKEY_CURRENT_ACCESS_PROFILE
  • HKEY_LOCAL_MACHINE

Of the two options the HKEY_CURRENT_ACCESS_PROFILE is preferable because it is version independent, meaning that what ever version of Microsoft Access you use, the add-in will be installed in the profiles key. If for some reason you need to install an add-in for a specific version of Microsoft Access, use the HKEY_LOCAL_MACHINE key with the specific version of Access your add-in needs. But in the overall I would recommend that you avoid creating version specific add-ins as much as possible because they don’t provide any flexibility and will just cause you to re-create the same add-in every time you want to use it with a different version of Access. For the subkey the value should be:

HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\Menu Add-in Name

This key must be repeated three times for each menu item.

  • Type - The Type field specifies the type of registry entry: key (0), string (1), or DWORD (4). The first of the three records should have 0 for the Type field and the ValName and Value fields should be blank.
  • ValName - The second record of the three should have 1 in the Type field and Expression in the ValName field. The Value field should contain a equal (=) sign, and the name of the function that will run the Menu Add-in. For example: =Menuitem1()
  • Value - The last record should have a 1 in the Type field and Library in the ValName field. The Value field should then contain the name of the add-in as well as the path to the add-in. For example:

    |ACCDIR\addin2.mda

Conclusion

It is very important that you understand the basic steps of creating a add-in before jumping into just creating any add-in that you want since it involves tinkering with the system registry. So make sure that you cover the basics as outlined in this article before attempting to create even the simplest of add-ins. In part 2 of Creating A Simple Microsoft Access Ad-In we will create an add-in based on what we have discussed here.

Download the sample Microsoft Access Add-Ins database (12kb)

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com