0 Comments

Access projects come in manifold different types, sizes and environments, so it is probably not appropriate to recommend the single one best practice.

I will rather describe what we are using in our Access developments projects.

Some of our practices can probably be taken as solid, general recommendations, while others are trade-offs and compromises to the particular setup of the project structure. - I will remark on that, while we go along.

Terminology

The terminology used in this text should be fairly common, so I’m going to explain it rather briefly, only to be sure we are all on the same page.

DEV - Development environment

The development environment is dedicated to development only, completely separated from the production environment.

Local Access development is completely isolated for each developer by design with Ivercy. - That is the way it has to be, with or without source code control.

(SQL-)Server-side development might be in a shared environment, where multiple developers use the same database in the same SQL Server. While this might have slight advantages regarding the maintenance effort, I would strongly advise against it. The advantages are completely outweighed by the disadvantage of one developer breaking the others environment with unfinished, non-working changes.

QA- / Test-Environment

An environment for testing and QA. Depending on the nature of the testing (automated or manual) you might need several different test environments.

We usually do not use a dedicated testing environment at all. The first level of tests is done on local development machines. These local tests are either Unit Tests, Coded-UI-Test (rarely with Access applications) or manual, explorative testing.

I’d love to have an automated process and environment for automated testing. - Unfortunately testing Access applications automatically is not an easy process. So, for now, we still use the above approach with a significant amount of manual user interaction.

The second level of manual tests, mainly done by testers and/or users of the client, is usually done in the staging environment.

Staging Environment

Staging is a dedicated environment to showcase and test new enhancements to an application, including the upgrade or migration process, before it is put in production.

This environment should be matching the production environment as closely as possible in regard to operating system, installed libraries and other components.

In our projects we usually use the production infrastructure but isolate files to dedicated folders and use a copy of the production database on the production SQL server.

There might be legal or technical requirements for your project, which require total isolation from production by independent infrastructure. For this a completely virtualized environment is probably the best solution.

With the absence of a dedicated test environment in our processes, we sometimes do deploy new versions to the staging environment for user acceptance testing without the intention to ever deploy them to production.

PROD - Production environment

The production environment is simply where the live application is running. Depending on the strictness of the organization, you might not even have direct access to this environment.

Our infrastructure

In most projects, we work in distributed teams of 2 - 3 developers. For that reason, every developer has his own, isolated development environment.

Synchronization between different developers happens via our central Sourcegear Vault source code control server.

Database server objects are stored as SQL Script and need to be executed against each local development server to bring it up to date.

Access Frontend

For DEV, each of our developers has a local copy of the Access file on his local computer. If and when they update that file is totally up to them.

Obviously, it is sensible to regularly get the latest changes from the repository to the local copy of the file. This should be done the latest, when a new designated version is created and released to QA and/or Production. But whether this means to only get the latest version of each file into the existing local copy of the database or building a completely new the database file from the repository is up to the developer and depends on the state of the local file.

Now, for QA and/or PROD, it is quite important that the whole Access application can be created from the repository alone, with no local files of any kind required. This ensures each and every change to required files, settings and components is traceable in the version history. Only external, third party components like database drivers or object libraries that are not deployed with our application are excluded from this. - Still, it is sensible to document the used files/components and tested version numbers of those.

We use a build script to build the whole local Access frontend file for a new version from the repository. It usually includes compiling the file to AccDE/ADE format.

This process does not require any human interaction except starting it and even that could be automated easily if required (think: Continuous Integration). Manually creating a new file version for release has been proven to be error prone and resulted in significant misconfigurations deployed to Staging or PROD several times in the past, even when there was a manual/checklist to guide through the process.

Whether the new frontend file is connected to the production environment or Staging-/QA- Environment depends on it configuration, either in a local configuration file or configuration table inside the Access frontend. It does not require any changes to the code of the application.

Our build process is run only once to create a new version. One copy of the created file is copied into the Staging/QA-Environment for testing. If satisfactory functionality was confirmed during QA, another copy of the created file is deployed to production. This is essentially the same file, only configuration values differ.

Never succumb to the temptation to apply any last-minute fixes to the already created output files outside of the source code control process. This creates an untraceable difference between the actual production version and the corresponding version in the repository. The result may be unreproducible bugs in production and/or DEV and may cost huge amounts of time and effort to analyze.

Backend Database

All the above applies to the Access frontend file only. Managing the backed database is mainly outside of scope for Ivercy. So, I’ll keep my thoughts on this rather brief.

Server DBMS Backends

We do not work with Access (JET/ACE) backend databases, but mainly with Microsoft SQL-Server, rarely with Oracle and MySQL, backends for our projects.

All procedural and logical objects on top of the data (View, Stored Procedures, Function, Trigger, etc.) are stored as repeatable script files. Meaning, all scripts creating/altering these can be executed repeatedly without causing any damage. So, even if there are interdependencies between multiple script files, just running all of them repeatedly until there are no more errors will bring the database to the most recent state, as a brute-force approach.

All structural changes to tables and their data are stored in SQL Scripts that can be applied to the backend database to bring it from one version to the next. - Currently executing these scripts is a manual process in our projects. Manual processes are bad! - Nevertheless, I haven’t found a lightweight process that is solid enough to handle the upgrade of the production database and still is flexible enough to be applied to different development and testing databases as well.

Access Backends

As we don’t use them, I feel hard pressed to give solid advice here.

You could still use SQL scripts to do most of the migration work. Ideally you would write a small client utility that executes your scripts against the Access database.

Alternatively you could use a VB-Script (or Access Application/VBA) that does the required modifications using either the DAO- or ADOX-Object-Libraries. - Unfortunately there are some aspects of Access databases that can only be managed with one but not the other (and vice versa).

Either way, two basic rules still apply:

  1. You automate as much of the process as possible.
  2. You should include the scripts for the modification process in your source code control repository as well.

0 Comments

In June 2017 the public downloads for TFS Team Explorer 2012 and 2013 were removed from the Microsoft website. The links we previously included in our Getting-Started-Guide for Ivercy and TFS suddenly were only leading to a page saying: “We're sorry, this download is no longer available.”

Team Explorer is a mandatory system requirement for the TFS-MSSCCI-Provider, which Ivercy needs to connect to Team Foundation Server. So, this is a major change for all Ivercy customers who are using Microsoft Team Foundation Server as their source code control solution.

I assumed these public downloads were removed in error and contacted Microsoft Support about this issue.

In the email conversation with the very helpful support technician I was able to establish several facts.

  • The public Team Explorer downloads were removed intentionally, though the rationale behind that change were not disclosed to me. The TFS-Team at Microsoft is aware of the fact that this makes it more difficult to obtain a working installation of the MSSCCI-Provider for TFS.

  • Team Explorer 2013 is still available for free by download from the Microsoft website. However, you now need a free Visual Studio Dev Essentials account to download Team Explorer. (It is available via MSDN-Subscription as well.)

    If you are logged into your Dev Essentials or MSDN account you should find Team Explorer 2013 in the download search results by using this link:  https://my.visualstudio.com/Downloads?q=visual%20studio%202013%20team%20explorer

  • Even though it is now slightly harder to get the prerequisites, the TFS-Team at Microsoft will continue support for the TFS-MSSCCI-Provider in the future.

Visual Studio Dev Essentials

The bottom line is, there is nothing serious to worry about. The Visual Studio Dev Essentials account provides many free benefits for developers, like free developer tools, free subscriptions to training and credit for Azure services. It is a sensible decision for any developer on the Windows Plattform to get a Dev Essentials account. (Unless you got a MSDN subscription anyway.)

However, once again Microsoft completely failed to communicate this change in tool availability to the developer community. It would have been easy to include a short text on the “download is no longer available”-pages, explaining how to get Team Explorer from now on. This would have been the sensible thing to do. It would have saved many of developers from the tedious search for alternative download locations and would have prevented multiple unnecessary support cases.

0 Comments

Anders Ebro, TheSmileyCoder, was so kind to invite me to do an online talk about source code control for Microsoft Access and a presentation of Ivercy for a virtual/online Access User Group he is in.

This event took place yesterday and I am amazed by all those excellent and insightful comments, questions, and feedback I received during the presentation. Thanks a lot guys!

Now, I uploaded my PowerPoint slides to Slideshare for the general public to view. Here it is:

Source Code Control for Microsoft Access Developers from Philipp Stiefel

Of course, browsing through the slides is only of limited value without my explanations accompanying them. Still, I think there is some valuable information on the benefits of source code control and how the integration into Microsoft Access works in there.

0 Comments

This article deals with the topic of introducing your clients to the use of source code control in the software development process from the viewpoint of a small software consulting company.

I recently participated in a very interesting discussion on the Utter Access Forums. Originally it was about the rate of comments in source code but soon digressed into related topics as code quality and using source code control for Access development.

UA-User GroverParkGeorge replied with some very interesting comments to my deliberations that source code control voids several arguments for adding comments to the code. Here are the most relevant excerpts:

“If YOUR consulting business includes projects for large clients, or for your own organization, all of which have a budget for all of the tools you need, and all of the documentation you want, then it would be reasonable to obtain and use things like Source Control tools.

If you are taking on a $5,000 project with an existing Access application for a three person customer shop, it's hard to insist that they should provide those tools for you before you take the project.” (full post on UA)

“I've discussed various approaches to Source Control with clients over the years. Most of them who are not deeply involved in software development themselves tend to be reluctant to delve into it, though. And that's just a fact of life with smaller consulting clients.” (full post on UA)

George, thank you very much for these interesting comments. Looking at your objections per se they are sensible, valid, and a strong argument against SCC in smaller projects.

However, I use a totally different approach to introduce source code control at my clients and, if I haven’t misunderstood the objections, this approach might weaken their argument.

Using SCC in client projects

I do not talk to clients about source code control at all. - Unless they got developers employed themselves, who are going to work with me on the project at hand (more on that later).

I’ve got all the required tools licensed to my name. So I do not need to discuss licensing with the client. If I work on project from my office then everything is settled already now. The client will get the completed software delivered. He will never be in contact with SCC in any case.

It’s a bit different if I’m required to work at the client’s location, but not too much. Most modern source code control systems can have their backend installed in the cloud. So there is no need for installation on the client’s site. I only request permission to install local/client tools on their dev computer and to access my (SCC)-server via the internet. - BTW, this is not limited to SCC but includes other tools as well. - If they would refuse that, I would actually consider to decline working with them. - However, no client has ever done this yet.

Of course, I assure the client that all tools I’m going to use at their office, are properly licensed to my name and that I’m legally allowed to use them in this project.

(Side note to Ivercy customers: This is perfectly ok with Ivercy licensing. Ivercy is licensed per developer. If the developer is covered by a valid license, we do not care about where the developer is located and on whose computer he is working on.)

Now, for the case that the client has got his own developers working on the project as well. In that case, I cover all the licensing for the required tools as well. If it is a big (read: lucrative) project compared to number of developers, I just absorb the cost.

Otherwise I include the cost for that in my budget for the project. As my providing of the required licenses is only temporary, for the duration of the project, the costs are usually tiny compared to the total costs of the project. There was never any discussion of costs in this regard.

In this situation, it is somewhat more difficult to convince the client’s project management to use my (SCC) infrastructure, as their project is somewhat dependant on it. - I convinced most clients pointing out that they haven’t got much to lose. They can continue to work without SCC at any time. They only would have to come up with some other means of synchronizing the collaboration between all developers of the project. - Which is a massive pain of course, but one they would have right from the start if they do not want to use SCC for their project.

This has been declined by a couple of clients over the years. - All of those opted to purchase, install, and use their own SCC-Infrastructure instead of mine for their project. Which is all the better, as I’m not responsible for it then. J

The most difficult part is to convince other developers to use SCC in a project if they haven’t done so before. The argument of the collaboration issues is usually convincing enough, even in this case.

If it should be not sufficiently convincing, I just need to remember a past project.

15 years ago I worked on a pretty large Access project together with up to 4 other developers without using SCC. Synchronizing all developer’s changes to a coherent, working release for the end user was a massive pain each and every time. It frequently caused problems with the release, because we missed some “minor change” from someone when integrating all changes into the new release.

More than once we missed the resulting bug during testing - No automated unit- or regression-tests back then either. Naturally, sooner or later the bug was discovered with the application in production. It was then causing significant additional costs due to the unplanned effort required to analyze and fix the issue and finally roll-out a bug-fix production release.

If some (potential!) client really wants to go down this road, he has to do so without me. I will happily walk away from projects like these.

The financial cost of using source code control

At the beginning of this text I wrote, I got all tools required for SCC licensed to my name. Let’s look closer on that side of the matter now.

We use Sourcegear Vault for version control internally. Vault is not free. (Unless you are a single developer.) We used the Microsoft source code control plug-in for Access integration until 2015, but this is only supported up to Access 2010, so it is of limited use today.

Of course, we now use Ivercy in our own projects for SCC-Integration into Access. Ivercy is a commercial product. For the sake of discussing the costs, I just include the regular price for an Ivercy-5-User-License.

I’ll break down and compare the cost of using either Vault or Microsoft Team Foundation Server Express Edition, which is free but limited to a maximum of 5 users.

  sg Vault TFS Express
Initial licensing cost for SCC-System (5 user) $1.710,10 $0,00
Initial Licensing cost for Ivercy (5 user, incl. 1yr support) $450,00 $450,00
Total initial cost $2.160,10 $450,00
Optional - SCC-System updates&support (for 10 yrs) $3.000,00 $0,00
Optional - Ivercy updates&support (for 9 yrs) $1.260,00 $1.260,00
Total cost after 10 years with full maintenance $6.420,10 $1.710,00
     
~ Cost per year (5 user) $642,01 $171,00
~ Cost per month (5 user) $53,50 $14,25
~ Cost per month per user $10,70 $2,85

 

Side notes:

All prices are approximate. Not included are the related costs for hardware and operation system, their maintenance, and internet connectivity.

In this comparison, the price is obviously in strong favor of TFS-Express. But Sourcegear Vault is an excellent system and in my opinion easier to use. This is particularly true if you used Visual SourceSafe in the past. So, there is even a point to spending the money for 5 users or less. But, assuming your requirements exceed 5 users at some point in time. With Vault you would just buy another license. With TFS however, you cannot use the Express Edition anymore and would need to buy the appropriate number of TFS licenses.

Conclusion

Of course, the initial cost of introducing source code control for the first time is considerable for any small business. If you only count it against the revenue from the very first project you plan to use it on, the cost might be prohibitively high at first glance.

But that is only part of the picture. If you are a software development shop, you will use source code control over and over again on many projects. Calculated over a long time (10 years in the above example) the cost will become almost negligible.

0 Comments

New Ivercy version 1.2.2

In the recent newsletter for version 1.2 I wrote: “we are planning to go back to more frequent, smaller releases”. When I wrote that, I had no idea how soon the next release would emerge.

It’s actually out now! – Just two weeks later.

We deliberately dropped some minor features from the last release due to time constraints. I didn’t want to defer that long overdue release any further. Still, I wanted to roll out those additions as soon as possible.

Additionally, there was a bug reported for v1.2 that was pretty annoying – though not critical.

These facts added up, made a compelling reason to release another version of Ivercy so soon.

Enhancements to the Object List Dialog

The main improvement in this new version is several small enhancements to the Object List dialog.

I was working on-site at a customer this week. Due to administrative constraints, he had the newest version not installed yet. Working with the previous version of Ivercy, I dearly missed the new status filter options in that dialog. – So, these changes might be only small, but they are incredibly helpful.

Status filter options

We removed some of the status filter items from the drop down that were not particularly useful und probably confused some of you more than they helped anyone.

The status filter values Modified (Local-SCC) and Modified (Local-Access only) were merged into the general Modified (Local) value.

Finally, we added the new, virtual status “New in repository” which is very helpful if you want to get new objects from the repository, which were added by someone else working on the project.

If you actually want to continue to use the more fine-grained old status filter options, you can set the UseSimplifiedSccStatusFilter-option to Yes.

Ivercy Objects List with highlighted enhacements in v1.2.2

Selected and problematic object counts

The Object List dialog shows the count of selected objects and the exact count of problematic objects with possible conflicts between the local and the repository version.

Differences Button

The Differences button in the Object List is now active and working. It enables you to invoke your Diff-Tool right from that dialog for the currently selected object. This is very handy if you want to take a quick look at the differences between two versions before deciding how to retrieve the latest version.

Support for integration into Japanese localized Access

This new version finally supports integration into the Japanese localized version of Microsoft Access. This was requested by several of you. Unfortunately, general support for a Japanese language environment is still not really there due to limitations in the MSSCCI-API-Definitions.

Bug fix – Create AccDE/MDE

When creating an AccDE/MDE from a controlled database with Ivercy 1.2 you would get an error message saying “The expression you entered refers to an object that is closed or doesn’t exist.”. – This error does not do any harm; everything still works as intended. But it is very annoying if you frequently create an AccDE/MDE from your project.

This error is fixed in the current release.

Improved error handling and logging

When Ivercy was exporting or importing the objects from Access some of you experienced error messages like “The search key was not found in any record” or “No current record”.

These error messages are caused by Access itself failing to import or export the object. The cause is most certainly some sort of corruption that object. We are still investigating these issues and will publish an FAQ article on this topic as soon as we know how to solve these issues.

In this new version of Ivercy, the error messages regarding such issues will display the Access object name that caused this error. – This should at least help to get to bottom of the issues.

Conclusion

Let’s wrap this up here. These are all rather small changes, but they are so useful that I absolutely recommend to download and upgrade to this new version!