Add-in Development Guidelines

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 sure the add-in is easy to use.

Note In addition to the guidelines presented here, you should be familiar with Microsoft Access optimization techniques. You want your end product to be as small, fast, and efficient as possible. For information on optimizing Microsoft Access and your add-in, see Chapter 13, "Optimizing Your Application."

General Design

When you design an add-in, consider modeling its interface on the Microsoft Access built-in add-ins. The advantage of doing so is that the user interface of your add-in will be consistent with that of Microsoft Access and thus already familiar to the user. There are several things you can do to give your add-in the same "look and feel" as the Microsoft Access built-in add-ins. For example:

Set the AutoCenter property of your add-in's forms to Yes.

Turn record selectors off.

Turn scroll bars off.

Turn the built-in navigation buttons off.

Place controls consistently on every form. For example, if you use multiple-selection list boxes in a report wizard to pick fields on 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 using dialog boxes, you prevent the user from moving to the next dialog box until conditions in your code are met. For example, you may want to prevent the user from moving past the last dialog box of your wizard until the user has entered all required data.

These guidelines are not absolute. Consider the purpose and interface of your add-in when evaluating these options.

Referring to Objects

An add-in database can contain any type of object: tables, queries, forms, reports, macros, and modules. Because add-ins coexist with the current database, it's important to understand how to refer to objects in your add-ins. 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. Microsoft Access always searches the add-in for a form or report's underlying table or query. If it doesn't find the underlying table or query, an error occurs.

If the object is a macro, Microsoft Access first searches the add-in containing the code that is running. If it doesn't find the macro there, Microsoft Access searches the current database.

Note You generally want to avoid using macros in your add-in. Macros don't offer the error checking and control that modules provide. Also, it's difficult to debug add-ins that use macros.

Domain aggregate functions such as DLookup, DMin, and DMax always refer to data in the current database. If the data referenced isn't in the current database, an error occurs. For this reason, avoid using domain aggregate functions in your add-in unless they refer to data sources in the current database.

Custom toolbars can be stored and retrieved in add-ins. When you use the ShowToolbar action to show and hide custom toolbars, Microsoft Access first searches your add-in and, if it doesn't find the specified toolbar, it then searches the current database.

Multiuser Environment

Microsoft Access always opens add-ins for shared access. This means that multiple users can use objects in your add-in without conflicts. If your add-in needs to write back to its database, you must open the add-in with read/write permissions. There are additional considerations if your add-in is to be used in a multiuser environment.

See Also For more information on multiuser considerations, see Chapter 10, "Creating Multiuser Applications."

If your add-in doesn't need to write back to its database or if it won't be used in a multiuser environment, it's best to load the add-in as read-only. This eliminates the need for Microsoft Access to maintain locks on the database.

International Support

If you want your add-in to support different language versions of Microsoft Access, you must write additional code to handle program differences. There are two main areas in which your application needs to handle differences between languages:

Menu items

Dialog prompts

Menu tree structures are the same across all language versions of Microsoft Access. However, the text of the menu items is in the language of the current copy of Microsoft Access. If your add-in uses the SendKeys action or the SendKeys statement to perform operations on menus, you must first determine the language version of Microsoft Access.

Also, if your add-in uses the SendKeys action or the SendKeys statement to respond to dialog boxes, you must take foreign-language differences into account. For example, Yes and No buttons in an English dialog box will be Ja and Nein buttons in a German dialog box.

Determining the Language Version

The following function returns the language version of the current copy of Microsoft Access.

Function GetLanguage () As String
   On Error Resume Next
   Err.Raise 3
   Select Case Err.Description
      Case "Return without GoSub":
         GetLanguage = "English"
      Case "Return sans GoSub":
         GetLanguage = "French"
      Case "Return ohne GoSub":
         GetLanguage = "German"
      Case "Return sem GoSub":
         GetLanguage = "Portuguese"
      Case "Return sin GoSub":
         GetLanguage = "Spanish"
   End Select
End Function

After you determine the current language version, your calls to the SendKeys action or the SendKeys statement can use the appropriate key combinations.

The following example uses the previous procedure to respond to a Yes/No dialog box according to the current Microsoft Access language version.

Select Case GetLanguage()
   Case "English"
      SendKeys "Y", True
   Case "German"
      SendKeys "J", True
      .
      .
      .
End Select

Using Localized Strings

A helpful feature in developing and maintaining internationalized applications is the use of translated, or localized, strings. By storing messages, warnings, prompts, and other strings in a central location, translation to new languages becomes much simpler. To use this technique, create a table or separate module to contain all your text messages. Using the table approach, assign a code or mnemonic to each message and store it as a single record. Using the module approach, define your strings as public constants.

When your code needs to display one of these messages, you retrieve the text from the central location and display it. You can see the advantage of this approach as you convert your application to another language. Instead of searching for all the hard-coded message strings in all of your modules, you can edit a single table or module, thereby greatly reducing the amount of time needed for translation.

Your decision about where to store localized strings depends on the speed and frequency with which your application needs to retrieve the strings. It's usually better to maintain your localized strings in a table. The advantages of storing the strings in a table are that you have a large amount of storage space and a way to logically group and sort strings. This approach uses less memory and is easier to maintain, but you also have the associated overhead of accessing the table every time your application needs a string.

The advantage of storing your localized strings as public constants in a module is that they are always available from memory, so their retrieval is very fast. The downside of this approach is that you can rapidly use up memory. Each message, no matter how long, will be stored in public memory, reducing the amount of memory available to your add-in and to Microsoft Access.

Note While localized strings are especially useful for internationalizing your application, this technique also has value in applications that are never translated to another language. By storing your strings in a central location, you make editing and maintaining your application easier. Any time a change needs to be made, you know exactly where the strings you want to edit are located.

Checking the Environment

Microsoft Access provides great flexibility in configuring the database environment. You can change default behavior in a variety of ways by using the Options command (Tools menu). Your user may have changed these settings in a way that can cause your add-in to fail. To minimize the potential for these situations, your add-in should check its environment as soon as it's loaded.

Potential Problems with SendKeys

One of the greatest areas for potential problems lies with the SendKeys action or SendKeys statement. Because the current database may have assigned keys by using an AutoKeys macro, your SendKeys action may cause unexpected results. For example, if you count on CTRL+C to always perform a Copy operation, your add-in will fail if the user's database has this key sequence remapped to another action. Disastrous results can occur if your add-in tries to copy a value to the Clipboard, but instead deletes a user's table. Additionally, the user can use the Options dialog box (Tools menu) to change the behavior of the arrow keys. If your add-in uses the SendKeys action or the SendKeys statement, changing the behavior of the arrow keys can cause unexpected behavior.

The simplest way to avoid these problems is to avoid the SendKeys action and the SendKeys statement. You can replace almost all SendKeys actions and SendKeys statements with the RunCommand action. In the rare case when you need to use SendKeys actions or SendKeys statements, be sure to check the user's database for an AutoKeys macro. If one exists, you may want to warn the user that unpredictable results can occur if they use your add-in. If you are relying on the default behavior of the arrow keys, you should also check the user's Arrow Key Behavior option setting on the Keyboard tab of the Options dialog box (Tools menu).

You can check for AutoKeys macros by using a combination of the GetOption method of the Application object and the Scripts Container object. Use the GetOption method to get the name of the user's AutoKeys macro, and then search the Scripts Container object for a macro of that name. If you find one, you should assume that the keys your add-in "presses" can cause problems with the user's database.

See Also For more information on the GetOption method or the Container object, search the Help index for "GetOption method" or "Container object."

Modifying the Environment

In general, your add-in should not modify the user's Microsoft Access environment, such as the availability of toolbars, the status bar, and other settings the user can change in the Options dialog box (Tools menu). However, there may be cases in which your add-in needs to change one or more of these options for optimum performance. You can use the SetOption method of the Application object to change options. For example, to prevent the status bar from displaying information as your add-in performs its work, you can turn the status bar off when your add-in starts.

Application.SetOption "Show Status Bar", False

If you do change option settings, be sure to restore the user's settings before exiting your add-in. For example, the preceding code doesn't save the user's settings. The following example saves the user's settings by using the GetOption method of the Application object:

Public intShowStatusBar As Integer
intShowStatusBar = Application.GetOption ("Show Status Bar")
' Change setting of status bar.
Application.SetOption "Show Status Bar", False
' Restore user's settings before add-in finishes.
Application.SetOption "Show Status Bar", intShowStatusBar

See Also For more information on setting options by using Visual Basic, search the Help index for "GetOption method" or "SetOption method."

Note You should also plan to restore the user's option settings if your add-in encounters a nonrecoverable run-time error. By using a public error-handling routine, you can make sure your add-in always restores the user's environment, even in the event of an unanticipated termination of your add-in. For more information, see "Implementing Error Handling" later in this chapter.

Casa de Bender