Presented by: 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 the Microsoft® Jet database engine.
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.
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:
Additionally, you integrate the functionality of add-ins into Microsoft Access through the use of library databases.
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:
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:
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.
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.
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.
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
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:
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:
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 run-time 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:
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.)
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.
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.
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
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
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
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.
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.
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.
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.
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.
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:
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:
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.
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.
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.
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:
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 needs.
For complete information on this table and the data it contains, search Microsoft Access online help for USYSREGINFO.
You can install or uninstall wizards, builders, and other add-ins using the Add-in Manager. Follow these steps:
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.
If you are converting a Microsoft Access 2.0 library or add-in, you should be aware of the following issues:
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.
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.
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.
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:
This offers the following benefits:
Once you have created an MDE file, the follow actions are disallowed:
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.
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.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft and Visual Basic are registered trademarks of Microsoft Corporation.
Other product or company names mentioned herein may be the trademarks of their respective owners.