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

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.

Line chart of the execution time of SaveAsText for all objects in a database

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:

Line chart of the execution time of SaveAsText for 100 objects

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.

0 Comments

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.

0 Comments

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.

Philipp Stiefel speaking at the AEK 10

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.

0 Comments

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.

Screenshot of Ivercy in the Access Navigation Pane with enhanced SCC-status information iconsSee 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!