Navigation:  Articles > May-2005 >

Building an Access Add-In with .Net

Previous pageReturn to chapter overviewNext page

200505_rs1 Russell Sinclair        

In Russell Sinclair's article in the March 2005 issue of Smart Access, he showed you how to create builder add-ins for Access. This month, he goes further and shows you the power of add-ins created outside of Access.

ad6468x60

In the past few months, my time has been divided between an Access ADP product and an ASP.NET application developed in Visual Studio .NET. These environments provide very different tools for developing and working with SQL Server databases. In addition to these development tools, I often switch out to SQL Query Analyzer when I have some SQL that I feel I really need to code by hand. Constantly switching development environments can really show you where each product shines and where improvements can be made. In fact, no one of these products provides everything I need in order to complete any development task.

Fortunately, Microsoft provides the ability to extend many of its applications with your own functionality. In my previous article ("Constructing a SQL Parameter Add-In" in the March 2005 issue), I discussed creating a standard Access add-in. That add-in was simply an Access database with specialized code and some configuration tables. There's another type of add-in that you can create for Access and many other Microsoft products: a fully compiled add-in.

Compiled add-ins are specially coded DLLs that allow Access (among other products) to call the code in your DLL. Products that can be extended by add-ins include all Microsoft Office programs, Visual Studio .NET, and the VBA development environment itself. There may be other host applications available, depending on whether or not they implement the standard Microsoft Office add-in APIs.

Compiled add-ins have several benefits over standard Access add-ins. First of all, they can be shared among multiple applications. You can create a single add-in that's loaded by Word, Excel, and Access and, as a result, share the code in your add-in among these applications. Second, compiled add-ins open you up to all of the benefits of the programming language in which they're created. There are a number of things that you can do easily in VB 6.0, VB.NET, C, or C# that are extremely difficult or impossible to do in VBA in Access. Finally, compiled add-ins provide you with the ability to share your customization among users without having to have each user on a computer install your add-in separately. Standard Access add-ins must be installed by each user of a single computer.

Creating an add-in

Compiled add-ins can be created in a number of applications including C++ 6.0, VB 6.0, and Visual Studio .NET. Since I don't want to date myself in this article (as I've been known to do before), I created my add-in in VB.NET. If you want to create an add-in in VB 6.0, the steps are very similar.

The first step to creating an add-in in Visual Studio .NET is to create a new Project. When the New Project dialog opens, select the Extensibility Projects folder in the Other Projects section and click on the Shared Add-In option, as shown in Figure 1.

200505_rs1
Figure 1

Give your project a name and choose a location in which it will be saved. When you click OK on this form, Visual Studio will start the Add-In Wizard. The first screen is a basic intro screen. Click Next to take you to the Programming Language screen. Visual Studio .NET allows you to create an add-in using Visual C#, Visual Basic, or Visual C++. Other .NET languages may be available on your computer but they can't be used with this wizard (you can create add-ins in other languages by bypassing the wizard, but I won't cover that here). Select the language to use (in my case VB.NET) and move to the next screen.

This screen, shown in Figure 2, shows a list of all of the enabled host applications on your computer.

200505_rs2
Figure 2

Checking off any of the applications will make the necessary changes to the project to allow it to load for the selected applications. In my case, I've only selected Microsoft Access since I don't want to share this add-in with other applications.

On the next screen, you must enter a name and description for your add-in. This information will be used by the host application to identify the add-in.

The next screen provides you with two options that will determine the behavior of your add-in. The first option asks whether you want your add-in to load when the host application is loaded. Since Access doesn't provide any interface to load or unload add-ins, I suggest that you enable this option. The second option allows you to define whether or not the add-in should be loaded for all users when it's installed. If you want to share it among multiple users on a single PC, enable this option.

The final screen shows a summary of the options you selected. Click Finish to create your new solution in Visual Studio.

If you look at the Solution Explorer window, you may notice that Visual Studio has created two projects in this solution. The first project is your add-in and the second project is a deployment project. The project we want to concentrate on at the moment is the main add-in project, as shown in Figure 3.

200505_rs3
Figure 3

Getting connected

When your add-in is created, Visual Studio .NET adds a Connect class file and a number of references to the project. The Connect class is called by host applications when predefined application events occur. In order to respond to these events, the Connect class implements an interface called Extensibility.IDTExtensibility2. This interface provides access to five methods that will automatically be called by your add-in's host application. You only need to be concerned with two events: OnConnection and OnDisconnection.

The OnConnection method is called by a host application when it loads your add-in. Only one of the parameters passed concerns us in this case: the first parameter, an object called "application." This object is a reference to the same Application object for the host that you can access through VBA. The Application object in Access is the default object in all Access VBA code and can be omitted in most code within Access itself. Because the Application object can be omitted from code, you may not know this object by name but you use it often. It's the Application object that provides you with functions such as DoCmd, CurrentProject, the domain aggregate functions, and many others. When developing a .NET add-in for Access, you need this object to use any of the standard Access functionality from your add-in.

The other parameters passed to this method provide more information about the state of your add-in and the calling application, but you can usually ignore them.

The OnDisconnection method is called by the host application when your add-in is being unloaded. This method is passed an argument that tells you why your add-in is being unloaded, along with some custom information. Neither of these parameters is really pertinent for Access add-ins since you need your add-in to start when Access starts. However, if there's any cleanup that your add-in needs to do (for example, unloading forms, releasing object references, or cleaning up any changes you made to the menus), you can put the necessary code in the OnDisconnection method.

Interacting with Access

Before you can do any real work with Access, you'll need to make sure that the objects that will handle the conversion between .NET and Office are installed. These are in the Microsoft Office XP or 2003 Primary Interop Assemblies, available for download from http://download.microsoft.com (see the ".NET Primary Interop Assemblies" sidebar for more information on Primary Interop Assemblies).

You'll need to add two or three references to your application. From the Project menu, choose Add Reference... When the Add Reference dialog opens, select the .NET tab and select the references for "adodb," "Microsoft.Office.Interop.Access," and, if you require it, "dao" (as shown in Figure 4).

200505_rs4
Figure 4

Once you have the references listed in the Selected Components section, click OK to add the references to your project. If you're using a version of Access prior to 2002, you won't be able to use the PIAs. Instead, you can add references to the Office and other libraries by selecting them from the COM tab.

Now that you have the basics configured, it's time to add some code to the application. In order for your add-in to be of any use, you probably want to provide a way for the user to interact with your add-in. The usual mechanism is to add some menu items using the Office CommandBars in Access. First, add these Imports statements to your module by adding this code above the Class declaration:

Imports Microsoft.Office.Interop

Imports System.Windows.Forms

This will allow you to shorthand some of the code you have to enter later on. Next, remove the default module-level variables that the wizard creates and replace them with these two variables:

Private mappAcc As Access.Application

Private WithEvents mcbbButton As CommandBarButton

The first variable will hold a reference to the Access application and the second will interact with a button that will be placed on a toolbar. By declaring this variable using WithEvents, you'll be able to add an event procedure to the Click event of this button.

Now we want to hook the application up to our add-in and add a button to a toolbar. To do this, use code like this:

mappAcc = CType(application, Access.Application)

cbrToolbar = mappAcc.CommandBars("Database")

mcbbButton = CType(cbrToolbar.Controls.Add( _

  "MyCommand"), CommandBarButton)

With mcbbButton

    .Caption = "Do Something"

    .BeginGroup = True

    .FaceId = 222

    .DescriptionText = "Do something useful"

    .TooltipText = "Do something useful"

End With

This code not only adds the button, it sets the text and image that will be used when the button is displayed. The image you use can be a built-in image identified by its FaceID (you'll have to know the ID or determine it from another button) or you can use an existing image file. Adding your own image to a toolbar is a bit more complicated. There's a good article on this in the Microsoft Knowledge Base at http://support.microsoft.com. Look for KB article 824017.

Strangely, one of the annoyingly difficult things to do in Access add-ins is to determine the name of the CommandBar to which you want to add your button. There are dozens of CommandBars in Access–menus, toolbars, shortcut menus. In order to place your button on one of these menus, you need to reference the menu by name or position. There's no map of the CommandBars available, and you can't necessarily determine their proper names from the names in the Customize Menus dialog. In order to make this job easier, the sample project for this article contains a form called frmCommandBars. This form provides a tree view of all of the Access command bars. Simply add a button to the Database toolbar as shown earlier and code to the click event to show the form.

Now that your button is hooked up and ready to go, you should probably add some code to so that your add-in does something when the user clicks the button and the button's Click event fires. Since you declared the button variable with WithEvents, creating the event is simply a matter of selecting the button name and then the Click event from the Navigation Bar dropdowns at the top of the editing window, as you would in Access code. You can then add any code you like to this procedure. Use code like the following if you want to show a form when the user clicks the button:

frm = New frmProperties

frm.LoadProperties(mappAccess)

frm.ShowDialog()

frm.Close()

frm.Dispose()

Testing the add-in

You won't want to use your add-in or give it to other people until you've tested it. The easiest way to test your add-in is to run it directly from Visual Studio. This will allow you to debug your application in real time. You can configure your project to run Access when you start your project in debug mode by following these steps:

1. Select Project | Properties from the menus to open the project property pages dialog.

2. Select the Configuration Properties folder on the left.

3. Select the Debugging item in that folder.

4. Select the Start External Program option.

5. Use the browser button to select the MSACCESS.EXE executable, as shown in Figure 5.

200505_rs5
Figure 5

You can also change the Working Directory option if you like, but it isn't absolutely necessary.

If you press the F5 key in Visual Studio .NET, Access will start and load your add-in. If your code is written properly, the add-in should load the toolbar items you created. Click on the toolbar button you created and your code should run. Voilà–you have an add-in!

Deployment

The final step on the road to add-in development is to deploy your add-in to users. If you use the add-in wizard, Visual Studio makes this very easy. The wizard actually creates two projects: your add-in and a standard setup project. The setup project contains all of the necessary files and information to create a standard Windows Installer installation. However, Visual Studio turns off compiling this project by default, so you'll need to turn that facility on. To do this:

1. From the Build menu, choose Configuration Manager. This dialog allows you to turn on and off the building of different projects within the solution, among other things.

2. When the dialog opens, change the Active Solution Configuration to Release so that you can change the options used when compiling in Release mode.

3. Check the Build option for the setup project, as shown in Figure 6.

200505_rs6
Figure 6

You've now turned on compiling your setup project when in Release mode. I wouldn't recommend setting the Build option for the setup project in the Debug configuration for Visual Studio .NET. When you're debugging, you don't want the setup project to be built (compiling a setup project is very time-consuming). If you've finished your testing and want the installer to be built, simply switch to Release mode (you can do this quickly from a dropdown in the Visual Studio toolbar) and build your project. When the build is complete, Visual Studio will have created an MSI file that you can send to other people to install your add-in.

The sample add-in

The download file for this article contains a simple .NET add-in that I created. This add-in replaces the Properties right-click menu item for tables and queries (the Properties menu item is disabled in ADP projects, and I found this annoying). The add-in replaces that menu item with my own Properties option.

The code behind my buttons checks to see what type of project is being used and either shows my own Properties form or calls the standard Access Properties function. This project should give you a good idea on how you can get started on your own Access add-ins.

 

Sidebar: .NET Primary Interop Assemblies

Primary Interop Assemblies, or PIAs, are the "official" bridge between COM libraries and .NET assemblies. When you directly reference a COM DLL from .NET, Visual Studio automatically creates a proxy .NET DLL that wraps any calls made to the COM component, marshaling calls, data, and objects between the two environments.

When Visual Studio creates an interop assembly, it uses generic algorithms designed to handle all COM components that it can. This means that the interop wrapper might not be optimized for the COM libraries it's calling. Also, each time you reference a COM component in a new project, a new interop assembly is created. PIAs are provided to overcome these two obstacles. The wrapper DLLs are standardized across all callers so that they can be shared between applications. They're also optimized to ensure that calls between the technologies are handled in an efficient manner.

 

Your download file is called 505SINCLAIR.ZIP in the file SA2005-05down.zip

This is found in theon this page