Creating and Registering a Simple Access Menu Add-In

John Clarkson
Microsoft Corporation

January 1999

Summary: Provides detailed examples and step-by-step instructions for creating and registering menu add-ins in Microsoft® Access. (8 printed pages)

What Is a Menu Add-In?

Add-ins are tools written in Access that provide functionality beyond what's in the basic product. Typically, add-ins are used to guide users through difficult tasks or to automate repetitive tasks. Access offers three types of add-ins: wizards, builders, and menu add-ins.

A wizard steps users through complex operations such as creating a new table, form, report, query, or control. A wizard usually consists of a series of dialog boxes. Examples of this type of add-in include Access form and report wizards.
A builder usually consists of a single dialog box, and guides users through the process of setting a property or setting an expression.
A menu add-in is a general-purpose tool that doesn't fit into either of the two categories just mentioned, and typically operates on multiple objects, or on the Access environment itself. Menu add-ins are supported by Access through the Add-Ins command on the Tools menu, and are available to users whenever the Tools menu is available.

The USysRegInfo Table

The Add-In Manager depends on the existence of a system table called USysRegInfo, which contains the required registry entries for an add-in. You could make these entries by hand to the Microsoft Windows® registry, but this isn't really efficient if the add-in will be installed more than once, or used on other machines.

USysRegInfo allows registry entries to be stored with the add-in, making it portable.
Adding registry data to USysRegInfo allows the Add-in Manager to automate the registry update process, saving time and avoiding errors.

More information on this topic can be found in Chapter 17 of Building Applications with Microsoft Access 97.

File Types: .mde, .mda, and .mdb

Working with add-ins you'll have occasion to work with three different Access file types: .mda, .mdb, and .mde. Briefly, here are the high points:

.mdb is the standard Access database file extension (but you already knew that).
.mda files are functionally identical to .mdb files, but the .mda extension is an indicator that the file is an add-in.
.mde files contain only compiled Microsoft Visual Basic® for Applications (VBA) code. By contrast, .mdb and .mda files with VBA modules can contain compiled VBA code, but they also contain the VBA source code, which is the important intellectual property a developer wants to protect.

You can create an .mde file by pointing to Database Utilities on the Tools menu, and then clicking Make MDE File. Once the .mde file is created, you (and any users snooping around) won't be able to look at your VBA source code. All that's left in the .mde file is the compiled pseudocode. Because the VBA source code is discarded, the .mde file is also smaller than the original database. The original .mdb still exists, so if you want to you can make changes to the original code, convert to .mde again, and overwrite the previous version. Because of the combined advantages of secured code and smaller size, you may find it convenient to distribute your add-ins as .mde files.

A "Hello, World" Menu Add-In

Creating and registering a menu add-in can be a quick and easy process. To see how simple this is, follow along in this section to develop and install a simple add-in that pops up a message box displaying the current date.

Step 1: Create the Add-In

This procedure shows you how to create a database and insert code for the add-in:

The default location for Access add-ins is C:\Program Files\Microsoft Office\Office. Create a new folder called MyAddins at that location (or be sure to adjust any paths in the code to follow; search for "ACCDIR").
In Access, click the New Database toolbar button (or press CTRL+N), and then click OK in the New dialog box.
Use the File New Database dialog box to navigate to the folder called Program Files\Microsoft Office\Office\MyAddins. In the File name text box, type MnuAdn.mdb. In the Save as type text box, click Add-ins (*.mda) as the file type, and then click Create.
In the Databases window, click the Modules tab and then click New.

Add the following procedure to Module1:

Function KickOff()
    MsgBox Date
End Function

Click the Save toolbar button, and accept Module1 as the module name.

After you've completed the steps to follow (adding the USysRegInfo table, adding records to the table, and installing the add-in), you'll be ready to try running the add-in.

Step 2: Add a USysRegInfo Table

Access provides a system table called USysRegInfo to hold registry information. (See the preceding section, "The USysRegInfo Table," for more information on this table.) The following procedure explains how to add a USysRegInfo table to your database:

If your system doesn't currently display system objects, display them. On the Tools menu, click Options, and then click the View tab. Under Show, check the System Objects check box, and then click OK.
Access displays the system tables (tables with names that start with "MSys" or "USys") in the Database window. (Click the Tables tab in the MnuAdn : Database dialog box.) With system objects showing, your new USysRegInfo table will be displayed in the list of tables in this tab after you complete this procedure.
On the File menu, point to Get External Data and click Import. Access displays the Import dialog box.
Use the Look In box to navigate to the \Office folder where Access is installed.
Click Wztool80.mde, and then click Import. Access displays the Import Objects dialog box.
On the Tables tab of the Import Objects dialog box, click USysRegInfo in the list of table objects, and then click OK. Access imports the table. (You can see it on the Tables tab of the MnuAdn : Database dialog box.)

The USysRegInfo table imported from Wztool80.mde is empty. You now need to add the records that will register your add-in. The next section describes how to do this.

Step 3: Add Records to the USysRegInfo Table

In the Database window, click the Tables tab, click USysRegInfo, and then click Open.

Add three records using the values listed in the following table. (You can copy this table, excluding the headings, and paste it directly into Access.)

Subkey	Type	ValName	Value
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins\Today's &Date	0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins\Today's &Date	1	Expression	=KickOff()
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins\Today's &Date	1	Library	\|ACCDIR\MyAddins\MnuAdn.mda

Save the database and close Access. (This is important—don't try to perform the next step without first closing Access.)

Step 4: Install the Add-In

Reopen Access and open a new database.
On the Tools menu, point to Add-Ins, and then click Add-In Manager.
Click Add New, navigate to the MyAddins folder, highlight MnuAdn.mda, and click Open.
Click Close to close the Add-In Manager.
To run the add-in, on the Tools menu, point to Add-Ins, and then click Today's Date.

A Form-Maker Add-In

In the "Hello, world" type of add-in just described, you see the mechanics of creating an add-in. This section shows you how to create a more useful example of a menu add-in—one that automates adding controls to a new Access form.

Step 1: Create the Form-Maker Add-In

In Access, click the New Database toolbar button (or press CTRL+N), and then click OK.
In the File New Database dialog box, navigate to the Program Files\Microsoft Office\Office\MyAddins folder. In the File Name box, type FrmMkr. Choose Add-ins as a file type and click Create.
In the FrmMkr: Database dialog box, click the Forms tab, click New, and then click OK. (This forces the default mode, Design view, and produces a new, blank form in Design mode.)
Click the Toolbox button on the toolbar. Draw three text boxes and a command button on the form.
Position the controls so that your form resembles the following.

Set properties for your controls, using the settings listed in the following table. (Right-click the control, and from the pop-up menu, click Properties.)

Object	Property	Setting
Command button	Caption	OK
First label control	Caption	How many ListBox controls?
Second label control	Caption	How many CommandButton controls?
Third label control	Caption	How many OptionButton controls?
First text box	Name	txtListBox
Second text box	Name	txtComBtn
Third text box	Name	txtOptBtn

With the form in Design mode, right-click the command button and click Build Event. Access displays the Choose Builder dialog box. Click Code Builder and click OK. Access displays the code window for a class module.

Insert the following statements and procedures:

Dim frmNew As Form
Dim ctlNew As Control

Private Sub Command0_Click()
' This procedure retrieves values entered in the textboxes,
' then passes these values to the BuildPage procedure.

    ' Create a form and set the caption.
    Set frmNew = CreateForm()
    frmNew.Caption = "Your New Form"
    
    Dim intCount       As Integer   ' How many of this control?
    Dim strControlType As String    ' What type of control?
    Dim intIteration   As Integer   ' A sequencer to help space
                                    ' control placement on the page.
    ' Add list box controls.
    ' Retrieve number of desired list box controls.
    intCount = txtListBox.Value
    
    ' If a value is entered, add list box controls.
    If intCount > 0 Then
        intIteration = 1
        strControlType = acListBox
        Call BuildPage(intCount, intIteration, strControlType)
    End If
        
    ' Add command button controls.
    ' Retrieve number of desired command button controls.
    intCount = txtComBtn.Value
    
    ' If a value is entered, add list box controls.
    If intCount > 0 Then
        intIteration = 10
        strControlType = acCommandButton
        Call BuildPage(intCount, intIteration, strControlType)
    End If
    
    ' Add option button controls.
    ' Retrieve number of desired option button controls.
    intCount = txtOptBtn.Value
    
    ' If a value is entered, add option button controls.
    If intCount > 0 Then
        intIteration = 20
        strControlType = acOptionButton
        Call BuildPage(intCount, intIteration, strControlType)
    End If
        
End Sub

Sub BuildPage(intCount As Integer, intIteration As Integer, _
        strControlType As String)
' This procedure receives quantity and type of control from the
' click event, then creates controls.

    Dim intCounter As Integer   ' This is a counter for 
                                ' the loop below.
    
    ' Loop 1 to number of desired controls.
    For intCounter = 1 To intCount
        Set ctlNew = CreateControl(frmNew.Name, strControlType, , , , (intCounter + intIteration) * 200, intCounter * 200)
    Next intCounter

End Sub

Save the form with the name FormMaker.

In the FrmMkr : Database dialog box, click the Modules tab and then click New. Add the following Function procedure:

Function EntryPoint()
' This function displays a form allowing a user
' to specify type and quantity of controls to create.

    DoCmd.OpenForm "FormMaker"
End Function

Save this module with the name Module1.

Step 2: Register the Form-Maker Add-In

Import a USysRegInfo table. On the File menu, point to Get External Data and click Import. Access displays the Import dialog box.
Use the Look In box to navigate to the \Office folder where Access is installed.
Click Wztool80.mde, and then click Import. Access displays the Import Objects dialog box.
On the Tables tab of the Import Objects dialog box, click USysRegInfo in the list of table objects, and then click OK. Access imports the table. (You can see it on the Tables tab of the FrmMkr : Database dialog box.)

The USysRegInfo table is there but empty. Click USysRegInfo and then click Open. Add three records to the USysRegInfo : Table dialog box using values listed in the following table.

Subkey	Type	ValName	Value
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins\&Form Maker	0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins\&Form Maker	1	Expression	=EntryPoint()
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Menu Add-Ins\&Form Maker	1	Library	\|ACCDIR\MyAddins\FrmMkr.mda

Save the database and close Access. (Do not skip this step!)

Step 3: Install and Run the Add-In

Reopen Access and open a database.
On the Tools menu, point to Add-Ins, and then click Add-In Manager.
Click Add New, navigate to the MyAddins folder, highlight FrmMkr.mda, and click Open. Click Close to close the Add-In Manager.
Click Close to close the Add-In Manager.
In the Database window, click the Forms tab and click New, then click OK.
On the Tools menu, point to Add-Ins, and then click Form Maker.
In the three text boxes, enter values indicating the number of controls to add.
Click OK. Access adds a minimized Form2 to the Database window. Click the Restore button to view the new form.