MS Access 97 Libraries, Wizards and Add-Ins

  • Article
Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

OFF413

Presented at Tech-Ed 97

Dan Haught

FMS Inc.

Dan Haught has been developing database applications and tools for more than ten years on a variety of platforms and has been using Microsoft Access since its pre-1.0 days. Dan manages product development for FMS in Vienna, Virginia, where he develops products for the Total Access line, and is the author of several books on Microsoft Access and theMicrosoft Jet database engine.

On This Page

Introduction
Terminology and Concepts
References
Programmatically Creating 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
Creating MDE Files
Conclusion

Introduction

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.

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

  • Builders

  • Menu add-ins

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

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, your builder appears with the list of Microsoft Access builders. The types of builders that Microsoft Access provides include:

  • Property builders

  • Control builders

  • Expression builders

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 is 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 that 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 a wizard or a builder, but is nonetheless context-sensitive. In this case, you would implement your tool as a menu add-in.

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 97 and Microsoft Visual Basic for Applications (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 Microsoft 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 may no longer work (see below).

  • 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 several other locations for the referenced library database:

  1. The directory in which the file that contains the reference is located.

  2. The directory in which Microsoft Access installed

  3. The windows directory

  4. The windows system directory

  5. Any directory in the Path statement in your AUTOEXEC.BAT file.

  6. If a key called RegLibPaths exists in the registry under Access\7.0, Microsoft Access will search this path.

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/OFFICE/80/ACCESS/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/OFFICE/80/ACCESS/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.

Programmatically Creating References

Microsoft Access 97 exposes new Reference objects that allow you to programmatically create and delete references. The sample files for this session include an add-in called the FMS Reference Manager. With this add-in, you can view, create and delete references.

Listing References

The following code is used to retrieve the current references into a table:

Function fBuildRefsTable_RL(rstRefs As Recordset) As Boolean
  ' Comments  : adds refs to the table
  ' Parameters: None
  ' Returns   : True/False - success/failure
  '
  Dim refTemp As Reference
  Dim varTemp As Variant
  For Each refTemp In Application.References
    With rstRefs
      .AddNew
        ' Reference objects don't have a properties collection
        ' (totally lame) so we have to check each on in code
        On Error Resume Next
        varTemp = refTemp.Name
        If Err = 0 Then
          !Name = varTemp
        Else
          !Name = "<error: " & Err.Number & ">"
        End If
        On Error GoTo 0
        On Error Resume Next
        varTemp = refTemp.BuiltIn
        If Err = 0 Then
          !BuiltIn = varTemp
        Else
          !BuiltIn = False
        End If
        On Error GoTo 0
        On Error Resume Next
        varTemp = refTemp.FullPath
        If Err = 0 Then
          !FullPath = varTemp
        Else
          !FullPath = "<error: " & Err.Number & ">"
        End If
        On Error GoTo 0
        On Error Resume Next
        varTemp = refTemp.GUID
        If Err = 0 Then
          !GUID = varTemp
        Else
          !GUID = "<error: " & Err.Number & ">"
        End If
        On Error GoTo 0
           On Error Resume Next
        varTemp = refTemp.IsBroken
        If Err = 0 Then
          !IsBroken = varTemp
        Else
          !IsBroken = False
        End If
        On Error GoTo 0
        On Error Resume Next
        On Error GoTo 0
        varTemp = refTemp.Kind
        If Err = 0 Then
          !Kind = IIf(varTemp = 0, "Typelib", "Project")
        Else
          !Kind = "<error: " & Err.Number & ">"
        End If
        On Error GoTo 0
        varTemp = refTemp.Major
        If Err = 0 Then
          !Major = varTemp
        Else
          !Major = "<error: " & Err.Number & ">"
        End If
        On Error GoTo 0
        varTemp = refTemp.Minor
        If Err = 0 Then
          !Minor = varTemp
        Else
          !Minor = "<error: " & Err.Number & ">"
        End If
      .Update
    End With
  Next refTemp
End Function

Adding References

The following code show how to programmatically add a reference:

Function fAddRef() As Boolean
  ' Comments  : adds a reference
  ' Parameters: none
  ' Returns   : true/false - success/failure
  '
  Dim refAdd As Reference
  Dim strName As String
  Dim fOK As Boolean
  Dim strPath As String
  Dim intSaveErr As Integer
  Dim strSaveErr As String
  Dim strMsg As String
  On Error GoTo PROC_ERR
  ' Assume failure
  fOK = False
  strPath = strGetOpenFile_RL("C:\WINDOWS\SYSTEM")
  If strPath <> "" Then
    If MsgBox("Add a reference to " & UCase(strPath) & "?", _       
              vbYesNo + vbQuestion) = vbYes Then
      DoCmd.Hourglass True
      On Error Resume Next
      Set refAdd = Application.References.CreateFromFile(strPath)
      intSaveErr = Err.Number
      strSaveErr = Err.Description
      On Error GoTo 0
      If intSaveErr <> 0 Then
        fOK = False
        Beep
        strMsg = "Could not add a reference to " & vbCrLf & _
                  UCase(strPath) & vbCrLf & "Error was: " & _ 
                  strSaveErr
        MsgBox strMsg, vbExclamation, gstrAppName_RL
      Else
        fOK = True
      End If
      DoCmd.Hourglass False
    End If
  End If
  fAddRef = fOK
PROC_EXIT:
  Exit Function
PROC_ERR:
  fAddRef = False
  Resume PROC_EXIT
End Function

Deleting References

The following code shows how to programmatically delete a reference:

Function fDeleteRef(strName As String) As Boolean
  ' Comments  : Deletes the named reference
  ' Parameters: strName - name of the reference to delete
  ' Returns   : true/false - success/failure
  '
  Dim refDelete As Reference
  Dim fOK As Boolean
  Dim intSaveErr As Integer
  Dim strSaveErr As String
  Dim strMesg As String
  On Error GoTo PROC_ERR
  ' Assume failure
  fOK = False
  strName = Me!lstRefs.Value
  If MsgBox("Delete reference to " & UCase(strName) & "?", 36) _
     = 6 Then
    DoCmd.Hourglass True
    Set refDelete = Application.References(strName)
    On Error Resume Next
    Application.References.Remove refDelete
    intSaveErr = Err.Number
    strSaveErr = Err.Description
    On Error GoTo 0
    If intSaveErr <> 0 Then
      fOK = False
      Beep
      strMesg = "Could not delete the reference to " & vbCrLf & _ 
                UCase(strName) & vbCrLf & "Error was: " & _ 
                strSaveErr
      MsgBox strMesg, vbExclamation, gstrAppName_RL
    Else
      fOK = True
    End If
    DoCmd.Hourglass False
  End If
  fDeleteRef = fOK
PROC_EXIT:
  Exit Function
PROC_ERR:
  fDeleteRef = False
  Resume PROC_EXIT
End Function

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 Microsoft 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 and work in it as you would any other database. It is a good idea to get the objects in the library database working the way you want them to and to debug all Visual Basic code before you use the database as a library.

On the other hand, sometimes objects and code 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

If you want to store custom toolbars for your application in a library database and show and hide them by carrying out the ShowToobar 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 is 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 your 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 is important to understand how to refer to objects. For example, when your add-in refers to a table or 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 Multiuser 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 is 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.

For complete information on this table and the data it contains, search Microsoft Access online help for USYSREGINFO.

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

    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.

  3. 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 97, you must establish a reference to the library database from each of your applications that use it.

Circular References

In Microsoft Access 97, 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.

Creating MDE Files

Microsoft Access 97 offers the exciting new capability of creating a "Compiled-Only" version of your database. This version (known as an MDE file) is a copy of your database, but it contains none of your VBA code in the source form. Instead, only the compiled version of the VBA code is included in the database.

If you have applications that use VBA code, you can benefit from this new feature. The process of creating an MDE does the following:

  • Creates a copy of your database

  • Copies all non-module objects to the new database

  • Copies the compiled form of your VBA code to the new database

  • Compacts the new database.

This offers the following benefits:

  • The MDE file is smaller since the source code is not contained in the database.

  • The MDE file should run faster, since it never can become decompiled.

  • The module code in the MDE file version is completely secure. Because there is no source code to hack into or steal, security is not an issue.

Once you have created an MDE file, the follow actions are disallowed:

  • Viewing, modifying, or creating forms, reports or modules.

  • Adding, deleting, or changing references to object libraries or databases.

  • Changing code using the properties or methods of the Microsoft Access or VBA Object models.

  • Changing the MDE database's project name using the Options dialog box.

  • Importing or exporting forms, reports, and modules to or from the MDE. Tables, queries and macros can still be manipulated.

It is important to note that when you make an MDE file, you still keep your original database for development work. Think of it this way: your original database is your source code, and the MDE version is the compiled executable.

Conclusion

As you can see from the topics covered here, Microsoft Access 97 and VBA introduce a number of changes that affect how you write library-based applications. And while some of the limitations of Microsoft Access 2.0 have been removed, the new referencing model introduces its own set of problems, and requires new approaches to distributing and installing wizards, builders, and menu add-ins.