This is an update on the current state of Ivercy. I share some things we worked on in the recent past, things we are working on right now, and a hint of future ideas. - And, there is a new beta version released.

Long time, no see

During the last months we received a couple of inquiries if Ivercy is still supported and actively maintained and when there will be a new version out and the likes.

I can understand the reasoning behind these questions very well. The last production version of Ivercy was only a small bug fix release after 18 months of no public release at all. This is a very long time and it might indicate that a software product is (nearly) dead.

The positive side to this is, there were no significant bugs found in Ivercy (except the one that triggered V1.2.5) that would have required a bug fix release.

While I’m very proud of there being no severe bugs in Ivercy, there are still lots of issues that definitely are annoying and should be improved. We voiced our dedication to improving things repeatedly but without committing to any date for a new and improved public release.

Beyond MSSCCI - Ivercy BETA Version 1.3.10 released!

Despite all the above, we have not been inactive regarding Ivercy in that long time.

It was a key feature and requirement for Ivercy from the start that we use Microsoft’s MSSCCI-API as a general interface to any version control software (in theory) instead of concrete implementation of the APIs of distinct systems.

This allowed us to support a variety of systems like Team Foundation Server, Visual SourceSafe, Sourcegear Vault, Source Anywhere, and several others with the same feature set as the Microsoft SCC-Plugin for Access right from the start. Even in hindsight, this was the right decision to get a broad basic support of a variety of common source code control systems.

However, there were a couple of drawbacks with that approach. There was no reliable Git-MSSCCI-Provider available while Git was gaining more and more attention in the developer community. The Subversion MSSCCI-Providers where somewhat lacking, particularly in performance, and finally Microsoft decided to remove the required components for their TFS-MSSCCI-Provider from current versions of Visual Studio/Team Explorer, forcing Ivercy users to additionally install VS/TE 2013 to get the Ivercy system requirements for TFS connectivity.

To address these issues, we underwent a major redesign in Ivercy’s core architecture to move from the close coupling to the MSSCCI-API to a more flexible and pluggable architecture. This has become available with the Ivercy Versions 1.3.* quite some time ago already, but it was only available to certain customers upon request.

Today we released Ivercy Version 1.3.10 as a beta release download to the general public.

This release is a bit of hybrid version regarding its beta state. If you use the MSSCCI-Connectivity to your SCC-Backend, this version should be production-ready, and you can use it with little concern. But keep in mind, that internally there were huge changes to the internal plugging and there is a possibility of bugs nobody has spotted yet.

Improved Subversion Support with SharpSVN

The first actual connectivity implementation using this new pluggable infrastructure is available now! It’s a new, optional connectivity to Subversion using SharpSVN (an open source library). This should significantly improve the performance of Ivercy with Subversion.

This is in use by select early adopters for quite some time and it should be reasonably stable for production use. – However, a wider user base will surely find some problems that where not noticed before.

Team Foundation Server connectivity without MSSCCI?

Microsoft has made it more complicate to use the MSSCCI-Provider for TFS, by removing the required base libraries from current versions of Visual Studio and TFS-Team-Explorer.

Despite Microsoft assuring us that they are dedicated to support the MSSCCI-Technology in the future, it’s still quite a nuisance to many of our customers and to us that they are making it harder to use it.

We are currently investigating options to connect Ivercy to Microsoft TFS without using the MSSCCI-API. – This is also something that has only become possible by switching to the new internal architecture.

Current shortcomings of the new Ivercy architecture

What is currently is lacking, is the appeal of some UI elements in that regard. With the MSSCCI-API it was the responsibility of the MSSCCI-Provider to also provide user dialogs to establish a connection, filter the history, display history-query-results, and so on.

When not using an existing MSSCCI-Provider to connect to a source code control backend, all those integrated UI elements are not available any more. So, we need to create our own. This is harder than you might think at first. Different version control systems use different concepts, different terminology and structure information quite differently. We need to account for that but still try to create universal interfaces to accommodate all different systems.

So, admitted, our user interface in the new SharpSVN-Subversion interface is very, very basic and provides only a minimal functionality while not looking pretty.

Further plans…

We’ve got many other ideas to extend and improve Ivercy. - One could be obvious, if you read between the lines of this text. – But still I refrain from committing to any schedule. Nevertheless, be assured that Ivercy will not only supported in its current state but also enhanced with new features in the future.

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.

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:

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.

General background

There are two undocumented functions in Microsoft Access, SaveAsText and LoadFromText. These functions were allegedly included in Access to support the Microsoft Source code control Add-In. They save an Access object (e.g. form, report, query, etc.) to a text source file or load/create such an object from a text file. These text files are ideal to store the definition and source code of an Access object in a version control system.

These functions are undocumented but still widely used, because they are very handy and the only build-in way to store the complete definition of a form or report outside the database in text format.

In Ivercy

Ivercy uses these functions internally not only to create the text files that will be passed to your source code control system as described above, but also as input to calculate the checksums for change detection in your database.

Last week I researched some problems reported by an Ivercy user, who has an unusual huge number of queries (~3,500!) in his database. In addition to a hard exception (fixed in the next release) related to this, I noticed that Ivercy has an abysmally slow performance in this scenario.

This certainly is an edge case, but still I wanted to investigate it and document my findings here.

Problem analysis

I had no plausible explanation for the observed behavior, so I created a test database to reproduce this scenario and gather some performance metrics with it. - The results where disturbing.

My test process was as follows.

1. Use the CreateQueryDef- / CreateForm-methods to create the desired number of objects (50, 100, 250, …)
2. Store current time in a variable
3. Export all those objects calling SaveAsText in a loop
4. Calculate execution time for the whole loop by comparing the stored time to current time

The more objects you are exporting from a database using SaveAsText, the longer it will take; common sense. With an increased number of objects, I expected a linear increase in execution time for the export. This expectation is represented by the grey line in the first chart. I had no expectation of the absolute execution times, so the grey line should be mainly seen as an expected trend.

First result

So here is a chart of the executions times I encountered in my test.

Let’s compare my expectations to the actual time for exporting forms with SaveAsText (orange line in the charts), you’ll see that the actual execution time follows my expected trend quite closely. So nothing unexpected there.

But now look at the executions time of SaveAsText for the queries (blue line). As soon as the number of queries surpasses 100, the execution time begins to increase significantly compared to the execution time exporting forms. With 500 queries it takes four times as long to export 500 queries compared to exporting 500 forms. With 750 objects, which is a huge but not unheard of number in a real world application, is almost ten times as long.

This is a really unpleasant result of this performance test.

Countercheck

To make sure this is not some kind of memory leak or whatever else strange problem just happening when calling SaveAsText so many times in a loop, I set up another test scenario.

The process for this test is basically like the one listed above. But, instead of exporting all objects, now I export a constant number (100) of forms / queries. Between test runs I still add more objects to the database. To be clear: Those additional objects are just in the database. They are not exported! I would have expected the execution times to be fairly constant, no matter how many objects there are in the database.

But, look what’s happening. Here is the chart for this scenario:

The export of the forms behaves as expected again, showing a constant execution time around the 1 second mark. So the export time of forms is independent of the total number of objects in the database.

But now look at the queries! Even though the number of exported queries is same in each run, the execution time increases with the number of objects in the database. So when exporting queries with SaveAsText, the execution time is massively dependent on the number of objects in the database. If you have a huge number of queries in your database, it does not only take longer because you need to export more queries, but also because each single call to SaveAsText needs significantly more time to execute.

Bottom line

First of all, please do keep in mind that this problem only affects a very specific scenario. – I work professionally with Microsoft Access for a very long time and I can’t remember to have worked on a database with more than 1000 queries ever.

This surely it is an explanation for the extremely poor performance of Ivercy is this very specific scenario.

As the root cause for this whole Phenomenon is the implementation of the SaveAsText function in Microsoft Access, there is not much we can do about this. It just makes it even more important to further optimize Ivercy’s internal functioning to call these functions as rarely as possible. - That’s what we will be trying to do.

The recently released Microsoft Access 2016 did not impress with many new features. Now Microsoft seems to be planning to step up the development of Access for the next release of Office.

They created the Access Suggestion Box forum on Uservoice to submit feedback to the Access product team.

Now some of the submitted feature requests are actually “Under review”. So Microsoft seems to be listening to what the Access community wants to be enhanced in Access.

It still remains to be seen, what (if anything) will actually be included in the next version of Access, but nevertheless this is an unprecedented opportunity for the Access community so submit feature requests for Access.

I suggest you don’t let this opportunity pass. Visit the feedback forum and submit your own idea or vote on ideas that would help you with your work in Access.