Preparing Your Access 2003 Database for Deployment, Part 1

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 one of a two-part series designed to help you prepare your database application for release. (20 printed pages)

Contents
Considerations When Deploying Your Application
A Check List for Deploying Your Application
Steps When Developing Your Application
Adding Help to Your Application
Adding Command Bars to Your Application
Conclusion
Additional Resources

So you completed development and are ready to deploy your Microsoft Office Access 2003 application. You made it "bullet-resistant" by adding error-handling code to all of your procedures and you are anxious to get it into the hands of your eager customer. But now what? You have invested a lot of time and effort in the project and you want to make sure that it is, first and foremost, easy to install and use, while also making sure that it permits you to protect the intellectual investment you made in its creation. Following are some things to consider.

Note   The majority of the information in this article applies to Access databases (.mdb files) and not Access projects (.adp files). Similar Help topics for each of the areas discussed in this article regarding .adp files can be found on the Microsoft Developer Network (MSDN).

As part of the interview and planning process, you must determine whether all of your users already have the retail version of Access installed on their computers. If the appropriate version of Access is installed, all you need to do to deploy your application is copy the database file (.mdb file or .mde file) onto the user's computer, along with any supporting files, and you are ready to go. You can do this by simply copying the files directly to the user's computer or by sending a zipped archive file to the distribution site. You can create setup disks by using any one of a number of setup creation tools, such as the Packaging Wizard that is included with the Access Developer Extensions (ADE).

Note   The ADE is a set of tools that, in addition to other features, gives you the rights to use and deploy the run-time version of Access to users who have not purchased the retail version of Access. The ADE is included with the retail version of Visual Studio Tools for the Microsoft Office System.

For users who do not have a version of Access capable of running your application installed, you must install the Access run-time components onto their computer. More information about the Access 2003 run-time components is discussed later in this article.

As the application developer, you are likely intimately familiar with all of the features and options in your application. This may lead you to assume that your users also become familiar with your application very quickly, just by using it. This can be a dangerous assumption. Instead, depending on the complexity of your application and the technical savvy of your users, you should provide some explicit help. More information about adding Help is discussed later in this article.

In most instances, it is important to ensure that a user's only means of interacting with the application is explicitly through the user interface you provide. This serves two purposes: it shields users from the details of how the application works, and helps prevent malicious persons from prying into and modifying the project. To accomplish this, you have options. For example, you can configure startup settings to hide the Database window and display a list of user-selectable options. You can also save the MDB as a MDE file or implement user-level security. Information about setting startup options is discussed later in this article, as are steps to save the application as a MDE.

Of course, the application needs to be easy to install and installation details need to be hidden from the customer. This can be accomplished with a comprehensive installation package, which is also discussed in this article.

First impressions are very important. It is therefore imperative that you test your application on as many of the different computer configurations that it will be installed on as possible. Nothing leaves a user with a negative impression more than having an application crash during installation. Likewise, it is equally as important to test the usability of your application with as many impartial testers as possible in order to uncover shortcomings in your user interface and catch problems with data entry or reporting.

This article helps you understand the technologies and choices you have when deciding to package and deploy your Access 2003 application. This article compiles existing information from Microsoft topics from Access Help and MSDN content for the purpose of bringing together several resources into one useful article.

Before exploring deployment issues and options, it might be helpful to review some of the steps and considerations to remember when planning, creating, and deploying your Access run-time application:

1. Expect to spend a considerable amount of time and effort planning and gathering information from the customer about the application. Doing a thorough job here reduces the number of revisions and headaches resulting from misunderstandings and poor design later.
2. Using the full version of Microsoft Access, create and debug your application's tables, queries, forms, reports, macros, and modules. Remember that forms are usually an important part of your run-time application. You should give them special consideration during the planning and design phases.
3. Build error handling into your application. If error handling is not integrated into your application and an error occurs while a user is running the run-time version, the user is abruptly exited out of the program. A generic, default error message is displayed and the user is left wondering what happened. It is essential that you add error handling into your application. For more information about adding error handling, see Additional Resources at the end of this article.
4. Create Help files and other documentation to accompany your application. As a minimum, you want to provide information to your users on how to use your application and provide information on areas that may not be intuitive. With a little effort, you can include custom Help that increases your customers' productivity, and at the same time, adds a professional touch to your application. More information on building a Help system is included in this article.
5. Build custom command bars for your forms and reports. Built-in toolbars are not available in the run-time version of Access and many of the features on the standard built-in menus are disabled. Therefore, you should strongly consider building your own command bars to Help users work with the objects in your application. Creating custom command bars adds both polish and functionality to your application. More details are presented in this article.
6. Add startup options to your database. Startup options affect the behavior and appearance of an application when it is first started and as the user works with the object it contains. In addition, running an application using the Access runtime does not provide access to the Database window. Therefore it is essential that, as a minimum, you include a startup form as the primary interface for your application. As you see in the next paragraph, you can also use startup options to protect the objects in the database.
7. Secure the objects in your application. This can range from setting simple startup options to implementing workgroup (user-level) security. For example:
   • One of the simpler ways of limiting access to the objects in your database application from the average user is to set startup options. You can do this either programmatically or manually from a dialog box. By setting these options you can hide the Database window, specify a startup display form such as a list of options, limit the use of special keys, or prevent users from modifying menus and toolbars. An adjunct to setting startup options from a dialog box is to use an AutoExec macro, which is generally less accessible to the casual user. An AutoExec macro lets you specify the things available from the startup dialog box as well as things like shutting down the application if the user attempts to run it as something other than a run-time version. More information about these and other startup options is presented in Preparing Your Access 2003 Database for Deployment, Part 2.
   • A slightly more involved method of adding protection is by splitting the application into a "back end" with tables and relationships and a "front end" with objects like forms and reports. This option gives you more protection than startup options because you can apply different levels of protection to each part of the application. Instead of being an all-or-nothing proposition where all objects have to be secured to the same degree, you can store your critical data in an enterprise-level database application such as Microsoft SQL Server with very robust security, and store your less critical forms and reports in an Access .mdb file with startup options or other more flexible protection models. Separating the data from the user interface also makes it easier for you to update your application by enabling you to develop and test the front end without impacting the data in the back end. To help you with this process, Access has a Database Splitting Wizard.
   • And in those situations where you need more than just protection for the objects in your database, you can implement user-level security. While not as secure as the enterprise-level security available with SQL Server, workgroup security can make your database more secure such that only a very competent computer user, with lots of time, can break into the database objects or change object settings. One thing to remember is that a database is not secure just because you are running it from a run-time version of Access. Without security, anyone with a full copy of Access can modify your application. Setting permissions on the objects in your application can be an important step in deployment. Implementing user-level security on an Access database is the subject of several articles and is not covered in any detail here. For more information about Access user-level security, see Additional Resources at the end of this article.
8. Run your application using the /run-time command-line option, and debug any run-time environment errors. This switch simulates the run-time environment, allowing you to simulate user actions under run-time conditions. Taking this step can save you time and energy by allowing you to find most, if not all, of the problems associated with running under the runtime. More details are presented in this article.
9. Create setup disks, or the files to perform a network install, by using the Packaging Wizard. You should also include the Access runtime as a part of the setup files.
10. Test your application by installing and running it on a computer that has never run a copy of either the retail or run-time version of Access. This helps ensure that the application correctly runs with the runtime.
11. Gather together your setup package and any printed documentation, and bring them to your distribution sites.

Note   Access run-time applications have the same system requirements as Microsoft Access or Microsoft Office.

The following are some general guidelines to help when planning and developing your application.

The first step, and one of the most important steps, in creating a commercial application is interviewing the customer. This helps you assess the type of application needed, any constraints in its use, and the method of deployment. For example, some questions to consider:

• What problem are they trying to solve?
• Is there pre-existing software that they could use instead? This might not make you any money but it shows your customer that you place their interests first, which is good for repeat business.
• What versions of Microsoft Office are they using?
• Where will the data reside? On a server? Locally?
• How many concurrent users will there be?
• How mobile does the data need to be? Is there a need to implement replication?
• What is the technical expertise of the users? Intermediate? Novices? Mixed?
• What are the security requirements? Password protection? User-level security?
• Should the data be stored in a Microsoft Jet database (.mdb file) or does it need to be stored in a more secure location, such as a SQL Server database?
• Can the application benefit (either in terms of performance, for security reasons, or for easier upgrade and maintenance considerations) from being split into a configuration with "front end" and "back end" elements?
• How many tables/records are involved?
• How will data entry be accomplished?
• What reports must be generated? How frequently? Which reports are consumed in-house? Which reports are for public use?
• How much growth is anticipated in the number of users? In the amount of data? In the number of reports?
• How will the application be maintained? By you? In-house?

After completing the initial interview, you can use the information to create a plan, including diagrams, to show the layout (front end/back end), number and layout of the tables, relationships, form layout, types of reports, and so forth. After the plan is completed, it is extremely important to conduct a follow-up interview to demonstrate the proposed design and get buy-off from the customer, as well as to clear up any questions or concerns.

If, after gathering and summarizing the data, you decide that an Access database is a good fit, the next step is to create a comprehensive design. Remember that it is much easier to modify the database schema during the planning phase or at the beginning of development than it is after development is in progress (or worse, after deployment). Therefore, it is important to put as much effort into the design of the database as into creating the application.

One design detail to consider at the beginning of the planning phase is the type of application to create. Applications you create with the Microsoft Office System are likely to fall into one of the following broad categories: data-management applications, document templates, add-ins, and Web applications. Some Office products are obvious choices for certain types of application; for other types of applications, choosing the right product is a little harder.

It is obvious that all database applications involve varying degrees of data storage and data management. For example, if you are creating an application to store large amounts of data or if the nature of the data being stored requires tight security, you would probably want to choose a product that provides robust data management and security, such as Microsoft SQL Server.

For smaller applications or applications where restricting access to the data is not as critical, using an enterprise-level application, such as SQL Server, for data storage and management may be unnecessary. Note that small is a relative term; for some, a small application may be anything fewer than 1,000 records, for others, that number may be fewer than 100,000 records. For these situations, your better choice is probably an application that you can create and maintain with a moderate amount of effort, and that restricts access to the data from the average user of the application. Here, Access is a good choice.

Another limiting factor to consider is the number of simultaneous users who will be accessing the data at any one time. Databases that are used by a dozen users at the same time have far different requirements than databases that have hundreds of concurrent users. If you are building either a single-user application or a multi-user application for a small workgroup, a good choice would be an Access database. For larger groups of users, SQL Server is designed to support hundreds to thousands of simultaneous users. In these situations, Access can be a great tool for creating a front end for queries, forms, and reports that link to the data in a SQL Server back end. Access MDB back-end databases can support up to 255 simultaneous users, although better performance is typically achieved with 25 to 50 users.

If your application requires users to enter data, a key part of your design process is determining how your application should get data from users and validate it. The extent to which your application must control user input is another factor in choosing which application to use as the basis for your application.

As stated previously, if you decide to store the data in a relational database, it is usually a good idea to separate the application into two parts: a back-end database and a front-end data-entry component. The back-end database contains the data tables and stored procedures to access that data, whereas the front end contains queries that either call stored procedures or access the data directly, and the forms and reports used to display the data and manage data entry. By designing the application in this way, you can store the data in a central location, for example, on a network server, and distribute a copy of the front-end file to each user. You can build the front-end data-entry component in many of the Office products or in Microsoft Visual Basic.

While developing plans for the data-storage and data-entry components of your application, you should plan how to present and summarize the data in a format that makes sense to users. Although generally not as difficult as database design, determining which data users want to see and building reports to display that data in a usable format can be a more challenging task than it initially seems.

Reports can be classified into two basic categories: detailed reports and summary reports. Detailed reports display the same information as a table or query but in a format that is easier to view and understand, usually with some summary information such as totals and percentages. Detailed reports also include report headers, page headers, and page footers not found in the data views of tables and queries.

Summary reports are similar to detailed reports except they do not present details; data is presented without the records. Access provides wizards that can help you create "instant" basic reports that you can customize to suit your needs. For example, you may want to add graphics such as charts or company logos or use different formatting such as alternate-line shading or elegant fonts for reports that you present publicly, rather than those that are used in-house.

After planning is completed, work on the application begins. It is highly recommended that you schedule regular demonstrations for the customer during development. This gives you a chance to update your customer on your progress and to solve any conflicts between the customer's expectations and the application design before they become large conflicts.

Built-in Help can mean the difference between a good application and a great one. Frequently when developing an application, it is easy to assume that just because a feature has become familiar to you, it is also well understood by the customer. This is where a well-defined Help system can act as a bridge between you and your customer.

Help can be as simple as creating tooltips and labels on a form, or you can create more formal Help, complete with a user interface.

1. Open a form, report, or data access page in Design view.

2. Select the control that you want to create a tip for, and then click Properties on the Form Design, Report Design, or Page Design toolbar to open the control's property sheet.

3. In the ControlTipText (forms and reports) or the Title (data access pages) property box in the property sheet, type the message you want to display for the control. You can type up to 255 characters.

   

   Figure 1. A tool tip in Access

More formal Help requires that you first create Help files, and then provide function calls to these files for each form that requires Help.

You can use Microsoft HTML Help Workshop to display custom Help when the user presses F1. For more information or to download the HTML Help Workshop, see Microsoft HTML Help 1.4 SDK.

1. Create a Help file.

   • Create the source file by using the text editor or word-processing program of your choice.
   • Save the source file in Rich Text Format (RTF).
   • Compile the Help file by using Microsoft Help Workshop.

2. Open the form or report in Design view.

3. Double-click the form selector or report selector to display the property sheet.

4. In the HelpFile property box, type the name of the compiled Help file.

   Note   For better results, put the Help file in the same folder as your application. That way, you can keep the setting for the HelpFile property the same for each site that uses your application, but allow users to install the application in the folder of their choice. If you omit a path setting in this property, Access looks for the Help file in the folder where your application is installed.

5. In the HelpContextID property box for the form or report, type the number of the topic (a number other than 0) that are displayed when a user presses F1. You assign numbers to Help topics as part of using the Microsoft Help Workshop. You must map the values set for the HelpContextID property to the context strings in your Help system. For more information, see the Help file that comes with the Microsoft HTML Help Workshop.

6. Next, you can call Help by making a call to the HtmlHelp API. To use the HtmlHelp API to display a Help topic, you must include a function declaration in the Declarations section of a form, class, or standard module, and then add a line of code such as the following to the Help button's Click event procedure:

   Call HtmlHelp(0, ActiveDocument.Path &"\sample.chm", HH_HELP_CONTEXT, ByVal 2001&)
   

In addition to the previous procedure, you can also provide Help by using the MsgBox or InputBox functions.

Both the InputBox and MsgBox functions provide optional helpfile and context arguments that you can use to display a Help topic when a user clicks the Help button or presses F1. To display a custom Help topic, you must specify both optional arguments. The helpfile argument is a string value that specifies the Help file that contains the topic you want to display. This argument can accept either a .chm file name or an .hlp file name. The context argument specifies the mapped context ID of the topic to display.

Figure 2. A message box with Help

If you specify the helpfile and context arguments when you are using the InputBox function, a Help button is automatically added to the dialog box that is created by the InputBox function. If you specify the helpfile and context arguments when you are using the MsgBox function, you must also specify the vbMsgBoxHelpButton built-in constant in the buttons argument, to add a Help button to the dialog box created by the MsgBox function.

The following code fragment shows how to display a Help topic when you are using the InputBox function.

InputBox Prompt:="Enter data", _
         HelpFile:=strAppPath & "\sample.chm", _
         Context:="2001"

The following code shows how to display a Help topic when you are using the MsgBox function.

MsgBox Prompt:="You must enter a valid date.", _
       Buttons:=vbMsgBoxHelpButton, _
       HelpFile:=strAppPath & "\sample.chm", _
       Context:="2002"

The InputBox and MsgBox functions allow you to display a Help topic, contained in a compiled HTML Help file, in all Office applications. There is no need to use the HtmlHelp API to display a Help topic in a .chm file when you are using these functions.

You can deploy the files that make up your HTML Help online Help system in any of the following ways:

• As a set of HTML files compiled into one or more locally-installed compiled HTML Help files (.chm files)
• As a set of locally-installed HTML files to be accessed through the Microsoft Windows file system
• As a set of HTML files to be accessed through an intranet, an extranet, or the Internet
• A combination of these formats

The advantage of using compiled HTML Help files is that you can install a single file or small set of files on each user's local drive that can then be accessed without a network connection. Additionally, compiled HTML Help files use disk space much more efficiently than un-compiled HTML files, particularly on hard disks that are formatted with the FAT file system. For more information about compiled Help files, see Microsoft Help Workshop Help.

The advantage of supplying the Help topics on a Web site is that you can update and add new Help topics from a single central location; however, providing Help this way requires that users have network access to your Web site. Also, certain features of HTML Help, such as full-text search, are available only when you are using compiled HTML Help files.

You can also provide Help topics through a combination of both standard and compiled HTML formats, most typically in the form of a locally installed compiled HTML Help file with links from individual topics or the table of contents to Web pages on an intranet, an extranet, or the Internet. In addition, you can display an HTML page contained in a compiled HTML Help file from Microsoft Internet Explorer by using an appropriately formatted URL.

You can also display context-sensitive Help for custom command bar controls and call a standard Help topic when a user clicks a toolbar button or menu item. In Microsoft Office Excel 2003 and Microsoft Office PowerPoint 2003, you can call the Help method of the Application object by calling a procedure from a command bar; in Microsoft Office Word 2003 and Access, you must call the Help engine directly by using an API call.

To implement context-sensitive Help for a command bar control, you set the control's HelpFile property to the name of the Help file that contains the context-sensitive Help topic, and set the HelpContextID property to the context ID of the topic you want to display. The Access runtime does not include Help topics, so you need to create your own Help references to provide this feature to your customers.

Displaying a Help topic when a user clicks a toolbar button or menu item is similar to creating a Help button for a form. However, command bar controls do not provide a Click event, so you must first create a Sub or Function procedure that displays the Help topic, and then set the control's OnAction property to the name of that procedure. The same restrictions apply when you are creating a procedure to display a Help topic as when you are creating a Help button: You can use the Help method of the Application object to display a custom Help topic in Excel and PowerPoint, but you must use an API call to HTML Help to display a custom Help topic in Word and Access.

Command bars are used throughout Microsoft Office applications to allow users to perform actions. Building custom command bars associated with your application's forms and reports is another valuable addition to your application. To prevent users from making changes to your Access run-time application, the Access run-time environment removes several menus from the menu bar. For example, all the following menus are removed from all the windows of your Access run-time application:

• The View menu
• The Tools menu
• The Format menu

These menus are also removed from the menu bars in Datasheet view for tables and for queries.

To prevent users from making changes to the Access run-time application, the Access run-time environment also removes the commands from the following drop-down menus:

• The commands on the Edit menu
• The commands on the Insert menu
• The commands on the Records menu

These commands are also removed in Form view for forms and in Print Preview for reports.

You can control the menus and the commands that are available to users of your Access run-time application. To do this, build the application by using forms that have custom menus. The Access run-time environment does not provide all built-in Access toolbars and does not support all built-in Access toolbars. However, you can add your own custom toolbars to an Access run-time application. When you create a custom toolbar, the custom toolbar is stored in the current database of the Access run-time application. Therefore, the custom toolbar is automatically available to the application.

There are two forms of command barstoolbars and menus. If you have performed actions such as creating an Office document by clicking New on the File menu or clicking the Save button on the Standard toolbar, you are already familiar with command bars.

In addition to toolbars, there are two types of menusmenu bars and pop-up menus.

• Toolbars contain menus, buttons, and other types of controls that can be used to perform commands. Common toolbars include the Standard toolbar, the Formatting toolbar, and the Web toolbar.
• Menu bars contain pop-up menus. The main Menu Bar includes pop-up menus such as File, Edit, and View.
• Pop-up menus include menus that drop down from menu bars, submenus that cascade off of menu commands, and shortcut menus (also known as right-click menus).

Although there are many types of command bar controls, only the following command bar controls can be created or modified through the Microsoft Office Object Library (MSO.dll):

• Command button. This control enables users to click a button to perform an action. Examples of command buttons are the Open, Save, and File Search command buttons on the Database toolbar in Access.
• Combo box. This control enables users to select a choice from a list or enter one of their own choices. An example of a combo box is the Font Size combo box in Query Design view in Access.
• Drop-down list box. This control enables users to select a choice from a list, but does not allow users to enter one of their own choices.
• Text box. This control enables users to type information in the control.
• Pop-up menu. This control (also called context menu or right-click menu) displays a menu containing child command buttons, combo boxes, drop-down list boxes, text boxes, or other pop-up menus. Examples of pop-up menus include any of the pop-up menus that are displayed when you right-click a control in the Design view of a form or report in Access.

Command bars can either be created from the user interface or by using Microsoft Visual Basic for Applications (VBA). In Access, you can create custom command bars from the Customize dialog box. In all other Office applications, you create custom command bars by using VBA code. To create a custom toolbar from the Customize dialog box, follow these steps:

1. Open the Customize dialog box by right-clicking the grey background of a toolbar and selecting Customize from the shortcut menu.

2. Click the Toolbars tab, and then click New.

3. Give the new toolbar a name and click OK to close the New Toolbar dialog box. The new toolbar appears. Note that the toolbar is small and is easy to miss.

   

   Figure 3. The Customize dialog box

To create a command bar in VBA code, use the Add method of the CommandBars collection. The Add method takes the following arguments:

• The optional Name argument is a String value that specifies the name of the new command bar. If the Name argument is omitted, a default name is assigned to the command bar.
• The optional Position argument is an MsoBarPosition enumerated constant representing the position or type of the new command bar. The msoBarLeft, msoBarTop, msoBarRight, and msoBarBottom constants specify the left, top, right, and bottom coordinates of the new command bar, respectively. The msoBarFloating constant specifies that the new command bar is not be docked. The msoBarPopup constant specifies that the new command bar is a pop-up menu.
• The MenuBar argument is a Boolean value; True replaces the active menu bar with the new command bar. The default value is False.
• The Temporary argument is also a Boolean value; True specifies that the new command bar is temporary, which means the command bar is deleted when the container application is closed. The default value is False.

To demonstrate how to create different types of command bars, the following code creates a toolbar.

Public Sub CreateToolbar()
    ' Creates a sample toolbar.
    Dim objCommandBar As Office.CommandBar
    
    For Each objCommandBar In Application.CommandBars
        If objCommandBar.Name = "My Toolbar" Then
            objCommandBar.Delete
        End If
    Next objCommandBar
            
    Set objCommandBar = Application.CommandBars.Add("My Toolbar")
    
    ' To delete this toolbar, call the following:
    ' Application.CommandBars("My Toolbar").Delete
    
End Sub

The following code creates a menu bar.

Public Sub CreateMenuBar()
    ' Creates a sample menu bar.
    Dim objCommandBar As Office.CommandBar
    
    For Each objCommandBar In Application.CommandBars
        If objCommandBar.Name = "My Menu Bar" Then
            objCommandBar.Delete
        End If
    Next objCommandBar
    
    Set objCommandBar = Application.CommandBars.Add _
        ("My Menu Bar", , True)
    objCommandBar.Visible = True
    
    ' To delete this menu bar, call the following:
    ' Application.CommandBars("My Menu Bar").Delete
    
End Sub

The following code creates a pop-up menu.

Public Sub CreatePopUpMenu()
    ' Creates and displays a sample pop-up menu.
    Dim objCommandBar As Office.CommandBar
    
    For Each objCommandBar In Application.CommandBars
        If objCommandBar.Name = "My Popup Menu" Then
            objCommandBar.Delete
        End If
    Next objCommandBar
    
    Set objCommandBar = Application.CommandBars.Add _
        ("My Popup Menu", msoBarPopup)
    
    objCommandBar.ShowPopup _
        Application.Width / 2, Application.Height / 2
        
    ' To delete this pop-up menu, call the following:
    ' Application.CommandBars("My Popup Menu").Delete
    
End Sub

Similarly, to add a command bar control to a command bar, use the Add method of the CommandBarControls collection. Use the following MsoControlType enumerated constants when calling the CommandBarControls collection's Add method to add a command bar control to a command bar:

• Use the msoControlButton constant for a command button.
• Use the msoControlComboBox constant for a combo box.
• Use the msoControlDropdown constant for a drop-down list box.
• Use the msoControlEdit constant for a text box.
• Use the msoControlPopup constant for a pop-up menu.

The following code creates a toolbar with various controls.

Public Sub CreateCommandBarWithControls()
    ' Creates a sample command bar with a number of controls. 
    Dim objCommandBar As Office.CommandBar
    Dim objCommandBarControl As Office.CommandBarControl
    Dim objCommandBarButton As Office.CommandBarButton
    Dim objCommandBarComboBox As Office.CommandBarComboBox
    Dim objCommandBarPopup As Office.CommandBarPopup
    
    For Each objCommandBar In Application.CommandBars
        If objCommandBar.Name = "Controls Demo" Then
            objCommandBar.Delete
        End If
    Next objCommandBar

    Set objCommandBar = Application.CommandBars.Add _
        ("Controls Demo")
        
    With objCommandBar.Controls
        Set objCommandBarButton = .Add(msoControlButton)
        
        With objCommandBarButton
            .Caption = "&An Access Application"
            .FaceId = 59
            .Style = msoButtonIconAndCaption
            .TooltipText = _
                "This is a good place for a Help message."
        End With
        
        Set objCommandBarComboBox = .Add(msoControlComboBox)
        
        With objCommandBarComboBox
            .AddItem "Red"
            .AddItem "Green"
            .AddItem "Blue"
            .AddItem "Yellow"
            .AddItem "Other"
            .Text = "Color"
            .Caption = "Colors"
            .Style = msoComboNormal
            .TooltipText = "Select or type your favorite color."
        End With
        
        Set objCommandBarComboBox = .Add(msoControlDropdown)
        
        With objCommandBarComboBox
            .AddItem "Corn"
            .AddItem "Brussel Sprouts"
            .AddItem "Peas"
            .AddItem "Other"
            .AddItem "None"
            .Style = msoComboLabel
            .Caption = "&Vegatbles"
            .TooltipText = "Select your favorite vegetable."
        End With
        
        Set objCommandBarComboBox = .Add(msoControlEdit)
        
        With objCommandBarComboBox
            .Caption = "Search"
            .Text = "Type search term"
            .TooltipText = "Type the term you want to find."
        End With
        
        Set objCommandBarPopup = .Add(msoControlPopup)
        
        With objCommandBarPopup
            .Caption = "&More"
            
            Set objCommandBarComboBox = .Controls.Add(msoControlDropdown)
        
            With objCommandBarComboBox
                .AddItem "Arizona"
                .AddItem "California"
                .AddItem "Delaware"
                .AddItem "Georgia"
                .AddItem "Washington"
                .Caption = "Sales Areas"
                .Style = msoComboNormal
            End With
            
            Set objCommandBarButton = .Controls.Add(msoControlButton)
            
            With objCommandBarButton
                .Caption = "&Checked"
                .Style = msoButtonCaption
            End With
            
            Set objCommandBarButton = .Controls.Add(msoControlButton)
        
            With objCommandBarButton
                .Caption = "Cool &Shape"
                .FaceId = 43
                .Style = msoButtonIconAndCaption
            End With            
        End With        
    End With
    
    objCommandBar.Visible = True
    
End Sub

To delete a command bar, use the CommandBar object's Delete method. To delete a command bar control, use the Delete method associated with the CommandBarButton object, CommandBarComboBox object, CommandBarControl object, or CommandBarPopup object.

Use the Visible property to show or hide a command bar or command bar control (use True to display and use False to hide).

You can add built-in Office application images to command bar buttons. Each built-in image has an associated face ID that can be referenced by using the CommandBarButton object's FaceID property. Note that these images are referenced by number only and do not have readable names.

The following code creates a toolbar and fills it with command buttons containing built-in images corresponding to a range of face IDs that you provide. Each image has a tooltip listing the image's face ID.

Figure 4. Custom toolbar with images

Public Sub ListButtonPictureswithIDs(ByVal intStart As Integer, _
        ByVal intEnd As Integer)
    ' Given a starting and ending number, creates a
    ' command bar with pictures corresponding to the face IDs.
    Dim objCommandBar As Office.CommandBar
    Dim objCommandBarButton As Office.CommandBarButton
    Dim intButton As Integer
    
    On Error GoTo ListButtonPictureswithIDs_Err
    
    If intStart > intEnd Then        
        MsgBox "Ending number must be smaller than starting number. " & _
            "Please try again."        
        Exit Sub    
    End If
    
    For Each objCommandBar In Application.CommandBars
        If objCommandBar.Name = "Button Pictures and IDs" Then
            objCommandBar.Delete
        End If
    Next objCommandBar
    
    Set objCommandBar = _
        Application.CommandBars.Add("Button Pictures and IDs", , , True)
    
    For intButton = intStart To intEnd    
        Set objCommandBarButton = _
            objCommandBar.Controls.Add(msoControlButton, , , , True)
    
        With objCommandBarButton
            .FaceId = intButton
            .TooltipText = "FaceID = " & intButton
        End With    
    Next intButton
    
    objCommandBar.Visible = True
    
ListButtonPictureswithIDs_End:
    Exit Sub
    
ListButtonPictureswithIDs_Err:
    Select Case Err.Number
        Case -2147467259    ' Invalid FaceIDs.
            MsgBox "Invalid range of numbers for face IDs. " & _
                "Please try again."
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description    
    End Select
    
    Resume ListButtonPictureswithIDs_End
                
End Sub

You can call the ListButtonPictureswithIDs subroutine with code similar to the following.

Public Sub TestListButtonPictureswithIDs()
    Call ListButtonPictureswithIDs(100, 200)    
End Sub

Assuming that you want to market your Access application, there are a number of options you should consider. For example, will your customers have the retail version of Access installed? Is your application as easy to use and understand as you intend? Do you need to protect the code and other objects in the application? Planning and developing your application while remembering these considerations, 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. For more information about preparing for deployment, see Preparing Your Access 2003 Database for Deployment, Part 2, the second article of this series.

You can find additional information in the following resources:

• Access User-Level Security
• About Protecting a Microsoft Access Database
• User-Level and Share-Level Security
• Users and Groups
• Creating Secure Data Access Pages
• Elements of Run-Time Error Handling
• Basic Error Handling
• Microsoft Office Developer Center: Access