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.
Guide · How-to
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
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.
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.
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.
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.
What a field means, which values are allowed and how the tables relate is written down nowhere. Every change starts with finding that out.
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.
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
Which option fits depends on how specific your process is, who works with the application and who will look after it in future.
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.
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.
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
If the application stays for now, the built-in tools of Access reduce the risk.
Do it yourself
Replace 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
The steps apply to standard software and a custom application alike. With standard software, configuration takes the place of the prototype.
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.
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.
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.
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.
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
The migration runs in four steps. Each can be repeated until the result is right.
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.
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.
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.
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
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:
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
A web application needs ongoing care after go-live. Who takes on which task should be set down in writing before you commission it.
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.
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.
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.
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.
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.
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.
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