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:
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:
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:
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:
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:
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:
|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:
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:
The subkey field takes one of two registry keys:
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.
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.