Ivercy

Ivercy Query Serializer

Up until now it was very tedious to use Ivercy with very large Access databases, at least if the database contained a huge number (> 1000) of queries. A main reason for this is a problem with the LoadFromText/SaveAsText methods in Access that we use to write the source code of the Access objects to text files.

As we analyzed some time ago, the LoadFromText/SaveAsText methods suffer from a massive performance degradation when there is a huge number of queries in the database. With Ivercy version 1.4.0 (released as beta version on 2021-05-10!) we were able to significantly improve the performance by creating our own query serializer to write and read Access queries to/from text files.

Our own serializer does not suffer from degraded performance when there are many queries in your project.

There are two different serialization options.

Ivercy Basic

The “Ivercy Basic” serialization method is optimized for best performance. It will deliberately ignore all field properties defined in the query and will only export and import the bare essentials of a query to/from the source code control repository.

For clarification: Your query will keep all properties relevant to data retrieval or data modification by the query, but it will lose all visual UI properties such as field formats, caption, lookup control configuration, and total row aggregations defined on query level.

Ivercy Full

The “Ivercy Full” serialization method will serialize all query and field properties except those listed under “General Limitations”. In our performance test this serialization method is still much faster with huge databases than LoadFromText/SaveAsText.

Important: For the “Ivercy Full” serialization method to work, all tables on which the queries are based need to be present in the database. Linked tables need to be linked to the database before queries are fetched.

General Limitations

There are some general limitations with the Ivercy Query Serializer.

It cannot (de)serialize the layout of the table/query relationships in the design view of the query. Of course, the source tables/queries and their relationships as defined by joins in the query will continue to be there. However, any custom sizing and positioning you did in the relationships window will get lost.

The GUID property of queries and fields and the and DOL and NameMap properties of the query are ignored. The exact purpose of these properties is not documented by Microsoft. We believe they are all related to the Name Autocorrect Tracking in Access. If we are correct, putting these properties into source control does not make any sense. We are not aware of any negative effect that is caused by omitting these properties.

As the above properties are ignored by the Ivercy Query Serializer, they will get lost when you check-in a query into the SCC repository!

Compatibility with LoadFromText/SaveAsText

The text file format created by both Ivercy query serialization methods is compatible to the LoadFromText method of Access. I.e., you can use the Ivercy serializer to write a query to a text file and then use Access.Application.LoadFromText to import this file into an Access database.

However, the Ivercy Query Serializer only understands a subset of the file structure that is written by the SaveAsText method. Therefore, Ivercy serialization will fail to import most query text files that were created with the SaveAsText method.

How to use the Ivercy Query Serializer for your project

By default, Ivercy will still use Access query serialization with LoadFromText/SaveAsText. If you want to use the Ivercy query serializer, you need to explicitly enable it in the Ivercy Options.

For new projects

If you want to use the Ivercy Query Serializer to add a new project to source code control for the very first time, open the Ivercy options dialog, go to the “New Project Template” tab and change the option value of QuerySerializer to either “IvercyBasic” or “IvercyFull”.

Then add the project to source code control as you normally would.

For existing projects

If you want to use the Ivercy query serializer in an existing project you must convert the format of the source files from the old format to the new one with the following steps.

  • Make everybody working on the project has checked-in all changes to queries.
  • Open your project in Access.
  • Make sure you’ve got the most recent version of all queries downloaded in your local copy of the Access database.
  • Check out all queries.
  • Open the Ivercy Options dialog and change the option value of QuerySerializer to either “IvercyBasic” or “IvercyFull” for the current project.
  • Check in all queries.

Every other developer working on that project should also change the QuerySerializer option for the current project and then get the latest version of all queries.

Keep in mind: When you create a fresh local copy of the project, you need to adjust the QuerySerializer option in the “New project Template” before you get the project from the repository.

In any case: Make sure everybody working on a project uses the same QuerySerializer option!