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.”
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:
These guidelines are not absolute. Consider the purpose and interface of your add-in when evaluating these options.
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:
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.
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.
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 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.
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
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.
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.
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.”
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.