Preparing Your Access 2003 Database for Deployment, Part 2

  • Article

 

Frank Rice
Microsoft Corporation

September 2005

Applies to:
    Microsoft Office Access 2003
    Microsoft Office 2003 Editions

Summary: While planning and creating your Access application, there are decisions to make and additional options to consider when preparing for deployment. This is part two of a two-part series designed to help you prepare your database application for release. (21 printed pages)

Contents

Setting Startup Options
Protecting Your Application
Using the Access Run-Time Components
Using MDE Files
Deploying Your Application with the Package Wizard
Conclusion
Additional Resources

Setting Startup Options

Startup options affect the behavior and appearance of a Microsoft Office Access 2003 application when it is first launched or opened. Because these settings stay with the database, they are stored in the database Properties collection, and only apply to the database in which they are set. You can set startup options several ways: through the Startup Options dialog box, from the command line, by using the Custom Setup Wizard that comes with the Access Developer Extensions (as part of Microsoft Visual Studio Tools for the Microsoft Office System), or programmatically.

Note   For more information about the Access Developer Extensions, see Build with Access 2003 Developer Extensions on the MSDN Office Developer Center.

For your custom applications, it is recommended that you set them up with the Custom Startup Wizard or set them up programmatically.

The Startup dialog box (click to see larger image)

Figure 1. The Startup dialog box (click picture to see larger image)

Startup options do not initially exist for new databases; they do not exist until you make a change to them in the user interface or in programming code. This means that, when you work with the startup options in code, you should always first check that the property exists. One way to do this is to trap for the Property Not Found error, as is demonstrated later in this article.

Startup Options Summary

The following section discusses the different options available from the Startup Options dialog box and the values that you can use to manipulate startup options in code. The name values are the exact names that you should use in code.

Startup Options

The following options are equivalent to the standard startup options found in the Startup Options dialog box.

Table 1. Startup options in the Startup Options dialog box

Name Value Description
AppTitle String The title of an application, as displayed in the title bar
AppIcon String The file name and path of an application's icon
StartupMenuBar String Sets the default menu bar for the application
AllowFullMenus True/False Determines if the built-in Access menu bars are displayed
AllowShortcutMenus True/False Determines if the built-in Access shortcut menus are displayed
StartupForm String Sets the form or data page to show when the application is first opened
StartupShowDBWindow True/False Determines if the database window is displayed when the application is first opened
StartupShowStatusBar True/False Determines if the status bar is displayed
StartupShortcutMenuBar String Sets the shortcut menu bar to be used in all forms and reports
AllowBuiltInToolbars True/False Determines if the built-in Access toolbars are displayed
AllowToolbarChanges True/False Determines if toolbar changes can be made

Advanced Option

The AllowSpecialKeys option (True/False value) determines if the use of special keys is permitted. It is equivalent to the advanced startup option found in the Startup Options dialog box.

Extra Options

The following options are not available from the Startup Options dialog box or from any other Access user interface component; they are only available in Microsoft Visual Basic for Applications (VBA) code.

Table 2. Startup options only available in VBA code

Name Value Description
AllowBypassKey True/False Determines if the SHIFT key can be used to bypass the application load process
AllowBreakIntoCode True/False Determines if the CTRL+BREAK key combination can be used to stop code from running
HijriCalendar True/False Applies only to Arabic countries; determines whether the application uses Hijri or Gregorian dates

Setting Startup Options in Your Application

The recommended way to set startup options in your Access applications is by using the Custom Startup Wizard. To create startup options by using the Custom Startup Wizard, install the Access Developer Extensions (which are included with Visual Studio Tools for the Microsoft Office System), start the Custom Startup Wizard, and follow the wizard screens.

A more difficult approach is to set the startup options for your MDB application by using VBA. To do this, you can use the Properties collection of the current database (CurrentDB) object, which is a method of the Application object. It does not matter whether you are using Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADO), accessing the startup options is the same for both.

If you try to retrieve or set a startup property that does not yet exist, the Jet/DAO error number 3270, Property not found, is generated. In your code, you should trap for this error. If it occurs, you know that you must create and append the property to the Properties collection. The following code demonstrates how to trap for the error and then set the Application Title property. Notice the use of the generic Object variables to hold the CurrentDB and Property values. This is because, internally, Access is using the DAO Database and Property objects to work with startup properties, but you cannot explicitly use those objects without setting a reference to the DAO Object Library. The reason for using the generic object variables is so that we do not assume a particular reference to either DAO or ADO, as either data access method works with this sample code.

Public Sub SetMDBAppTitle()
   Dim dbs As Object
   Dim prp As Object
   Dim strTitle As String

   Const PROPERTY_NOT_FOUND As Integer = 3270
   Const TEXT_TYPE As Integer = 10
   ' Equivalent to DAO dbText data type.
   Const BOOL_TYPE As Integer = 1
   ' Equivalent to DAO dbBoolean data type.
   Const LONG_TYPE As Integer = 4
   ' Equivalent to DAO dbLong data type.

   On Error GoTo SetMDBAppTitle_Err

   Set dbs = Application.CurrentDb
   strTitle = "Setting *.MDB Startup Options"

   ' Try to set the property. If it fails, the property does not exist.
   dbs.Properties("AppTitle") = strTitle

   ' Refresh the title bar to reflect the change.
   Application.RefreshTitleBar

ExitLine:
   dbs.Close
   Set dbs = Nothing
   Set prp = Nothing
   Exit Sub

SetMDBAppTitle_Err:
   If Err.Number = PROPERTY_NOT_FOUND Then
      ' Create the new property.
      Set prp = dbs.CreateProperty("AppTitle", TEXT_TYPE, strTitle)
      dbs.Properties.Append prp
      Resume Next
   Else
      Resume ExitLine
   End If

End Sub

To remove the startup property and return to the default value, use the Delete method of the Properties collection. If you try to delete a property that does not exist, the Jet/DAO error number 3265, Item not found in this collection, is generated. In your code, you should trap for this error and, if it occurs, simply exit the procedure. The following code demonstrates how to trap for the error and update the title bar if necessary.

Public Sub RemoveMDBAppTitle()
   Dim dbs As Object

   Const ITEM_NOT_IN_COLLECTION As Integer = 3265

On Error GoTo RemoveMDBAppTitle_Err

   Set dbs = Application.CurrentDb

   ' Remove the startup property.
   dbs.Properties.Delete ("AppTitle")

   'Refresh the title bar to reflect the change.
   Application.RefreshTitleBar

ExitLine:
   dbs.Close
   Set dbs = Nothing
   Exit Sub

RemoveMDBAppTitle_Err:
   If Err.Number = ITEM_NOT_IN_COLLECTION Then
      Resume ExitLine
   Else
      Resume Next
   End If

End Sub

Adding a Splash Screen and Startup Form

A splash screen is used to display important information about a program during startup and to give the user something colorful to look at and read while waiting for the program to complete its initial processing.

A typical splash screen contains the following information:

  • The program name and version number
  • The registered user's name and company
  • Copyright information
  • The developer's company name and logo

The splash screen in the Northwind sample database

Figure 2. The splash screen in the Northwind sample database

A startup form, such as a switchboard, is used as the entry point for users to work with your application. As stated at the start of this article, you need to include some type of startup form with your application. The startup form is also an effective approach to organizing and presenting tasks; you can use the form as a control center for the application. One example of this type of form is the switchboard that the Database Wizard creates.

The switchboard in the Northwind sample database

Figure 3. The switchboard in the Northwind sample database

When designing a startup form, try to bring your users' primary tasks as close to the surface of the application as possible. For example, if your application focuses on one task, you could use a form for that particular task as your startup form.

To use a splash screen in your application, you first need to create a form with the buttons and options that allow your customers to move through your application. Then, you need to configure the settings that ensure that the form is displayed when the application starts. You can do this manually or through code. For more information about manually setting the options to display a form at startup, search Access Help for the topic "Display a form or data access page at startup".

To set the startup form programmatically, you can use functions similar to the following:

  1. Create a module, and type or paste the following function.

    Function ShowSplashScreen(FormName As String, _
                          SecondsToDisplay As Single)
   ' Open the splash form.
   DoCmd.OpenForm FormName

   ' Pause for the desired length of time. 
   Dim TimerStart As Single
   TimerStart = Timer
   While Timer < TimerStart + SecondsToDisplay
      DoEvents
   Wend

   ' Close the splash screen.
   DoCmd.Close acForm, FormName
End Function

  2. Save the new module as Splash.

Using the ShowSplashScreen() Function

To use the ShowSplashScreen() function, follow these steps:

  1. Create a form, not based on any table or query, with the text and graphics you want to display on the splash screen. For more information, see the Setting Form Properties section later in this article.

  2. In the Database window, click the Macros tab and then click New.

  3. Create a macro with the following actions and save it as AutoExec:

    Table 3. AutoExec actions to display a splash screen at startup

    Macro Name Action
    AutoExec RunCode
      Function Name: ShowSplashScreen ("FormName", 5)

Setting Form Properties

It is recommended that you set the form properties of the splash form as follows:

  • On the Format tab of the form property sheet:
    • Scroll Bars: Neither
    • Record Selectors: No
    • Navigation Buttons: No
    • Auto Resize: Yes
    • Auto Center: Yes
  • On the Other tab of the form property sheet:
    • PopUp: Yes
    • Modal: Yes

Protecting Your Application

Protecting the investment that you made to create your application is very important. This protection can be as simple as hiding the Database windows or implementing workgroup (user-level) security. In addition, you probably want to protect the VBA code in your application. You can do this by setting a password to get to the code or by creating an MDE-formatted version of your database.

For more information, see part 1 of this series of articles, Preparing Your Access 2003 Database for Deployment, Part 1. Or, see the Additional Resources section at the end of this article.

Next, we discuss using the Access run-time component in your application.

Using the Access Run-Time Components

One of the statements I sometimes hear new Access developers say is, "I cannot develop applications in Access because my company refuses to buy each user a copy of the product." This is a common misconception.

Some of this misconception may be due to the fact that that there is no such thing as a stand-alone Access application; to run an Access application you need to have either the retail version of the product or the run-time version of Access installed. The Access runtime is provided as part of the Access Developer Extensions. The Access Developer Extensions include three features that may be of interest:

  • A royalty-free license to redistribute the Access 2003 run-time components
  • The Custom Startup Wizard, which can help you prepare your database for distribution
  • The Package Wizard, which you use to build the installation package for your application
  • The Property Scanner Add-in, which you can use to search values in the properties of any object in your database

Differences Between the Access Runtime and the Retail Versions of Access

The run-time version of Access disables certain things that are available in the retail version. It is important to understand the differences between the standard and run-time versions of Access. The following differences have definite implications for the way you develop any applications that you expect to run with the run-time version:

  • The Database, Macro, and Module windows are not available in the run-time environment. You need to build a user interface for your application.
  • No Design views are available.
  • No built-in toolbars are available.
  • Many windows, menus, and commands are invisible in the run-time environment. For example, the Window | Hide and Window| Unhide commands are invisible. Although these and other commands are not visible, their functions are accessible by using code.
  • Only generic error handling is available. You must build error handling into your run-time applications.
  • You must build your own custom Help files for the run-time application.

Making some of the features unavailable protects your application. For example, the absence of the Database and Design windows helps to ensure that your users cannot modify your application while it is running under the Access run-time version.

Installation of the Access Run-Time Components

When you package your application using the Package Wizard and you include the run-time components, they are automatically installed when your application is installed.

But what happens if the user has installed the run-time components with another application and decides to remove that one? This is not a problem. Access 2003 manages installations of the run-time components by keeping track of how many times an installation package has tried to install the program. This is called RefCounting. This means that Access is only installed on a computer once, no matter how many times you try to install it. Each additional time you "install" Access, the RefCount is incremented. Each time you "remove" Access, the RefCount is decremented. When the RefCount is less than one, the Microsoft Windows Installer removes Access 2003 from the computer.

Because of RefCounting, you can be assured that when you install two different applications that each include the Access 2003 run-time components, and then remove one of them, Access 2003 is still available for the remaining application. This mechanism applies equally to both the retail and run-time versions of Access 2003, so there is no need to go to extra effort to determine if another installation of Access 2003 is installed as part of your installation package.

Simulating the Microsoft Access Run-Time Environment

Because some standard Microsoft Access features are hidden or disabled in the run-time environment, it is important that you make sure that your application works correctly in the run-time environment before distribution.

You can test and debug your application by using the Access /runtime startup command-line option to turn off full Access features and simulate the run-time environment. Your application looks and functions as if your Windows registry contains the run-time licensing key.

You can specify the /runtime command-line option by clicking Run on the Start menu or by creating a shortcut.

To create a shortcut to start your application with the /runtime option

  1. Create a shortcut to start Microsoft Access.

    1. Open Windows Explorer.

    2. On the File menu, point to New, and then click Shortcut. The Create Shortcut dialog box is displayed.

    3. In the Type the location of the item text box, type:

      Complete path to the Access EXE fileComplete path to the Access database**/runtime**

    4. Click Next, type a name for the shortcut, and then click Finish.

  2. Right-click the shortcut, click Properties, and then click the Shortcut tab.

  3. In the Target box, following the path to MSAccess.exe, type the path to the database you want to open, and then type /runtime. If the path contains spaces, enclose it in quotation marks.

    For example, the following command line starts Access and then opens a sample database application in run-time mode.

    "C:\Program Files\Microsoft Office\Office\MSAccess.exe" 
"C:\Program Files\Microsoft Office\Office\Samples\Sample.mdb" 
/runtime

Using MDE Files

If your database contains VBA code, saving your application as an .mde file compiles all modules, removes all editable source code, and compacts the database. Your code continues to run, but it cannot be viewed or edited. Saving your database as an .mde file also protects the intellectual property contained in your source code. Your database continues to function as it did—you can still update data and run reports.

Make sure that you first save your .mdb file before you create an .mde file, because the .mde file does not contain the source code and Access disables the following actions:

  • Viewing, modifying, or creating forms, reports, or modules in Design view
  • Adding, deleting, or changing references to object libraries or other databases
  • Changing code, because an .mde file contains no source code
  • Importing or exporting forms, reports, or modules. However, tables, queries, data access pages, and macros can be imported from or exported to non-MDE databases

Considerations Before Saving Your Database as an MDE File

Some restrictions may prevent you from saving your Access database as an .mde file:

  • If your database is replicated, you must first remove replication.
  • If your database refers to another Access database or add-in, you must save all Access databases or add-ins in the chain of references as .mde files.
  • Additionally, if you define a database password or user-level security before saving an Access database as an .mde file, those features still apply to an .mde file created from that database. If your Access database has a database password or user-level security defined and you want to remove these features, you must do so before saving it as an .mde file.

To save an Access database that uses user-level security as an .mde file, you must meet the following requirements:

  • Your user account must have Open/Run and Open Exclusive permissions for the database.
  • Your user account must have Modify Design or Administer permissions for any tables in the database, or you must be the owner of any tables in the database.
  • Your user account must have Read Design permissions for all objects in the database.

About References and MDE Files

If you try to create an .mde file from an Access database (.mdb file) or an add-in (.mda file) that refers to another Access database or add-in, Access displays an error message and does not let you complete the operation. To save a database that refers to another database as an .mde file, you must save all databases in the chain of references as .mde files, starting from the first database referred to. After saving the first database as an .mde file, you must then update the reference in the next database to point to the new .mde file before saving it as an .mde file, and so on.

For example, if Database1.mdb references Database2.mdb, which includes a reference to Database3.mda, you proceed as follows:

  1. Open Database2.mdb and change its reference to point to the new Database3.mde.
  2. Save Database2.mdb as Database2.mde.
  3. Open Database1.mdb and change its reference to point to the new Database2.mde.
  4. Save Database1.mdb as Database1.mde.

Create an MDE File

Before creating an .mde file, consider the following cautions:

  • Save a copy of your original Microsoft Access database in a secure location. If you need to change the design of the objects in the database, you must do so in the original Access database, and then resave the Access database as an .mde file.
  • You must use the newest file format to create an .mde file. For Access 2002 or Access 2003, you must use the Access 2002–2003 file format. For Access 2000, you must use the Access 2000 file format.
  • You also cannot convert an Access database saved as an .mde file in later versions of Access. You can, however, run the .mde file in a later version of Access.

If your database enabled user-level security, you must meet certain requirements before you can save it as an .mde file:

  • You must join the workgroup information file that defines the user accounts used to access the database, or that were in use when the database was created.
  • Your user account must have Open/Run and Open Exclusive permissions for the database.
  • Your user account must have Modify Design or Administer permissions for any tables in the database, or you must be the owner of any tables in the database.
  • Your user account must have Read Design permissions for all objects in the database.

There are two ways to create an .mde file. You can either create it while a database is open or you can create it while no database is open. In either case, you must have exclusive access to the database. Make sure that no other users or instances of Access have the database open.

To create an .mde file from the currently open database, do the following:

  1. On the Tools menu, click Database Utilities, and then click Make MDE File.
  2. In the Save MDE As dialog box, specify a name, drive, and folder for the database.

To create an .mde file when no database is open, do the following:

  1. On the Tools menu, click Database Utilities, and then click Make MDE File.
  2. In the Database To Save As MDE dialog box, specify the database you want to save as an .mde file, and then click Make MDE.
  3. In the Save MDE As dialog box, specify a name, drive, and folder for the database.

Deploying Your Application with the Package Wizard

The Package Wizard included with the ADE provides a simple way to bundle and deploy your application. The wizard guides you through the steps necessary to bundle a stand-alone Access application into a setup package. You can also use the Package Wizard to include the Access runtime in your packages and to create shortcuts that invoke the appropriate Access files. The output of the Package Wizard is a Microsoft Windows Installer (.msi) setup file that guides users through the installation of your Access application.

Deploying Access Applications Using the Package Wizard

To demonstrate using the Package Wizard, create a package using the Northwind sample database that comes with Access.

  1. To start the Package Wizard, click Start, point to Programs (or All Programs), point to Microsoft Office, point to Microsoft Office Access 2003 Developer Extensions, and then click Package Wizard.

  2. On the Welcome page, click Next.

  3. On the Package Wizard for the Microsoft Office Access 2003 Developer Extensions page, choose an existing template or use the wizard to create a customized template.

    The Package Wizard for the Microsoft Office Access 2003 Developer Extensions page of the Package Wizard

    Figure 4. The Package Wizard for the Microsoft Office Access 2003 Developer Extensions page of the Package Wizard

    Templates are .xml files that store the configuration settings from a prior use of the wizard. Before exiting the wizard, you are prompted to save the current wizard settings. Then, the next time you invoke the wizard and elect to use a saved template, the pages are pre-populated with values from the template. Leave the default option set to Create a new template. Click Next.

  4. On the Database to Package page, specify the database that you want to package and the default location of the folder and subfolder on the end-user computer to which you want to distribute the solution.

    The Database to Package page of the Package Wizard

    Figure 5. The Database to Package page of the Package Wizard

  5. Specify that you want to include the Access runtime as part of your solution.

  6. In the Output Options section, indicate the location on your computer where the files created by the wizard are saved.

  7. Type the path and name of the Northwind database or browse to its location, and then click OK. In a typical installation, the Nortwind.mdb database is located at C:\Program Files\Microsoft Office\Office11\Samples.

  8. Select the Include Access 2003 runtime check box. Leave the remaining fields set to the default options, and then click Next.

  9. On the Shortcut Properties page, specify information about the shortcuts you want to appear on the end-user computer. Specify the location of the shortcut, the name to appear, and the icon to use. On this page, you can also select additional parameters that the database uses when it starts. Click Next.

    The Shortcut Properties page of the Package Wizard

    Figure 6. The Shortcut Properties page of the Package Wizard

  10. On the Other Files or Registry Keys to Install page, specify any additional files or registry keys required by your solution. You can also specify a Help file and the splash page you created previously to appear when your application is first opened. Click Next.

    The Other Files or Registry Keys to Install page of the Package Wizard

    Figure 7. The Other Files or Registry Keys to Install page of the Package Wizard

  11. On the Installer Experience page, specify some of the general properties of your solution, such as the name and installation language.

    The Installer Experience page of the Package Wizard

    Figure 8. The Installer Experience page of the Package Wizard

    Note   You must cache the setup files in the Installation Language list before you can use them in your solution. The options in the Installation Language list are cached based on the existence of the following files:
    Base.msi
    AccessRT.msi
    AccessRT.cab
    Setup.exe

    If one or more of these files is missing, the language file is not cached. If available, you can locate these files in the following directory:
         local_drive:\Documents and Settings\user_name\Desktop\ADE11\Templates

    You can also specify the information that users see when they choose the custom installation option. You can specify the location of the End User License Agreement (EULA) to include with your application. The EULA must be in Rich Text Format (.rtf).

  12. Finally, indicate what images, if any, appear while the application installs. Click Next.

  13. On the Installer Package Properties page, specify the information that appears on the Add or Remove Programs page in the Control Panel. For example, you may choose to include the URL to your support site or other support contact information. Next, specify the information that appears when the user right-clicks the setup (.msi) file and then clicks the Properties option. Click Next.

    The Installer Package Properties page of the Package Wizard

    Figure 9. The Installer Package Properties page of the Package Wizard

  14. On the Completing Your Installer Package page, save the settings that you specified as a template for later use. You can also choose to create a batch file to run your setup file or install other files. Click Finish.

    The Completing Your Installer Package page in the Package Wizard

    Figure 10. The Completing Your Installer Package page in the Package Wizard

That's all you need to do to package your solution with the Access run-time files.

Note   It is recommended that you test your application by installing it on a computer that has never had a copy of either the retail or run-time version of Access installed.

You are now ready to take your packaged application, and any printed documentation, and distribute it to your distribution sites.

Conclusion

When creating an application for deployment, setting startup options allows you to customize the appearance of what your user sees when starting the application. Setting security prior to deployment helps protect the application from unauthorized users. Packaging your product with the Access run-time components provides a royalty-free method of running Access on you user's computers. In addition, by saving the file as an .mde file, you can help protect your code and other objects from undesirable access. Further, creating a setup package using the Package Wizard addresses many details for you. Planning and developing your application while remembering these and the other considerations discussed in this article helps to make the process of developing your application an enjoyable one, and helps to provide your customer with a professional and easy-to-use product.

Additional Resources

You can find addition information about Access and deploying your Access application in the following resources.