Guides

Guide · How-to

Replacing an Access database.

A home-grown Access application is a database with forms and reports that someone in the organisation built and has extended over the years, for example for members, appointments or assignments. This guide describes how to recognise its limits, which options there are and how a replacement with data migration works.

By Paul Czichos, consulting and project support at czichos.net GmbH · Updated

Typical situation

The limits of an Access application.

Microsoft Access keeps tables, queries, forms, reports and program code in one file that runs on Windows. For one person at one computer, that is enough. When several people or sites work with it over years, these signs typically appear.

01

Installed on every computer

The application and its data sit on individual computers. A new computer means reinstalling and copying data across. If a computer fails without a backup, its data is lost.

02

Data in several places

If sites or services keep their own copies, there is no shared, current state. For a joint report, someone has to merge the tables by hand.

03

One person knows the application

Whoever built it knows which query feeds which report and which macro runs on saving. If that person hands over the task, nobody else can fix errors or change anything.

04

Undocumented

What a field means, which values are allowed and how the tables relate is written down nowhere. Every change starts with finding that out.

05

Tied to Windows and Office

Access is only available for Windows. After a change of Office version or from 32-bit to 64-bit, older VBA code may stop working, as may embedded add-on controls.

06

Remote and shared use

Over a VPN, access to an Access file on a network drive is slow, and a dropped connection can corrupt the file. If two people edit the same record, Access reports a write conflict.

Three options

Alternatives to an Access database.

Which option fits depends on how specific your process is, who works with the application and who will look after it in future.

01

Stabilise and keep for now

Fits if the application does its job, a few people work with it in one place and it holds no particularly sensitive data. You separate data and interface, back up and document. The limits of Access remain.

02

Standard software

Fits if there is a ready-made solution for your process, for example for membership management or rota planning. Further development lies with the vendor, and with hosted software the operation as well. Your process then follows the software. Check data import, export, permissions per role and where it is hosted beforehand.

03

Custom web application

Fits if standard software can only map your process with workarounds, or if several units are to work with separate data in one application. The application runs in the browser and the data is in one place. You commission development, operation and later changes.

Do it yourself or have it done

Stabilising an Access application yourself.

If the application stays for now, the built-in tools of Access reduce the risk.

Do it yourself

If the application stays for now

  • Separate data and interfaceThe Database Splitter wizard in Access moves the tables into a separate file. It sits in one place on the network, and every computer gets a copy of the interface.
  • Back up and test restoringBack up the file with the tables automatically and keep several versions. Try once whether a version can be restored.
  • Write down what is thereThe Database Documenter in Access lists tables, fields, queries and reports. Add a short note on who receives which report.
  • Brief a second personIf a second person knows the structure and processes, the application remains maintainable when the first is unavailable.

Replace it

If the limits shape everyday work

  • Several sites or unitsIf offices, groups or services work with their own copies, you need one database in one place with separate areas.
  • Working remotelyA web application runs in the browser. It does not need access to a file on a network drive.
  • Sensitive dataAn Access file in the current format supports a password for the whole file, but no permissions per person. Health or social data needs roles and separate areas.
  • Changes pile upIf nobody can change the application any more, new requirements, such as revised reports for funders, end up in Excel lists alongside it.

On our own behalf: we earn money from the second column. If your Access application does its job and a few people work with it in one place, the first column is enough.

Approach

Replacing an Access application.

The steps apply to standard software and a custom application alike. With standard software, configuration takes the place of the prototype.

  1. Step 1: Assessment

    Record what the old application does: tables, forms, reports, queries and macros. This includes what happens around it, such as Excel lists or reports that are reworked by hand. The people who work with it every day know which functions they use and which they do not. The result is a list of the functions and reports the new application needs.

  2. Step 2: Early prototype

    A requirements specification describes processes on paper. A prototype shows them as screens that staff try out. Missing fields, wrong terms and unnecessary steps show up before much has been built.

  3. Step 3: Improvement in rounds

    In each round, staff work with the current version and report what is missing or gets in the way. The application is then adjusted. A fixed contact person per site or service collects the feedback and helps decide when it conflicts.

  4. Step 4: Trial data migrations

    The data is migrated several times as a trial before the cut-over. Each trial run shows where the clean-up is not yet right. The steps are in the next section.

  5. Step 5: Cut-over on a set date

    On the cut-over date, the data is migrated one last time. From then on, everything is recorded in the new application only. The old file stays read-only for reference until it is clear that nothing is missing. Staff are briefed beforehand and know whom to ask.

Data migration

Migrating Access data.

The migration runs in four steps. Each can be repeated until the result is right.

01

Export

Extract the tables from the Access file, from every copy that exists. Tables, fields and relationships can be read from the file itself, even without documentation.

02

Standardise

Bring data kept in different ways into one uniform structure. This covers spellings, list values and dates. Duplicate records are merged or flagged for review.

03

Import

Load the cleaned data with its links into the new database. The history comes along as far as it is needed and may be retained.

04

Check with real cases

Staff open known cases in the new application and compare them with the old one. Reports, for example for funders, must produce the same figures from the migrated data.

Data protection

Data protection for sensitive data.

Client data held by social services can include health information. Under Article 9 GDPR, it belongs to the special categories of personal data. Planning a new application therefore includes these points:

  • Separation per unit. Each site or service sees only its own data, even when all of them work in the same application.
  • Roles. Whoever records, plans or reports gets only the permissions the task requires.
  • Personal accounts. Everyone signs in with their own account. This makes it traceable who changed what.
  • Data processing. If a service provider runs the application, you conclude a data processing agreement with them under Article 28 GDPR.
  • Hosting location. Where servers and backups are located and who can access them belongs in that agreement.
  • Retention periods. A file that has grown over years can hold data whose purpose has ended. The migration is an occasion to set retention periods and delete such data beforehand.

What applies in your case is a question for your data protection officer. The general duties are covered in the guide to the GDPR for associations.

Operation

Operation after the cut-over.

A web application needs ongoing care after go-live. Who takes on which task should be set down in writing before you commission it.

  • Updates. The operating system, the database and the application’s libraries receive security updates. Someone installs them and then checks that the application still works.
  • Backups. Automatic, with several versions and in a second location. Whether a version can be restored is checked regularly.
  • Monitoring. An automatic check reports an outage to whoever is responsible, at night and at weekends too.
  • Changes. When processes or requirements change, the application has to be adapted. Clarify who takes change requests and how they are billed.
  • Exit. Your data must be exportable at any time in an open format such as CSV. This belongs in the contract.

Access replacement FAQ.

Can an Access database be used in the browser?

Not directly. Access runs only as a Windows program. For remote access, Access can run on a terminal server that staff connect to via Remote Desktop. That keeps the application with its limits. It runs in the browser only once it has been rebuilt as a web application.

Can the data be migrated if there is no documentation?

Yes. Tables, fields and relationships can be read from the Access file itself. Standardising takes more work if the same data was kept differently in different places.

Does a new application need a requirements specification?

To start, the list from the assessment is enough: which functions and reports the new application needs. Details are settled on the prototype, because staff can see and try out what is being built.

Can several sites work separately in one application?

Yes, if the application is built for it. Each record then belongs to one unit, and a person’s account determines which unit they see. Reports across all units are only possible with a role that permits it.

What does it cost to replace an Access database?

That depends on the scope: the number of forms and reports, the state of the data and whether several units work separately. Running costs for operation and changes come on top. A reliable figure requires an assessment. We bill development by effort, with a written quote up front.

Case study: assignment database for the Berliner Mobilitätshilfedienste

For more than twenty years, the mobility assistance services in twelve Berlin districts managed their assignments with a locally installed Access application without technical documentation. We replaced it with a shared database in the browser in which each service sees only its own data. The case study describes the approach and the data migration.

Read the case study

Tell us about your Access application.

Describe in a few sentences what you use the application for. We get back to you and arrange a call.

Without obligation. Before work starts, you receive a written quote.

Prefer to reach us directly? Send an email · call +49 30 994048000

Address czichos.net GmbH
Königsweg 220
14129 Berlin, Germany