In the previous seven chapters, I've given you a wealth of techniques for creating well-structured databases, code routines, and forms. Taken individually, these techniques are useful, but taken together they are an "application." In this chapter, I will show you how to combine your Access components into a solid, reliable application by using a design concept that I call "bulletproofing." Bulletproofing is a set of guidelines and techniques that include the following:
Start Access Up
"From time to time, I like to visit Access events to see what folks are saying about our products. Stan's been giving a speech called "Bulletproofing Access Applications" for quite some time, so in mid-1994 I stopped by his session at a developer's conference to see what was up. In his speech, Stan revealed the nuts and bolts of taking a "90 percent finished" Access application and bringing it up to a deployable state.
"As Stan was talking, I noticed all the steps we made folks go through to get an application ready to ship. The steps weren't complicated-designate a form as the main menu, create an AutoExec macro, write some code that hides the Database window, and the like-but they were tedious. That's when I came up with the idea of the Startup properties dialog box for Access 95, which is nothing more than a simple way of declaring how you want to "bulletproof" your application. Best of all, the properties can be applied with no coding effort. "Perhaps I should have called it the Bulletproofing properties dialog box instead, but then I might have had to pay Stan a royalty!"
David Risher, Access Product Team Manager
If you are responsible for data and need to have control over its quality, Access is a dangerous product. Because your users can enter, update, and query important business data quickly and inexpensively, they get comfortable with the product. When they get comfortable, they lose their sense of fear and respect, and without the proper sense of seriousness, users can get sloppy. Because an Access application brings your users into very close contact with your crucial data, sloppy people can easily introduce "bad" data into a system or delete data or even delete objects! So, when you give users the power to enter, edit, and query data, you also must give them one of the following powers as well:
Many companies mistakenly calculate that the first option is more expensive because custom development may cost tens of thousands of dollars. The reality is that the second option is usually more expensive for the following two reasons:
Consequently, an application can often protect important data more cost effectively than good training in the use of Access. This is true, but not automatically true because a poor application is no better than none at all. Only a solid application is a better investment than training.
I am in no way downplaying the importance of training. Instead, I am saying that a particular dataset, deployed in an environment with a fixed and limited budget, will benefit more in terms of data quality and protection from a good application than from the same amount of money spent on training. Consider this question: "Is it more important that our users know how to use Access, or how to manage our inventory data?" With a well written inventory application in place, most users will neither know nor care that Access is the engine.
As David Risher noted in the introductory quote to this chapter, creating a solid application (bulletproofing) takes a 90 percent complete application and brings it from a code-complete state to a user-ready state. What David failed to note is that setting a few of the new Access properties is not enough to make an application totally bulletproof by my definition. Instead, a developer must apply a rigorous methodology to hunt down and eliminate areas in an application where users can experience confusion or problems. This methodology is the essence of bulletproofing.
Many bulletproofing concepts are quite simple, but in my experience, they are often overlooked in the rush to ship an application-or because of budget constraints. Once developed and formalized, the bulletproofing strategy described in this chapter can be easily applied to all new applications.
You can find most of the examples in this chapter on the CD-ROM in the file AES_BPRF.MDB. You will also find BULLETPF.DOC, a set of guidelines for application bulletproofing that you can print and use during development.
Philosophically, bulletproofing assumes that database application users are most productive-and application development projects are most successful-when the application provides an organized and controlled environment layered over the data. Although the objective in application development is to put the power in the hands of the user, that power should be tempered by an interface that organizes the work flow, guides a user through the application in a directed manner, and validates important procedures. This approach minimizes the impact of a user's curiosity, inexperience, or improper keystrokes, and consequently protects the underlying data.
Bulletproofing safeguards data by controlling a user's access to it. It also presents an ordered and professional application interface to the user.
At the simplest level, an Access application consists of a set of base tables, some queries to group and sort the table data, entry/edit forms, reports, and perhaps some macro routines. I call such a collection of objects an "unstructured application" and do not consider it an expert solution. Instead, true applications are more than a collection of objects in an MDB file. Interface objects must be added to enhance usability, and Basic code routines must be provided to organize the application flow and perform repeated tasks reliably.
In an unstructured application, where users can roam the Database window, a user can easily edit or delete vital records directly or possibly open and redesign database objects. For most installations, this model shifts too much power and risk from the developer to the end user. A bulletproof application, on the other hand, does not expose the user to data directly or to design tools. Figure 17.1 shows the conceptual difference between these two application types.
This conceptual diagram compares an unstructured application to a bulletproof application.
Bulletproofing is the process of pulling together all the pieces of the application to which you've applied your expert solution techniques individually and "shrink-wrapping" the pieces inside a protective coating that makes the application more robust and the data better shielded. The protective layer you apply is simply the implementation of some basic features that enable the application to escort the user safely through the maze of its own data and objects.
Before you finalize your application by bulletproofing it, make sure that it is complete in other respects and that you test the individual components. After you add the protective bulletproofing features, you must perform final testing on the application.
At its most basic level, a bulletproof application survives the following tests:
For applications to satisfy such tests and qualify as bulletproof, I use the following ten guidelines during the application development process:
This chapter makes the assumption that a bulletproof application guides the user, protects the data, and collects status information. It does not assume that even the best application can teach a worker how to do a job he or she does not know or understand. Sometimes, users are thrown at your application with not only a lack of knowledge of the application but a lack of knowledge of the workflow it automates as well. In such a scenario, the bulletproof application particularly shines because an untrained or unskilled worker is the most dangerous of all, and your automated solution needs to help these people get their work done with a minimum of mistakes and frustrations while protecting critical business data. A bulletproof application can never fully compensate for a skills deficit, so make certain that your users understand their job "sans application" before they sit down in front of your application.
I discuss techniques for each of these guidelines in the sections that form the remainder of this chapter. Although some of the techniques are simple to understand and implement, building a truly bulletproof application requires some extra development effort and an intermediate-level of Access development skills. Those financing a development project sometimes see this final layer of "polish" on an application as unnecessary, so be wary that you may have to sell the concept on its merits, which I've detailed throughout this book.
To justify the cost of the bulletproofing effort to project financiers, compare the cost of adding the features in this chapter with the cost of recovering and reentering lost data in this scenario: Near the end of the day, a user working in a table datasheet on a production version of the database accidentally selects all the records in a table and presses the Del key.
You've noticed that when I talk about bulletproofing, I don't automatically assume that the runtime is being used. Thus, the following two questions probably arise in your mind at this point:
In a poorly constructed application, the runtime mode will not prevent the random deletion of an object that you can't even see on the screen.
You can see that good bulletproofing boils down to being able to say, "Users can use my application with full Access or with the runtime option, and in either case the application is solid and the data is protected."
Table 17.1 summarizes some of the features that the runtime adds to an application and shows how bulletproofing provides the same protection even when it runs in full Access.
Table 17.1 Runtime Features Matched to Features Provided by Bulletproofing Techniques
|Runtime Feature||Bulletproofing Technique|
|Some menu options are not available.||Implement a custom menu bar and shortcut menu for the application or each form.|
|Built-in toolbars are not available.||Implement a custom toolbar for the application or each form.|
|Some keyboard shortcuts are not available.||Use Startup properties, AutoKeys macros, and event code to trap keystrokes.|
If your application needs to know whether it is running in the Access runtime, your code can test the result of the SysCmd() function as follows:
blnRet = SysCmd(acSysCmdRuntime)
This function returns True or False depending on whether the application is running in runtime mode. Runtime mode is provided by the actual runtime executable as well as by full Access opened with the /RunTime command-line argument.
Starting with Access 97, you are now able to "lock up" the source code and structure of application objects before distribution without employing database security. When you use the menu option Tools, Database Utilities, Make MDE File..., Access creates a copy of your database with an .MDE file extension and does the following to the file:
The MDE file is now a fully functional application file, but without the ability to:
The restrictions that apply to MDE files as previously listed apply from both the Access user interface and from VBA code. An MDE file's code cannot execute these tasks either.
You can add, change, delete, import, and export tables, queries, and macros from within an MDE file.
How does an MDE file compare with an application shipped with the runtime? The runtime environment is a limitation placed on the Access executable, not the database file, while Make MDE... places limitations on a specific file. An application created for the runtime will still work in full Access, but it will work differently there (design views and full menus are available). In contrast, an MDE file's objects are locked in either environment.
Use Access's MDE capabilities to create a smaller (no source code), faster (can't decompile), protected (objects and code cannot be changed) version of your bulletproof application when development is complete.
Access has no ability to rebuild an MDB file from an MDE file. Do not lose the source file from which an MDE is created or your application can never be debugged or changed. Each time you need to enhance the application, do so in the source MDB file and then make a new MDE from it when deployment time arrives.
In Windows 95 and NT 4, you can launch your Access application using any of the methods you use to start other programs, including from the Start menu, from a desktop shortcut, or from the Office Shortcut Bar. In Windows NT 3.x, you can create a program item in Program Manager for your application. In either case, the last thing you want your users to do is to double-click directly on the application's MDB file. This approach has several problems associated with it, as follows:
For this same reason (reliance on registration), it is sometimes not a good idea to have the command line in a shortcut point only to an MDB file without specifying the location of the Access executable.
Instead of requiring the user to hunt for the application, always create a shortcut or icon that specifically points to the copy of the Access executable that you want your application to launch (see Figure 17.3).
An Access application shortcut is displayed on the desktop with the properties showing.
Pay attention to the graphic image used as your program icon. Although it is tempting to use the Access icon or some icon readily available from Windows, the following are my two rules of thumb:
At the least, your bulletproof application requires the following two graphic images:
If you do not have the tools to create icon files (ICO), assign a Windows bitmap file(BMP) to your shortcut instead of an icon. You can do this in Windows 95 even though its Change Icon dialog box does not show bitmaps as an option in the Files of type filter combo box. Note the following two special considerations:
- Windows 95 may add an odd shadow to your bitmap if you saved it as 16-color bitmap from Paint; instead, use the 24-bit bitmap format when creating your own desktop bitmaps.
- Windows 95 clips off the outside pixel when displaying a shortcut bitmap (it does not do this with icon files). To compensate, make your bitmap no larger than 31 by 31 pixels.
Even if you have the tools to create a Windows 95 format icon file (one that contains both 16-by-16- and 32-by-32-pixel icons), Access does not use the 16-pixel icon for the application icon. Instead, it takes the larger icon and shrinks it to 16 by 16 pixels. (Unfortunately, this flaw has carried forward from Access 95 to 97.) This means that you may decide to create a different icon for the application than the icon you used for the shortcut because the application icon will be designed to look best when reduced rather than when full size.If you think you can cheat Access by creating a 16-by-16-pixel bitmap instead, you are unfortunately mistaken. It is possible to assign a 16-by-16-pixel bitmap file to the application icon property even though the Icon Browser dialog box does not show bitmaps as an option in the Files of type filter combo box. Access makes some color changes to the bitmap, however, that it does not apply to an icon file. This usually makes the bitmap appear less attractive than its icon alternative.
In addition to these small graphics, you can create a larger graphic to use for the application's dialog boxes and switchboard menus if you so desire. Figure 17.4 shows the Pencil Pushers sample application in this book, highlighting the application graphics assigned to it.
The Pencil Pushers main graphic is shown with its matching icon.
Numerous shareware applications for creating icon files are posted on CompuServe, the Microsoft Network, and elsewhere. If you choose to use bitmap files instead, you can create them with Paint, which ships with Windows 95.
When your application is launched, Access enables certain optional command-line arguments that your shortcut can pass to Access and the application. You can add command-line options to your shortcut (or Program Manager item) following the path and file name string that loads Access. Listing 17.1 shows the syntax for starting Access with command-line options.
Listing 17.1 The Syntax for Access Command-Line Options
\\path\MSACCESS.EXE databasename /Compact
/Convert targetdatabase /Excl /NoStartup /Profile userprofile
/Pwd password /Repair /RO /RunTime /User username /X macroname
/Wrkgrp workgroupfile /Cmd commandstring
The /NoStartup, /Profile, /RunTime, and /X option switches were introduced with Access 95.
The following list describes the command-line options that I think are important when you bulletproof an application:
If Command() = "Browser" Then
' Do something
You can abbreviate the /Cmd string in your command line using a semicolon, as in the following example. However, I prefer to use the longer syntax, so that the command line is more readable and self-documenting. \\path\MSACCESS.EXE databasename ;commandstring
When testing an application that expects a variety of values passed in from the command line, you can test your code without repeatedly changing the shortcut command line and restarting Access. The trick is to set the Command-Line Arguments option in the Advanced tab of the Options dialog box to mimic a string passed in to the application via the /Cmd option.
If your application is created for a single user, opening an Access file for exclusive use adds up to a ten percent performance improvement for the user.
varRet = SysCmd(acSysCmdProfile)
If your user opens the application file with File Open... in full Access, the AutoExec macro will still run (if available) instead of the custom macro you designated in the shortcut with the /X argument. To trap this situation, you can include a /Cmd string in your shortcut and include code in your startup form to check the Command() function for this string. If the string is not found, the user has not started the application from a shortcut and your code can force an exit.
The name of the workgroup information file was changed from SYSTEM.MDA to SYSTEM.MDW beginning with Access 95. Your code can detect the path and file name of the specified workgroupfile by using the SysCmd function as follows: varRet = SysCmd(acSysCmdGetWorkgroupFile)
These following three command-line options are not used directly to launch a bulletproof application, but may still have certain useful implementations:
The following list summarizes my tips for using command-line options:
Access 97 now includes the ability to create a bitmap graphic used as the application "splash" graphic-a form or graphic that displays before any application menu or dialog box. When you use the databasename argument in your shortcut, Access looks in the same directory as the specified database for a Windows bitmap file with the same root file name as the database and a BMP file extension (for example, PENCIL.BMP in the same directory as PENCIL.MDB). If Access finds a bitmap with the appropriate name, Access displays the graphic at startup instead of showing its internal splash graphic.
The positive aspect of this feature is that a graphic is displayed very soon in the application load cycle-quite a bit sooner than a splash screen supplied by a form in your application. (Your bitmap is actually drawn before Access has completely loaded into memory.) Thus, your users' perception of the load time of the application is favorably influenced because they see feedback almost immediately after launching the application. A second positive aspect of this feature is that the Access splash screen is replaced with your own, which enables the application to appear more "customized" to the user by hiding the programming platform.
Your graphic is only shown while Access finishes loading itself into memory, which happens at different speeds on different machines. You cannot predict or control how long this graphic will be displayed.
On the negative side, the bitmap graphic does not show dynamic version or registration text or other information your application supplies-the bitmap's content is fixed and not programmable. Thus, using a bitmap does not reduce the need for a standard application splash screen coded as an Access form in your application.
Because the bitmap is stored as a file on disk and not inside the application file, it is not protected by any security in your application. Users can alter or remove the bitmap if they choose to.
How, then, do you best use this feature? I prefer to have Access display a startup graphic and still show my application splash screen because the two options are not mutually exclusive. I use the application graphic (which I call an "early splash" screen) to note that the application is starting up, and I use a fun graphic such as a stopwatch or hourglass for this effect. When Access finishes loading, I show the actual application splash form as the first form the application displays. Because I can
control the length of time the application splash form is displayed, I place any text information there (such as a copyright notice or version information).
Figure 17.5 shows an early splash graphic that tells the user to wait while the application is loading.
I have included on the CD-ROM several bitmap files that we use as early splash graphics. You can use these files-ESPLASH1.BMP through ESPLASH6.BMP-in your own applications.
An early splash screen is a bitmap file with the same name and location as the application database file.
When users open your application, you want the application code to be in charge, not the user. In previous versions of Access, you created AutoExec macros to begin executing application code when each database file was opened. This technique is still useful and available in Access, but much of the work previously performed by AutoExec macro actions and the code they call can now be done by Access's new Startup options. Startup options not only control how the application appears when it is opened but what kind of interaction is allowed between the application's users and underlying Access features.
Wishes Can Come True
Frustrated by some of the shortcomings in the configuration options of earlier versions of Access, I had been writing and speaking on the subject of application bulletproofing since 1993. Over the years, various members of the Access team at Microsoft attended my presentations. Thus, when I first saw the Startup dialog box in Access 95, which solved many of the shortcomings in Access that I had highlighted in my speeches, I felt somewhat responsible. Later in the beta cycle, Microsoft acknowledged that my tricks in the bulletproofing sessions and articles had influenced them to design Startup options (see the introductory section of this chapter).The point of this story is that the Access team at Microsoft, despite all their great ideas and momentum, doesn't home-grow every innovation-they do take notice of what people outside the company are saying about their product. If you have a suggestion on how to improve Access, don't be silent-you can phone your idea in to Microsoft at 206-936-WISH or fax it to them at 206-936-7329. If you submit a product "wish," there's a good chance that it will bereviewed by someone on the Access team. (I know because I wrote the database that manages the product wish list for Microsoft!)
In a database, choose Tools, Startup... to display the Startup dialog box, as shown in Figure 17.6. Options set in this dialog box are applied each time the database is opened with full Access or the runtime option.
Startup options are saved with the database file and travel with it wherever it goes-unlike the user preferences set in the Options dialog box, which are saved to the workstation and not the database.
The Startup options dialog box with the advanced options is displayed.
In a bulletproof application, you will want to set each Startup property to tightly control the initialization and use of your application.
Startup options are applied before the AutoExec macro or a macro run with the /X command-line option is executed. If you use a combination of Startup options and an initial macro, be careful about any event collisions that might occur if they both open forms or initiate processes that could overlap. Specifically, you can set the Display Form startup option to show a form when the application is launched. This form may contain code. After the form is opened and the code begins running, Access will begin execution of any AutoExec macro actions it finds in the database. Running your macro actions and form code in parallel may generate an error.
The following list details the options in the Startup dialog box and my suggestions for using each option:
You can display your own toolbars instead of those built in to Access by using the ShowToolbar method from form and report code:
DoCmd.ShowToolbar "tbrPencilMain", acToolbarYes
Clearing this option is only useful if you also clear the Allow Built-in Toolbars check box; otherwise, a user can still access design options via the toolbar buttons even if the menu options are restricted.
Clearing this option also disables the View, Toolbars command from the built-in menus.
The Startup option, Allow Viewing Code After Error, controls the display of the Debug button in this dialog box.
You should use the same icon or bitmap for the Application Icon property that you used when you created a shortcut to start the application.
The specified icon is not saved inside the Access database file-only the file path is saved. The users who receive the application file from you must have the icon file in the folder on their machines that matches the file path saved in this property. Alternately, your application can include code to search for the icon, as shown in the example procedure lci_AppIconCheck in the database AES_BPRF.MDB on the CD-ROM.
This application's icon and title are defined by Startup properties.
I mentioned in the section "Displaying an Early Splash Screen" earlier in this chapter that you may want to create a splash form that loads when your application starts. If you set the Display Form option to the name of your splash form, it will be the first form the user sees when the application has finished loading. You can place an OnTimer event in the form that triggers after three or four seconds, closes the splash form, and opens the main switchboard. Figure 17.9 shows the splash form displaying for the Pencil Pushers application.
This splash form for the Pencil Pushers application is displayed by the Display Form setting.
If you do not clear the Use Access Special Keys option when using the Menu Bar option, your user will be able to switch between your designated menu bar and the menu of Access by using the Ctrl+F11 key combination.
When the ShortcutMenuBar property is set for a form, the designated shortcut menu will still be displayed even if you clear the Allow Default Shortcut Menus check box in the Startup dialog box.
Table 17.2 Access Keystrokes Disabled by the Use Access Special Keys Startup Option
|F11, Alt+F1||Shows the Database window when hidden or brings it to the front of the window stack when displayed.|
|Ctrl+G||Shows the Debug window when the user has dropped into application code by pressing Ctrl+Break or by clicking the Debug button in the Access runtime error alert.|
|Ctrl+F11||Alternates the menu bar between your custom menu bar for an object and the Access built-in menu bar for the object.|
|Ctrl+Break||Halts code that is executing and displays the Module window with the current line of code highlighted.|
If the application database is secured, revoke the Administer permission from the Database object for any users who should not be able to change Startup properties. (Without this permission, a user also cannot replicate the database and cannot create a database password for the file.)
All the Startup options previously listed are actually stored in your database file as properties of the Database object. You can read these property values from your application code when you need to detect the setting of an option and you can set them from Basic using code like this:
Dim dbs As Database
Set dbs = CurrentDb
dbs.Properties("AllowFullMenus") = False
A likely scenario that you would set Startup properties of a database from code occurs when you create a new database using program code and want to create certain default options for it. You might also want to be able to detect a current application setting by reading a Startup property. Table 17.3 lists all of the Startup dialog box options and the Database object property name that is used to store each option.
Changes to Startup properties, whether through the interface or by code, do not take effect until the database is closed and reopened, with the exceptions noted below.
Table 17.3 Startup Properties as Database Properties
|Startup Dialog Box Option||Database Property|
|Allow Built-in Toolbars*||AllowBuiltInToolbars|
|Allow Default Shortcut Menus*||AllowShortcutMenus|
|Allow Full Menus*||AllowFullMenus|
|Allow Toolbar Changes*||AllowToolbarChanges|
|Allow Viewing Code After Error*||AllowBreakIntoCode|
|Display Database Window*||StartupShowDBWindow|
|Display Status Bar*||StartupShowStatusBar|
|Shortcut Menu Bar||StartupShortcutMenuBar|
|Use Access Special Keys*||AllowSpecialKeys|
* None of the properties in the table exist as database properties for a new database. Starred items indicate properties created by Access the first time the Startup dialog box is used. I will divulge the importance of this information later in this current section.
Table 17.3 includes a Startup property, the AllowBypassKey property, that is not displayed in the Startup dialog box. This property determines whether the user can override Startup options and/or the AutoExec macro by holding down the Shift key when opening the database. This property can only be set from code, and you should only set it after you have completed development and testing and are preparing to deploy the application.
In a bulletproof application, once you have set the AllowBypassKey property to False, you can no longer override the Startup properties and design application objects. You may want to leave yourself a "backdoor" that helps you to re-enable this property so that you can further develop the database in the future.
One backdoor I use is to create an shortcut to unlock an application and install it on developers' workstations only. The shortcut has a special string in the /Cmd option of the command line, for example: \\path\MSACCESS.EXE databasename /Cmd AllowShift This command line passes the phrase AllowShift to the Access Command() function. I then add code to my application's startup form to check for the special string (AllowShift) in the Command() function and to set the AllowBypassKey property to True if the string is found. Because property changes don't take effect until the database is closed, my application code then closes the database after setting the property. A developer can then reopen the unlocked application while holding down Shift, which again provides access to the built-in menu system.
Startup properties are Access interface constructs as opposed to DAO constructs and do not actually exist in the Database object in Jet until Access determines that they should be stored there. The point at which this happens is not uniform. When you create a new database, none of the properties in Table 17.3 exist in the Database object's Properties collection. After you open the Startup dialog box and set at least one property (any one), the starred (*) items in the table are all created as database properties and persist for the life of the database.
The non-starred items (corresponding to the combo box options in the Startup dialog box) are created as properties on a case-by-case basis, however. In other words, each property is created when the property is set via the interface or code, and each only persists as long as the property has a value, after which it is deleted from the Database object.
The upshot of this confusing situation is that you can set and retrieve Startup properties from code, but you need to make certain that the property has been added to the Database object's Properties collection first or an error will occur. The code in Listing 17.2 shows how to test for the existence of a Database property and to create it if necessary. The function shown uses the example of setting the application title bar caption dynamically.
Although most Startup property changes do not take effect until the database is closed and reopened, you can repaint changes to the application title or application icon immediately with the RefreshTitleBar method on the Application object.
Listing 17.2 AES_BPrf.Mdb-How to Set a Startup Property from Code
Public Sub lci_AppTitleCheck()
' Purpose: Try to locate and set the application title
' Calls: lci_IsBlank
On Error GoTo lci_AppTitleCheck_Err
Const cintErrPrpNotFound As Integer = 3270
Const cstrProc As String = "lci_AppTitleCheck"
Dim dbsCurr As Database
Dim prpTitle As Property
Dim strTitle As String
Set dbsCurr = CurrentDb
' If the property does not exist, the next line jumps to _Err
strTitle = dbsCurr.Properties("AppTitle") ' Get the title
dbsCurr.Properties("AppTitle") = strTitle ' Set the title
Application.RefreshTitleBar ' Repaint the screen to show the change
' Trap the nonexistence of the property
If Err.Number = cintErrPrpNotFound Then
' Create the property in the database
If lci_IsBlank(strTitle) Then ' If none specified, use the constant
strTitle = lci_gcstrAppTitle
Set prpTitle = dbsCurr.CreateProperty("AppTitle", dbText, strTitle)
Call lci_ErrMsgStd(cstrProc, Err.Number, Err.Description, True)
Access has greatly improved your ability to control how much interaction the user has with your database objects outside of your application's interface. Additionally, testing an application is easier now that you can use the /RunTime option to tell full Access to emulate the runtime product.
If you start an application without the /RunTime argument in full Access and the user has enabled the option Break on All Errors in the Options dialog box, this option setting will override any error trapping in your code. This situation is dangerous for you because your carefully crafted error traps are ignored. To protect your application from this situation, consider issuing the following line of code as the application starts: Application.SetOption "Break on All Errors", False Because you are changing an interface option that may be important to the user, you may want to store the initial setting in a variable before changing it and set the option back to its original state as your application closes. Also, note that if you are a developer testing an application, you may be using Break on All Errors as part of your debugging effort and choose not to disable the option.
Table 17.4 summarizes my recommended settings for Startup options in a bulletproof application. Note that I recommend that you disable almost every user feature listed in the Startup dialog box.
Table 17.4 Recommended Startup Options for a Bulletproof Application
|AllowBypassKey||False||Not available in the Startup dialog box; must be set from code|
|Allow Viewing Code After Error||False||Disables the Debug button from the Runtime error alert.|
|Allow Built-in Toolbars||False||Removes access to the standard toolbars.|
|Allow Toolbar Changes||False||Removes the ability to change built-in and custom toolbars.|
|Allow Full Menus||False||Removes design actions from the built-in menu bar.|
|Allow Default Shortcut Menus||False||Removes built-in shortcut menus.|
|Application Icon||iconname||Specifies an icon file to represent the application.|
|Application Title||titlestring||Displays the application name in the Access window title bar.|
|Display Database Window||False||Hides the Database window from the users.|
|Display Form||formname||Displays the named form first.|
|Display Status Bar||True||Displays the Access status bar.|
|Menu Bar||menuname||Names the default menu bar to display when no other menu bar is designated.|
|Shortcut Menu Bar||shortcutmenuname||Names the default shortcut menu bar to display when no other shortcut menu bar is designated.|
|Use Access Special Keys||False||Disables keystrokes that bypass the application interface layer (see Table 17.2).|
Startup options are an excellent new device for configuring the usability of your custom application, especially an application that may run in full Access instead of the runtime, where all control of the interface must be provided by the application.
In most data-centric applications, users are expected to login (or logon) to the application by providing a name and password to validate their right to enter the system. If your application uses Access security, Access provides the login dialog box when the application starts. If the application is not secured, however, consider providing a login dialog box through your application for the following reasons:
If your application uses Access security, Access provides a standard login dialog box. After login, the user name is available to your application via the CurrentUser() function, as in this example:
lci_gstrUser = CurrentUser()
If you trap user logins with your own form in an unsecured system, you must design a strategy to supply login validation, if any. Bear in mind that any programmatic password validation your application does is not hacker-proof and provides validation as a convenience, not as true security. In some installations, however, the burden of setting up Access security and managing the workgroup information file(s) that define the group/user security model is greater than the expected return, so login validation through code may be an adequate model.
To validate user names with code, provide a name and password dialog box like the one shown in Figure 17.10.
You can create an application login dialog box from an Access form.
Users often complain about having to reenter their name at each login to an application, so you may want to default the user name to the previous name used. You can default the user name at login in one of the following ways:
C:\MSOffice\Access\MSAccess.Exe F:\PencilPusher.Mdb /Cmd Bob
Private Sub Form_Load()
' Preload the user name
Me!txtUserName = Command() ' Retrieve the /Cmd parameter
You cannot use the /User option to pass the user name into an application that does not use Access security. In contrast to my previous example using /Cmd, the /User option will cause the display of the Access security login dialog box, and any user name typed in will be rejected because it is not a defined user in the workgroup information file.
Listing 17.3 AES_BPrf.Mdb-Seeding the User Name Text Box with
Prior Login Value
Private Sub Form_Load()
' Purpose: Preload the previous user name
Me!txtUserName = DFirst("LastUserName", "zstblDefault")
To validate the login name and user password through program code, I keep a table of users and passwords (tblUser) in the shared (back-end) database. The table is not linked to any interface (front-end) database, so a user somehow browsing through the linked tables in their local (application) database will not see it. After the OK button is pressed on the login dialog box, I use program code to validate the user name. See the example in Listing 17.4.
This technique requires that your code knows where on the network the data back-end file is located. Ideally, the back-end path and database name are stored in a table in your database, perhaps in the zstblDefault local table previously described if your application involves only one back-end database.
Listing 17.4 AES_BPrf.Mdb-A User and Password Validation Routine
' The Click event on your login form OK button
Private Sub cmdOK_Click()
' Purpose: Validate the user name and password
Dim dbs As Database
Dim rst As Recordset
Dim intRet As Integer
Dim varBackEnd As Variant
' For this app, the user table is in the current database
' In a real app, use this next line instead:
' varBackEnd = DFirst("BackEndData", "zstblDefault")
varBackEnd = CurrentDb.Name
' Look for user name in zhtblUser in back-end
intRet = lci_UserValidate(Me!txtUserName, Me!txtPW, varBackEnd)
Select Case intRet
lci_gstrCurrentUser = Me!txtUserName ' Set global user name
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("zstlogLogin", dbOpenDynaset)
' Add record of login to log table
rst!UserName = lci_gstrCurrentUser
rst!LoginAt = Now
Set rst = dbs.OpenRecordset("zstblDefault", dbOpenDynaset)
' Update most recent user name for next login
rst!LastUserName = lci_gstrCurrentUser
MsgBox "You are logged-in and validated." _
, vbInformation, lci_gcstrAppTitle & " Login"
If lci_FrmOpenStd("fmnuMain", "") Then
intRet = lci_FrmCloseStd(Me)
MsgBox "The user name you entered is not valid for this application." _
, vbCritical, lci_gcstrAppTitle & " Login"
MsgBox "The password you entered is not valid for this user name." _
, vbCritical, lci_gcstrAppTitle & " Login"
' The Declarations section of a standard module in the application
' Current application user, fed during login
Public lci_gstrCurrentUser As String
' A function in the standard module
Public Function lci_UserValidate(rvarUser As Variant, rvarPW As Variant _
, rvarDbs As Variant) As Integer
' Purpose: Validate a user name and password against the table
' zhtblUser in a remote database
' Arguments: rvarUser:=user name
' rvarPW:=user password
' rvarDbs:=database path and name
' Returns: -1=both OK, 1=invalid user, 2=invalid password
' Example: lci_UserValidate(Me!txtUserName, Me!txtPW, varBackEnd)
Dim dbs As Database
Dim rtbl As Recordset
Dim wsp As Workspace
Set wsp = Workspaces(0)
Set dbs = wsp.OpenDatabase(rvarDbs)
Set rtbl = dbs.OpenRecordset("zhtblUser", dbOpenTable)
rtbl.Index = "PrimaryKey"
rtbl.Seek "=", rvarUser ' Find the user
If rtbl.NoMatch Then ' User not found
lci_UserValidate = 1 ' Flag as invalid user
If rtbl!UserPassword = rvarPW Then ' Password is valid
lci_UserValidate = -1 ' Flag success
lci_UserValidate = 2 ' Flag as invalid password
Notice that the event procedure behind the command button stores the user name into a global variable (lci_gstrCurrentUser in the example above) for the application to use. (I show how to make use of this variable in the "Collecting Information from the Application" section later in this chapter.) If you use Access security, set the global variable to the value of the CurrentUser() function after the user has logged in to the workgroup so that your code can always refer to one variable (lci_gstrCurrentUser) whether the application is running on a secured or unsecured system. This enables you to write generic, reusable code.
Note that the login dialog box in Figure 17.10 has a Data... button to refresh table links. The option to locate the back-end database in a new installation or when the network is reconfigured must be early in the application flow (before the first bound form is shown), and I find that the login dialog box is the best place for this option. See Chapter 7, "Understanding Application Architecture," for a discussion of linked tables.
For unsophisticated users of full Access, the Database window is an intimidating and dangerous place, full of dozens of strangely named objects in different bins. As a developer of bulletproof applications, you will remove the Database window and the object design features and instead provide a menu system that introduces more structure into the application.
Old Name, New Name
Nothing is more terrifying to me as a developer than to have a user of my database wandering around the Database window. Such a user can execute action queries at random, redesign my forms, and so forth. This concept should terrify you as well.Beginning with Access 95, a new reason to be terrified exists. Access now employs the Windows 95 metaphor for renaming objects by clicking once on the object name in the Database window to enter Rename mode. This is a terribly dangerous option to provide a user who may not understand that renaming a table object will immediately invalidate all dependent objects, such as queries and forms. This feature is even dangerous for a developer who can click an object in an attempt to set focus, accidentally enter Rename mode, and inadvertently pass the next keystrokes to Access as the object's new name-and this entire sequence can occur unnoticed if the developer is working too quickly. There is no way to disable this new feature, so be aware of its dangers. The feature provides yet another reason to remove the Database window and reproduce its navigation benefits using application objects instead.
Another of the prime tenets of the bulletproofing regimen is to think through the various ways your user can get confused or into trouble in your application and to remove or restrict such opportunities.
When you have removed the Database window and design features, the user requires a way to navigate through your application. One or more switchboard menus usually provide such navigation. In addition, the following considerations apply when you provide a restrictive yet navigable interface.
As you restrict the interface options to bulletproof an application, consider disabling the keyboard keystrokes in Table 17.5 to implement better control of the interface. With these keystrokes, the user can bypass a Close button and/or navigation buttons that you place on your forms or add and delete records via the keyboard-possibly skirting data validation and other important code routines. To disable each option, define a macro for the keystrokes in the Autokeys macro group, and leave the Action column blank.
Table 17.5 Dangerous Keystrokes That Can Be Masked with Null AutoKeys Macros
|Select all records||Ctrl+A|
|Display the Find dialog box||Ctrl+F|
|Display the Replace dialog box||Ctrl+H|
|Open a new database||Ctrl+N|
|Open an existing database||Ctrl+O|
|Close the active window||Ctrl+F4 and Ctrl+W|
|Cycle between open windows||Ctrl+F6|
|Display next Find or Replace match||Shift+F4|
|Display the Save As dialog box||F12 and Alt+F2|
|Save the current object||Shift+F12 and Alt+Shift+F2|
The key combinations Alt+F2 and Alt+Shift+F2 shown in the table cannot actually be remapped in AutoKeys. Their corresponding (sibling) keystroke combinations shown can be remapped, however, which causes the related key combinations to become disabled as well.
Table 17.6 lists keystroke options that cannot be disabled via AutoKeys settings or Startup dialog box options. Unfortunately, if users issue these keystrokes in your application, they could incur negative consequences. You will have to write code to trap these keystrokes as they are sent to each form.
Table 17.6 Dangerous Keystrokes That Cannot Be Masked with AutoKeys Macros
|Add a new record||Ctrl+Plus (+)|
|Delete the current record||Ctrl+Minus (-)|
|Move to the next page/record||PgDn|
|Move to the previous page/record||PgUp|
|Move to the next record||Ctrl+PgDn|
|Move to the previous record||Ctrl+PgUp|
|Select all records||Ctrl+Shift+Spacebar|
|Save changes to the current record||Shift+Enter|
|Quit Access or close a dialog box||Alt+F4|
To trap these keystrokes coming to a form, create a routine called a "keyboard handler" and discard the keystrokes that you consider dangerous. Listing 17.5 shows how to create a simple keyboard handler that uses the KeyDown event on a form to trap and discard the keystrokes for the Replace dialog box.
Listing 17.5 How to Disable Keystrokes Sent to a Form
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
' Trap the Ctrl+H key combination to disable Replace
' The form's KeyPreview property must be True
Dim intCtrl As Integer
intCtrl = (Shift And acCtrlMask) > 0 ' Test for Ctrl key
If intCtrl And KeyCode = vbKeyH Then ' Ctrl+H pressed
KeyCode = 0 ' Throw out the keystrokes
A more comprehensive example of a keyboard handling procedure is shown in Chapter 15, "Protecting and Validating Data."
If you intend to use your keyboard handler from many forms, create a shared (library) routine to hold the keyboard testing code and call the routine from each form's KeyDown event. See the sample procedure lci_FrmKeyIsAllowed in AES_BPRF.MDB on the CD-ROM for an example.
Even though disabling features is a step forward in the protection of your data, it can be a step backward in overall usability if you do not offer alternatives to specific users for the features you remove from view as you bulletproof. Thus, for some useful features you disable, you must provide a replacement that is equally useful and more appropriate to the specific application.
For example, the database administrator may need to be able to reproduce a problem that a user is having and to pass meaningful information about the root of the problem to a developer. As such, the administrator would find value in being able to enter debug mode in the application. As a developer, your debugging strategy might include setting a conditional compilation constant in the Options dialog box to enable the application's debug routines. In a bulletproof application, users (including the administrator) will not have access to the Options dialog box, so your applications should provide a button on the administrator's menu (or some other device) to set the conditional compilation constant from code and thus toggle debug mode on.
As another example, if you clear the Use Access Special Keys property check box and the Display Database Window check box in the Startup dialog box, there is no way for the user to get to the Database window. Thus, even the system administrator and other power users (perhaps you yourself) are locked out of expected or needed features. To alleviate this situation, you can provide a backdoor or other mechanism for selected users to see the Database window. One approach is to provide a menu option on a maintenance menu that initiates the following command:
DoCmd.SelectObject objecttype, objectname , True
For this command, use any object type and name that will always be available in the application, such as the first table in the Database window. Because the third argument (InDatabaseWindow) of the command is set to True, Access forces the display of the Database window.
This trick further highlights the difference between a bulletproof application and a runtime application. The trick works for users of full Access, but it does not work when the /RunTime command-line argument is used-runtime mode never enables the Database window. Thus, an application that has an audience of sophisticated and unsophisticated users may be most usable when installed with full bulletproofing applied, yet without the /RunTime argument.
Further, in an application for sophisticated (and well-trained) users, you may choose to allow the database to be opened in some instances with the Shift key held down, which enables the user to bypass the Startup options. The /RunTime argument in a shortcut disables this override of Startup properties.
A basic tenet of human nature is that people prefer positive over negative feedback. Then why is most computer software so negative or vague when giving feedback to a user? As a simple example, if you assign a value greater than 32767 to an integer variable in Basic, you get an error message back from Access that states simply "Overflow." This might make sense to you as a developer, but it is going to cause confusion, frustration, even anger for any of your users viewing this message in an alert that your application shows. How many users will enjoy using your system if it evokes any of these emotions?
To minimize the impact of negative feedback, bulletproof application developers should give careful consideration to the messages that are displayed to users. The next four topics provide my suggestions on how you can make your applications provide the best possible feedback.
To their credit, Microsoft developers have tried to "humanize" some of the error alerts generated by the Access 97 interface. Some messages include not only a problem description, but a suggested resolution, as shown in Figure 17.11.
Access 97's built-in error alerts are friendlier than they used to be.
Not all Access error messages are intelligible, however. Try to imagine the confusion of your users when they see an alert like the one for error number 97: "Can not call friend function on object which is not an instance of defining class."
In a perfect world, all developers would create a table of frequently used error numbers in their applications and craft specific and informative messages for each error number. Most budgets don't allow for such niceties, however.
Because your applications will rely on error traps placed in the code, they will not be displaying Microsoft's built-in, friendlier alerts, which are not exposed to the developer. Thus, you're stuck with Access' Err.Description string as your primary alert text, which unfortunately leads back to messages like "Overflow" wrapped inside your application's message box.
Compare Figure 17.12 and Figure 17.13, both generated by a MsgBox() function placed in application code. The first figure shows an alert that uses the Access error number and message only. The style shown reflects the standard approach taken by most developers. The second figure shows a more polite and detailed wrapper around the standard error message. Even though the Access error message shown on the alert is the same, the alert contains additional helpful context information for the user to convey to the developer, and provides a suggested course of action. The alert in Figure 17.13 is far less intimidating and more useful than the one in Figure 17.12.
A common alert placed in applications simply shows the internal error number and message.
A friendly error alert provides more information than the common, terse style.
Well-written code can detect the most common negative situations and handle them with the least impact on the user. For some situations, your error trap should filter out specific errors by number with a Select Case statement and act on them accordingly, providing a customized message that improves upon the Access error description text. In other cases, it may not be necessary to show the user an alert every time an error occurs-specific nonfatal errors can sometimes be trapped by your code and discarded without giving any feedback to the user at all.
To create standardized, friendly alerts such as the one in Figure 17.13, write a centralized alert routine that each of your error handlers can call. The procedure should accept error information and display a formatted message box. Chapter 11, "Expert Approaches to VBA," discusses such standard error handlers in detail, including the one used in Figure 17.13.
The alert in the Figure 17.13 includes bold text in the message body. Access looks for an internal flag (the @ character) in your message string to facilitate this formatting for you. The message string you supply to the MsgBox() function can include one or two @ characters to delimit the message into three parts, like this:
MsgBox "headingtext@bodytext@solutiontext", vbOKOnly, caption
The three sections are displayed as follows in the message box:
The total length of the combined string passed to the MsgBox function can not exceed 1,024 characters.
Only a small amount of development effort is required to make your application produce alerts that are less intimidating and more useful than the error messages built into Access.
Funny But Not Friendly
In an attempt to lighten up the spirit of his application, a friend of mine once put a random number generator in his code to vary the information in the alerts the system produces. When an error occurred, his standard error dialog box randomly displayed messages such as the following:
- You're History!
- Bad Move!
- You Broke It!
Although his idea was well-intentioned, this approach was not received with enthusiasm by the users. When new and unskilled users saw these alerts, they took the message seriously and assumed they had caused serious trouble. When skilled users saw these alerts, they craved a more useful and deeper level of information on the problem than was provided.The humorous message text lasted only one week.
One primary approach to helping your application interact better with users is to prevent them from getting into trouble in the first place. In addition to good training, documentation, and supporting materials (help files, for example), you can employ various form design techniques to give users a better sense of what the program expects of them.
Place reminders on your forms wherever possible to help the user understand the right and wrong way to use the form and its controls. A reminder can be as simple as a label in an application stating "Press the Tab key to move to the next field" for many of the users who are new to Windows computing. Alternately, reminders can be more complex, such as having the Enter event for each text box trigger the display of a long message on the form stating the data entry and validation requirements for the current control.
Access provides many ways of giving the user constructive information, including the following techniques:
Table 17.7 Suggestions for Using Form Control Tips
|Control||Type of Tip|
|Bound Object Frame||Tell the user what kind of objects are allowed in the control.|
|Chart||Tell the user exactly what data that the chart is based.|
|Check Box||Tell the user the ramifications of selecting the box and any related events that are triggered.|
|Combo Box||Tell the user the source of the list and/or whether items not on the list are valid selections.|
|Command Button||Tell the user exactly what happens when the button is pressed.|
|Custom Control||Describe unique information about the control and its use.|
|Image||Tell the user if clicking or double-clicking the object performs any action.|
|Label||Provide expanded information beyond that shown on the label.|
|List Box||Tell the user whether one or multiple list items can be selected and how to do it.|
|Option Button||Tell the user the ramifications of selecting the option and any related events that are triggered.|
|Option Group||Describe the purpose of selecting an option from the group.|
|Text Box||Provide information about data entry requirements or validation rules.|
|Toggle Button||Tell the user what happens when the button is selected.|
|Unbound Object Frame||Note whether the user can double-click to activate the object and/or the source of the object.|
The only controls that do not have ControlTipText properties are Line, PageBreak, Rectangle, Subform, and Tab controls.
A control tip, shown here on a button, can provide useful, customized information.
InputMask and control group techniques convey data entry restrictions to users.
The best way to measure your success or failure in this area is to watch how users interact with your application during the working model release or alpha testing phase of the development. Watch first-time users enter data records into your forms and encourage them to verbalize any confusion or mistakes; then you can make corrections to the application.
Too many Windows applications rely almost exclusively on an hourglass mouse pointer to tell the user "Please hold, the program will be back with you shortly...." Certainly any feedback is better than no feedback, but the reality is that the hourglass pointer has become for most of us the ubiquitous reminder that we don't have enough horsepower in our machines to perform operations instantly, and we need to spend more money on hardware! Also, an hourglass does not by itself really answer any questions the user might have about the status of the application, such as:
With all the effort you put into making your application work well, it does not take much extra time to make it friendly for the user. When you have completed primary development, run the application on a slow machine (a 486/33 with 8M of RAM is a good candidate) and note where the application delays more than a few seconds. Each of these areas is a candidate for adding feedback for the user. The following are some good ways to give feedback:
VBA now includes the same MousePointer property that you are used to if you program in Visual Basic. If you are writing code that you will move from Access to VB, consider using Screen.MousePointer = 11 and Screen.MousePointer = 0 in place of DoCmd.HourGlass True and False.
varRet = SysCmd(acSysCmdSetStatus, "Posting, please wait...")
varRet = SysCmd(acSysCmdClearStatus)
When you use SysCmd() to set the status bar, text in the StatusBarText property of form controls will not override the status bar text you have set. Until you clear the status bar text with the code line shown, your form controls will not display status bar text.
' Initialize the meter text and maximum value
varRet = SysCmd(acSysCmdInitMeter, message, maxvalue)
' Set the meter current value
varRet = SysCmd(acSysCmdUpdateMeter, value)
' Remove the meter
varRet = SysCmd(acSysCmdRemoveMeter)
The upshot of using these techniques is that users should not have any questions about what is going on while the application takes control of their mice and keyboards.
At some point, the user may have exhausted the training materials, the system documentation, the screen prompts, and the help file and still not understand some feature of the application. When that happens, frustration mounts quickly, and your application can placate users with information describing how to get support or help. I suggest each of your bulletproof applications employ one or more of the following devices:
This About dialog box provides a variety of suggestions for getting assistance.
The latter two options previously mentioned-an About dialog box and a Help Support menu option-are especially useful if you make them available on the menu bar throughout the application.
Although bulletproofing is intended to minimize the potential for problems in the data and with the use of the application, issues often arise in a complex system that will benefit from an audit trail. My bulletproofing strategy includes creating the following four audit trails in all of our applications.
As I discussed in the "Logging In to an Application" section above, your application can collect and validate login information and set a global variable such as lci_gstrCurrentUser with the user identifier. Each of the audit routines that follow make use of this variable.
The first level of auditing involves finding out who uses the system and when. This information can be useful for answering such questions as the following:
To answer these questions, you can create an activity log table in the back-end database with a name like zstlogLogin and include the fields shown in Table 17.8.
Table 17.8 Fields for a Login Activity Table
When a user logs in to the application, write a new record to the login activity table, as shown in the first function in Listing 17.6. The login record stays open until the user logs out, at which point you close the record by stamping the logout time with the second function in the listing. This combination of a very simple table and two simple routines provides your application with a wealth of information.
Listing 17.6 Routines That Manage User Login and Logout
Public Function LoginStart() As Boolean
' Create a login record for the login activity table
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("zstlogLogin", dbOpenDynaset)
rst!UserName = lci_gstrCurrentUser
rst!LoginAt = Now
Public Function LoginEnd() As Boolean
' Close a login record on logout
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM zstlogLogin WHERE UserName = '" _
& lci_gstrCurrentUser & "' And LogoutAt Is Null; "
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
' In actual implementation, your code at this point should check
' for more than one record in the dynaset, which would indicate
' either a reboot or system crash on a previous session, thus
' the activity record is still open, or a user logged in from
' more than one workstation. In either case, you would want to
' notify the user or run some resolution code to decide which
' user to logout.
rst!LogoutAt = Now
You'll note that the login activity table also has a sequential field, SessionID, which creates a unique number for each user login session. Because a named user can actually login to the application more than once on the same or different machines, a login record must note the unique user login instance. This number can then be used to manage multiple logins for the same user, lock records or flag status values, to track distinct user sessions, and so forth. The following section gives more insights into using this value.
As your needs require, you can use a method similar to the login audit in Listing 17.6 to track other important application events, such as when a specific form is loaded, procedure is run, or record is added to a table. Simply copy and modify the code in Listing 17.6 to write an explicit message string to an activity table, zstlogActivity, that looks something like that in Table 17.9.
Table 17.9 Table Fields for an Activity Log Table
|UserName||Text 10||User name|
|Object||Text 65||Name of form or process|
|Table||Text 65||Name of affected table|
|RecordKey||Long Integer||Primary key of affected record|
|Records||Long Integer||Record count of affected records|
|Activity||Text 30||Activity description|
Whenever an event occurs that has value for tracking purposes, you can have your code write a record to this activity table. Table 17.10 shows a few examples of such events in the log table (minus the user name and time stamp fields). The code to create such activity records will be very similar to that in procedure LoginStart in Listing 17.6.
Table 17.10 Sample Records from an Activity Table
Recalling the SessionID that was created for each unique user login in the previous section, you can optionally use the SessionID as an activity's owner instead of UserName. The session number indicates not only the user name but which unique user session triggered the event. To enable this feature, you will have to carry the SessionID in a global variable when the application is running and replace the user name variable in your logging code with the session variable. For most developers, however, this extra information is not highly useful; its greatest benefit comes when the same user (for example, Guest) can be logged in from multiple terminals/locations and thus can generate multiple simultaneous SessionID numbers for the same user name.
Note that the sample data in Table 17.10 has a mix of data in the Records field: for record modifications I write the actual record primary key affected, but for a process I write the quantity of records processed. If you prefer your activity records to be less free-form, you can separate this information into two fields.
With the affected table name and a record primary key in the activity table, you can now query the audit data by target table name and record key to get a picture of how records are affected and by which users. For example, a query where the Table field equals "tblCust" and sorted by the RecordKey and ActivityAt fields would show you the entire sequential history of each individual customer record.
You can have your application write to the log table at event triggers that you deem important, whether code events or user-generated events. For example, for some clients we add three more columns to the activity table-Field, OldValue, NewValue-and our audit routine tracks individual edits to critical fields. In some extreme cases, we even create a separate audit table for each major data table and place archive copies there of records that are edited before changes are saved.
If you have no need of special activity flags, such as "Posted" shown in Table 17.10, you can use built-in Access constants, such as acAdd, acDelete, acEdit, and acSave, to set values in your activity log table.
As a further audit technique, I always add to every major data table the fields shown in Table 17.11.
Table 17.11 Table Fields for Edit Logging in Each Data Table
|CreatedAt||Date/Time||When was record created|
|CreatedBy||Text 10||User ID of creator|
|ChangedAt||Date/Time||When was record last edited|
|ChangedBy||Text 10||User ID of last editor|
Our applications update these fields when each record is created and edited, so we can easily track who has created a specific record and who changed it last. Attach to the BeforeUpdate property on each form a code routine that plugs the four fields with user and date information before the record is first saved and plugs only the changed fields when each edited record is saved. See the examples in Listing 17.7.
Listing 17.7 AES_BPrf.Mdb-The Routines Required to Track Record Edits
Private Sub Form_BeforeUpdate(Cancel As Integer)
Public Sub lci_FrmRecStamp(rfrm As Form)
' Purpose: Timestamp current form record, called from Form_BeforeUpdate
' Arguments: rfrm:=Form object with recordset to stamp
' Example: lci_FrmRecStamp(Me)
rfrm!ChangedAt = Now
If lci_IsBlank(lci_gstrCurrentUser) Then
rfrm!ChangedBy = CurrentUser()
rfrm!ChangedBy = lci_gstrCurrentUser
' Only stamp Created fields if Null
If IsNull(rfrm!CreatedAt) Then
rfrm!CreatedAt = Now
If lci_IsBlank(lci_gstrCurrentUser) Then
rfrm!CreatedBy = CurrentUser()
rfrm!CreatedBy = lci_gstrCurrentUser
Note that the information in these audit fields might duplicate information that you place in the activity log described in the previous section. Even so, having this record creation and edit information in the table itself is very useful because it can be printed on reports, displayed on forms, or viewed when browsing the table directly. You can also add these fields to ancillary tables, such as lookup tables, that may not be audited by the activity log process.
You will need to capture the SessionID in numeric versions of the CreatedBy and ChangedBy fields instead of using the user name if you decide to track all activities by session instead of by user name, as I described in the section "Collecting Activity Information" immediately preceding this section.
The final audit technique enables you to keep track of error messages that your application generates. I use a table, zstlogError, in the back-end database with the structure shown in Table 17.12.
Table 17.12 Table Fields for an Error Log Table
|ErrWhen||Date/Time||Time of error|
|ErrProcName||Text 130||Module.Procedure name|
|ErrUserName||Text 10||User ID|
Each time a code error is generated and your code jumps to its error handling routine, you can write a log record to this table. The table's information provides great value in the following two ways:
The VBA code structures and procedures to support this table are detailed in Chapter 11, "Expert Approaches to VBA."
Although the four primary audit routines described in the preceding section give you detailed information about the application and how users interact with it, they operate "behind the scenes." I find it handy to give users a chance to actively participate in the feedback process as well.
One mechanism for gathering feedback is to create a table and form to gather users' comments. Periodically print reports from the table to review user issues and suggestions. Another technique is to provide users with an e-mail dialog box or an e-mail-enabled form with which they can send their comments directly to a support or development person. Whichever of these options we employ, we usually place a Support or Feedback option on the Help menu to launch it from any form.
Once you have developed a ritual for bulletproofing applications, your efforts accrue the following benefits:
The time investment to implement these bulletproofing techniques is small in contrast to the benefits described. For more information about related issues, consult the following chapters:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.