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

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.