Here are the materials from Thursday night's presentation.

• Sample Databases (DEMOS.ZIP, 363 KB)
• PowerPoint File (PPT.ZIP, 256 KB)
• White paper in Word 97 format (DOC.ZIP, 40KB)

The Access Bug?

Last night I mentioned a newly-uncovered bug that exists in all versions of Access. To see the article, go to the Microsoft support site and search for KB article # Q191883.

The remainder of this page is the whitepaper in HTML format.

FMS Whitepaper
User-Proof Your Access Applications
By Dan Haught, Vice President of Product Development, FMS Inc.
www.fmsinc.com

When you develop Access applications that are to be distributed to other users, you must take many factors into account. You must develop your application to run on varying hardware, operating system versions, directory structures and network servers. This session covers the key aspects of developing an Access Solution for distribution to other users. You'll learn about machine-independence, startup control, re-attaching linked tables, robust error-handling, long distance tech support, and developing for the least-common denominator machine. You will learn the techniques of developing and application that you can distribute with confidence. This session is essential information for developers and technical managers.

When you develop Access applications that are to be distributed to other users, you must take many factors into account. You must develop your application to run on varying hardware, operating system version, directory structures, and network servers. This session covers the key aspects of developing Access solutions for distribution to other users. You'll learn about machine-independence, startup control, re-attaching linked tables, robust error handling, long distance tech support, and developing for the least common denominator machine. You will learn the techniques of developing an application that you can distribute with confidence.

Splitting an Access application into two separate databases is a common technique. When you are developing applications for distribution, this technique becomes required. First let's explain how this works. The first database, known as the Data database contains all tables and data. The other database, known as the Application database contains all other objects including queries, forms, reports, macros and modules. The two are joined together by linking (or "attaching" in Access 2 parlance) the tables from the Data database to the Application database.

The key benefit to this design is that you distribute new Application databases to your users without overwriting their data. Since the data is stored in separate database, you don't have to worry about importing or converting the user's data into your new version.

Access 95 and Access 97 offer an add-in that does this for you. For more information, search Access Online help for "database splitter".

Have you ever spent a lot of time designing a form to look just right, only to have it appear incorrectly on another system? This is a common problem when developing visual applications for use under Windows. Since Windows offers the user so many configuration options, and there are so many ways that Windows can be installed, the application the looks and runs fine on your system may not work well on another system. This section covers some basic techniques to handle this problem.

Windows 3.x offers both "Large Fonts" and "Small Fonts" options for displaying fonts. Windows 95 has these options, and adds arbitrary font scaling. These settings can drastically affect how your application works. It can be very difficult to design efficient forms that can handle for font scaling differences. It is probably best to require your users to run your application with the standard font scaling.

One thing you can do to alleviate this situation is to set any image or bitmap controls to "Zoom" or "Stretch". This allows Access to stretch or enlarge bitmaps to display correctly regardless of font scaling.

Standard Windows 3.x installations have a very limited set of fonts. Windows 95 expands the list of basic fonts, but it is still somewhat limited. Because of this, you should assume that your users will not have any fonts other than the standard ones. Do not use other fonts in your application because Access will try to approximate them on your user's system, sometimes with strange visual results.

Because Windows allows you to specify your own system colors, and application designed on your system may appear strangely on another user's system. For example, if a user specifies a different system color for 3d objects, Access will use that color when it displays command buttons. If your application's command buttons are hardcode to be gray, they will appear strange in the user's color scheme.

To get around this, Access has a set of predefined color values that you can use to force Access to use system colors. The following table shows these values. For more information, search Access Online help for "color".

Access 2 requires that SHARE.EXE be loaded for correct operation. In Windows 3.1 and 3.11, this must be done in the Autoexec.Bat file. In Windows for Workgroups, this is handled by Windows automatically with the VSHARE driver. This is not an issue for Access 2.0 under Windows 95 or Windows NT, nor is it an issue for Access 95 or Access 97.

Despite the fact that your application works on your 32MB Pentium system with no apparent performance issues, you application running on a user's 33mhz 486 may be a different matter altogether. Be sure to test your application on the type of computers that will on you user's desks. At a minimum, be sure to put in hourglass cursors where an operation may take more than 1 second.

If your application uses the two-database approach mentioned earlier, then you have to worry about attached table issues. When a user installs your application on their system, there is a good chance they will install it in a directory that is different from the one on your system where the database was created. This means that all links to attached tables will be broken on the user's system.

To get around this, your application should have a startup routine that fixes all link for attached table.

Note: In Access 2.0 applications, the only way to invoke your startup routine is to through the AutoExec macro. Access 95 and 97 offer additional startup capabilities that are more robust, flexible and powerful. See the section later in this paper entitled "Application Startup Techniques" for more information.

The first step is to determine where the data database resides. If it is always installed in the same directory as your application database, this is easy. Simply get the path of the current database using the following code technique:

Function GetCurrentDBPath() As String
  ' Comments  : Returns the path of the current database
  Dim intCounter As Integer
  Dim strPath As String
  
  strPath = CurrentDb.Name
  
  For intCounter = Len(strPath) To 1 Step -1
    If Mid$(strPath, intCounter, 1) = "\" Then
      Exit For
    End If
  Next intCounter

  GetCurrentDBPath = Left$(strPath, intCounter)
  
  strPath = CurrentDb.Name
  
End Function

If your data database can placed in a directory other than the application database's directory, the above technique will obviously not work. In such a case, you need to prompt the user for the location of the data database.

Once you have the path where the data database exists, it is a simple matter to fix the attached tables. The following three procedures can be used to accomplish this:

Function ReAttachAllTables_TSB(strDatabase As String, strNewPath As String) As Integer
  ' Comments  : Relinks all the attached tables to a new database
  ' Parameters: strDatabase - Path and name of database containing the attached table(s) or "" (blank string) for the current database
  '             strNewPath - full path of the database to attach to
  ' Returns   : True if successful, False otherwise
  '
  Dim dbsTemp As Database
  Dim intCounter As Integer
  Dim tdfTemp As TableDef
  Dim fOK As Integer

  On Error GoTo ReAttachAllTables_TSB_ERR

  If strDatabase = "" Then
    Set dbsTemp = CurrentDb()
  Else
    Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
  End If

  fOK = True

  For intCounter = 0 To dbsTemp.TableDefs.Count - 1
    Set tdfTemp = dbsTemp.TableDefs(intCounter)

    If GetAttachedType_TSB("", (tdfTemp.Name)) <> "" Then
      fOK = ReAttachTable_TSB((dbsTemp.Name), (tdfTemp.Name), strNewPath)
      
      If Not fOK Then
        Exit For
      End If

    End If
  Next
  
  ReAttachAllTables_TSB = (fOK = True)

ReAttachAllTables_TSB_EXIT:
  dbsTemp.Close
  Exit Function

ReAttachAllTables_TSB_ERR:
  ReAttachAllTables_TSB = False
  Resume ReAttachAllTables_TSB_EXIT

End Function

Function GetAttachedType_TSB(strDatabase As String, strTable As String) As String
  ' Comments  : returns the type of the named attached table
  ' Parameters: strDatabase - name and path of database to look in or "" (blank string) for the current database
  '             strTable - name of table to check
  ' Returns   : attachment type
  '
  Dim dbsTemp As Database
  Dim tdfTemp As TableDef
  Dim strConnect As String
  Dim intPos As Integer
  Dim strTmp As String

  If strDatabase = "" Then
    Set dbsTemp = CurrentDb()
  Else
    Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
  End If

  Set tdfTemp = dbsTemp.TableDefs(strTable)
                  
  strConnect = tdfTemp.Connect
  If strConnect <> "" Then
    intPos = InStr(tdfTemp.Connect, ";")
    strTmp = Mid$(strConnect, 1, intPos - 1)
    If strTmp = "" Then
      strTmp = "Access"
    End If
  End If

  dbsTemp.Close

  GetAttachedType_TSB = strTmp

End Function

Function ReAttachTable_TSB(strDatabase As String, strTable As String, strPath As String) As Integer
  ' Comments  : Re-attaches the named table to the named path
  ' Parameters: strDatabase - Path and name of database containing the attached table or "" (blank string) for the current database
  '             strTable - table name of the attached table
  '             strPath - path name of the database containing the real table
  ' Out       : True if successful, False otherwise
  '
  Dim dbsTemp As Database
  Dim tdfTemp As TableDef
  Dim strPrefix As String
  Dim strNewConnect As String

  On Error GoTo fAttachTable_TSB_ERR

  If strDatabase = "" Then
    Set dbsTemp = CurrentDb()
  Else
    Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
  End If
  
  Set tdfTemp = dbsTemp.TableDefs(strTable)

  strPrefix = Left$(tdfTemp.Connect, InStr(tdfTemp.Connect, "="))
  strNewConnect = strPrefix & strPath
  
  tdfTemp.Connect = strNewConnect
  tdfTemp.RefreshLink
  
  ReAttachTable_TSB = True

fAttachTable_TSB_EXIT:
  dbsTemp.Close
  Exit Function

fAttachTable_TSB_ERR:
  ReAttachTable_TSB = False
  Resume fAttachTable_TSB_EXIT

End Function

When creating forms and reports, be sure to consider the following issues.

If you design your Access forms with a screen resolution of 640x480, this won't be an issue. But if, like most developers, you use a higher resolution, you may forget that form designed to fit on your 1024x768 screen will be too large for your users with the 640x480 setting. Always consider the lowest common denominator machine. The following table shows the maximum form size that will fit within a given resolution:

Note that the above figures are baseline numbers. You must take into account other form sizing issues, such as the Windows 95 taskbar, and the use of scrollbars, navigation buttons and other form design elements.

Access saves printer settings with each form and report. This means that if you select a specific printer using a form or report's Print Setup dialog, that printer name is stored with the form or report. When you then distribute your database to another user, that form or report will not print unless the user has exactly the same printer connected to exactly the same port (or network path).

To avoid this error, open each form and report in design view and set the printer to "Default Printer".

Be sure to convert all bitmaps to pictures (available through the edit menu in Form design). This breaks the link between the bitmap and its associated application. In Access 95 and 97, use the new Image control to handle this automatically.

If you designed your application correctly, you are only using macros for Autoexec, AutoKeys and menu assignments. Macros for any other use have no place in professional application development, especially in applications that are to be distributed to other users.

Additionally, in Access 95 and 97, new startup properties make the Autoexec macro obsolete, and Access 97's inclusion of Command Bars make menu assignment macros obsolete.

In order for libraries to peacefully coexist with databases that are opened through the Access user-interface, Access defines rules that partition areas of memory for use by each database. These rules, known as scoping rules, tell Access how and where to look for objects and procedures that your application calls. If your application can potentially be installed on a machine that already has Access installed, and the user will be using their own MSACC20.INI initialization file, there is a chance that Access Basic names that you use in your application may conflict with library databases that the user may have installed. This section discusses this problem, and its resolution.

The scoping rules for libraries in Access 2 are somewhat confusing at first, and are inconsistent in places. The following rules are used for form, report and macro objects:

• If a form or report is loaded from a library, it looks in the library for its data. So by default, all library forms and reports are bound to tables or queries in the library database.
• If an object is loaded from a library, and that object refers to a macro, Access searches first in that library, and if the macro is not found, searches in the current open database.
• The Access domain functions such as Dlookup and Dmin are hardcoded to always look in the current open database. If a form or report is loaded from a library and it uses the domain functions, Access looks in the current open database for the table or query named in the domain function.

For Access Basic code, the scoping rules are more complex, yet more consistent than the rules for forms and reports. In order to understand the scoping rules for module code, you must understand the global namespace. When Access starts, it populates the global namespace by reading the [Libraries] section of the initialization file. Each library it finds there is opened behind the scenes. Access loads each standard module in each database and looks for all global code. This includes procedures that are defined without the Private keyword, global variables and constants, all Types, and all Declare functions. It then loads this global code into the global namespace. By the time it has finished loading, Access has a structure in memory identifying all objects in the global namespace, along with the name of the database where the objects are defined. It is through this mechanism that Access makes module code available to the current open database. Form and report modules are not opened during this process and are not added to the global namespace since, by definition, form and report modules cannot contain global code.

It is important to note that this global namespace is also populated when you open a database through the Access user interface (for example with the File|Open Database menu command). All global elements in your database modules are added to the global namespace. The reason this is important is that you must take care to avoid global namespace conflicts. A conflict occurs when Access tries to load a procedure, constant, type or variable name into the global namespace, and an object of that name already exists. This is a common problem for first-time library developers. For example, assume your library contains a function called IsLoaded(). You start Access and it loads this function from your library into the global namespace. Then you try to open one of your databases that contains a module with a function that is also called IsLoaded(). Access generates an error message at this point and does not load that module from the current open database.

The key to avoiding global namespace conflicts is to ensure that each global object in your modules has a unique name that cannot conflict with other code names. This is typically handled by using prefixes or suffixes on your object names that are unique to each library. For example, if you have a library of code routines you use in many places, consider using a name such as "UTIL" as a unique identifier. Each of your non-private procedures, global variables and constants, types and Declares should use this name. So instead of defining the procedure named IsLoaded() in your library, you would call it IsLoaded_UTIL(). This suffix text reduces the chance of a conflict (also known as disambiguation) and allows your library and your current open database to coexist within the global namespace. Of course, to further reduce the potential of conflicts, you may have to come up with a more distinct disambiguator, such as your company name, or project name.

While the use of Option Explicit in modules is not directly related to distributing applications, it is the single most important thing to do to reduce the number of bugs in your application. Every module in a distributed application should have Option Explicit set.

If your application uses library databases, you must ensure that the reference to the library database is established correctly on the user's machine. If you are using Access 2.0, this reference is made in your application's INI file in the [Libraries] section. The easiest way to handle library databases is to install them in the same directory as the Access runtime executable file. This ensures that Access will always be able to find the library.

In Access 95 and Access 97, INI file references are no longer used to call code from library databases. The following section explains the concept of references, and how they can affect distributed applications.

A library database is a collection of procedures and database objects that you can call from any Microsoft Access application. You can use libraries to store routines that you use often, so you don’t have to write the same routine for each application you create. You can also use libraries to distribute new features to your users.

When you create an application, the objects and Visual Basic code in the application database work only within that application. For example, you can call functions in the UtilityFunctions module in the Northwind sample database only from objects in that database—they aren’t available from other databases. This structure works fine for objects and code that you use in only one application. However, you might find that you need functionality to be available from all your databases. This is achieved through the use of library databases.

A library database is structurally the same as any other Microsoft Access database. It can contain tables, queries, forms, reports and module code. The only real difference between a library database and regular database is that a library database is referenced by Microsoft Access directly. You do not open the library database, Microsoft Access does it for you.

All add-ins require access to one or more library databases. One of the more difficult to master aspects of library databases is referencing. Referencing means that Microsoft Access must know were to find the library database. By establishing a reference to a library database, you are letting Microsoft Access know where to look for the library database.

Under Microsoft Access 2.0, establishing a reference was easily accomplished by adding a key to the Microsoft Access Initialization File (typically called MSACC20.INI). For example, if you had a library database called C:\TOOLS\MYTOOLS.MDA, you could add the following line to the INI file:

[Libraries]
C:\TOOLS\MYTOOLS.MDA=rw

From there on, any database could access objects in that library database. The setting made the libraries modules global.

Because of the architecture of Microsoft Access 97 and VBA, there is no longer a concept of global modules. In order to establish a reference to a library database, you have to learn some new techniques. References can be established in several ways:

• Creating a Library Reference
• Creating an Explicit Reference
• Creating a Runtime Reference

You can create a Library Reference by putting a reference to it in the [Libraries] section of the registry. This is essentially the same as putting it in the INI file under 2.0. However, doing this does not make the module global in the same way that Access 2.0. It only allows functions from the library database to be invoked as a menu add-in. If you need your database to be able to call functions from the library database, this method will not work.

Explicitly adding a reference that establishes a link between your database and the library. This method has the following restrictions:

• References are added at the database level. They are not global. This means that you need to add a reference to a library in every one of your databases that will call functions in the library database.
• References contain explicit paths. If the library is moved, the reference may no longer work (see below).
• You cannot programmatically add a reference in Access 95. They can only be added manually.

When you create an explicit reference, Microsoft Access stores the explicit path to the referenced database. However, by default, Microsoft Access will look in several other locations for the referenced library database:

1. The directory in which the file that contains the reference is located.
1. The directory in which Access installed
1. The windows directory
1. The windows system directory
1. Any directory in the Path statement in your AUTOEXEC.BAT file.
1. If a key called RegLibPaths exists in the registry under Access\7.0, Microsoft Access will search this path.

To add a path to registry for Microsoft Access to search for referenced databases, add a key called REFLIBPATHS in:

HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/ACCESS/7.0/

Under this key, you can add the following string and value:

mydatabase.mda x:\path

where mydatabase.mda is the name of your library database and x:\path is the full path to search in.

This technique establishes a reference at runtime using the Application.Run method. This method opens the explicitly named library database and executes the requested function. This method has the following restrictions:

• The library database must have an MDA extension

• It must be located in the directory specified by the AddInPath key in the registry. This string is located in:
  
  HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/OFFICE/80/ACCESS/WIZARDS
  
  (note that you could change this path, but then the built-in Microsoft Access Wizards will no longer work.)

You can add a key call LoadOnStartup to the following tree:

HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/OFFICE/80/ACCESS/WIZARDS

By adding this key and adding your library database to this section, you can control how your library database code is loaded. This, in effect, causes the type information from your library database to be loaded when Microsoft Access is started. By placing your add-ins in this section, you can save approximately 10% of loading time. This works because 10% of your wizard’s loading time will occur when Microsoft Access is started. This is only a time-shifting device and nothing more. It does not change the reference model described earlier.

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

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

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

The following code show how to programmatically add a reference:

Function fAddRef() As Boolean
  ' Comments  : adds a reference
  ' Parameters: none
  ' Returns   : true/false - success/failure
  '
  Dim refAdd As Reference
  Dim strName As String
  Dim fOK As Boolean
  Dim strPath As String
  Dim intSaveErr As Integer
  Dim strSaveErr As String
  Dim strMsg As String

  On Error GoTo PROC_ERR
  
  ' Assume failure
  fOK = False
 
  strPath = strGetOpenFile_RL("C:\WINDOWS\SYSTEM")
  
  If strPath <> "" Then
    If MsgBox("Add a reference to " & UCase(strPath) & "?", _       
              vbYesNo + vbQuestion) = vbYes Then
      
      DoCmd.Hourglass True
      
      On Error Resume Next
      Set refAdd = Application.References.CreateFromFile(strPath)
      intSaveErr = Err.Number

      strSaveErr = Err.Description
      On Error GoTo 0
      
      If intSaveErr <> 0 Then
        fOK = False
        Beep
        strMsg = "Could not add a reference to " & vbCrLf & _
                  UCase(strPath) & vbCrLf & "Error was: " & _ 
                  strSaveErr
        MsgBox strMsg, vbExclamation, gstrAppName_RL
      Else
        fOK = True
      End If
          
      DoCmd.Hourglass False
  
    End If
    
  End If
  
  fAddRef = fOK
  
PROC_EXIT:
  Exit Function
  
PROC_ERR:
  fAddRef = False
  Resume PROC_EXIT
  
End Function

The following code shows how to programmatically delete a reference:

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

PROC_EXIT:
  Exit Function
  
PROC_ERR:
  fDeleteRef = False
  Resume PROC_EXIT
End Function

Access 95 and Access 97 have a wealth of startup options. You can use these options to control not only how your application start, but also how it is presented to the user, and other aspects of its operations. This section outlines these options.

With your database open, select Tools, Startup from the menu. The following form is displayed:

• Other Properties

The following properties can be created/set to control how an application starts and behaves:

• You can use the AllowBreakIntoCode property to specify whether or not the user can view Visual Basic code after a run-time error occurs in a module.
• You can use the AllowBuiltInToolbars property to specify whether or not the user can display Microsoft Access built-in toolbars. For example, you can use the AllowBuiltInToolbars property to prevent a user from seeing a Microsoft Access built-in toolbar within a database application.
• You can use the AllowFullMenus property to specify whether or not full Microsoft Access built-in menus will be available when the application database opens. For example, you can use the AllowFullMenus property to disable menu items that give users the ability to modify table, form, query, or report structures.
• You can use the AllowShortcutMenus property to specify whether or not your application allows Microsoft Access to display built-in shortcut menus. For example, you can use the AllowShortcutMenus property in conjunction with the AllowFullMenus property in your application to prevent users from using any built-in menu bar, toolbars, or shortcut menu commands that enable users to change the design of database objects.
• You can use the AllowSpecialKeys property to specify whether or not special key sequences (ALT+F1 (F11), CTRL+F11, CTRL+BREAK, and CTRL+G) are disabled or enabled. For example, you can use the AllowSpecialKeys property to prevent a user from displaying the Database window by pressing F11, entering break mode within a Visual Basic module by pressing CTRL+BREAK, or displaying the Debug window by pressing CTRL+G.
• You can use the AllowToolbarChanges property to specify whether or not your database allows users to customize toolbars, menu bars, and shortcut menus. For example, you can use the AllowToolbarChanges property to prevent users from deleting a toolbar button or an entire toolbar from your application.
• You can use the AppIcon property to specify the name of the bitmap (.bmp) or icon (.ico) file that contains the application's icon. For example, you can use the AppIcon property to specify a .bmp file that contains a picture of an automobile to represent an automotive parts application.
• You can use the AppTitle property to specify the text that appears in the application database's title bar. For example, you can use the AppTitle property to specify that the string "Inventory Control" appear in the title bar of your Inventory Control database application.
• You can use the StartupForm property to specify the name of the form that opens when your database first opens. For example, you can use this property to display a specified form that contains a menu of all available forms, queries, and reports within a Microsoft Access application when the database opens.
• You can use the StartupMenuBar property to specify a custom menu bar to use as the global menu bar for your application. For example, you can use the StartupMenuBar property to display a custom menu bar that doesn't contain the Security menu. This prevents a user from accessing any of the Security menu commands from the menu bar. You can also specify a menu bar macro that displays the custom menu bar you want to use as the global menu bar.

There are additional properties and settings you can use. Search Access online help for "Startup Options" for more information.

All applications should have some form of error handling. Applications that you distribute have an even more acute need for error handling. Think about it for moment: if you are going to distribute an application to a site other than your own, you are going to have troubleshoot over the phone. This is not enviable task—often the user you are talking with is a computer novice, and is not going to be much help in debugging your code.

Therefore, your application needs to handle unanticipated errors gracefully, and log enough information to make it possible for you to debug and troubleshoot the problem. This section covers a strategy for providing robust and informative error handling and logging.

The first step is to create an array in your application's VBA code to keep track of which procedures and operations are running. Whenever a form opens, or a VBA procedure is invoked, the basic information is popped onto this array to form a call stack. This call stack can be logged to a file or table when an error occurs, giving you full information about what happened when the user encountered the error. The following code (compatible with Access 2.0, 95 and 97) illustrates how to do this:

Place the following code in the Declarations Section of a new module:

' Define the maximum number of procedure names held in the stack
Const ERH_MAXITEMS_TSB = 20

' Add two for our own handling
Const ERH_USEITEMS_TSB = ERH_MAXITEMS_TSB + 2

' Array to simulate a stack of procedure names
Dim aERH_Stack_TSB(0 To ERH_USEITEMS_TSB) As String

' Pointer to the current position in the stack
Dim intERH_Pointer_TSB As Integer

' Set the following to True to included expanded system information in the log file
Const ERH_EXPANDEDINFO = True

' Set the following to True to display an error dialog in the event of an error
Const ERH_DISPLAY_ERROR = True

' Set the following to true to log errors to a table
Const ERH_LOG_TO_TABLE = True

' Set the following to the table to log errors to
' (if ERH_LOG_TO_TABLE is True)
Const ERH_LOG_TABLE = "tblErrors_TSB"

' Set the following the path and name of the file to log errors to
' (if ERH_LOG_TO_TABLE is False)
Const ERH_LOG_FILE = "C:\ERRORS.TXT"

The following procedure initializes the error handling. Call it once when your application starts.

Function ERH_Initialize_TSB () As Integer
  ' Comments  : Initializes the error handler
  ' Parameters: None
  ' Returns   : True if successful, False otherwise
  '
  On Error GoTo ERH_Initialize_TSB_ERR
                                       
  ' Initialize the stack
  Erase aERH_Stack_TSB
  intERH_Pointer_TSB = LBound(aERH_Stack_TSB)

  ERH_Initialize_TSB = True

ERH_Initialize_TSB_EXIT:
  Exit Function

ERH_Initialize_TSB_ERR:
  ERH_Initialize_TSB = False
  Resume ERH_Initialize_TSB_EXIT

End Function

This procedure adds the name of the procedure of operation to the stack. Call it whenever you initiate a "starting" operation such as entering a procedure, or opening a form or report.

Sub ERH_PushStack_TSB (strProc As String)
  ' Comments  : Pushes the supplied procedure name onto the error handling stack
  ' Parameters: strProc - name of the currently executing procedure
  ' Returns   : Nothing
  '
  intERH_Pointer_TSB = intERH_Pointer_TSB + 1

  If intERH_Pointer_TSB <= ERH_MAXITEMS_TSB Then
    aERH_Stack_TSB(intERH_Pointer_TSB) = strProc
  Else
    aERH_Stack_TSB(intERH_Pointer_TSB + 2) = strProc
  End If

End Sub

This procedure removes the last string that was placed on the stack. Call this procedure whenever you end an operation, such as exiting a procedure, or closing an object. Note that for this technique to work properly, you must match "Push" and "Pop" operations into pairs. For example, if you push the name of form onto the stack with the Push procedure, be sure to pop the form name off of the stack when the form closes.

Sub ERH_PopStack_TSB ()
  ' Comments  : Pops the current procedure name off the 
  '             error handling stack
  ' Parameters: None
  ' Returns   : Nothing
  '
  If intERH_Pointer_TSB <= ERH_MAXITEMS_TSB Then
    aERH_Stack_TSB(intERH_Pointer_TSB) = ""
  End If

  intERH_Pointer_TSB = intERH_Pointer_TSB - 1
  
  If intERH_Pointer_TSB < 0 Then
    intERH_Pointer_TSB = 0
  End If

End Sub

When an error occurs, call the following procedure:

Sub ERH_Handler_TSB ()
  ' Comments  : the master error handler
  ' Parameters: none
  ' Returns   : nothing
  '
  Dim strError As String
  Dim strTemp As String
  Dim intError As Integer
 
  Const MB_STOP = 16

  ' Get the current error information
  strError = Error$
  intError = Err
  
  ' Since we don't know what state we are in, disable error trapping
  On Error Resume Next
  
  ' Log the error
  If ERH_LOG_TO_TABLE Then
    Call ERH_LogErrorToTable_TSB(strError, intError, Erl, ERH_LOG_TABLE, ERH_EXPANDEDINFO)
  Else
    Call ERH_LogErrorToFile_TSB(strError, intError, Erl, ERH_LOG_FILE, ERH_EXPANDEDINFO)
  End If

  If ERH_DISPLAY_ERROR Then
    Beep
    strTemp = "The following error has occurred in procedure: " & Chr$(13) & Chr$(10)
    strTemp = strTemp & aERH_Stack_TSB(intERH_Pointer_TSB)
    strTemp = strTemp & " on line " & IIf(IsNull(Erl), "<unknown>", Erl) & Chr$(13) & Chr$(10)
    strTemp = strTemp & "Error (" & intError & ") " & strError
    
    MsgBox strTemp, MB_STOP, "Database Error"
  End If

End Sub

This procedure logs the error information to a file:

Sub ERH_LogErrorToFile_TSB (strError As String, intError As Integer, intErl As Integer, strFile As String, fExpandedStats As Integer)
  ' Comments  : Logs the most recent error to a file
  ' Parameters: strError - error string
  '             intError - error number
  '             intErl - error line number
  '             strFile - name of the file to log the errors to
  '             fExpandedStats - True to include additional system information, False otherwise
  ' Returns   : nothing
  '
  Dim dbsCurrent As Database
  Dim intFile As Integer
  Dim intCounter As Integer
  Dim varTemp As Variant

  Set dbsCurrent = CurrentDB()
  intFile = FreeFile
  Open strFile For Append As intFile
  
  Print #intFile, "Access Application Error Information"
  Print #intFile, "===================================="
  Print #intFile, "Current Time   : " & Now
  Print #intFile, "Error String   : "; Left$(strError, 255)
  Print #intFile, "Error Number   : " & intError
  Print #intFile, "Error Line     : " & intErl
  Print #intFile, "Error Procedure: " & aERH_Stack_TSB(intERH_Pointer_TSB)
  Print #intFile,
  Print #intFile, "Procedure Stack"
  Print #intFile, "---------------"
  
  For intCounter = LBound(aERH_Stack_TSB) To UBound(aERH_Stack_TSB)
    If aERH_Stack_TSB(intCounter) <> "" Then
      varTemp = varTemp & Format(intCounter, "00") & Space$(intCounter * 2) & aERH_Stack_TSB(intCounter) & Chr$(13) & Chr$(10)
    End If
  Next intCounter
  
  Print #intFile, varTemp

  If fExpandedStats Then
    Print #intFile,
    Print #intFile, "Additional Information"
    Print #intFile, "----------------------"

    varTemp = "Current User            : " & CurrentUser() & Chr$(13) & Chr$(10)
    varTemp = varTemp & "Access Version          : " & SysCmd(SYSCMD_ACCESSVER) & Chr$(13) & Chr$(10)
    varTemp = varTemp & "Jet Version             : " & DBENgine.Version & Chr$(13) & Chr$(10)
    varTemp = varTemp & "LoginTimeout            : " & DBENgine.LoginTimeout & Chr$(13) & Chr$(10)
    varTemp = varTemp & "Current Diretory        : " & CurDir & Chr$(13) & Chr$(10)
    varTemp = varTemp & "Access Directory        : " & SysCmd(SYSCMD_ACCESSDIR) & Chr$(13) & Chr$(10)
    varTemp = varTemp & "Runtime Version         : " & SysCmd(SYSCMD_RUNTIME) & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB Path/Name     : " & dbsCurrent.name & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB Version       : " & dbsCurrent.Version & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB CollatingOrder: " & dbsCurrent.CollatingOrder & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB Connect       : " & dbsCurrent.Connect & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB QueryTimeout  : " & dbsCurrent.Querytimeout & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB Transations   : " & dbsCurrent.Transactions & Chr$(13) & Chr$(10)
    varTemp = varTemp & "CurrentDB Updatable     : " & dbsCurrent.Updatable & Chr$(13) & Chr$(10)

    Print #intFile, varTemp

  End If

  ' Close the objects
  Close #intFile
  dbsCurrent.Close

End Sub

This procedure logs the error information to a table:

Sub ERH_LogErrorToTable_TSB (strError As String, intError As Integer, _ 
      intErl As Integer, strTable As String, fExpandedStats As Integer)
  ' Comments  : Logs the most recent error to a table
  ' Parameters: strError - error string
  '             intError - error number
  '             intErl - error line number
  '             strTable - name of the table in the current 
  '                        database to log errors to
  '             fExpandedStats - True to include additional system 
  '                              information, False otherwise
  ' Returns   : nothing
  '
  Dim dbsCurrent As Database
  Dim rstErrors As Recordset
  Dim intCounter As Integer
  Dim varTemp As Variant

  Set dbsCurrent = CurrentDB()
  Set rstErrors = dbsCurrent.OpenRecordset(strTable, DB_OPEN_DYNASET, _
      DB_APPENDONLY)
  
  rstErrors.AddNew
    rstErrors![ErrorDate] = Now
    rstErrors![ErrorString] = Left$(strError, 255)
    rstErrors![ErrorNumber] = intError
    rstErrors![ErrorLine] = intErl
    rstErrors![ErrorProc] = aERH_Stack_TSB(intERH_Pointer_TSB)
  
    For intCounter = LBound(aERH_Stack_TSB) To UBound(aERH_Stack_TSB)
      If aERH_Stack_TSB(intCounter) <> "" Then
        varTemp = varTemp & Format(intCounter, "00") & Space$(intCounter * 2) & aERH_Stack_TSB(intCounter) & Chr$(13) & Chr$(10)
      End If
    Next intCounter
  
    rstErrors![CallStack] = varTemp

    If fExpandedStats Then
      varTemp = "Current User: " & CurrentUser() & Chr$(13) & Chr$(10)
      varTemp = varTemp & "Access Version: " & SysCmd(SYSCMD_ACCESSVER) & Chr$(13) & Chr$(10)
      varTemp = varTemp & "Jet Version: " & DBENgine.Version & Chr$(13) & Chr$(10)
      varTemp = varTemp & "LoginTimeout: " & DBENgine.LoginTimeout & Chr$(13) & Chr$(10)
      varTemp = varTemp & "Current Diretory: " & CurDir & Chr$(13) & Chr$(10)
      varTemp = varTemp & "Access Directory: " & SysCmd(SYSCMD_ACCESSDIR) & Chr$(13) & Chr$(10)
      varTemp = varTemp & "Runtime Version: " & SysCmd(SYSCMD_RUNTIME) & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB Path/Name: " & dbsCurrent.name & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB Version: " & dbsCurrent.Version & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB CollatingOrder: " & dbsCurrent.CollatingOrder & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB Connect: " & dbsCurrent.Connect & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB QueryTimeout: " & dbsCurrent.Querytimeout & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB Transations: " & dbsCurrent.Transactions & Chr$(13) & Chr$(10)
      varTemp = varTemp & "CurrentDB Updatable: " & dbsCurrent.Updatable & Chr$(13) & Chr$(10)

      rstErrors![AdditionalInfo] = varTemp

    End If
  rstErrors.Update

  ' Close the objects
  rstErrors.Close
  dbsCurrent.Close
  
End Sub

To implement the error handling procedures in this category, follow these steps:

1. Your application's startup procedure should call the fERH_Initialize_TSB() procedure. This procedure initializes the error handling code by clearing the error handling array and resetting its pointer. Calling this procedure should be one of the first thing your application does at startup.
2. Every one of your procedures should have a defined entry point. The entry point is a section of code at the beginning of the procedure. Typically, it is immediately after any DIM statements, and before any actions are performed. In this entry section, place a call to the ERH_PushStack_TSB() procedure, passing the name of the procedure. The ERH_PushStack_TSB() procedure takes the procedure name you pass and places it on the error handling stack. This stack is then used when an error occurs to display the call tree of procedures in place when the error occurred. See the example code for the ERH_PushStack_TSB() procedure to see how to do this.
3. Every one of your procedures should have a *single* defined exit point. Code in this section is executed every time the procedure finishes executing. In this exit section, place a call to the ERH_PopStack_TSB() procedure. This procedure removes the procedure's name from the stack. Obviously, for this to work correctly, you need to avoid Exit Function or Exit Sub calls anywhere in the proc. If you use such Exit statements, the ERH_PopStack_TSB() function won't get called, and the error handling stack will be incorrect. See the example code for the ERH_PopStack_TSB() procedure to see an example of this.
4. Every one of your procedures should have error handling enabled in the form of an On Error Goto statement. This statement defines a label in the procedure that should be executed when an error occurs. In the error handling section of the procedure, you call the ERH_Handler_TSB() procedure to handle the error. This procedure logs the error to a table or file (depending on how you set the ERH_LOG_TO_TABLE constant in the declarations section of your code). See the example for the ERH_Handler_TSB() procedure to see this in action.

This code contains the elements necessary for the error-handling procedures to work. The following constants and variables are defined:

• ERH_MAXITEMS_TSB specifies the maximum number of procedures to store in the procedure stack. The default value is 20.
• ERH_USEITEMS_TSB is a constant that adds two elements to the procedure array for internal tracking. You should not adjust this value.
• aERH_Stack_TSB is an array of strings that holds the procedure stack.
• intERH_Pointer_TSB is an integer that is used internally to identify the current procedure's position within the stack.
• ERH_EXPANDEDINFO is a True/False constant. Set this value to True if you want to include expanded information in the error log.
• ERH_DISPLAY_ERROR is a True/False constant. Set this value to True if you want to display an error dialog in the event of an error.
• ERH_LOG_TO_TABLE is a True/False constant. Set this value to true to log errors to a table. If this value is set to True, the ERH_LOG_TABLE constant must be set to the name of a table to log errors to. If this value is set to false, errors are logged to a file identified by the ERH_LOG_FILE constant.
• ERH_LOG_TABLE is a string constant. Set this value to the name of the table you want to log errors to. This value is only used when the ERH_LOG_TO_TABLE constant is set to True.
• ERH_LOG_FILE is a string constant. Set this value to the path and name of the text file you want to log errors to. This value is only used when the ERH_LOG_TO_TABLE constant is set to False.

Although developing robust applications can require a substantial amount of additional work, the end result is well worth it. End users base their impressions of your application on a number of factors, any number of which may cause problems. By applying the techniques outlined in this session, you should be able to create applications that avoid these problems.