0 Comments

Note: Japanese is the most relevant Asian language for us, as we had multiple request from developers in Japan, who would like to use Ivercy, but can’t for the reasons described in this post. So I frequently refer to Japanese in this post, but the issues are the same for any Asian language!

The last couple of days I struggled with supporting the Japanese Language in Ivercy. Today I have to admit defeat. - And I’m not doing this lightly.

There are two problems with using Ivercy in a Japanese language environment. As you probably are a software developer yourself and maybe interested in that, I’m going to describe the issue with quite a bit of technical detail. If you are not interested in that kind of detail, skip right to the bottom line.

The UI-Problem

The integration into Access is partially dependent on the UI-Language selected in Office. So Ivercy needs to know and understand the Japanese terms for Query (???), Form (????), Report (????) and so on. This is required for the UI-Integration into the Navigation Pane.

Access and the .net Framework, which Ivercy is implemented in, use Unicode Strings. So any character set can be represented in a simple string. So, this first issue can (and is) solved by simply adding a translation table between the Japanese terms and their English counterparts. This solves the UI-Integration issue and will be included in the next release of Ivercy (version 1.2.+x) and will be available soon.

The MSSCCI-API- Problem

The second issue with supporting Japanese is the MSSCCI-API. Unlike any Win32-API, the MSSCCI-API has no Unicode (…W) declaration/implementation of its functions. The functions are only available in an ANSI implementation, which uses the LPCSTR-Type or an array of LPCSTRs for character data.

Codepages and character encoding

With Western languages that can be represented by the Windows Codepage CP1252, it is fairly easy to pass data to the functions of the API. You simply declare the LPCSTR parameters as Strings and you are good to go. The .net runtime will sort out the marshalling of the data automatically.

Now, if you have got a file name (derived from an object name in Access) that consists of Japanese Characters, it will be automatically mapped using Codepage 1252. The result will be something like this: “????????????????”. - The question mark replaces any character that cannot be properly mapped. As CP1252 was never intended to represent Asian Characters, none of them can be mapped and the resulting data is completely unusable.

Now, the .net Framework supports all Windows Character Encodings, so it is possible to convert the Strings using the appropriate Codepage for Japanese (CP932). The result is a byte-array containing the string’s representation in the correct Codepage. – So far, so good.

Marshalling in the .net runtime

The conversion to the correct Codepage leaves us with an array of bytes that represent the Japanese character string

Now, for simple LPCSTR parameters it is pretty straight forward to implement. You would use an Byte() type in the declaration of the function and just pass our array of property encoded bytes to that. – Done.

Unfortunately, it is not that simple. Most of the functions in the MSSCCI-API do not use just simple LPCSTRs, but arrays of LPCSTRs. They would translate to an array of byte-arrays in our declaration – Byte()().

Other than a String or a simple byte array, the .net runtime cannot automatically marshal nested /jagged arrays. It should be possible to write a CustomMarshaler that would handle the marshalling of these types. There are actually some samples around on the internet. But I spend quite a bit of time trying to implement a CustomMarshaler for our jagged byte arras in Ivercy and simply could not get it to work. :-(

As I’m the developer with the most experience on WinApi and P/Invoke on our team, this is a pretty disappointing result.

Bottom line

You can use Ivercy in a Japanese edition of Microsoft Access and the icons and integration will work with the next version of Ivercy after 1.2.

But, you can only and strictly use plain English (western) characters in your object names in Access. As soon as there is any object in your Access database that contains a Japanese (or any Asian) Character, there will be exceptions all over the place and it will not be possible to work with Ivercy in that database.

We’re truly sorry about that. We might come back to this topic and try to address and fix the issues at a later time.

If you are an expert with Interop and P/Invoke in the .net environment and think you are able and willing to help with this, please get in touch. And we don’t expect your help for free.

2 Comments

I’m glad I can finally begin to write this. It has been 6 months since the last (public) release of Ivercy. That is a long time. Much too long.

While we never really stopped to work on Ivercy, for a multitude of reasons, we never made it all the way to a public release. - Until now.

During the last couple of weeks, we got several inquiries, if Ivercy is still actively developed. Yes, it is! And here’s the proof. – However, if you were waiting for some of the functional enhancements we have got planned, you might still be a bit disappointed.

Infrastructure

The overbearing topic of this release is infrastructure.

Bridge in a huge wood (Photo byGreg Rakozy, used here under CC0 licensing)

Soon after the last public release of Ivercy, back in April, we were getting more and more requests for things like supporting Access 64 bit, the .net Framework 4.0 and improvement of the installation options and experience in a restrictive corporate environment.

While the necessary changes to Ivercy itself were not that difficult to implement, and in fact are available via non-public releases to some of our customers for some time, the overall experience was ruined by the shortcomings of our installer. To overcome these limitations was one of the key issues holding this release back for so long.

.net Framework 4 support

Ivercy is built against the .net Framework 2.0, which was the basis of everything .net out there for quite a while. With Windows 10 (and 8) by default only the newer .net Framework 4.0 is installed. On these operating systems, you had to explicitly add the 2.0 Framework to use Ivercy.

This new release is still built against the .net Fx 2.0, but it works with the .net Fx 4.0 as well. During installation, our setup will detect whether you have got 2.0 or 4.0 installed and will configure Ivercy accordingly.

Ivercy running with .Net 4.0 runtime

Ivercy itself work flawlessly with .net 4.0, but during our testing we discovered that some 3rd party components do not. For example, the MSSCCI-Provider for older versions of Sourcegear Vault will crash instantly if invoked from .net 4.0. For that reason, we play it safe. If both versions of the .net Fx are present, version 2.0 will take precedence.

Microsoft Access 64 Bit Support

I was surprised that we got quite a few requests to support Access x64. So we enhanced Ivercy to accommodate this requirement. Thanks to the .net Framework we were able to make Ivercy dual platform instead of building two separate versions of Ivercy. There is just one binary assembly running on both platforms. Only one small satellite component is actually platform dependent, so we deploy two versions of that and load the correct one at runtime.

Ivercy in Microsoft Access x64

Once again the installer makes things more difficult than necessary. An MSI-Package can either be compiled for 32bit or for 64bit, but not both. Considering that more than 95% of our users only need 32bit support, we do not provide a dedicated 64bit installer (yet).

If you want to use Ivercy in Access 64 bit, just install the 32bit version. After installation please run the batch script Ivercy_install_registry_x64.cmd in the folder Ivercy was installed in. This will enable Ivercy in the 64bit version of Access.

Before you get too excited about using 64Bit-Access, please remember: Ivercy requires an MSSCCI-Provider for your source code control system. This provider needs to be available for 64Bit as well! Most vendors of source code control systems do not offer a 64Bit-Provider yet.

Of the primarily supported SCC-Systems only Microsoft has got a 64bit-MSSCCI-Provider for TFS.

Improved Setup/Installer

We originally used a Visual Studio Installer project to create the MSI-Setup for Ivercy. That was a very simple solution that worked. – Sort of.

While it was easy to implement, it had lots of drawbacks. To name just two:

  • The setup required administrative permissions.
  • It would only work for the user actually running the setup.

These two combined were a major a nuisance, as each user that should have Ivercy available, would need administrator permissions during setup (at least temporary).

To get rid of these nuisances, we built an all new installer package for Ivercy. It will run with user permissions only, but still can be optionally run with admin permissions to install for all users on a shared computer or terminal server environment.

There will be more detailed documentation dedicated to sysadmins available soon. If you just want to install Ivercy for yourself on your computer, choose the “per user” installation and you are good to go.

Important bug fix for Get Latest Version

The new version does include several bug fixes. While I would consider most of the bugs fairly minor, there was one actually that was nasty.

I hate to lose work already done! So I am profoundly sorry that some of you experienced exactly this due to a misbehavior of Ivercy. – My apologies!

There were two situations where this could happen.

If you checked out an Access Object, which was already modified locally before checkout, and you activated the “Get Latest Version”-option in the checkout dialog, then Ivercy did not warn you about losing your local changes.

If you explicitly invoked the “Get Latest Version” command, it was possible to select Access Objects that were already checked out (and potentially modified) for the Get-Latest-Operation. If you continued any local changes were overwritten.

Both incarnations of this bug are fixed in the new version.

First and foremost, Ivercy now displays a highlighted warning message, if you are about to overwrite local changes with the repository version of an object.

Ivercy Object List displaying a warning message

Second, you can configure Ivercy to always exclude objects that are already checked out from the Get-Latest-Command. Use the ExcludeCheckedoutFromGetLatest-Option to control this behavior. Please be aware that this is enabled by default!

Other features

There are some other improvements, such as further reduced startup time, clarified configuration dialog showing the current configuration, the template configuration and global options (new) in a single tabbed form. As well as several other bug fixes.

So go to our download page and “check out” the new version and let me know what you think. – We appreciate any feedback!

1 Comments

Today I did a first time ever test of Ivercy with Dynamsoft SourceAnywhere and did a screen recording of it. We do not officially support SourceAnywhere yet. Still I wanted to check if there are any serious problems visible at first glance.

The test went very well. Except for a minor issue with the SCC status not being refreshed immediately after creating the Access database from the repository, everything was working perfectly fine.

This is my very first published screen recording ever. It was done unprepared and with hardly any script at all. So there are few Uhs and Ahs in there and the content could (should!) have been more concise. – Nevertheless, I think the resulting video helps getting an impression of Ivercy in action and of some simple steps in the source code control workflow.

So I published it. You can watch the video here.

I’ll probably do some more screen recordings of Ivercy in the future. What do you think?

0 Comments

Here is last Friday's newsletter. It was only sent to subscribers who indicated interest in beta releases.

Here we go:

New beta version out

We released the new beta version 1.1.04 yesterday and I’m really excited about it!

This was originally planned as bug fix release only. Some of those bug fixes were addressing issues when working with super huge databases (think 1000++ objects). While working on those bugs, we realized that only fixing the hard errors wouldn’t help much. The dreaded delay when opening a database would have made it still impossible to work with databases of that size.

The slow opening of databases has been reported frequently as the most critical pain-point with Ivercy. So we put some extra effort into finally addressing this issue. – I think we succeeded with it!

So if you want to try the new version, you can get if from our download page. (Scroll down for the beta versions.)

Please keep in mind: This is a beta release for now!

Reasons to use source code control

A small side note here: Last week I published the article 7 reasons to use source code control for Microsoft Access development on my private website.

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.