VSTO Add-ins for Access?

In my previous post, I looked at how the VSTO add-in model is flexible enough to be used in prototyping scenarios for versions of Office not yet released. In principle, the same applies to Office host applications that are not currently supported.

VSTO supports add-ins for most Office applications that support IDTExtensibility2-based COM add-ins, that is: Excel, InfoPath, Outlook, PowerPoint, Project, Visio and Word, but not for Access, FrontPage, Publisher or SharePoint Designer. The VSTO AddIn base class is host-agnostic (that is, agnostic to the type of the host application), and the VSTO add-in project templates/wizards are almost (but not quite) host-agnostic. Each project template generates code that is host-specific. For some hosts (Excel and Outlook) there is host-specific host-specific code, while for others there is only generic host-specific code.

That last sentence doesn't make a lot of sense, so let me explain. Here's what I mean by "host-specific host-specific" code – that is, code that only applies to a specific host, and uses types that are specific to that host:

· In Excel projects, the template generates a line in the assemblyinfo.cs to set the assembly-level ExcelLocale1033 attribute. This causes the Excel object model to act the same in all locales, which matches the behavior of VBA. This attribute is specific to Excel, and is only used in Excel projects.

· In Outlook projects, the template generates additional code in the hidden part of the partial ThisAddIn class (in ThisAddIn.Designer.cs) specific to custom form regions, based on the FormRegionReadOnlyCollection class. This is specific to Outlook, and only used in Outlook projects.

On the other hand, all add-in templates generate code in the ThisAddIn.Designer.cs that hooks up the add-in with the host application – however, although this is generic in behavior, it is host-specific in implementation. Each add-in gets an Application field. This field is of a type that is specific to each host, that is Microsoft.Office.Interop.Excel.Application or M.O.I.Word.Application, M.O.I.Outlook.Application, and so on. This is what I mean by "generic host-specific" code: all add-ins get this code, but the specific type of the field is different for each one.

Apart from the above, the generated add-in code is practically identical across all hosts. The other differences are in project settings:

· The PIA references for each add-in include the host-specific PIA that the add-in targets.

· The default debug behavior (on F5) is set to register the add-in for the target host application and run that application.

· The node names and icons used in the Solution Explorer are different for each host application.

Armed with this information, you can see it would be pretty easy to create a VSTO add-in for say Access. I could start off with an add-in for any of the supported hosts, and then make a few minor adjustments to turn it into an add-in for an unsupported host. Detailed steps follow…

1. To start building an Access add-in, I could create a Word (or InfoPath, PowerPoint, Project or Visio) add-in (Excel or Outlook would also work, but they have additional redundant host-specific code).

2. Then, I'll add a reference to the Microsoft Access Object Library, on the COM tab (this also pulls in references to ADODB and DAO). It also pulls in Microsoft.Office.Core.dll, which duplicates the Office.dll already referenced by default - so I'll delete one of these two duplicates.

3. In Solution Explorer, I can select the project and click the "show all files" button. This makes it easier to open the ThisAddIn.Designer.cs file – here I can change the declaration and initialization of the Application field from M.O.I.Word.Application to M.O.I.Access.Application. Note that this step changes a file that is auto-generated: the file is not normally re-generated, but can be if I corrupt the project (the point being that my manual changes will be lost if the file is re-generated):

//internal Microsoft.Office.Interop.Word.Application Application;

internal Microsoft.Office.Interop.Access.Application Application;

//this.Application = this.GetHostItem<Microsoft.Office.Interop.Word.Application>

(typeof(Microsoft.Office.Interop.Word.Application), "Application");

this.Application = this.GetHostItem<Microsoft.Office.Interop.Access.Application>

(typeof(Microsoft.Office.Interop.Access.Application), "Application");

4. That's all the code changes. Now for the project changes. There are two ways to do these changes – through the IDE in a way that overrides or counters the default settings; or by manually editing the .csproj file directly, to replace the default settings. Let's look at both approaches: first through the IDE, then manually.

5. First, I'll change the Project Properties | Debug | Start action, to "Start external program", and specify the path to Access, for example:

C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE

                               

6. Then, I'll create a .reg file with the same name as my add-in solution, and put it in the solution folder. This reg file is used to register the add-in for Access (and unregister it for Word). The example reg file listed below is simply a dump of what the standard VSTO build task does for each add-in type, with an additional line. The additional line (the first reg entry below) simply removes the entry that the build task puts in for Word. The remaining entries are identical for Word and Access, with the only changing being to replace "Word" with "Access":

Windows Registry Editor Version 5.00

[-HKEY_CURRENT_USER\Software\Microsoft\Office\Word\Addins\MyAddIn]

[HKEY_CURRENT_USER\Software\Microsoft\Office\Access\Addins\MyAddIn]

"Description"="MyAddIn"

"FriendlyName"="MyAddIn"

"LoadBehavior"=dword:00000003

"Manifest"="C:\\Temp\\MyAddIn\\bin\\Debug\\MyAddIn.vsto|vstolocal"

7. In Project Properties | Build Events, I add a Post-build event commandline to merge the .reg file into the registry:

regedit /s "$(SolutionDir)$(SolutionName).reg"

8. That's it. I can now press F5 to build the solution: this will register the add-in for Access, and run Access for debugging with the add-in loaded.

9. Note that instead of setting the Debug property to an external program (step 4 above), I could modify the .csproj file directly, to set the <ProjectProperties DebugInfoExeName> from Word to Access. For example, change this:

<ProjectProperties HostName="Word" HostPackage="{D2B20FF5-A6E5-47E1-90E8-463C6860CB05}" OfficeVersion="12.0" VstxVersion="3.0" ApplicationType="Word" Language="cs" TemplatesPath="" DebugInfoExeName="#Software\Microsoft\Office\12.0\Word\InstallRoot\Path#WINWORD.EXE" AddItemTemplatesGuid="{147FB6A7-F239-4523-AE65-B6A4E49B361F}" />

… to this:

<ProjectProperties HostName="Access" HostPackage="{D2B20FF5-A6E5-47E1-90E8-463C6860CB05}" OfficeVersion="12.0" VstxVersion="3.0" ApplicationType="Access" Language="cs" TemplatesPath="" DebugInfoExeName="#Software\Microsoft\Office\12.0\Access\InstallRoot\Path#MSACCESS.EXE" AddItemTemplatesGuid="{147FB6A7-F239-4523-AE65-B6A4E49B361F}" />

Note that changing the <ProjectProperties HostName> value, as show above, changes the icons used in the Solution Explorer .

10. I could also change the <Host> element's Name value to change the name of the parent node for the ThisAddIn.cs in the Solution Explorer. Change this:

<Host Name="Word" GeneratedCodeNamespace="MyAddIn" IconIndex="0">

<HostItem Name="ThisAddIn" Code="ThisAddIn.cs" CanonicalName="AddIn" CanActivate="false" IconIndex="1" Blueprint="ThisAddIn.Designer.xml" GeneratedCode="ThisAddIn.Designer.cs" />

</Host>

… to this:

<Host Name="Access" GeneratedCodeNamespace="MyAddIn" IconIndex="0">

<HostItem Name="ThisAddIn" Code="ThisAddIn.cs" CanonicalName="AddIn" CanActivate="false" IconIndex="1" Blueprint="ThisAddIn.Designer.xml" GeneratedCode="ThisAddIn.Designer.cs" />

</Host>

11. Also, registration is determined by the <OfficeApplication> element value. So, instead of setting up a .reg file as a post-build task (steps 5-6 above), I could edit the .csproj directly to change this:

<OfficeApplication>Word</OfficeApplication>

…to this:

<OfficeApplication>Access</OfficeApplication>

It should be obvious from all this that we designed the AddIn base class and the add-in projects to be almost completely host-agnostic. This is what allowed us to add support for so many Office hosts in the very rapid VSTO 2005 SE release, and to add support for Project in the VS 2008 release at the same time as adding a whole raft of new functionality. From a development perspective, each additional Office host application is a minor amount of work. So, why didn't we simply add add-in projects for all Office hosts?

There are two main reasons. First, there is a lot less demand for Access, FrontPage, Publisher and SharePoint Designer add-ins compared to the demand for add-ins for the hosts that we do support. Second, while the dev cost is small for each host, the test cost is huge. If you consider the permutations of test (all the Office 2003 apps, all the Office 2007 apps, running on XP, on Windows 2003, on Vista, with varying levels of SP, both x86 and 64-bit, with all the other variants such as other managed/unmanaged add-ins loaded or not, VSTO doc-level customizations also loaded or not, different load sequences, with/without custom task panes, with/without ribbon customization, etc etc), you can see the matrix rapidly balloons to unmanageable proportions. This trade-off always applies when releasing general-purpose software: somewhere you make a compromise between the features you can build and the features you can support (that is, the features you have the time+resources to test thoroughly enough to support them).

In this post, I've explored the largely host-agnostic nature of VSTO add-in projects, by converting a Word add-in into an Access add-in. Note, however, that I'm not encouraging people to use this approach in production – we have not tested this behavior, and it is expressly not supported in any way. What I've done is to explore how VSTO is designed to be optimally host-agnostic, so that the add-in model is as flexible as possible – without going to the extreme of loose typing offered by the old "shared" add-in model.