How to choose a source code control system for Access development

by Philipp Stiefel, originally published April 10^th, 2016

Immediately after I wrote the first draft of my article on reasons to use source code control many months ago, I put an item on my to-do-list to write a guide on how to choose a source code control system for Access development work.

Recently someone on LinkedIn’s MS Access Development Professionals group asked for opinions on replacing Visual SourceSafe with Team Foundation Server for his Microsoft Access development process with a small team. Soon after I started writing an answer, it became apparent that now is the time to finally write a more substantial post on this topic, which is far beyond the scope of a LinkedIn comment.

The basis for this article is the switch from Visual SourceSafe to another system. Except for the part dealing specifically with VSS and the migration process, all the aspects covered here are equally valid, if you are planning your very start with version control.

Now I quote the question here to get started:

“Source Control for Microsoft Access Databases

I have been using Visual Source Safe to share development with a handful of other developers at a large client of mine for the past 8 or so years. Looking to migrate to something more modern and was thinking Team Foundation Server. Has anyone used that successfully with Microsoft Access in the past? Any gotchas?”

I think this question is incomplete. There is an essential part missing. Is there a concrete reason to switch source code control systems and are there any specific requirements?

And the standard disclaimer of financial investment products comes to mind. – To mine at least. “Past Performance is No Guarantee of Future Results.” – Read on, you will learn why this might be relevant in this context.

The issues with Visual SourceSafe

Visual SourceSafe had three main problems during most of its lifetime.

1. The repository database was prone to corruption frequently resulting in data loss. This is of course a serious issue for source code control system, which is meant to keep your code and its history safe. – This problem alone is a solid case against Visual SourceSafe.
2. Visual SourceSafe used file system based access to the repository for a long time. Every client had to access the repository database directly on file level. There was no server component coordinating concurrent read and write operations by different users. – I assume this to be the main reason for the database corruptions.
3. With bigger repositories and several concurrent users accessing the repository was very slow. Particularly with operation affecting many files (Like a branch / merge).

With Visual SourceSafe 2005, which was the final version of VSS, Microsoft introduced an optional sever process that was acting as a http-sever on the computer hosting the repository. This server process was then accessing the repository on behalf of the remote client.

This change with Visual SourceSafe 2005 was addressing problem no. 2 and thus mitigating problem 1. So the final version of VSS was not that bad any more, but the potential risk of data loss is still a strong argument for moving away from Visual SourceSafe, especially considering it is not supported and maintained anymore.

Looking for Alternatives

We established now that Visual SourceSafe has its shortcomings. Looking for alternatives is a rational thing to do. – But the person asking the question was working with VSS and it issues for 8 years until today and was not mentioning particular problems working with it.

So I wonder what is driving the desire for switching now? What are the requirements this person has for a source code control system? I can imagine quite a few, but without knowing it is really hard to suggest or even recommend alternative systems here.

In my opinion there are four main factors that should be considered when choosing a source code control system today.

1. Centralized or distributed?

For a very long time source code control was synonym with having a central repository to store the code and its history. Today this is not true anymore.

Centralized version control

With a centralized version control system every developer gets the latest version of the code from this central repository and checks-in/commits his changes directly into that repository. If there are several developers working on the same repository, everyone would be able to get the changes everybody else made, as soon as they are in the repository. This is a good thing, but it can be a bad thing as well. It makes it somewhat cumbersome to work on a feature in isolation. To do that you have got two options.

Option one is to not check in any changes until your feature is complete and ready to be integrated into the main development line. This is not ideal, as you would lose most of the benefits of source code control until you are ready to integrate your code.

The other option is to create a new branch of the code in the repository and work in this “private” branch until your feature is complete. After completion you would need to merge all the changes into your branch into the main code line. Depending on the system you use, the merge operation can be quite painful. – I haven’t used a SCC-System yet, where a merge was a pleasurable experience.

Distributed version control

In recent years a new paradigm of version control gets more and more popularity: Distributed version control (DVC). Other than with a central system there is not a single repository for everyone, but there is a whole repository locally on each developer’s computer.

With a DVC-System you can work locally on your own repository in complete isolation from the rest of your team. You can check-in/commit or branch or do whatever you like in the repository without affecting anyone else. Every operation on the repository will be very fast as there is no network and no concurrency involved. Most of the time you work completely independent of any external dependency, like a central SCC-Server.

You can pull (and automatically merge) the changes from any other repository into your local repository. Whenever your completed something and want to integrate it into the main line of the code, you push it to the designated main repository for your project.

I haven’t got any personal experience with distributed version control yet, so I will not go into any more detail here.

Choosing either one

I think distributed version control might be technically superior, but in my perception it has a pretty steep learning curve and is much harder to understand completely. Unless you have got specific requirements to favor distributed version control, you should probably use a centralized system.

Lacking first-hand experience, with DVCS, I am not really qualified to give advice on this choice. – So be all means please research this further yourself, if you are intrigued by my short introduction to this topic.

The most popular centralized source code control systems are Microsoft Team Foundation Server and Subversion.

The most popular DVCS are Git and Mercurial, with Git becoming the de-facto standard application for distributed version control. Git can even be used with Microsoft Team Foundation Server as source code control solution instead of the classic Team Foundation Version Control.

2. Migration of history

If you have used version control for some time already, as the person asking on LinkedIn has, you will have accumulated quite a bit of code history in your repository. This history is a valuable asset, as it may help you to understand how your project has “grown” to its current state.

Unless you want to start from scratch, it will be a very important factor in your decision, if it is possible to migrate the code history to the new system.

For Microsoft TFS there is a migration available tool to move an existing Visual SourceSafe repository to Team Foundation Server. The VSS to TFS migration process seems to be pretty straight forward. – I have never tried it myself though.

Another source code control system I’d like to mention here is Sourcegear Vault. This system was specifically designed to be a replacement for Visual SourceSafe. The user interface and the terminology used is very similar. So there is virtually no learning curve required to get up to speed. And of course there is a migration tool available to migrate your repository.

I migrated my own repositories using this tool. It takes a very long time to complete (depending on repository size) but was very easy to use and produced a flawless result.

There might be migration options available for other SCC systems, but I am not aware of them at the moment.

3. Access integration

This is a critical factor, obivously!

Source code control software is meant to control and version source code files. These are almost always simple text files. Unfortunately, Access is an exception to this rule. It stores all source code and the definition of your objects in a single binary file.

Of course you could just check in the Access database file into any source code control repository. – But it is extremely unwise to do so. As all code is in one file only, everybody working on the file would need exclusive access to the file for modifications, thus locking the file against other edits. Whenever anybody changed anything, he would need to check-in the whole file. On top of this clunky process, all the code would be stored in binary format which cannot be compared by conventional Diff-Tools. Merging any conflicting changes is completely impossible then.

So please just forget about this idea right now.

You need a tool that extracts each of the objects (queries, forms, reports, …) into individual text files. Ideally this tool will manage the communication with the source code control system as well, enabling you to control all common operation directly from within Access.

Add-Ins for SCC-Integration into Microsoft Access

There are a couple of tools available that are, more or less, capable of doing this.

Microsoft Access Source Code Control

Microsoft provided an add-in for Microsoft Access to integrate source code control. There were distinct versions available starting with Access 97 to Access 2010.

If you use Visual SourceSafe for version control, you are probably using this tool at the moment. This add-in is sometimes referred to as SourceSafe integration add-in, but it is not limited to Visual SourceSafe as it uses the MSSCCI-API to interact with the SCC-System. Many source code control systems have got a client that implement this API. Not all of them work well with this Access add-in though, as the Access integration scenario is not the default use case of that API and vendors usually do not test it specifically. - Surprisingly this applies to Microsoft as well. If you try to use this add-in with the latest release of the TFS-2013-MSSCCI-Provider, you might find you are not able to create a local copy of your database from the TFS repository any more.

Microsoft discontinued this add with Access 2013. Previous versions of the add-in do not work in Access 2013 and Access 2016 anymore.

Ivercy

(Disclosure: I am the owner of Ivercy. It is a commercial product.)

I developed this add-in as replacement for the discontinued Microsoft Access Source Code Control Add-In. It integrates the same commands (Ribbon and context menu) with the same nomenclature into Access, so if you are used to Microsoft’s tool, you will feel instantly “at home”.

Other than the Microsoft Add-In, Ivercy does not only integrate the SCC-API-Commands into Access, but has some enhanced features to deal with some of the peculiarities of how Access development works.

Ivercy uses the MSSCCI-API as well to communicate with the source code control client, so it supports an equally large number of SCC-Systems. As it has some configurable compatibility settings, it even supports some SCC-Clients that do not work with Microsoft’s add-in.

Ivercy is a fairly new product (V1.0 released in September 2015) so there are some things, which are not as smooth yet as they should be. With the most recent release (in public beta at the time of this writing) Ivercy should be as fast as or faster than Microsoft’s add-in with all common operations. It already has several useful features the Microsoft tool has not and you can expect further enhancements in the future.

OASIS

OASIS is another commercial product to integrate version control into Microsoft Access. It is primarily built for the integration of Subversion, but as far as I know, other SCC-Systems can be used as well, if they come with a command line client.

While OASIS integrates the necessary command for version control into Access, it does not supply rich SCC-Status information in the Access GUI. However, with Edit-Merge-Commit style of working, which is preferred by many Subversion users, this is information is not as critical as it is with the stricter CheckOut-Edit-CheckIn-workflow encouraged by the MSSCCI-API in general and Visual SourceSafe in particular.

One noteworthy feature of OASIS is the export of local Access tables in text format. This a feature that neither the Microsoft Add-In nor Ivercy have at the moment.

msaccess-vcs-integration

This an active open source project hosted on GitHub that provides source code control integration for any source code control system with a command line client. The developers particularly focus on supporting the free SCCS-Systems Subversion, Git and Mercurial.

MSAccess SVN

This is an open source project dedicated to creating a source code control Add-In for Access specific for Subversion. The available releases seem to have a basic functionality implemented and received some good reviews. Unfortunately, this projects seems not to be active at the moment.

Roll your own

The core functionality need for using version control in your Access development project are built right into Access already. The hidden SaveAsText- and LoadFromText-Methods of the Access allow you store Access objects (forms, reports, …) as text files on disk. You can then use VBA to pass these files to a command line client of your SCC-System or manually manage them with its GUI-Client.

A good starting point for writing your own code for exporting/importing the access objects to individual files is Arvin Meyer’s DocDatabase VBA Module.

4. Beyond source code control

Today source code control is only one part of the larger concept of application lifecycle management (ALM). ALM combines requirement engineering, work item tracking, source code control and build/release management into one wholly integrated software project management process.

Team Foundation Server as well as Sourcegear Vault have features beyond source code control to support the other aspects of ALM as well. Subversion has an open architecture that supports adding third party systems that can provide additional features. The Mantis BugTracker is an example for such an additional system, which can be integrated with Subversion to handle work item tracking.

5. Other factors

This guide does not claim completeness. There might be other factors that are important to you.

OS Platform support

Some source code control system can be installed on various operating system platforms. E.g. Subversion and Git run on Windows as well as Unix/Linux based systems. Others like Sourcegear Vault and Team Foundation Server are bound to specifically to the Windows platform. – As Access on your client PC requires Windows anyway, only the server components have to be considered in this context.

Price point and maintenance

The different systems come with very different licensing models. Licensing costs for SCC/ALM-Software can be quite substantial or nothing at all, if you use a free/open source system. I will not try to compare the advantages and disadvantages of the individual licensing models. Just keep in mind how assuring it is, to be able to contact competent customer support if you encounter a problem with the product you are using.

Conclusion

I don’t claim to cover every single aspect in the process of choosing a source code control solution for your Access development projects here. Rather I hope I was able to advise you what most of the key factors in your decision process should be. And do not forget any other factor that might be totally specific to your individual project!

The source code control systems I mentioned above are only the most popular systems around today. There are more. And you should not limit your choice to them. I do encourage you to research what else is available out there. If you check for the factors covered above, you will be able to quickly determine if any particular system is a good fit for your needs.

The definitive answer to the question is not here. It can’t be. You will have to find it yourself considering your own preferences and requirements. – If you should encounter any further question in your context that wasn’t covered here, but should be, don’t hesitate to send me an email.

Share this article: