I will be speaking at this years AEK-18 (Access-Entwickler-Konferenz) about working with Microsoft Access and source code control. Of course I will be showing how to work seamlessly with source code control using Ivercy there. But the scope of the talk will be much broader and will deal with general aspects of source code control and Microsoft Access as well.

Me speaking at the AEK-10, 2007 (Picture courtesy of  Christoph Jüngling)

The AEK is a very informal and relaxed conference and features some of Europe’s best known technology experts for Microsoft Access. If you are working with Access and are based in central Europe you should definitely consider taking part. All the talks will be in German though.

The dates are: 

• Nuremberg, Germany – Sept. 19th/20th 2015
• Cologne, Germany – Oct. 03rd/04th 2015
• Hannover, Germany – Oct. 17th/18th 2015

For the complete agenda and other details visit the the official AEK website.

If you want to discuss any matter with me in person, don’t hesitate to approach me. Hope to see you there.

I’m excited about the latest new feature in Ivercy. I call it Ivercy Local Change Tracking (LCT) and it’s really cool. Let me tell you a bit about it.

The problem with change tracking of version controlled Access files

If you ever worked with the Microsoft Source Code Control Add-In in Access, you most certainly know the problem. Someone else in you team has changed a file (an object in Access) and you know nothing about it. Maybe the SCC-Add-In displayed the Checked-Out-Status for some time while you colleague was working on it, but it changed to Checked-In after a while and you do not know if your team member actually changed something or just undid his changes. If you actually noticed it and are interested, you could do a Diff of your local version against the repository and will see any changes, but that is a hassle. It is like flying blind.

It might get even worse. You absolutely know someone else has changed a file because they told you, or you even did it yourself on another computer. You select the form, report or query in Access in and hit the Get Latest Version button of the MS-SCC-Add-In. – And…

…it doesn’t get it. It just does nothing. You know the changes are there, but you just can’t get then. – Frustrating. The only thing that helps here, is deleting you local copy of the objects (forms, reports, etc.) , including it’s temporary file in the working directory and then get the latest version from the repository.

One of the underlying problems is, that you are not working directly with the source files, but with their representation in Access. Access changes the contents of the files while you are just importing them. So you are working on an incarnation of the file that might be different from it’s current source file, but these changes are irrelevant. If you actually modify the file yourself, the changes suddenly matter, but from the technical point of view, the MS-SCC-Add-In can not tell the difference.

Ivercy Local Change Tracking

The change tracking features of your SCC-Provider simply can not track changes to the objects in Access. They don’t see those objects there because they know only about the files.

So here comes Ivercy Local Change Tracking. Ivercy is a version control add-in that runs within Access. It’s Local Change Tracking feature calculates and tracks it’s own checksums not only of the files, as the the SCC-Client will do, but also of the objects within Access. This allows Ivercy to know which objects are still matching the source files in your local working folder and it is able to highlight those objects that don’t any more. It does not matter if you checked out an object or not, Ivercy shows you, if you modified the object. That is what matters much more than the former.

See this screenshot of the Access Navigation Pane. In addition to the usual icons for Checked-In, Checked-Out, etc. it has small informative icons that show you additional status information. The small asterisks indicate that you modified an object. Your local object is different from the version you pulled from the repository. And it does not matter if these changes are only in Access or already synced to the files in your local working folder.  Ivercy recognizes both types of changes and marks them for you. – This is great, isn’t it?

But wait, it get’s even better. The most annoying problem described above weren’t your own changes, but the changes of someone else. These changes have to be tracked by you SCC-Client as they are only available in the repository yet. But the good thing is: Ivercy visualizes these changes other team members did to you. See the red exclamation point next to some files SCC-Status-Icons? These indicate that there is a newer version of that object in the repository than you have got on your local computer. – So no more flying blind. You see what really goes on with file and Access-Object modifications.

And as an explicit side note to the Subversion users and all others who favor Edit-Merge-Commit: This is extremely valuable help in your style of working. You are not so dependent on the Check-Out/Check-In-Stuff any more if you want to be supported visually by your SCC-Solution.

This also proves some of the points I made recently in defense of the MSSCCI-API for the use with Edit-Merge-Commit.

Ivercy Local Change Tracking is not yet included in the current version (V0.9.11) available for download. It will be included in the next release of Ivercy.

This blog post describes only the most obvious benefits of Ivercy LCT, there is even more to it. Stay tuned for further updates.

Why don’t you sign up for the Ivercy newsletter, to be among the first to know? Or join our beta program to test Ivercy right now!

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.

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.

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.

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.

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.

By chance I came across this support page of Visual SVN during some research on Subversion. It tries to explain why VisualSVN is not a generic MSSCCI/SCC provider. I include a screenshot for your convenience.

After reading this, I have to comment on this a little. Not because I want to flame the VisualSVN-Team. They are doing a great job! But I think this a quite common view of the MSSCCI-API, which I encounter frequently when talking about it. - I think this view is wrong in several ways.

And while this view of MSSCCI was originally expressed in the context of Subversion, my comments are not at all limited to the Subversion context, but apply to all SCM systems that favor the Edit-Merge-Commit style of working.

BTW: MSSCCI is the abbreviation of Microsoft Source Code Control Interface.

OK, let’s look at those statements one by one…

· MSSCCI is dedicated to Visual SourceSafe and is not compatible with Subversion;

Well, what does “dedicated” mean here? Of course MSSCCI was designed with SourceSafe in mind and was certainly supposed to fully support and cater to Microsoft SourceSafe. But MSSCCI is certainly not dedicated to SourceSafe in a way that it is not suited to work with other systems.

Why do they claim it “is not compatible with Subversion”? That is certainly not true. The inner circle of SVN developers has chosen not to support MSSCCI, right. But MSSCCI is just a specification. If someone wants to implement it with a Subversion client he just can. There is nothing incompatible there. And there are actual implementations available at the moment, to prove this. Just a list of those I know of:

• Push OK SNVSCC - http://www.pushok.com/software/svn.html
• TamTam SVN SCC - http://www.daveswebsite.com/software/tamtamsvn/
• Agent SVN – SCC - www.zeusedit.com/agent/
• fire-development SVN-MSSCCI - http://www.fire-development.com/produkte/svn-msscci/
• Aigenta Unified SCC – Seems to be not existent any more.

· MSSCCI is dedicated to Checkout-Edit-Checkin approach, but Subversion is built for Edit-Merge-Commit;

“Dedicated” again. Yes, Checkout and Checkin are actual terms used in the specification. As I wrote above, MSSCCI was designed to support this well, but it is not dedicated to it in a way that would prevent you working Edit-Merge-Commit style.

You can just edit your local copy of a file without Checkout, there is nothing in the MSSCCI spec that prevents you doing this. – Visual SourceSafe will actually try to do so, but that is a different matter. (MSSCCI is an API-Specification, SourceSafe has (not is!) an implementation of the API.) And there are File-Status defined in the spec that support the edit-without-checkout approach well.

If you edit a file, you can merge it with the current version in the repository any time. It is the job of the actual client implementation to support this well. There is nothing in the MSSCCI-Spec that prevents any client from doing this.

When it comes to commit, there is actually a little difference there. When using a MSSCCI based implementation, the client would have to do a checkout followed immediately by a checkin of the modified file. That is not ideal as those are two independent operations that have to be coordinated on the client, so it is not atomic. If the client fails in between, it will leave the file checked out and unmodified. – But that is just in case something goes wrong unexpectedly.

· MSSCCI doesn't provide atomic transactions;

As said above regarding the “commit”. There actually is a weakness (you may call it flaw) in the Spec as it does not define an atomic way of combining several independent operations in a transaction.

The MSSCCI spec actually defines a way to group several independent operations together as “Batches”. (See SccBeginBatch / SccEndBatch). However these where not specifically meant to control transactions. As far as I know, most Development Environments that support MSSCCI do not support the Begin/End-Batch API at all.

So I have to admit, the spec makes it rather difficult to implement transactions covering multiple different SCC operations.

· MSSCCI provider can't show status of folders;

That is correct. - But is that a serious issue? I don’t think so! A folder is a container for individual files. I do not care much about the state of the folder. I’m just interested in the files. And any IDE could still display a folder status based on the status of the files it contains.

· only one MSSCCI provider is available at this time. There are use cases where Subversion and Visual SourceSafe controlled projects are used together in one solution.

Right, the MSSCCPRJ.SCC File, which stores the SCC bindings of a project according to the MSSCCI spec does not define a way to store which provider is used for this project. And I agree, there should have been a way to do this. Currently on Windows there is only one default MSSCCI provider defined in the registry. So most IDEs will only be able to use the default provider for all projects within one solution.

That is somewhat annoying. When creating entAscc, I wanted to work around that. Ivercy explicitly stores the provider to use for one project in it’s own configuration file for the project. That is absolutely possible while being MSSCCI compliant. – But it is not covered by the MSSCCI spec of course.

So my overall bottom line is:

There definitely are some aspects of the MSSCCI spec that are not ideal. Some things should have been specified in a more flexible and open way. But what I see as the core critique in those reasons for Visual SVN not supporting MSSCCI, being dedicated to SourceSafe and Checkin-Edit-Checkout and not being compatible with Subversion and Edit-Merge-Commit, is simply not a substantial case against MSSCCI.

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