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.