Basics for Building Microsoft Office Access 2003 Runtime-Based Solutions
Microsoft® Office Access 2003
Microsoft Office System
Summary: Jan Fransen reviews how to use the Microsoft Office Access 2003 Runtime with the Microsoft Access 2003 Developer Extensions so users without Microsoft Access 2003 installed can still use Access-based applications. (8 printed pages)
Running in Runtime vs. Full Access 2003
Creating User Interface Objects
Setting Startup Options
Error Handling in VBA Code
Protecting Your Code with MDE Files
Streamlining the Distribution Process with the Access 2003 Developer Extensions Tools
Legend has it that some developers of business applications work in environments where all the computers run the same—and latest—version of all software. Here in the real world, though, it is rather common for Microsoft® Office Access developers to create applications that must run on computers that do not have Microsoft Office Access 2003 installed, or are running older versions of Access. For those developers, Access 2003 Runtime is a necessity.
The Access 2003 Runtime license comes with the Microsoft Office Access 2003 Developer Extensions, which is a component of Microsoft Visual Studio® Tools for the Microsoft Office System. A developer who owns Visual Studio Tools for Office can bundle his application (MDB files and any other support files) with Access 2003 Runtime and distribute it to as many users as necessary. The physical runtime files are included with Microsoft Office 2003 Professional Edition or Microsoft Office Access 2003.
Access 2003 Runtime is, in essence, Microsoft Access 2003, but with a few key features disabled. A user with Access 2003 Runtime installed instead of full Access can open and run an Access application (either an MDB file or, with a few extra steps, a SQL Server back-end database) but does not see the Database Window and cannot switch the view of any Access object to Design view.
The following features are not included as part of Access 2003 Runtime without additional customization:
- The Database window
- Title bar text/icon (can be specified by the MDB instead of by Access)
- Some menu items, such as Open and Save in the File menu, and the entire View, Format, and Tools menus
- Shortcut menus
- Built-in toolbars
- Access Help
- Designers (including Filter by Form)
- VBA editor
Figure 1 shows two instances of the same minimal MDB application. The instance on the left is running using Access Runtime, and the instance on the right is running in full Access. Not only is the difference stark, but the Access Runtime instance does provide a direct UI for opening objects as the intent is for developers to create the UI for this purpose.
Figure 1. An Access Runtime instance of a sample application compared to a full Access instance of the same application
The developer of an application destined for an Access Runtime environment must create the application in such a way that the "missing" features of Access in the Access Runtime version are not obvious to the user. This document focuses on the steps the developer should take to create an Access application that runs just as effectively in an Access 2003 Runtime environment as it does with full Access installed.
Simulating the Access 2003 Runtime Environment from Full Access 2003
If you want to see how your application looks and works in an Access Runtime environment, you can use the /runtime command line switch with the full Access executable. Create a shortcut with a target like this:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /runtime "drive:\path\file_name"
Note The line above has wrapped for printing. No line wrapping is supported in command line switches.
Test your application under Access Runtime (either by using the command line switch or by installing with Access Runtime on a clean computer) before deploying the application to other users.
At a bare minimum, every Access application destined for an Access Runtime environment must include a user interface object that allows navigation through the application. This object might be a main menu, but is more typically a replacement for the Database window—a form that allows the user to open the forms and reports that make up your application. You can use the Switchboard Manager (from the Tools menu, select Database Utilities and then choose Switchboard Manager) to create a standard switchboard, like the one shown in Figure 2, or you can create your own unbound or bound form to act as the main form for your application.
Figure 2. Access 2003 provides the Switchboard Manager to create a startup form for your application quickly
The limited menu provided by Access Runtime may be sufficient for simple applications, but most applications benefit from custom menus, toolbars, and shortcut menus. To create custom menus and toolbars, use the Customize dialog box (on the toolbar, choose Tools and then Customize) and select New on the Toolbars page of the dialog box. To create menu items or buttons, select and drag standard Access menu items onto your new menu or toolbar. You can also add menu items or buttons that call Access macros you created. By default, items created through the Customize dialog box are toolbars. You can change them to menus or shortcut menus by clicking the Properties button on the Toolbars page of the Customize dialog box.
If you create toolbars, menus, or shortcut menus that are specific to a certain form, you can use the Form properties Menu Bar, Toolbar, and Shortcut Menu Bar to specify the items you created. You can specify default toolbars, menus, and shortcut menus for all forms in the application in the Startup options discussed in the next section.
None of the standard Help for Access is available in Access Runtime. Often, a few well-written Help topics can head off a multitude of support calls and you can use a help authoring tool to create custom Help for your application. Once you have created the file, set the Help File property of each form to the name of your Help file. If your Help file includes topics for each form, you can also set the form's Help Context ID property so that the Help file opens directly to the most relevant page.
Once you create the items that make up your application's user interface, you can cause most of them to load automatically at startup by using the Access Startup dialog box. Use these startup options whether you are working in Access Runtime or in full Access.
Open the Startup dialog box by selecting the Tools menu then choose Startup in full Access. The Startup dialog box is shown in Figure 3.
Figure 3. The Startup dialog box provides a way to set defaults for your MDB file
You can use the Startup dialog box to specify the following:
- A title for the application. This title appears in place of Microsoft Access as the title in the application's window.
- An icon for the application. The icon appears next to the window's title and in the Windows taskbar in place of the standard Access icon.
- A form that opens automatically when the database is opened.
- A default menu bar, toolbar, and shortcut menu for the application. You can override these settings for a particular form by changing the corresponding form properties.
- Whether or not the Status Bar should be displayed on startup.
The following settings are ignored when the application runs Access Runtime and are relevant only when an application runs under full Access:
- Whether or not the Database window should be displayed on startup.
- Whether or not the default menus and toolbars should be allowed.
- Whether or not the user can make changes.
- Whether or not special keys are enabled in Access. In full Access, these special keys provide shortcuts to display certain windows and cause executing code to pause.
Once Startup properties are set, the application from Figure looks like Figure 4 at startup in Access Runtime.
Figure 4. The application now provides its own user interface when running in Access Runtime
When you run a VBA procedure in full Access and an error occurs, you see an error message like the one shown in Figure 5.
Figure 5. Access uses a default dialog box for unhandled VBA errors
From this message box, you can choose to debug the code in the Microsoft Visual Basic® Editor, or you can stop the code's execution and return to interactive Access.
The default error message for the same error under Access Runtime, shown in Figure 6, is quite different. Your only option is to click OK and subsequently exit the application entirely.
Figure 6. Access Runtime uses a different default dialog box for unhandled errors
Obviously, this is not the user interface you want to distribute to your users. It is always good programming practice to handle errors that may occur in VBA code, but when users work with the application in an Access Runtime environment, such error handling becomes crucial. Every procedure in an application should have, at a minimum, a basic
On Error GoTo label error handler.
Access macros provide no structure for handling errors. Macros are useful for creating custom menu items and defining shortcut keys, but because they lack error handling, use them only sparingly in any application.
It is important to remember that although an application looks different in Access Runtime, the MDB file itself is the same regardless of the environment under which it is running. With its hidden Database window and designers, developers are sometimes tempted to think that Access Runtime provides security for the application. It does not. Any user who has full Access installed can open the database, just as the developer can, and change the design of objects or code. If your application requires user-level security, you must implement it with the Security tools supplied as part of Access on the Tools menu, in the Security dialog box.
Access provides a database format that adds a measure of security to the design elements of your application: the form and report designs and code, and the code in VBA modules. This tool is the MDE file.
You can create an MDE file from an MDB file by choosing Tools and then Database Utilities and Make MDE File from the Access toolbar. When you make an MDE file, Access compiles all the code in the database and strips the editable version of the code from the new MDE file. This process both decreases the size of the resulting database and guarantees that no one can change the original source code. When you open an MDE file in Access, the form and report designers and the Visual Basic Editor are not available. In addition, you cannot transfer forms, reports, and modules to another database by export or import.
Note To create an MDE file, the original MDB must be in Access 2002-2003 file format. MDE and ADE files are not backward-compatible. Therefore, you can only open them in the version of Access in which they were created or a later version.
In addition to the Access 2003 Runtime license, the Access 2003 Developer Extensions include two wizards to streamline the process of packing and distributing Access applications. These wizards are the Custom Startup Wizard and the Package Wizard.
Using the Custom Startup Wizard
The Access Startup dialog box allows you to set several useful restrictions on users of the application, such as disallowing standard menus and certain shortcut keys. However, developers do not usually wish to place such restrictions on themselves. Therefore, in the iterative process of application development, you can switch the startup options many times. You work with the application with one set of startup options, add more restrictions while preparing the application for user testing or deployment, and remove the restrictions again when changes to the application are required.
The Custom Startup Wizard, new in the Access 2003 Developer Extensions, simplifies the process of converting the development version of an application to an end-user version. The wizard asks questions about what the final version should look like and uses the answers to create a database with the appropriate settings. In addition, you can save all the choices made to a template. When you make changes to the original application and require a new deployed version, you can create it by using the wizard's command line interface and specifying the template as a command line argument.
Among the changes the wizard can make are these:
- Make the final version an MDE.
- Prevent the Properties dialog box from appearing in Form view.
- Copy a selected macro to the Autokeys macro, providing shortcut keys for the end user that the developer does not want in his own version.
- Set startup options that differ from the original database.
- Disallow the Bypass (SHIFT) key so that users cannot avoid the startup options by holding down the SHIFT key while opening the database file.
- Digitally sign the database.
- Check the database for problems such as code that is not compatible with Access 2002, expressions that are blocked in Sandbox mode and missing references.
Using the Package Wizard
In some situations, distributing an Access application is as simple as copying the MDB or MDE file to the user's drive and fixing any data links to point to the appropriate source. However, for applications that include other non-Access files such as Microsoft ActiveX® controls and icons, or applications that you distribute with Access 2003 Runtime, you want a more robust and professional setup routine. The Package Wizard provides an easy way to create a Microsoft Windows® Installer (.msi) setup file that guides the user step-by-step through installation.
Because the Package Wizard's output is based on Windows Installer technology, the solutions that the Package wizard creates are importable by a wide variety of third-party tools, such as Installshield Developer and Wise for Windows Installer. You can use the Package Wizard to prepare the Access portion of a larger application and integrate the result into a package created with another third-party tool.
The Package Wizard provides options that allow you to:
- Include Access 2003 Runtime as part of the installation.
- Supply a title for the installation.
- Provide a name for the destination folder.
- Choose a language for the installation (you need a CD or folder that contains Access in the language you want to use for this feature).
- Specify support files such as icons, a Help file, a Workgroup file, an End User License Agreement (EULA) in Rich Text Format, or any other files necessary for your application to run properly.
- Add information for the msi file's Properties.
- Specify parameters for a shortcut file created for your solution.
Similar to the Custom Startup Wizard, the Package Wizard allows you to save all choices as a template. The Package Wizard also provides a command line interface that supports a template name as an argument.
If you are creating an application that runs in an Access Runtime environment, you must carefully consider how to provide an interface for the user. You must also consider the fact that some users may own the correct version of Access and run the application in a full Access environment. Take care to test your application under both environments to make sure it properly balances usability in the Access Runtime environment with code security in a full Access environment.
Once you created and tested your application, you can use the wizards in the Access 2003 Developer Extensions to create a final version that you can deploy to end users. It is a good idea to save the settings of both wizards as templates. You can then create a batch file that calls the command line interface of each wizard, sending the names of the respective template files. The next time you change the application and must create a deployment package, the process is as simple as running a single batch file.
About the Author
Jan Fransen, based in Minneapolis, MN, spends her days helping people find ways to use Microsoft Office to do their jobs better. As a consultant, she creates applications in Microsoft Visual Basic for Applications (VBA), Visual Basic 6.0, and Visual Basic .NET. As a trainer and writer, she teaches people both interactive Office tricks and VBA programming.