Creating Managed Add-ins for Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how you can extend Access 2007 by creating and incorporating managed add-ins into your applications. This article describes how to create a simple add-in that consumes data returned from a Web service, by using the Visual Studio Shared Add-in template. (25 printed pages)

Ken Getz, MCW Technologies, LLC

Jan Fransen, A23 Consulting

October 2006

Applies to: 2007 Microsoft Office system, Microsoft Office Access 2007, Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System, Microsoft Visual Studio 2005

Contents

  • Introduction to Managed Add-ins for Access

  • Access and COM Add-ins

  • Investigating the Behavior of the Finished Add-in

  • Creating the Sample Form

  • Deploying Your Add-in

  • Conclusion

  • Additional Resources

Introduction to Managed Add-ins for Access

Microsoft Office Access has supported add-ins, of one kind or another, for several years. In that time, developers created external code libraries written in Microsoft Visual Basic for Applications (VBA) that are stored in database files that use an .mda extension. They created complete sets of functionality that included forms, reports, and code, and made the functionality available to other databases by setting references to the libraries. Developers created tools that end-users can load and use, all written in VBA.

Microsoft Office Access 2007 is designed to help you quickly and easily create managed add-ins (that is, add-ins that run code created by using Microsoft Visual Studio 2005, written in either Microsoft Visual Basic or Microsoft Visual C#). Now, you can use professional tools for creating managed applications, and incorporate the rich and powerful functionality of the Microsoft .NET Framework. You can use code-access security in the.NET Framework to protect your add-ins, by using Visual Studio 2005 or later versions with Access 2007.

NoteNote

When this article refers to managed Visual Basic, it refers to Visual Basic .NET (specifically, Visual Basic 2005). When this article refers to the unmanaged version, it uses the specific term VBA or Microsoft Visual Basic 6.0.

If you use Access add-ins, you know that they generally work on their own, without interacting at run time with your Access forms and reports. These add-ins might document the contents of a database, or provide statistics support, or enable an XML Web services call. Add-in vendors create their add-ins to work across multiple database applications. They are not intended for use with a specific application.

The add-ins you create will probably be different. You can create managed add-ins that target multiple applications. It is likely that you will create a managed add-in associated with a single database application. You might call managed code to accomplish a particular task for the application. Managed code makes certain tasks easier, especially consuming XML Web services (and incorporating Microsoft Windows Communication Foundation) as part of an Access application.

The assumption in this article is that you are not a tools vendor, creating add-ins for the world to consume. Instead, the assumption is that you are a developer who has to make an application work and managed code might help you solve a particular problem. By creating an add-in that targets a particular application, you can call managed code in reaction to Access object events; this includes handling button clicks and mouse moves.

NoteNote

All the issues shown here apply similarly to developers working in other applications in the 2007 Microsoft Office release, including Microsoft Office Word 2007, Microsoft Office Excel 2007, and Microsoft Office PowerPoint 2007. However, the focus of this article is how to create add-ins for Access 2007.

This article, intended for professional Access developers, discusses how to create a simple add-in that consumes data returned from a Web service. You might encounter a situation in which you have to retrieve data returned by a Web service and, without managed code, the task is somewhat difficult. If you use Visual Basic .NET or C#, the work of interacting with the Web service is taken care of for you. This scenario is one of many in which managed code can help make your life easier. It takes additional effort to hook Access control events from managed code. This article describes that task.

Although you can create managed add-ins for Access 2007 without any help from Visual Studio 2005 and its templates, the simplest technique is to use the Shared Add-in template in Visual Studio 2005.The Shared Add-in template is available in all editions of Visual Studio 2005. This template includes classes and enough code to help you get started creating your add-ins.

This article shows how to create a simple add-in by using the Visual Studio Shared Add-in template. The code and instructions are provided (in Visual Basic and in C#) so that you can try this in the language that you prefer. Likely, you will not use the add-in in another application which is a point of this exercise. The intent is to demonstrate how to create application-specific, rather than general-purpose, add-ins.

Notice that Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (otherwise known as Visual Studio 2005 Tools for Office, Second Edition) provides templates for creating managed add-ins for several products in the 2007 release. This tool does not provide support for creating Access 2007 add-ins. Therefore, it is not covered here.

Access and COM Add-ins

Access has supported loading and running add-ins created by using the COM for several versions. The steps to create a standard COM add-in (by using a tool such as Visual Basic 6.0) are documented and you can find this information in the MSDN library. These instructions were created for Microsoft Office XP and Microsoft Access 2002. However, the same issues apply when you create add-ins by using managed code—you are still creating a COM add-in, but you expect Visual Studio to create a transparent layer between the COM-based code that Access requires and the managed code that you are creating in Visual Studio. To create a COM add-in for Office, you must implement a well-known COM interface, IDTExtensibility2. Implementing this interface requires your code to provide five specific procedures (many of which can be empty, and still provide a working add-in). All COM add-ins, for all Office applications, are similar—they all include an OnConnection, OnDisconnection, OnAddInsUpdate, OnStartupComplete, and OnBeginShutDown procedure. These procedures are required by the IDTExtensibility2 interface. By using some custom configuration to install the add-in locally, Access loads your add-in as it starts (or on-demand) and calls procedures within your add-in to perform the tasks that you require.

NoteNote

If you are unfamiliar with the concept of an interface, or implementing an interface, do not worry. You do not have to fully understand this concept to create a managed add-in for Access. All you have to understand is that every COM add-in must provide the same five procedures to interact with Access. The interface defines these procedures, and your job is to provide the contents of the procedures within the add-in. Usually, you must only supply code for one of the procedures, but they all must exist. The Visual Studio template handles these details for you.

It all comes down to this: After you create and register your add-in, Access expects and is ready to call a COM add-in. You are creating a COM add-in, but use managed code to do this. Visual Studio creates a managed assembly (a compiled executable that runs within the common language runtime) and hooks up the necessary registry information and COM interop code so that Access can handle your managed assembly as a COM add-in. This gives you the best of both worlds: You create an add-in for Access, and you get the best development environment (Visual Studio) to do so.

Investigating the Behavior of the Finished Add-in

The sample that you build in this article is simple: It performs two actions, each calling a single Web service. (Although Access has historically been able to call Web services directly, it was limited in the types of data it could handle on return. If you want to explore working with Web services in Access 2003, you can install the Microsoft Office 2003 Web Services Toolkit. But it is easier to use managed code to call the Web service and to handle its return data.)

Specifically, the add-in calls a Web service that returns information from the Microsoft SQL Server Northwind sample database, at the address http://www.mcwtech.com/northwinddata/products.asmx. When you click a button on a form (shown in Figure 1), the add-in fills a ListBox control with the available product names. If you select a product name from the list box, the add-in pushes the unit price and quantity-on-hand values into read-only TextBox controls on the form. This example shows you how to call a Web service that returns an ADO.NET dataset, copying the data from the dataset into an ADO recordset, and binding that recordset to an Access ListBox, all from the managed add-in.

Figure 1. The managed add-in calls a Web service that returns data from the Microsoft SQL Server Northwind sample database

Add-in calls a Web service that returns data

Creating the Sample Form

To get started, you create the form that you will use for this demonstration. To do that, open Access 2007 and follow these steps:

  1. Click the Microsoft Office Button, and then select New.

  2. Select the option to create a new blank database, and type a name for your new database, such as SampleAddIn.accdb. Store the database in a folder that you can easily find later.

    NoteNote

    Although the example uses the new ACCDB format, add-ins work equally well within databases in the.MDB format.

  3. Select Create, and then select Form Design to create a blank form in Design view.

  4. Add controls to the form so that it resembles Figure 1. (You can add an AutoFormat to the form if you want, as shown in the figure, or you can lay out the controls.) Set the names and other properties of the controls as shown in Table 1. Set other properties as you prefer.

    Table 1. Property values for the form controls

    Control

    Name

    Other Properties

    Button

    fillProductsButton

    ListBox

    productsListBox

    TextBox

    unitPriceTextBox

    Format: Currency

    TextBox

    quantityTextBox

    Format: Fixed; Decimal Places: 0

  5. After setting the properties, save the form. Use the default name, Form1.

  6. Exit Access 2007.

  7. Start Visual Studio 2005. (If you are using a later version, adjust the instructions given here to match your version.)

  8. On the File menu, click New, and then click Project.

    NoteNote

    This article assumes that you installed Visual Studio with the default General Development settings. If you instead selected Visual Basic Developer, for example, the menu items lay out differently. Adjust these instructions for your specific environment.

  9. In the New Project dialog box, in the list of project types, expand the Other Project Types node.

  10. In the list of project types, select Extensibility.

  11. In the Templates pane, select Shared Add-in, as shown in Figure 2. Type a name and a location for your add-in, and then click OK to create the add-in.

    Figure 2. Selecting the Shared Add-in template

    Select the Shared Add-In template

  12. On the Welcome to the Add-in Wizard page, click Next.

  13. On the Select a Programming Language page, select Visual C# or Visual Basic, and then click Next.

  14. On the Select An Application Host page, clear all the check boxes exceptMicrosoft Access, and then click Next.

    NoteNote

    It is possible to create a single add-in that can work with multiple applications (and that is the intent of this page of the dialog box), but it is unlikely. Because this article discusses how to create application-specific Access add-ins, you must clear all other check boxes.

  15. On the Enter a Name and Description page, type values (as shown in Figure 3), and then click Next.

    NoteNote

    These values are optional. However, they make it easier to work with your add-in within Access.

    Figure 3. Typing optional values for your add-in

    Type optional values for your add-in

  16. On the Choose Add-in Options page, ensure that only the second option is selected, and then click Next.

    NoteNote

    The first option causes your add-in to be loaded automatically when Access loads. This enables easier debugging, but does cause your add-in to reside in memory. Most likely, you should rarely select this option. It is easy to load the add-in manually when it is required. The second option determines which users can interact with the add-in; unless you have a reason not to, it is recommended that you do not allow all users to interact with the add-in.

  17. On the Summary page, review your choices, and then click Finish to create the add-in.

    NoteNote

    When you create the add-in project, Visual Studio actually creates two projects in a solution that has the same name as your add-in. The first project is your add-in. The second project is a setup project so that you can easily deploy your add-in to other users. The "Deploying Your Add-In" section describes how to use the setup project.

The Visual Studio Shared Add-in template creates a class that resembles the following code example.

<GuidAttribute("E3532B3F-1D99-4C62-9D04-F58D75FCAC40"), _
 ProgIdAttribute("SharedAddIn.Connect")> _
Public Class Connect

    Implements Extensibility.IDTExtensibility2

    Dim applicationObject As Object
    Dim addInInstance As Object

    Public Sub OnBeginShutdown( _
     ByRef custom As System.Array) _
     Implements Extensibility.IDTExtensibility2.OnBeginShutdown

    End Sub

    Public Sub OnAddInsUpdate( _
     ByRef custom As System.Array) _
     Implements Extensibility.IDTExtensibility2.OnAddInsUpdate

    End Sub

    Public Sub OnStartupComplete( _
     ByRef custom As System.Array) _
     Implements Extensibility.IDTExtensibility2.OnStartupComplete

    End Sub

    Public Sub OnDisconnection( _
     ByVal RemoveMode As Extensibility.ext_DisconnectMode, _
     ByRef custom As System.Array) _
     Implements Extensibility.IDTExtensibility2.OnDisconnection

    End Sub

    Public Sub OnConnection( _
     ByVal application As Object, _
     ByVal connectMode As Extensibility.ext_ConnectMode, _
     ByVal addInInst As Object, ByRef custom As System.Array) _
     Implements Extensibility.IDTExtensibility2.OnConnection

        applicationObject = application
        addInInstance = addInInst
    End Sub

End Class
namespace MyAddinCS
{
    using System;
    using Extensibility;
    using System.Runtime.InteropServices;

    /// <summary>
    ///   The object for implementing an add-in.
    /// </summary>
    /// <seealso class='IDTExtensibility2' />
    [GuidAttribute("799003CA-042E-4EB5-A58C-DF04D2C438FB"),
        ProgId("MyAddinCS.Connect")]
    public class Connect : Object, Extensibility.IDTExtensibility2
    {
        /// <summary>
        ///        Implements the constructor for the add-in object.
        ///        Place your initialization code within this method.
        /// </summary>
        public Connect()
        {
        }

        /// <summary>
        ///      Implements the OnConnection method of the 
        ///      IDTExtensibility2 interface.
        ///      Receives notification that the add-in is being loaded.
        /// </summary>
        /// <param term='application'>
        ///      Root object of the host application.
        /// </param>
        /// <param term='connectMode'>
        ///      Describes how the add-in is being loaded.
        /// </param>
        /// <param term='addInInst'>
        ///      Object representing this add-in.
        /// </param>
        /// <seealso class='IDTExtensibility2' />
        public void OnConnection(object application,
          Extensibility.ext_ConnectMode connectMode,
          object addInInst, ref System.Array custom)
        {
            applicationObject = application;
            addInInstance = addInInst;
        }

        /// <summary>
        ///     Implements the OnDisconnection method of 
        ///     the IDTExtensibility2 interface.
        ///     Receives notification that the add-in is being unloaded.
        /// </summary>
        /// <param term='disconnectMode'>
        ///      Describes how the add-in is being unloaded.
        /// </param>
        /// <param term='custom'>
        ///      Array of parameters that are host application specific.
        /// </param>
        /// <seealso class='IDTExtensibility2' />
        public void OnDisconnection(
            Extensibility.ext_DisconnectMode disconnectMode,
            ref System.Array custom)
        {
        }

        /// <summary>
        ///      Implements the OnAddInsUpdate method of 
        ///      the IDTExtensibility2 interface.
        ///      Receives notification that the collection of 
        ///      Add-ins has changed.
        /// </summary>
        /// <param term='custom'>
        ///      Array of parameters that are host application specific.
        /// </param>
        /// <seealso class='IDTExtensibility2' />
        public void OnAddInsUpdate(ref System.Array custom)
        {
        }

        /// <summary>
        ///      Implements the OnStartupComplete method 
        ///      of the IDTExtensibility2 interface.
        ///      Receives notification that the host application 
        ///      has completed loading.
        /// </summary>
        /// <param term='custom'>
        ///      Array of parameters that are host application specific.
        /// </param>
        /// <seealso class='IDTExtensibility2' />
        public void OnStartupComplete(ref System.Array custom)
        {
        }

        /// <summary>
        ///      Implements the OnBeginShutdown method of the 
        ///      IDTExtensibility2 interface.
        ///      Receives notification that the host application 
        ///      is being unloaded.
        /// </summary>
        /// <param term='custom'>
        ///      Array of parameters that are host application specific.
        /// </param>
        /// <seealso class='IDTExtensibility2' />
        public void OnBeginShutdown(ref System.Array custom)
        {
        }

        private object applicationObject;
        private object addInInstance;
    }
}

The template provides procedure stubs for all five procedures that the Extensibility.IDTExtensibility2 interface requires. The OnConnection procedure is the one that you are likely to add code to, and for this example, it is the only one that is required. Access calls the OnConnection procedure of your add-in when it first connects to the add-in. This gives your add-in an opportunity to perform whatever configuration it needs. For this example, that involves hooking up event handlers for the controls on your form.

You are less likely to need the other four procedures in the context of creating application-specific add-ins for Access. However, you can add code to any of these, as necessary. Access calls the OnDisconnection procedure of your add-in when it unloads your add-in, and it passes a value to the procedure that indicates how the add-in was removed (for example, because Access was shutting down or because the user manually removed the add-in).

Access calls your add-in's OnStartupComplete procedure when Access is finished loading so that your add-in can take any needed action that could not be taken until after the application completed loading. Access calls your add-in's OnBeginShutdown procedure when it starts to shut down. This gives the add-in time to save any open files, for example. Access calls your add-in's OnAddinsUpdate procedure when it loads or unloads other add-ins. Access passes a System.Array to each procedure; the System.Array could contain extra information necessary to the procedure. (In fact, this parameter is always Nothing/null—Access never supplies any data in this parameter.)

Access passes useful information to your OnConnection procedure, including a reference to the application that hosts the add-in (Access, in this case), a value that indicates how the add-in was started, and a reference to the Access COM add-in that calls the current managed code. This example does not have to consider the manner in which the add-in was loaded, but it will use the two object references. It needs the application object so that it can call into the Access object model, and it needs the reference to the COM add-in layer so that it can expose functionality from within the add-in back to VBA code in your Access application.

To make it possible for your add-in to interact with the Access object model, follow these steps:

  1. In Solution Explorer, right-click your add-in project (the second node in the treeview, not the solution itself, which is the topmost node), and then select Add Reference.

  2. In the Add Reference dialog box, select the COM tab.

  3. Scroll through the list of available references to select Microsoft Access 12.0 Object Library, and then click OK to add the reference.

  4. To save typing, add the following statements before the Connect class.

    Imports Access = Microsoft.Office.Interop.Access
    
    using Access = Microsoft.Office.Interop.Access;
    using System.Data;
    
  5. Within the Connect class, but outside any procedure, delete the following two lines of code.

    Dim applicationObject As Object
    Dim addInInstance As Object
    
    private object applicationObject;
    private object addInInstance;
    
  6. Within the Connect class, but outside any procedure, replace the previous declarations with the following two declarations.

    Private applicationObject As Access.Application
    Private addInInstance As Microsoft.Office.Core.ComAddIn
    
    private Access.Application applicationObject = null;
    private Microsoft.Office.Core.COMAddIn addInInstance = null;
    

    The two declarations provide class-level variables that refer to the Access application, and to the particular instance of the add-in class that Access has created.

  7. Modify the OnConnection procedure so that it contains the following code. Replace the current code within the procedure, which performs similar operations by using the original untyped variables.

    applicationObject = CType(application, Access.Application)
    addInInstance = CType(addInInst, Microsoft.Office.Core.COMAddIn)
    
    ' This line enables VBA to call back into this object.
    addInInstance.Object = Me
    
    applicationObject = (Access.Application)application;
    addInInstance = (Microsoft.Office.Core.COMAddIn)addInInst;
    
    // This line enables VBA to call back into this object.
    addInInstance.Object = this;
    

    This code takes care of three issues. First, it converts the Object application variable to a strongly typed Access Application object:

    applicationObject = CType(application, Access.Application)
    
    applicationObject = (Access.Application)application;
    

    Next, it converts the Object addInInst variable into a strongly typed Office COMAddIn type, so the code can use its properties:

    addInInstance = CType(addInInst, Microsoft.Office.Core.COMAddIn)
    
    addInInstance = (Microsoft.Office.Core.COMAddIn)addInInst;
    

    Finally, the code sets the Object property of the addInInstance variable to refer to the current instance of the Connect class (the running object that contains your code). With this information set, your Access application can call public methods in this class.

To be able to interact with Access objects, it is easiest if you create managed variables that refer to the objects. If you want to react to events of the controls, you have to hook up those events. In Visual Basic, that is easy—you use the WithEvents keyword, and you can handle any event of the object. In C#, this requires manually hooking up each individual event. (This is not as difficult as it sounds, because you always have to set the corresponding Access event property to "[Event Procedure]" anyway. You always have to write some code for each event. If you do not set the event property's value, Access does not raise the event.)

Follow these steps to create the "hookup" variables, and to connect to the Access objects' events:

  1. Within the Connect class, outside any procedures, add the following declarations, connecting local variables with Access controls. (Visual Basic developers, note the use of the WithEvents keyword associated with the controls for which you handle events).

    Listing 1. Add references to the Access controls

    Private WithEvents fillProductsButton As Access.CommandButton
    Private WithEvents productsListBox As Access.ListBox
    
    Private unitPriceTextBox As Access.TextBox
    Private quantityTextBox As Access.TextBox
    
    private Access.CommandButtonClass fillProductsButton = null;
    private Access.ListBoxClass productsListBox = null;
    
    private Access.TextBoxClass unitPriceTextBox = null;
    private Access.TextBoxClass quantityTextBox = null;
    
  2. Add the following procedure, which enables the Access application to pass in references to all the controls on the form that you must interact with at run time. This procedure sets your local variables so that they refer to the Access controls, and hooks up event handling for the controls for which you will handle events. C# developers, notice that you have not yet hooked up the event handlers in your code to take any action in reaction to the Access objects' events—you will do that after you add the corresponding event handlers.

    Listing 2. Add code to hook up the controls so that you can refer to Access controls from managed code

    Public Sub HookupControls( _
     ByVal button As Access.CommandButton, _
     ByVal listBox As Access.ListBox, _
     ByVal textBox1 As Access.TextBox, _
     ByVal textBox2 As Access.TextBox)
    
        fillProductsButton = button
        fillProductsButton.OnClick = "[Event Procedure]"
    
        productsListBox = listBox
        productsListBox.AfterUpdate = "[Event Procedure]"
    
        unitPriceTextBox = textBox1
        quantityTextBox = textBox2
    End Sub
    
    public void HookupControls(
       Access.CommandButtonClass button,
       Access.ListBoxClass listBox,
       Access.TextBoxClass textBox1,
       Access.TextBoxClass textBox2)
    {
        fillProductsButton = button;
        fillProductsButton.Click += 
            new Access.DispCommandButtonEvents_ClickEventHandler(
            fillProductsButton_Click);
        fillProductsButton.OnClick = "[Event Procedure]";
    
        unitPriceTextBox = textBox1;
        quantityTextBox = textBox2;
    }
    
    NoteNote

    Unless your code explicitly sets each individual event property to contain the text "[Event Procedure]", Access never raises the corresponding event.

Before you can call the Web service, you must add the Web reference. Follow these steps to add the Web reference:

  1. Select Project, and then select Add Web Reference.

  2. In the Add Web Reference dialog box, type the following address: http://www.mcwtech.com/northwinddata/products.asmx.

  3. Click Go to begin the search.

  4. After Visual Studio locates the reference, set the Web reference name to Products.

    Figure 4 shows the completed dialog box.

    Figure 4. Select the Web reference

    Select the Web reference

  5. Click Add Reference.

When you click Fill Data on the Access form, the sample calls the Products Web service, retrieves information about available products, fills an ActiveX Data Objects (ADO) recordset, and binds the ListBox control on the Access form to the data. Follow these steps to add that functionality:

  1. In the Connect class, create a new procedure named fillProductsButton_Click, with the following signature.

    Private Sub fillProductsButton_Click() _
     Handles fillProductsButton.Click
    End Sub
    
    private void fillProductsButton_Click()
    {
    }
    
  2. Add code within the fillProductsButton_Click procedure to call the Products Web service, and retrieve a DataTable containing the requested results.

    Dim table As DataTable = _
     My.WebServices.Products.GetAllProducts.Tables(0)
    
    Products.Products myProducts = 
      new Products.Products();
    DataTable table = 
      myProducts.GetAllProducts().Tables[0];
    
  3. Add code to the procedure, setting up the properties of the ListBox control on the Access form so that you can later bind the control to the ADO recordset that you will create.

    productsListBox.ColumnCount = 4
    productsListBox.BoundColumn = 2
    productsListBox.ColumnWidths = ";0;0;0"
    productsListBox.RowSource = ""
    
    productsListBox.ColumnCount = 4;
    productsListBox.BoundColumn = 2;
    productsListBox.ColumnWidths = ";0;0;0";
    productsListBox.RowSource = "";
    
  4. Add the following code to the procedure, which sets up the ADO recordset that you use to display data in the ListBox control.

    Dim rst As New ADODB.Recordset
    rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    rst.LockType = ADODB.LockTypeEnum.adLockOptimistic
    rst.CursorType = ADODB.CursorTypeEnum.adOpenStatic
    
    ADODB.Recordset rst = new ADODB.Recordset();
    rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
    
  5. Add the following code to the procedure, which creates the four fields in the recordset. (C# developers, note that you must supply a value for all the parameters—C# does not support optional VBA parameters.)

    rst.Fields.Append( _
     "ProductName", ADODB.DataTypeEnum.adVarChar, 50)
    rst.Fields.Append( _
     "ProductID", ADODB.DataTypeEnum.adSmallInt)
    rst.Fields.Append( _
     "UnitPrice", ADODB.DataTypeEnum.adCurrency)
    rst.Fields.Append( _
     "UnitsInStock", ADODB.DataTypeEnum.adSmallInt)
    
    rst.Fields.Append(
      "ProductName", ADODB.DataTypeEnum.adVarChar, 
      50, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
    rst.Fields.Append(
      "ProductID", ADODB.DataTypeEnum.adSmallInt, 
      2, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
    rst.Fields.Append(
      "UnitPrice", ADODB.DataTypeEnum.adCurrency, 
      8, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
    rst.Fields.Append(
      "UnitsInStock", ADODB.DataTypeEnum.adSmallInt, 
      2, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
    
  6. Add the following code to the procedure, which opens the recordset and copies data into it from the DataTable.

    rst.Open()
    For Each row As DataRow In table.Rows
        rst.AddNew()
        rst("ProductName").Value = row("ProductName")
        rst("ProductID").Value = row("ProductID")
        rst("UnitPrice").Value = row("UnitPrice")
        rst("UnitsInStock").Value = row("UnitsInStock")
        rst.Update()
    Next
    
    rst.Open(Type.Missing, Type.Missing,
      ADODB.CursorTypeEnum.adOpenStatic,
      ADODB.LockTypeEnum.adLockOptimistic, 
      (int) ADODB.CommandTypeEnum.adCmdUnspecified);
    foreach (DataRow row in table.Rows)
    {
      rst.AddNew(Type.Missing, Type.Missing);
      rst.Fields["ProductName"].Value = row["ProductName"];
      rst.Fields["ProductID"].Value = row["ProductID"];
      rst.Fields["UnitPrice"].Value = row["UnitPrice"];
      rst.Fields["UnitsInStock"].Value = row["UnitsInStock"];
    }
    
  7. Finally, add this code to the procedure, which binds the data to the ListBox control on the form.

    ' Tell the ListBox to use this recordset as its data source.
    ' Later, you can retrieve values from any of the columns.
    productsListBox.RowSourceType = "Table/Query"
    productsListBox.Recordset = rst
    
    // Tell the ListBox to use this recordset as its data source.
    // Later, you can retrieve values from any of the columns.
    productsListBox.RowSourceType = "Table/Query";
    productsListBox.Recordset = rst;
    

    The finished Click event handler looks like this:

    Listing 3. Handle the Click event of the button

    Private Sub fillProductsButton_Click() _
     Handles fillProductsButton.Click
        Dim table As DataTable = My.WebServices. _
         Products.GetAllProducts.Tables(0)
    
        productsListBox.ColumnCount = 4
        productsListBox.BoundColumn = 2
        productsListBox.ColumnWidths = ";0;0;0"
        productsListBox.RowSource = ""
    
        Dim rst As New ADODB.Recordset
        rst.CursorLocation = _
         ADODB.CursorLocationEnum.adUseClient
        rst.LockType = ADODB.LockTypeEnum.adLockOptimistic
        rst.CursorType = ADODB.CursorTypeEnum.adOpenStatic
    
        rst.Fields.Append( _
         "ProductName", ADODB.DataTypeEnum.adVarChar, 50)
        rst.Fields.Append( _
         "ProductID", ADODB.DataTypeEnum.adSmallInt)
        rst.Fields.Append( _
         "UnitPrice", ADODB.DataTypeEnum.adCurrency)
        rst.Fields.Append( _
         "UnitsInStock", ADODB.DataTypeEnum.adSmallInt)
    
        rst.Open()
        For Each row As DataRow In table.Rows
            rst.AddNew()
            rst("ProductName").Value = row("ProductName")
            rst("ProductID").Value = row("ProductID")
            rst("UnitPrice").Value = row("UnitPrice")
            rst("UnitsInStock").Value = row("UnitsInStock")
            rst.Update()
        Next
    
        ' Tell the ListBox to use this recordset as its data source.
        ' Later, you can retrieve values from any of the columns.
        productsListBox.RowSourceType = "Table/Query"
        productsListBox.Recordset = rst
    End Sub
    
    private void fillProductsButton_Click()
    {
        Products.Products myProducts =
            new Products.Products();
        DataTable table =
        myProducts.GetAllProducts().Tables[0];
    
        productsListBox.ColumnCount = 4;
        productsListBox.BoundColumn = 2;
        productsListBox.ColumnWidths = ";0;0;0";
        productsListBox.RowSource = "";
    
        ADODB.Recordset rst = new ADODB.Recordset();
        rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
    
        rst.Fields.Append(
            "ProductName", ADODB.DataTypeEnum.adVarChar,
            50, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
        rst.Fields.Append(
            "ProductID", ADODB.DataTypeEnum.adSmallInt,
            2, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
        rst.Fields.Append(
            "UnitPrice", ADODB.DataTypeEnum.adCurrency,
            8, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
        rst.Fields.Append(
            "UnitsInStock", ADODB.DataTypeEnum.adSmallInt,
            2, ADODB.FieldAttributeEnum.adFldUnspecified, Type.Missing);
    
        rst.Open(Type.Missing, Type.Missing,
            ADODB.CursorTypeEnum.adOpenStatic,
            ADODB.LockTypeEnum.adLockOptimistic,
            (int)ADODB.CommandTypeEnum.adCmdUnspecified);
        foreach (DataRow row in table.Rows)
        {
            rst.AddNew(Type.Missing, Type.Missing);
            rst.Fields["ProductName"].Value = row["ProductName"];
            rst.Fields["ProductID"].Value = row["ProductID"];
            rst.Fields["UnitPrice"].Value = row["UnitPrice"];
            rst.Fields["UnitsInStock"].Value = row["UnitsInStock"];
        }
    
        // Tell the ListBox to use this recordset as its data source.
        // Later, you can retrieve values from any of the columns.
        productsListBox.RowSourceType = "Table/Query";
        productsListBox.Recordset = rst;
    }
    
  8. Add the following new procedure, which handles the AfterUpdate event of the ListBox control. This code pushes selected values into the corresponding TextBox controls on the form. (C# developers, note that you cannot retrieve the value of the Columns property directly—C# does not support parameterized properties. Instead, you must call the get_Column method.)

    Listing 4. The add-in runs this code in reaction to the Click event of the button on the form

    Private Sub productsListBox_AfterUpdate() _
     Handles productsListBox.AfterUpdate
        unitPriceTextBox.Value = _
         Convert.ToDecimal(productsListBox.Column(2))
        quantityTextBox.Value = _
         Convert.ToInt32(productsListBox.Column(3))
    End Sub
    
    private void productsListBox_AfterUpdate()
    {
        unitPriceTextBox.Value = 
            Convert.ToDecimal(
            productsListBox.get_Column(2, Type.Missing));
        quantityTextBox.Value = 
            Convert.ToInt32(
            productsListBox.get_Column(3, Type.Missing));
    }
    
    NoteNote

    When you are working in C#, some event names and event-handling delegates are different from the corresponding items you would use when programming in Visual Basic.

  9. C# only: Modify the HookupControls procedure, adding the code shown in Listing 5 to the end of the procedure. This code hooks up the Access event handlers so that they call the code you created within the add-in. (If you investigate the objects in the Object Browser, you see that the Access.ListBox class does not raise events—for that, you must use the ListBoxClass class.)

    NoteNote

    You can enter just the first portion of each line of code, and then press the TAB key twice. The Visual Studio editor fills in the remainder of each line—but it does not get the correct event name for the AfterUpdate event. You have to replace that event name, as shown in the code listing.

    Listing 5. Add these event hookups to the HookupControls method

    productsListBox = listBox;
    productsListBox.DispListBoxEvents_Event_AfterUpdate +=
        new Access.DispListBoxEvents_AfterUpdateEventHandler(
        productsListBox_AfterUpdate);
    productsListBox.AfterUpdate = "[Event Procedure]";
    
    NoteNote

    Yes, the sample could have moved data from the ListBoxcontrol to the TextBox controls in another way without using this add-in at all—it could have set the ControlSource properties of the TextBox controls to retrieve their data from the various columns in the ListBox control directly. Creating the solution in that way, however, would not have allowed this add-in to demonstrate handling a second Access event.

Getting Ready to Run

If you want to be able to debug your add-in, you have to set up Visual Studio appropriately. Follow these steps to configure your environment:

  1. On the Project menu, select SharedAddIn Properties.

  2. In the Project Properties window, select the Debug tab.

  3. Select the Start external program option, then specify the location of msaccess.exe so that Visual Studio can start Access 2007 when you press F5 to begin debugging.

  4. In the Command line arguments option, type the full path to the sample database that you created earlier, so that Access can automatically load the database for you.

  5. On the File menu, select Save All.

  6. Press F5 to start debugging your add-in (which loads Access and your database for you).

  7. In Access, open Form1 in Design view.

  8. Add the following code to the form's Load event handler:

    With COMAddIns("SharedAddIn.Connect")
        ' Make sure the COM add-in is loaded.
        .Connect = True
    
        ' Hook up the desired objects.
        .Object.HookupControls Me.fillProductsButton, Me.productsListBox, _
            Me.unitPriceTextBox, Me.quantityTextBox
    End With
    

    This code uses the COMAddIns collection of the Access Application object, supplying the name of the add-in as the index into the collection. (The name of the add-in is its ProjectName.ClassName.) The code explicitly calls the add-in's Connect method, which then calls the OnConnection method in your code. Calling this method explicitly enables you to load your add-in on demand. It does not consume memory unless it is actually used by your application. Next, the code shown here calls the HookupControls method of the exposed Object property of the add-in. (Remember, in your add-in, you set the Object property of the add-in to contain a reference to the instance of the add-in class. This class contains a public HookupControls method, so your Access code can call back into the add-in and hook up the Access controls to the managed variables that refer to those controls.)

  9. Save your form, and then display it in Form view.

  10. Click Fill Data, and verify that the add-in runs, filling the list with products it retrieved by calling the Web service.

  11. Select a value from the list, and verify that the add-in copies values from the list into the two text-box controls on the form.

  12. Close the form when you are finished.

  13. In Access, in the upper-left corner of the window, click the Microsoft Office Button and then select Access Options. This displays the Access Options dialog box.

  14. In the Access Options dialog box, select Add-ins from the list of options on the left (see Figure 5).

    You can see information about all the current application add-ins. Your add-in appears here, with its location set to mscoree.dll. If you had loaded multiple add-ins created by using the Visual Studio Shared Add-in template, they would all display with the same location. They are all loaded within the same appdomain, all with the same security level. (An appdomain is a context in which the common language runtime loads and runs applications.) If any of these add-ins were to fail, all of the add-ins would fail, because they are all running within the same appdomain.

  15. When you are finished, click Cancel to dismiss the dialog box.

    Figure 5. Display information about Access add-ins

    Display information about Access add-ins

  16. Exit Access, and return to Visual Studio.

  17. If Visual Studio indicates that it is still debugging, on the Debug menu, select Stop Debugging and approve any alerts Visual Studio displays by clicking OK.

Note   You can debug your managed add-in just like any other application. To do this, set a breakpoint within the fillProductsButton_Click event handler. Press F5 to run the add-in again. Then, when you click the button on the Access form, you can use Visual Studio debugging features, including single-stepping through code and examining variables.

Deploying Your Add-in

For your managed add-in to work on other computers, it must be registered. Under the covers, the add-in still uses COM to perform its communication with Office—so, to load and run, it requires registry information. On your development computer, Visual Studio takes care of these details for you; you can run the add-in and test it without needing to take any extra steps.

To make it easier for you to deploy your add-in on other computers, Visual Studio creates a setup project when you create the add-in project. Figure 6 shows the Solution Explorer window for the Visual Basic sample, and the SharedAddinSetup project.

Figure 6. Visual Studio creates a setup project for you

Visual Studio creates a setup project for you

Follow these steps to use the setup project:

  1. In Solution Explorer, right-click the SharedAddInSetup project, and then select Build.

  2. To install, right-click the SharedAddInSetup project, and then select Install.

    (This is the same as if you browsed to the folder on the drive containing the Setup.exe application and ran that program.)

    Figure 7 shows the setup program in action. Follow the prompts to complete the installation. To uninstall, right-click the project, and then select Uninstall.

    Figure 7. Use the automatically generated Visual Studio setup program to install your add-in

    Use the setup program to install your add-in

The output of the setup program is the necessary installation files—these are the files you need to deploy to other computers, to be able to run your sample add-in (in addition to the Access database that uses your add-in—the setup program does not include that file). Figure 8 shows the setup project's output.

Figure 8. Setup project creates the necessary files

Setup project creates the necessary files

Note

In addition to Access 2007, your end users must have the .NET Framework 2.0 runtime or the .NET Framework 3.0 runtime installed on their computers to use your managed add-in.

Conclusion

You can use Access 2007 support for managed add-ins to create cross-application tools, as many software vendors will. But, that is not the most likely scenario: Most likely, you will use add-ins to supplement your VBA code, and to gain access to the rich .NET Framework environment. If there are tasks you want to accomplish that are easier in managed code, Access support for managed add-ins is a perfect solution: You get the unmatched Access form and report designers, and the power of Visual Studio and the .NET Framework when writing code. You can extend Access 2007 in ways that were previously difficult (if not impossible) by creating and incorporating managed add-ins into your applications.

About the Author

Ken Getz is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC, a Microsoft Solution Provider. He has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, the Access Developer's Handbook series, and the VBA Developer's Handbook series. Ken co-authored the AppDev C#, ASP.NET, VB.NET, and ADO.NET courseware. Ken is a technical editor for Advisor Publications' VB.NET Technical Journal, and he is a columnist for both MSDN Magazine and CoDe Magazine. Ken speaks regularly at a large number of industry events, including Advisor Media's Advisor Live events, FTP's VSLive, and Microsoft Tech-Ed.

Jan Fransen is a writer, trainer, and consultant specializing in Microsoft products. As a writer, Jan developed training courseware for AppDev, contributed to books about Microsoft Office, wrote white papers for publication on MSDN, and created samples designed to help developers get up to speed quickly on new Microsoft products and features.

This article was developed in partnership with A23 Consulting.

Additional Resources

For more information, see the following resources: