VSTO Add-ins for Access?

Article
02/19/2008

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:

… to this:

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>

… to this:

</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:

…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.

Comments

Anonymous
March 02, 2008
Do you know when Access add-ins are likely to be fully supported?
Anonymous
March 05, 2008
We're still gathering data on how useful it would really be to support Access add-ins. I know there is demand, but I'm not clear on how much demand, and exactly what kind of add-ins people are likely to want to build for Access.
Anonymous
March 25, 2008
I have tried your example and made my own usercontrol and it works fine.I only need to have focus in my new control(Custom panel), when I click on a button in Access. Now I have to Click on the usercontrol before I start typing, and it's not what my customer's want.I can't find a way to do this.Do you thing it's possible?/Alex Pedersen
Anonymous
April 30, 2008
I was able to create an Access Add-In. However, I created a ribbon but it would not show up in Access. Any ideas why?That's my class:namespace AccessAddIn{ public partial class ThisAddIn { Ribbon1 myRibbon; private void ThisAddIn_Startup(object sender, System.EventArgs e) { myRibbon = new Ribbon1(); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion }}
Anonymous
April 30, 2008
Lukas - it's not clear from your comment exactly what the type of Ribbon1 is. Is this a class that implements IRibbonExtensibility? Or is it a class generated by the VS Visual Ribbon designer, derived from OfficeRibbon?In either case, your code doesn't look correct. If the Ribbon1 class implements IRibbonExtensibility, then you need to instantiate it in an override of either RequestService or CreateRibbonExtensibilityObject. See here: http://msdn.microsoft.com/en-us/library/bb608623.aspx and here: http://blogs.msdn.com/andreww/archive/2006/12/23/low-level-support-for-icustomtaskpaneconsumer-iribbonextensibility-formregionstartup-etc.aspx for more details.
Anonymous
May 05, 2008
Hi Andrew - I created it using the VS Visual Ribbon designer, that's the generated code:using System;using System.Collections.Generic;using System.Linq;using System.Text;using Microsoft.Office.Tools.Ribbon;namespace AccessAddIn{ public partial class Ribbon1 : OfficeRibbon { public Ribbon1() { InitializeComponent(); } private void Ribbon1_Load(object sender, RibbonUIEventArgs e) { } }}
Anonymous
May 09, 2008
People have been building native shared add-ins for Office (and related apps) since their introduction
Anonymous
May 16, 2008
Lukas - that should work. However, you do need to set the RibbonType property on the ribbon wrapper object to the correct Access string. Do this in the InitializeComponent of your custom ribbon class:this.RibbonType = "Microsoft.Access.Database";
Anonymous
May 22, 2008
In an earlier post , I looked at how you could morph a VSTO project for one application into a project
Anonymous
June 09, 2008
Do we need VSTO for Access? Well, MS supports COM Add-ins for Access, which must be written in C++ (yuck). If MS is serious about .NET (and sometimes I still wonder), then the COM add-ins need a .NET counterpart.
Anonymous
July 09, 2008
My search for a way to re-use forms among several disparate databases has led me here. Is there any other way to share/re-use "standard" forms across separate databases? I've been wondering how hard it is actually for Microsoft to provide the ability to to link forms contained in another (master, if you will) database to another database. They already do it for tables, and if I remember correctly, there is a way to use the code WITHIN forms in external databases, but why not the entire forms themselves?
Anonymous
July 10, 2008
Don - sorry, I can't answer that question. Your best bet is to post your question on one of the Access programming forums, such as this one:http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.modulesdaovba&lang=en&cr=USor this one:http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.formscoding&lang=en&cr=US
Anonymous
July 14, 2008
So what exactly are we doing here? Playing around with experimental stuff and HOPE that it works in the business world? I've been looking for some plausible solution to creating a reusable add-in for Access, and I thought this thread on "VSTO Add-ins for Access?" might have given me a legitimate solution. Since you're still asking questions and posting hypothetical solutions, I thought that maybe my question could get usable solutions flowing. Sorry if I sound a little harsh, but I'm truly frustrated with the fact that MS constantly gives piecemeal solutions and leaves the rest of the business community to pontificate. Thanks for the links anyway. My search continues...
Anonymous
July 15, 2008
Don - thanks for your comments. My aim in this post was purely to explore the technology and see how feasible it might be to build Access add-ins with VSTO. As this is not supported, I explicitly didn't position this as a realistic option for production systems. As you suggest, this was simply experimentation. As I've said elsewhere, the reason we don't support Access just yet is mainly a resource constraint. We take great pains to do very comprehensive testing on the runtime and design-time pieces that we release - and every additional application we support adds very significantly to the test matrix. If we had an endless supply of time and/or bodies, we could test more and support more, but as it is we make feature decisions based on priority. Support for hosts such as Excel, Word, Outlook, PowerPoint, Project, InfoPath and Visio was higher priority than for Access. We still hope to get to Access at some point - and this blog post hopefully shows that the technology will support it even if our product release logistics can't yet.
Anonymous
July 15, 2008
Thank you for your response. My application uses Access as the "lynchpin" and conduit to the other Office applications you mention here, so I hope you can appreciate my point of view. I know that the powers that be would rather me use Visual Studio, but I "grew up" with Access, and at least having the option to incorporate VSTO into Access instead of the other way around would indeed be sweet. I leave you with at least one vote for Access VSTO support.... Good luck and godspeed with your endeavors. Thanks again.
Anonymous
July 29, 2008
The comment has been removed
Anonymous
July 29, 2008
Sascha - thank you for your comments. I agree with you that there is clearly a requirement for supporting Access with VSTO add-ins, and this is something we consider every time we release a new version of VSTO. We will definitely be considering it very carefully for the next release also.
Anonymous
July 30, 2008
Andrew, thanks for your interesting posts. I created COM-Add-Ins for Access using another way: I implemented IDTExtensibility2, ICustomUI and IRibbonControl to add a ribbon control to Access that opens a Winform. When I use the del key when a textbox has the focus, the text is not deleted - but Access tries to delete the current object (like a table or a query) in the navigation bar (if any object is open, Access tries to delete whatever has the focus - for example a recordset in a table).Maybe this is the reason for not publishing a COM-Add-In template for Access ... ;-)Anyway: Do you have an idea what is going on here? I tried to catch del with the form's and the textbox's keydown events, but nothing happens when using del or some other keys like strg + f (this opens the search dialog of Access).Thanks in advance, André
Anonymous
July 31, 2008
Andre - this sounds like a message pump issue. Is your winform a modeless window? It seems Access is grabbing the focus away from your winform. This shouldn't happen if your winform is modal.
Anonymous
July 31, 2008
Andrew, thanks for your fast answer. You are right, the window is not modal, but it is "always on top". The winform can't be modal because I want to drag Access objects like tables or querys from the navigation pane and drop them into the textbox on the form. If that's not possible I have to try another way like choosing tables/queries in a combobox on the winform, but I hope you have an idea how to make the winform/textbox catching the delete key message. Thanks!
Anonymous
July 31, 2008
Andre - I believe the problem is that your add-in doesn't own the message pump. The message pump is owned by the host application. Therefore, all the keystroke messages are taken by the host app and not dispatched to your modeless dialog.The problem does not occur with a modal dialog because modal dialogs have their own message pump.The only other workaround I'm aware of is to use an API such as SetWindowsHookEx to intercept messages, then call IsDialogMessage to figure out if the message really should go to your dialog instead of the main window.
Anonymous
August 04, 2008
Andrew, I managed to intercept the del key and to find out if it should go to the main window or the dialog, but I didn't manage to forward it. For now I decided to make the dialog modal and to let the user choose existing tables or queries in a combobox instead of dragging them from the navigation pane.Nevertheless I would appreciate an example for how to intercept the keyboard message and to forward it to different windows.
Anonymous
August 04, 2008
Andre - glad to hear you got it working. If you want to forward a message to a window, you could use the SendMessage or PostMessage APIs. That said, in general, it's probably safer to avoid using modeless windows in the context of an Office app. I'd generally be very reluctant to interfere with the Office app's message pump, as you're likely to get unexpected behavior. Your decision to use a modal dialog is probably a more robust design.
Anonymous
September 11, 2008
I support an Access 2003 site and have been trying to finda way to obtain the property scanner add-in that was part of VSOT 2003. I am not having much luck. Any help will be appreciated.
Anonymous
September 11, 2008
Judd - I don't know what "property scanner add-in" you're referring to. Can you provide more details please?
Anonymous
October 15, 2008
http://msdn.microsoft.com/en-us/office/aa905403.aspxIt is part of the ADE Access Developer Extensions and is documented at the above link. Still looking without any feeling I am getting closer.
Anonymous
October 15, 2008
Judd - the Access 2003 Dev Extensions were shipped with VSTO 2005. The Access 2007 Dev Extensions are a web download (but the 2007 version does not include the property scanner add-in). Per the documentation here: http://office.microsoft.com/en-us/access/HA011208861033.aspx, you can only get the 2003 ADE either with VSTO 2005 or as part of an MSDN Universal subscription.
Anonymous
October 18, 2008
I just uploaded VS2008 VSTO templates for creating Access add-ins to http://www.codeplex.com/VSTO. With those templates, you won't have to modify the files by hand.
Anonymous
December 14, 2008
ネタ元： http://blogs.msdn.com/andreww/archive/2008/02/19/vsto-add-ins-for-access.aspx なるほどね。結局、VSTOアドイン
Anonymous
March 29, 2009
Hi,I'm just a newbie in Shared/VSTO-AddIn's so I'm searching over the internet and read a lot about them. At first I found the article http://msdn.microsoft.com/en-gb/library/aa902693.aspx en created a shared add-in to easily contact a webservice from an Access 2007-application. Everything went ok (an it's working fine when opening the mdb or mde), but when I run/simulate that application in runtime it is not working. Is that a know problem? And will it be solved when I make an VSTO-Access-plugin (with the help of the above mentioned templates) instead of an Shared one?Thanks in advance, Marianne
Anonymous
March 30, 2009
Marianne - as I said in my post, the technique of using modified VSTO projects for Access is not supported - this was merely a discussion topic.Regarding the problems you're having with shared add-ins, it's impossible to tell what the problem is without significantly more details.Also, you'll probably get better answers if you post your question to the Access development forums:http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.modulesdaovba&lang=en&cr=US http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.office.developer.com.add_ins&lang=en&cr=US
Anonymous
March 31, 2009
Tnx, I'll try it on one of the forums you recommended.GrtzMarianne
Anonymous
June 02, 2009
I am looking for an Access Add-in to allow some method of passing a string field to C# code that reads a Webpage and returns the processed data (3 -5 string fields) or an error. I am using Visual Studio 2008 professional and the application are written in either in Access 2007 and Excel 2007. I have the Excel and Access template to start for VS2008 Professional.I can call routines that say things like 'hello world' bit I need to pass and return real data. Any help. I don't care about a ribbon. I have my other C# code working, I can read and parse the web site just fine and I have the data ready to return. I just need to pass a string to the Addin from Access and/or Excel, it does its work and i either get a preset returned data or I get an error that is trapped and return a flag if it failed.In Access I need to read one record at a time to the AddIn and return a preset count columns of data (an array). In Excel I want to read about 500-1000 records at a time. If i am limited to 255 I can deal with that. I can pass an array of the data and return with a couple of columns of data.I can write everything but the add-in that passes data back and forth.
Anonymous
June 10, 2009
sigmanfraud - it's not at all clear what you're trying to do. If I understand correctly, you have built an add-in for Excel and another add-in for Access. Then, you want to call methods on these add-ins from VBA code in Excel/Access? (Or from somewhere else?) And you can successfully call methods that display message boxes, but for some reason you can't call methods that take arguments or return values? That doesn't make a lot of sense - there should be no reason you can't do that. So, I must be misunderstanding what you're doing.

Share via

VSTO Add-ins for Access?

Comments

Additional resources