0 Comments

Recently I was nearly driven crazy by the task to create an msi-setup for my version control add-in for Microsoft Access in Visual Studio 2013. I referenced the correct PIA assemblies in my Visual Studio project, but other versions of them ended up on the target machines. My add-in was not working properly on some computers. Actually it was not working at all then.

I finally managed to solve the problem to reliably target multiple versions of an Office Application (Access in my case) with a single add-in. But as it required a bit of research and a lot of trial and error, I am writing this down to help you preserve your sanity if to want to achieve the same.

1. Use the PIAs for the oldest application version you are targeting

 

The first important thing is to reference the PIAs (Primary Interop Assemblies) for the oldest Office Version you want to support. That is Access 2007 (version 12.0) in my case. Even without the technical requirement to do this to compile an assembly that will run with all intended version, it is helpful during coding, as it prevents you from accidentally using features, which are not available in the older versions of your targeted application.

Downloading and installing the PIAs is fairly straight forward. Referencing them is as well, but that is where the trouble starts. The msi-setup for the PIAs installs them right into the GAC (Global Assembly Cache). When adding the references to your project, you can select the version you need in your project. But if you have got a newer version of the same PIA installed on your machine as well, Visual Studio will automatically perform assembly binding redirection and relink the references to the newer Versions of the files. That is not what I wanted! And the properties of the reference in Visual Studio are somewhat confusing.

Confusing version information in reference properties

2. Put the PIAs in a separate folder specific to your project

 

To prevent this version mix-up and really compile your add-in with the old PIA-version you selected, you will have to export the PIAs from the GAC to another folder, specific to your project. - I used GACView for this. - Then reference the PIA-files from that folder into your Visual Studio project, using the “browse”- option when adding the references.

It would probably work equally well to disable the assembly binding redirection by editing the policy files in the GAC, as described in this stack-overflow-answer. But that would affect all projects that reference these files on your computer. Therefor I choose the option to put them in a separate folder for my project.

Even now Visual Studio will still write the version number of the highest available version of those files to your project file.

VS project file in text editor showing wrong version information

But that does not affect the build process. If you open the compiled assembly in ILDASM and take a look at the assembly manifest, you’ll see that the correct versions of the PIA are referenced there.

External assembly references in ILDASM

Now with the build issues solved we should take care of the setup/deployment process.

3. Explicitly include your local copies of PIAs in the installer project

 

I currently use a Visual Studio Installer Project to build an msi setup for the users to install my add-in on the target machines. I think this is the most convenient way to create a setup for simple projects. I probably will use WiX (Windows Installer XML toolset) for that end in the future. WiX gives you much more control over your setup project, but it has a much steeper learning curve as well.

When I first just referenced the Office 12.0 PIAs from the GAC in the add-in-project, I ended up with a reference to the 12.0 version, my assembly was linked to the 15.0 version and yet the installer project, automatically detecting dependencies, added the 14.0 (!?) version to the setup. – That was bound for failure.

After changing the things described in Step 1 and 2 it looks like the installer behaves now and is packing the correct versions. Still I want to take no chances anymore and control this myself instead of using the automatic dependency detection.

Therefore I explicitly exclude the automatically detected dependencies to the PIA files from the setup project and then add the files from the file system folder. That leaves no room for any excuse for VS to mess with my setup project.

Installer project with excluded autodetected PIAs and manually added PIAs

Now I just build the solution and the installer project and upload the msi setup file to my website. Done!

 

Alternatives

 

As an afterthought, I would like to mention that there are some interesting alternatives to this approach that might prevent the whole problem.

1. NetOffice

You could use the NetOffice API instead of the PIAs to write add-ins for MS Office. NetOffice looks very promising and I actually tried to use it to address my problem. However I found a couple of Microsoft Access’ Enums and Methods (e.g. AcObjectType-Enum and the ControlType- and SourceObject-Properties) are not implemented yet. If you are writing an add-in for Word- or Excel you should definitely evaluate NetOffice as an option for you. These Office Applications are probably better supported by NetOffice as they are more widely used.

2. Embedded Types

Another option might be to use the CLR 4.0 and embed the types from the PIA you are using. This would have actually been my preferred solution, but my project needs to talk to some other APIs as well and those are having serious issues when called by a CLR 4.0 component.

 

Do you use another reliable approach to create and distribute COM-Add-Ins for Microsoft Office? – Let me know in the comments.

0 Comments

The previous part in this article series left off, when I put the project on hold indefinitely in 2013 due to technical difficulties and other work commitments.

Earlier this year (2015) I was in the fortunate situation to have completed all major ongoing consulting work for my clients and finally had a bit of “free” time. So I did some research on the state of source code control and Microsoft Access. Unfortunately, even though almost two years had passed, the situation regarding source code control integration into Microsoft Access 2013 had not changed a to better.

My research revealed that there were several open source projects for Subversion or general source code control integration, but on the first glance they seemed all to be just in various states of incompleteness. And I got the impression they would not fit well into my workflow at all.

While still none of my clients had upgraded to Microsoft Access 2013 that had become a dark shadow looming on the horizon. This was only a matter of time, so I had to find a solution soon. Furthermore I was contemplating the idea to become a Micro-ISV (Independent Software Vendor) for quite a while. But had given up all earlier approaches to that end because they seemed not to be viable at their time. Now I lacked the idea for a product to build and sell that was really providing value to someone enough for them to part with some of their hard earned money.

In that situation I had another look at the code of my source code control integration add-in. I started once again to ponder the problem of integration into Microsoft Access and luckily had an Idea that finally worked out much better than I expected. With this hurdle out of the way, I finally decided to try to develop that old SCC project of mine into a real product to sell to other developers.

While there was still a lot of tedious and sometimes frustrating work to do, to bring the integration into Microsoft Access to a point where I was happy with it, it was all doable and there were no further total blocks that would jeopardize the whole project from the technical side.

My previous work experience lies mainly with consulting. So it was quite challenging to learn all the other skills required to bring a product to market, like mapping out a marketing strategy, using Google AdWords and Bing-Ads, creating a modern and appealing website for the product. But I love challenges and learning those things was new and interesting. So while it all was taking so much more time than I had anticipated, it was rewarding and enjoyable work with a goal in sight.

I am well aware that it is usually not the best choice for a solo entrepreneur Micro-ISV to do all that work yourself. It is rather advisable to outsource it to someone who is experience with it and gets it done much quicker. Still I do have the philosophy to first learn the basics of how things work myself before I even think about outsourcing stuff to someone else. So while it is clearly not the most effective way to go about things, I still am happy with my choice of doing all that myself for the sake of learning it.

When I first decided to build the product entAscc from my experimental add-in-code, it planned to release the first non-public beta version at the beginning of April 2015 and the first public release about a month later. Now it is the beginning of May and it is just a little over week that I released the first non-public beta of entAscc. Extremely valuable feedback is coming in from the testers now and that is giving me a new boost of motivation, to complete the final steps to a public release.

I am curious how the future of entAscc will unfold.

(Maybe there will a part 3 to this article series, but that will probably take a couple of weeks.)

0 Comments

I'm using source code control for Microsoft Access development since the year 2002 and I cannot imagine developing without it now. I'm working frequently with distributed teams and source code control has proven itself to be invaluable for our development process.

So it came as bit of shock as Microsoft announced that there will be no source code control add-in for Microsoft Access 2013. I was wondering: What could I do now? 

The only viable alternative to the Microsoft Source Code Control Add-In at that time was the OASIS-SVN add-in for Microsoft Access that provides Subversion source code control integration for Microsoft Access. But I started working with Microsoft Visual Source Safe for source code control in 2002 and later, as is was the most painless way to upgrade to a "proper" source code control system, changed to Sourcegear Vault. - An excellent product that is meeting most of my needs for source code control until now. So I was not prepared to move my numerous existing projects with many years of history over to Subversion. From the day I was one of the early beta testers of OASIS, I asked Bernd Gilles, the maker of OASIS, if he would consider supporting Vault or the MSSCCI. Although het put support for Sourcegear Vault on his OASIS roadmap early on, there seemed to be not much happening in that regard even years later.

At that time I found the documentation for the MSSCCI-API on the MSDN Website by chance and decided to just try if I could implement an Access add-in for source code control myself.

So in early 2013 I started writing code without much of a planning of design phase, rather for the fun of it. The source code control API was not too difficult handle and I made good progress implementing that. However, as soon as I reached the point where integration into Microsoft Access came into focus, I hit one barrier after the other, each harder to overcome than the previous. The problem was, there is no documented API for Microsoft Access that allows third parties to integrate into the inner workings of Access the way I felt was necessary to build really seamless version control into the Access IDE.

In summer 2013 I put the development on hold as I could not find a way to properly integrate into the Access Navigation Pane. At the time none of my clients was planning to upgrade to Microsoft Access 2013, so I did not really feel the pain to push on and overcome those barriers. Furthermore several client projects were taking all of my time back then. And as time went on, the temporary hold for my SCC Project became a rather permanent one.

Continue reading part 2 of this series here.