Building Microsoft Access 95 Libraries and Wizards

by Dan Haught, Vice President of Product Development

• Preface
• Terminology and Concepts
• References
• Creating and Using Library Databases
• Guidelines in Add-In Development
• Making Your Library Database into an Add-In
• Converting Libraries and Add-Ins from Microsoft Access 2.0

Preface

When you ask most people what they think is the most important defining characteristic of Microsoft Access, they will answer "easy to use". Indeed, the product has re-defined the term for desktop databases. But underneath this slick interface is the power to extend Microsoft Access to almost any degree. This extensibility is provided through the mechanism of library databases and add-ins.

These tools make difficult tasks easier, automate repetitive operations, and add new functionality. Add-ins can increase productivity by focusing on a single task or function. You can design them to use yourself, to use within your organization, to distribute with your application, or to sell separately.

A library database is much like any other Microsoft Access database. It contains module code, tables, queries, forms and reports, and can be opened like any other database. The key difference that makes a database a "library" is the way you load it.

To get an idea of the power of Library databases, all you have to do is look at Microsoft Access 95 itself. Almost all of the wizards and builders in the product are implemented as library databases containing Visual Basic code and other database objects. A short list should impress you:

• Database Wizard
• Database Splitter and Analyzer
• Table, Query, Form and Report Wizards
• Add-In Manager
• Database Documenter

Terminology and Concepts

Microsoft Access supports a wide variety of add-ins. Each type has its own advantages and uses. Before you create your add-in, you need to decide which type of user interface you want to use. This decision affects how the user starts your add-in, and how you develop and install it. Microsoft Access offers three types of add-ins:

Wizards

A wizard handles complex operations. It usually consists of a series of dialog boxes which provide a step-by-step interface that guides the user through the process of creating an object. Wizards usually use forms, graphics and helpful text to shield the user from the technical intricacies of an operation. Microsoft Access form and report wizards are examples of this type of add-in. These applications guide you through the process of creating forms and reports.

Microsoft Access provides direct support for several types of wizards. This support enables the wizards you create to be available in the same manner as the Microsoft Access wizards. For example, if you create a wizard to design a specific type of form, your wizard can be installed to appear in the same list as the Microsoft Access form wizards. The types of wizards that Microsoft Access supplies direct support for are:

• Table and query wizards
• Form and report wizards
• Property wizards
• Control wizards

Builders

A builder is generally simpler in concept than a wizard. Builders usually consist of a single dialog box or form that assists the user in constructing an expression or some other single data element. The Microsoft Access Expression Builder, and the Command Button Picture Builder are examples of this type of add-in.

As with wizards, Microsoft Access provides direct support for many types of builders. When installed correctly, you builder appears with the list of Microsoft Access builders. The types of builders that Microsoft Access provides include:

• Property builders
• Control Builders
• Expression builders

Menu Add-ins

A menu add-in is a general purpose application or tool that accomplishes a task that doesn't fit into the wizard or builder categories. A menu add-in typically operates on multiple objects or on Microsoft Access itself. The Database Documenter and Performance Analyzer are examples of this type of add-in.

Menu add-ins are supported by Microsoft Access through the Add-ins command on the Tools menu. When you install your menu add-in, it's available to users through the Add-Ins submenu of the Tools menu. This means that menu add-ins are not generally context sensitive like wizards and builders. A wizard exists to aid the user within a specific context, such as form or query design. A menu add-in exists to perform a general function that might not fit within the context of the user's current operation.

This is not to say that a menu add-in cannot be context sensitive. For example, you might create a tool the aids the user in the process of form design, such as a tool that helps the user format controls on a form in design view. This add-in does not fit within the definition of the a wizard or a builder, but is nonetheless context-sensitive. In this case, you would implement your tool as a menu add-in.

Additionally, you integrate the functionality of add-ins into Microsoft Access through the use of library databases.

Library Databases

A library database is a collection of procedures and database objects that you can call from any Microsoft Access application. You can use libraries to store routines that you use often, so you don't have to write the same routine for each application you create. You can also use libraries to distribute new features to your users.

When you create an application, the objects and Visual Basic code in the application database work only within that application. For example, you can call functions in the UtilityFunctions module in the NorthWind sample database only from objects in that database-they aren't available from other databases. This structure works fine for objects and code that you use in only one application. However, you might find that you need functionality to be available from all your databases. This is achieved through the use of library databases.

A library database is structurally the same as any other Microsoft Access database. It can contain tables, queries, forms, reports and module code. The only real difference between a library database and regular database is that a library database is referenced by Microsoft Access directly. You do not open the library database, Microsoft Access does it for you.

References

All add-ins require access to one or more library databases. One of the more difficult to master aspects of library databases is referencing. Referencing means that Microsoft Access must know were to find the library database. By establishing a reference to a library database, you are letting Microsoft Access know where to look for the library database.

Under Microsoft Access 2.0, establishing a reference was easily accomplished by adding a key to the Microsoft Access Initialization File (typically called MSACC20.INI). For example, if you had a library database called C:\TOOLS\MYTOOLS.MDA, you could add the following line to the INI file:[Libraries]C:\TOOLS\MYTOOLS.MDA=rw

From there on, any database could access objects in that library database. The setting made the libraries modules global.

Types of References

Because of the architecture of Microsoft Access 95 and VBA, there is no longer a concept of global modules. In order to establish a reference to a library database, you have to learn some new techniques. References can be established in several ways:

• Creating a Library Reference
• Creating an Explicit Reference
• Creating a Runtime Reference

Creating a Library Reference

You can create a Library Reference by putting a reference to it in the [Libraries] section of the registry. This is essentially the same as putting it in the INI file under 2.0. However, doing this does not make the module global in the same way that Access 2.0. It only allows functions from the library database to be invoked as a menu add-in. If you need your database to be able to call functions from the library database, this method will not work.

Creating an Explicit Reference

Explicitly adding a reference that establishes a link between your database and the library. This method has the following restrictions:

• References are added at the database level. They are not global. This means that you need to add a reference to a library in every one of your databases that will call functions in the library database.
• References contain explicit paths. If the library is moved, the reference will no longer work.
• You cannot programmatically add a reference. They can only be added manually.

When you create an explicit reference, Microsoft Access stores the explicit path to the referenced database. However, by default, Microsoft Access will look in two other locations for the referenced library database:

1. If a key called RegLibPaths exists in the registry under Access\7.0, Microsoft Access will search this path.
2. If all else fails, Microsoft Access will search the Windows path for the referenced database.

To add a path to registry for Microsoft Access to search for referenced databases, add a key called REFLIBPATHS in:

HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/ACCESS/7.0/

Under this key, you can add the following string and value:

String Value

mydatabase.mda x:\path 

where mydatabase.mda is the name of your library database and x:\path is the full path to search in.

Creating a Runtime Reference

This technique establishes a reference at runtime using the Application.Run method. This method opens the explicitly named library database and executes the requested function. This method has the following restrictions:

• The library database must have an MDA extension
• It must be located in the directory specified by the AddInPath key in the registry. This string is located in:

HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/ACCESS/70/WIZARDS
(note that you could change this path, but then the built-in Microsoft Access Wizards will no longer work.)

The LoadOnStartup Key

You can add a key call LoadOnStartup to the following tree:

HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/ACCESS/70/WIZARDS 

By adding this key and adding your library database to this section, you can control how your library database code is loaded. This, in effect, causes the type information from your library database to be loaded when Microsoft Access is started. By placing your add-ins in this section, you can save approximately 10% of loading time. This works because 10% of your wizard's loading time will occur when Microsoft Access is started. This is only a time-shifting device and nothing more. It does not change the reference model described earlier.

Creating and Using Library Databases

Anytime you want to make a procedure or feature available to multiple applications, add it to a library database. This section discusses topics relating to the creation and use of library databases.

Creating Library Databases

Here are the basic steps for creating a library database in Microsoft Access:

Step One: Write the functions and create the objects

In a new database, write and debug the Visual Basic functions that you want to be available in the library. Design and create forms and other objects comprising the interface for any generic feature. Because the forms that make up the feature's interface and the Visual Basic functions that make it all work are stored in the library database, they're available in any database that has a reference to the library database.

Step Two: Load the database as a library database

You may want to rename your library database so that it has an extension of .MDA instead of the default .MDB extension. This is the convention used by Microsoft Access to allow users to easily identify library databases, but it has no effect on the structure or behavior of the database itself.

Debugging Library Databases

While you are creating objects and writing code in a library database, you open the library as a database an work in it as you would any other database. It's a good idea to get the objects in the library database working the way you want the do and to debug all Visual Basic code before you use the database as a library.

On the other hand, sometimes objects and code that work fine when you have the library database open as a regular database. However, when you load the database as a library, problems can occur with the way the library database interacts with the current database. Therefore, after loading the database as a library, you may still need to make some adjustments to your objects and code. If you have problems in your application that appear to be related to the behavior of a library you have referenced, close the current database you are working in and open the library database for testing and debugging.

Storing Custom Toolbars in Library Databases

IIf you want to store custom toolbars for your application in a library database and show and hide them by carrying out the ShowToolbar action from the library, then the library database must be loaded as an add-in. Loading a library database as an add-in is discussed later in this paper.

Guidelines in Add-In Development

As you develop your add-in, it's a good idea to follow some general guidelines. These guidelines will help you write, test and debug your add-in. They'll also make your add-in easier to use.

General Design

When you design an add-in, consider modeling its interface on the Microsoft Access built-in add-ins. After all, this interface is probably already familiar to the users of your add-in. There are several things you can do to give your add-in the same "look and feel" as the Microsoft Access add-ins. For example:

• Set the AutoCenter property of your add-ins' forms to Yes.
• Turn record selectors off.
• Turn scroll bars off, unless they are explicitly needed.
• Don't use Navigation buttons unless you are using a form that has multiple records.
• Place controls consistently on every form. For example, if you use multiple-selection list boxes in a report wizard to pick fields in which to sort and group, make sure that the list boxes appear in the same location in consecutive dialog boxes.
• Design you add-in's forms as dialog boxes. By making your forms with Dialog frames, you prevent the user from moving to the next dialog until conditions in your code are met.
• Consider using multi-page forms with [Back], [Next] and [Finish] buttons. This allows you to integrate a large part of your add-in's functionality in a single form.

Referring to Objects

An add-in database can contain any type of object that a regular database can. Because add-ins coexist with the current database, it's important to understand how to refer to objects. For example, when your add-in refers to a table of form, you must be sure that you are referring to it in the correct database.

When your add-in refers to an object, Microsoft Access uses the following rules:

• Forms and reports in the add-in are bound to data sources in the add-in's database. Microsoft Access always searches the add-in's database for a form or report's underlying table or query. If it doesn't find the underlying table or query, an error occurs. You can work around this behavior if you need to, by making the form or report unbound and using DAO to directly reference table or query objects in the current database. Alternatively, you can use the SQL IN clause to specify a specific database for data sources.
• When you refer to a macro, Microsoft Access first searches the add-in database containing the code that is running. If it doesn't find the macro there, Microsoft Access searches the current database.
• Domain aggregate functions such as Dlookup, DMin, and Dmax always refer to the data in the current database, not the library database.
• When using Data Access Objects, you can use CurrentDB(), or DBEngine(0)(0) to refer to objects in the current database, or CodeDB() to refer to objects in the library database.

Working in a Multi-user Environment

Microsoft Access always opens add-ins for shared access. This means that multiple users can use objects in your add-in.

Additionally, if your add-in needs to write back to its database, you must open the add-in with read/write permissions.

Making Your Library Database into an Add-In

There are several steps to convert your library database into a wizard, builder or add-in. This section explains the steps you should take to allow your add-in to be installed with the Add-In Manager. This tool takes care of updating registry entries for you.

Preparing your Library Database

When developing your own add-in (a wizard, builder, or menu add-in), you must set several database properties and create a USysRegInfo table in the add-in database so that it can be installed using the Add-in Manager.

Setting Database Properties

You must set various properties in your database in order for the Add-In Manager to use it correctly. To set database properties before you install your add-in:

1. In the Database window, click Database Properties on the File Menu.
2. Click the Summary tab (if it's not already selected).
3. In the Title, Company, and Comments boxes, enter values that provide information about your add-in.
4. Click OK to close the dialog box. This automatically saves the changes you made.

The USYSREGINFO Table

The USYSREGINFO table is used by Microsoft Access to identify how your add-in should be installed. Your database must contain this table for the Add-In Manager to work. The easiest way to create this table is to import the table from the WZTOOL70.MDA file that ships with Microsoft Access. You then modify the values in this table to match your add-in's needs.

First record: Creating the Registry key

The value in the Subkey field defines the names of the subkeys that will be created in the Windows Registry to register the add-in. This value must be the same for all records in the USysRegInfo table. The format of this entry depends on the kind of add-in you're installing. No matter what kind of add-in you're installing, for the first record, the value in the Type field must be 0 (which means "add this key"), and the ValName and Value fields must be left blank. The beginning of the Subkey entry can be either HKEY_CURRENT_ACCESS_PROFILE or HKEY_LOCAL_MACHINE. If you use HKEY_CURRENT_ACCESS_PROFILE and the installation is using a user profile (an alternate set of Registry keys that is invoked when Microsoft Access is started with the /Profile command-line option) the Add-in Manager will add the keys and values required to register the add-in in the user profile. If you use HKEY_CURRENT_ACCESS_PROFILE and a user profile is not in use, the Add-in Manager will add the keys and values required to register the add-in in the appropriate key below HKEY_LOCAL_MACHINE. If you use HKEY_LOCAL_MACHINE, the keys and values to register the add-in will always be written below HKEY_LOCAL_MACHINE.

Subkey format for control wizards, OLE custom control wizards, or builders

A control wizard is invoked from the toolbox when you click one of the control tools while designing a form or report. An OLE custom control wizard is invoked when you add an OLE custom control by clicking Custom Control on the Insert menu. A builder is invoked when you click the Build button next to a property box. To register a control wizard, OLE custom control wizard, or builder, the format for the Subkey field is:HKEY_CURRENT_ACCESS_PROFILE\Wizards\WizardType\WizardSubType\WizardName

The first part can be either HKEY_CURRENT_ACCESS_PROFILE or HKEY_LOCAL_MACHINE, as described earlier. The second part must be Wizards. Assuming there is no user profile in use, this will cause the Add-in Manager to write the keys and values to register the wizard or builder in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Wizards. WizardType defines whether the add-in is a control wizard, or a builder (called a Property Wizard in the Registry). OLE custom control wizards are registered as control wizards. WizardSubType defines the specific type of wizard. WizardName is the name of the key where the values to register the wizard or builder will be written. Note that WizardName must follow the Microsoft Access object-naming rules. The following table describes the possible values for WizardType and WizardSubType:

Subkey format for object wizards /font>

An object wizard is used to create a table, query, form or report. An object wizard is invoked by clicking its name in the corresponding New Object dialog box. When you are registering an object wizard, the format for the Subkey field is:

HKEY_CURRENT_ACCESS_PROFILE\Wizards\WizardType\WizardName 

The first part can be either HKEY_CURRENT_ACCESS_PROFILE or HKEY_LOCAL_MACHINE, as described earlier. The second part must be Wizards. Assuming there is no user profile in use, this will cause the Add-in Manager to write the keys and values to register the wizard in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Wizards. WizardType defines what kind of object wizard is being installed. WizardName is the name that displays in the corresponding New Object dialog box and is also the name of the key where the values to register the wizard will be written. The following table describes the possible values for WizardType and WizardName:

Subkey format for menu add-ins

A menu add-in is invoked by clicking its name on the Add-ins submenu of the Tools menu.

HKEY_CURRENT_ACCESS_PROFILE\Menu Add-Ins\MenuAddInName

The first part can be either HKEY_CURRENT_ACCESS_PROFILE or HKEY_LOCAL_MACHINE, as described earlier. The second part must be Menu Add-Ins. Assuming there is no user profile in use, this will cause the Add-in Manager to write the keys and values to register the menu add-in in

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Menu Add-Ins.

MenuAddInName defines the name that displays on the Add-ins submenu and is also the name of the key where the values to register the add-in will be written. To define an access key for the menu add-in so that you can use the keyboard to choose the menu add-in, type an ampersand (&) before the letter that you want to be the access key.

Second and subsequent records: Adding records to create values

The first record in the USysRegInfo table creates a key or keys used to register your add-in. Each record following the first record defines a value written to the last key in the subtree. The value in the Subkey field must be the same as the entry for the first record. The value in the Type field defines the type of the value created in the Registry: 1 to create a String (REG_SZ in Windows NT) or 4 to create a DWORD (REG_DWORD in Windows NT). The value in the ValName field defines the name of the value. The value in the Value field defines the value itself. The number of records you need to add depends on the type of add-in you are registering. The following tables define the records you need to add for each type of add-in.

Records required to define values for control wizards, builders, or OLE custom control wizards

Records to define values for object wizards

Records to define values for menu add-ins

Using the Add-In Manager

You can install or uninstall wizards, builders, and other add-ins using the Add-in Manager. Follow these steps:

1. On the Tools menu, point to Add-ins, and then click Add-in Manager.
2. The Add-in Manager dialog box is displayed. The Available Add-ins list displays the add-ins that are currently available. An add-in is already installed if there is an X next to its name. If an add-in is uninstalled, Microsoft Access removes the X but keeps the name in the list
3. Do one of the following:
   To add an add-in to the list, click Add New, and then specify its location.
   To install a currently available add-in, click the add-in name in the Available Add-ins list, and then click Install.
   To uninstall a currently available library database, click the add-in name in the Available Add-ins list, and then click Uninstall.
4. Click Close when you've finished.

Note that you should uninstall an add-in before opening it as a database to modify its design. Uninstalling add-ins can also improve response time in Microsoft Access.

You can no longer customize wizards using the Add-in Manager. In most cases, customization is now available as an option in the wizard dialog boxes.

Converting Libraries and Add-Ins from Microsoft Access 2.0

If you are converting a Microsoft Access 2.0 library or add-in, you should be aware of the following issues:

Converting the Database

As with any other database created with a previous version of Microsoft Access, you must run the Convert operation to convert the database into the Version 7 format. You must also update any objects not handled by the conversion process.

Referencing and Loading Library Databases

Before using a library database in Microsoft Access 95, you must establish a reference to the library database from each of your application that uses it.

Circular References

In Microsoft Access 95, you can't implement circular library references. In other words, once you have created a reference from Library A to Library B, you cannot create a reference from Library B to Library A.