When users launch your application using the runtime version of Access, the Database window is not visible. It’s actually there, but it is hidden because its colors are set to the same colors as the Windows background color. This means that you can interact with the Database window using code, but the users of your application will be unable to interact with the Database window directly.

The fact that the Database window is hidden tends to be a double-edged sword. On one hand, it prevents most users from modifying the objects in your application. On the other hand, it puts the responsibility on you to build a complete interface for your application. Remember that, for you as a developer, the Database window is a starting point. You must provide a different starting point and navigational tools for your users to maneuver throughout your application.

The users of your application won’t have direct access to any design views, which means that they are unable to create or modify tables, queries, forms, reports, macros, or modules. You still can get to all this functionality through code, though. You can build a wizard that enables your users to define all aspects of a query or some other object, for example, and then build the query (or other object) using ADO (ActiveX Data Objects) code. Again, this helps protect your application from novice users, but it puts the pressure on you to ensure that your application provides its users with all the functionality they need.

All built-in toolbars are completely unavailable with the runtime version of Access, which means that you must design your own toolbars and attach them to your forms and reports as appropriate. This is covered in the “Adding Custom Menus and Toolbars” section of this chapter.

Built-in toolbars are not supported at all when using the runtime version of Access. Menus are simply modified after the runtime key is found. Many menu items are hidden in the runtime version. These hidden menu items prevent users from making changes to your application.

Although many of the menu commands are hidden from the user, they can be accessed by using the DoMenuItem command. In other words, the functionality is there, but it is simply hidden from your users.

Several keystrokes are unavailable to your users when they run your application with the runtime version of Access. Table 32.1 lists these keystrokes.

As you can see, these are keys that you would rarely, if ever, want your users to use. You might consider the disabling of these keystrokes a positive side effect of using the runtime version of the product.

You must be concerned about several things when designing an application for use with the Access runtime version. Although the following items are niceties in any application, they are a mandatory aspect of developing an application for use with the Access runtime version:

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
    Call ToolBarShow("tbrMainForm", True)
    Me.fsubClients.Requery

Exit_Form_Activate:
    Exit Sub

Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
End Sub

Sub ToolBarShow(strToolbar As String, fShow As Boolean)
   DoCmd.ShowToolbar strToolbar, _
         IIf(fShow, acToolbarYes, acToolbarNo)
   DoCmd.ShowToolbar "Form View", _
         IIf(fShow, acToolbarNo, acToolbarWhereApprop)
End Sub

Private Sub Form_Deactivate()
    Call ToolBarShow("tbrMainForm", False)
End Sub

Setting Startup Options

Access 2000 and Access 2002 provide you with several startup options that enable you to control what happens to your application when it is loaded. Table 32.2 lists each option in the Startup dialog box.

Table 32.2. Startup Options

Option	Function
Application Title	Sets the AppTitle property, which displays a custom title in the application title bar.
Application Icon	Sets the AppIcon property, which displays a custom icon in the application title bar.
Menu Bar	Sets the StartupMenuBar property, which specifies the custom menu bar displayed by default when the application is loaded.
Allow Full Menus	Sets the AllowFullMenus property, which allows or restricts the use of Access menus.
Allow Default Shortcut Menus	Sets the AllowShortcutMenus property, which allows or restricts the use of standard Access shortcut menus (menus accessed with a right-click).
Display Form/Page	Sets the StartupForm property, which specifies the form displayed when the application is loaded.
Display Database Window	Sets the StartupShowDBWindow property, which determines whether the Database window is visible when the application is opened.
Display Status Bar	Sets the StartupShowStatusBar property, which determines whether the status bar is visible when the application is opened.
Shortcut Menu Bar	Sets the StartupShortcutMenuBar property, which specifies that a menu bar be displayed by default as the shortcut (right-click) menu bar.
Allow Built-in Toolbars	Sets the AllowBuiltInToolbars property, which indicates whether built-in toolbars are available to your users.
Allow Toolbar/Menu Changes	Sets the AllowToolbarChanges property, which determines whether your users can customize toolbars in the application.
Use Access Special Keys	Sets the AllowSpecialKeys property, which determines whether the user can use keys such as F11 to display the Database window, Ctrl+F11 to toggle between custom and built-in toolbars, and so on.

Note

Notice the Use as Form and Report Icon property available when an Application icon is designated. When checked, the icon designated as the Application icon is used as the icon for forms and reports.

As you might have guessed, many of these options apply only when you are running the application under the full version of Access (as opposed to the runtime version.) You do not need to set the Display Database Window property, for example, if your application will be running only under the runtime version of Access. The Database window is never available under the runtime version of the product, so Access ignores this property when the application is run under the runtime version. Nevertheless, I like setting these properties to ensure that the application behaves as I want it to under both the retail and runtime versions of the product.

All the properties can be set by using the Startup dialog box or by using code. If you use code, you must make sure that the property exists for the Database object before you set it. If the property does not exist, you must append the property to the Database object.

Only users with Administer permission for the database can modify the Startup properties. If you want to ensure that certain users cannot modify the startup options of the database, you must make sure that they do not have Administer permissions.

As part of setting startup options for your database, you should determine what code, if any, is run when the application is loaded. You can accomplish this in one of two ways. You can start the application with an AutoExec macro and then issue a RunCode action to execute a VBA procedure. The other option is to designate a Startup form for the application and then call a custom routine from the Open event of the Startup form. I always use the second option because it provides you with more control, and you can include error handling in the code module behind the startup form, whereas an AutoExec macro cannot contain error handling. The code shown in Listing 32.3 is called from the Open event of the Startup form for the Time and Billing application. This code, and the rest of the code in this chapter, is located in the CHAP32.MDB database file on the sample code CD-ROM.

Example 32.3. Setting Options from a Startup Form Routine

Private Sub Form_Open(Cancel as Integer)
    'Turn the hourglass on
    DoCmd.Hourglass True

    'Display the splash screen
    DoCmd.OpenForm "frmSplash"

    'Verify links and attempt to relink if necessary
    If LinkTables Then

        'If linking successful, load company info
        Call GetCompanyInfo
    Else

        'If linking unsuccessful, close the splash screen
        'and Quit
        DoCmd.Close acForm, "frmSplash"
        Cancel = True
        DoCmd.Quit
    End If

    'Turn hourglass off
    DoCmd.Hourglass False
End Sub

This routine, placed in the Open event of the startup form, first displays an hourglass. It uses the OpenForm method to open a form called frmSplash. The routine calls a user-defined function that ensures that the database tables are linked successfully. This LinkTables routine is covered in the “Automating the Process of Linking to Tables” section of this chapter. If the LinkTables function returns False, the frmSplash form is closed, and the application is exited. As long as the tables’ links have been established successfully, the routine proceeds to call a routine called GetCompanyInfo, where it loads frequently used information into a type structure. The hourglass mouse pointer is removed, and the splash screen is unloaded after it reaches a timer value.

To add polish to your application and ensure that the help you provide to your users applies to what they are looking at in your application, you must provide a custom Help file. In essence, adding help to your application involves first creating Help files. You then must add help to the various objects in your application. There are many excellent tools available that can assist you in this.

Before you even bother trying to run your application under the runtime version, you should fully test and debug the application under the retail version of the product. When you are fairly confident that you have all the kinks worked out of the application, you are ready to test it in the runtime environment.

If you have the Microsoft Office 2002 Developer tools installed, Microsoft provides a very easy way to test an application and see how it will perform under the runtime version of Access without having to actually create distribution disks. You can do this by using the /Runtime command-line switch. The /Runtime switch forces Access to load in runtime mode. Here’s how it works:

c:program filesmicrosoft officeofficemsaccess.exe c:databaseschap32.mdb /runtime

After you load the application with the /Runtime switch, you should once again test all aspects of the application. At times, you might want to test to see whether the application has been launched with the runtime or retail version of the product. You can accomplish this with the following code:

If Not SysCmd(acSysCmdRuntime) _
      And CurrentUser <> "Admin" Then
      MsgBox "You aren't allowed here"
End If

The SysCmd function, when passed the constant acSysCmdRuntime, checks to see whether the application was launched using the runtime version of Access. In this case, if the program was run with the retail version of Access and CurrentUser is not Admin, a message is displayed, indicating that the user is not allowed. Of course, you easily could modify this routine to check for other users and to quit the application if an unauthorized person attempts to launch the application without the runtime version of the product.

After you have fully tested and prepared your application for distribution, you are ready to run the Packaging Wizard. The Packaging Wizard walks you through all the steps required to build distribution disks that include all the components your application needs to run. You launch the Packaging Wizard from VBE, as follows:

Before you can begin installing the application on workstations, you must deploy your project’s packages to a distribution site. Once again, this is accomplished using the Packaging Wizard. Complete the following steps:

The most important thing you must do when packaging and distributing your application is test the application on a machine that has never had a copy of Access or any Access runtime application installed. This ensures that your application includes all required components. I like to keep a “virgin” machine available for testing my application setups. Here’s what I do:

By following these steps, I ensure that I always have a “clean” machine on which to test my application. Obviously, it is imperative that you test all aspects of your application on the machine on which you performed the installation from your setup disks.

When you are ready to test the Setup process, follow these steps:

The installation process is completed. If you opted during the Packaging Wizard to create desktop shortcuts, they are created automatically when the Setup program is executed.

Access hard-codes locations for table links. This means that, if you install your application on another machine, the tables will not link successfully unless the other machine has exactly the same folder structure as you do on your machine. The code shown in Listing 32.4 checks to see whether the required tables are available. If they are not found in the expected location, the routine attempts to locate them in the same folder that holds them in the application database. If they still cannot be found, the user is given an opportunity to locate the files. If they still cannot be found, the application terminates.

Sub LinkTables()
    On Error GoTo LinkTables_Err:

        Dim objFileDialog As FileDialog
        Dim strFileName As String

        'Determine if links are ok
        If Not VerifyLink Then

            'If links not ok, attempt to link with default file name
            'in the current folder
            If Not ReLink(CurrentProject.FullName, True) Then

                'If still unsuccessful, allow user to locate the data database
                MsgBox "You Must Locate Tables to Proceed" & vbCrLf & _
                    "The Tables are Located in the Chap32Data Database" & _
                    vbCrLf & _
                    "in the Directory Where You Placed the Sample Files"

                Set objFileDialog = Application.FileDialog(msoFileDialogOpen)

                With objFileDialog
                    .Show
                    .AllowMultiSelect = False
                    strFileName = .SelectedItems(1)
                End With

                'Attempt to relink with the database the user selected
                If Not ReLink(strFileName, False) Then

                    'If still unsuccessful, display message to the user and
                    'return false from this routine
                    MsgBox "You Cannot Run This App Without Locating Data Tables"
                    LinkTables = False
                Else

                    'User successfully designated new location; return True
                    LinkTables = True
                    End If
                Else

                    'Data database located with default name in the same location
                    'as the application database; return True
                    LinkTables = True
                End If
            Else

                'Table links not broken; return True
                LinkTables = True
            End If

        Exit Function

    LinkTables_Err:
        MsgBox "Error # " & Err.Number & ": " & Err.Description
        Exit Function

    End Sub

The routine begins by executing a function called VerifyLink. The VerifyLink function is shown in Listing 32.5 and is found in the FinalLibrary.MDA file on the sample code CD. It first creates an ADOX Catalog object. It sets the ActiveConnection property of the Catalog object to the Connection property of the CurrentProject. The CurrentProject object returns a reference to the database using the library, rather than to the library itself. The heart of the routine is the For...Next loop. It loops through each Table object in the Tables collection of the Catalog object. If the table is linked, it attempts to reference the first field in the table. If an error occurs, the table link must be broken. The error number is non-zero, and the routine exits the For...Next loop. Because the function returns whether or not the error number is equal to zero, False is returned if an error occurs, and True is returned if no error occurs.

Function VerifyLink() As Boolean
    'Verify connection information in linked tables.

    'Declare Required Variables
    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.Table
    Dim strTemp As String

     'Point Database object variable at the current database
     Set cat = New ADOX.Catalog

     With cat
         Set .ActiveConnection = CurrentProject.Connection

         'Continue if links are broken.
         On Error Resume Next

         'Open one linked table to see if connection
         'information is correct.
         'For Each tdf In .Tables
         '    If tdf.Type = "LINK" Then
         '        strTemp = tdf.Columns(0).Name
         '        If Err.Number Then
         '            Exit For
         '        End If
         '    End If

         'Next tdf

         'If code above is too slow, this is the
         'less conservative alternative
         For Each tdf In .Tables

             If tdf.Type = "LINK" Then
                 strTemp = tdf.Columns(0).Name
                 Exit For
             End If

           Next tdf


       End With

       VerifyLink = (Err.Number = 0)

End Function

If the VerifyLink function returns a False, the Relink function is executed. The Relink function is shown in Listing 32.6. It receives two parameters. The first parameter is the name of the database to which the function will attempt to link. The second parameter is a Boolean variable that designates whether the database is considered the default database.

The function begins by modifying the status bar. It then creates a Catalog object and an instance of a custom class called DBInfo. The ActiveConnection property of the Catalog object is set equal to the Connection property of the current project. Next the FullName property of the DBInfo class is set equal to the name of the file that is passed as a parameter to the function. The DBInfo class extracts the path and the filename from the full filename. Just as with the VerifyLink function, the ReLink function uses a For...Next loop. As it loops through each table in the database, it attempts to establish a link to a database with the name passed as a parameter to the Relink function.

This is where the DefaultData parameter comes into play. The first time that the LinkTables routine calls the Relink function, it passes the name and path of the application database as the first parameter, and True for the second parameter. The Relink function then attempts to link to a database located in the same folder as the application database, but with the word Data appended to the end of the filename. For example, if the application database is named Membership, the Relink function looks for a database called MembershipData in the same location as the application database. If it is successful, it returns True, and, if it is unsuccessful, it returns False. I use this method to attempt to re-establish the link because I commonly place both the application and data databases on a client’s network, both in the same folder. When I do this, I employ a naming convention where the data database has the same name as the application database, but with the word Data appended to it.

If no data database with the expected filename is found in the folder where the application database is located (False was returned from the Relink function), the LinkTables routine uses the FileDialog object to display a File Open dialog box. This gives the user the opportunity to locate the data database. The filename and path that the user selects in the dialog box is passed to the Relink routine, along with False as the second parameter. Because the user has selected the file that he believes contains the data, there is no reason to append the word Data onto the filename. Once again the Relink routine loops through the Tables collection of the Catalog object, attempting to re-establish the broken links. If successful, it returns True, and, if unsuccessful, it returns False. If False is returned from the second call to the Relink function, the LinkTables routine exits the Access application.

Function ReLink(strDir As String, DefaultData As Boolean) _
    As Boolean

    Dim cat As ADOX.Catalog
    Dim tdfRelink As ADOX.Table
    Dim oDBInfo As DBInfo
    Dim strPath As String
    Dim strName As String
    Dim intCounter As Integer
    Dim vntStatus As Variant

    'Update status bar
    vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

    Set cat = New ADOX.Catalog
    Set oDBInfo = New DBInfo

    With cat

        'Use File Information class to extract the application
        'database file name
        .ActiveConnection = CurrentProject.Connection
        oDBInfo.FullName = strDir
        strPath = oDBInfo.FilePathOnly
        strName = Left(oDBInfo.FileName, InStr(oDBInfo.FileName, ".") - 1)

        'Disable error handling
        On Error Resume Next

        'Update progress meter
        Call SysCmd(acSysCmdInitMeter, "Linking Data Tables", .Tables.Count)

        'Loop through each table, attempting to relink
        For Each tdfRelink In .Tables
            intCounter = intCounter + 1
            Call SysCmd(acSysCmdUpdateMeter, intCounter)
            If .Tables(tdfRelink.Name).Type = "LINK" Then
                tdfRelink.Properties("Jet OLEDB:Link Datasource") = _
                    strPath & strName & IIf(DefaultData, "Data.Mdb", ".mdb")
           End If

           'If an error occurs, exit the loop
           If Err.Number Then
                Exit For
            End If
        Next tdfRelink
    End With

    'Remove the progress meter
    Call SysCmd(acSysCmdRemoveMeter)

    'Clear the status bar
    vntStatus = SysCmd(acSysCmdClearStatus)

    'Return whether or not an error occurred
    ReLink = (Err = 0)

End Function

You might not want to rebuild and redistribute Setup disks each time you change the design of your application database. Not only is this time-consuming, but also it is difficult to ensure that each user runs the Setup process in order to obtain the application database. If your organization is networked, it generally is much more effective to distribute application updates using replication. This involves making changes to the Design Master and then synchronizing with a hub after the changes are completed and tested properly. Replication is covered briefly in Chapter 20, “Developing Multiuser and Enterprise Applications,” and in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

Distributing the Time and Billing Application

To complete your applications and prepare them for distribution, switchboards should be added to assist the user with the process of navigating throughout the application. Error handling should be built in to most of the application’s routines. Custom toolbars and menu bars should be added to the application’s forms and reports. Startup options must be set. Make sure that you test the application under the runtime version of the product. You must then properly secure the application to meet the specific needs of your organization and to build the Setup disks.