Moving a Microsoft Access database to a web app

Why the Access database is still there

Because it was a good idea at the time, and it did the job. Somewhere between the late nineties and about 2015, a lot of small firms had someone, an employee, a relative, a contractor, who built them a database in Microsoft Access. Customers, jobs, orders, stock, membership, whatever the business ran on. It had forms to type into, reports to print, and it lived on a shared drive.

Twenty years on it is often still the system of record. It is also usually the thing the owner worries about most, for reasons that come up again and again.

• Only one or two people can be in it at once before it slows down or locks.
• It only works in the office, on a Windows PC, on the network. Nobody can use it from a van, a site or home.
• The person who built it has gone, and nobody understands the queries behind the reports.
• It has started corrupting now and then, and the fix is a restore from last night's backup, if there was one.
• New staff find it baffling, and the training is "ask Sue".
• Each new version of Windows or Office brings a nervous week.

None of that means it was badly built. It means it was built for a business and a way of working that have both moved on.

What a web app is, in plain terms

A web app is the same idea, a database with screens on it, built so it runs in a browser on anything: the office PCs, a laptop at home, a tablet in the workshop, the phones of the people on the road. The data lives on a server in a UK data centre rather than on a file on your shared drive, backed up every night, and everyone works on the same live copy at once.

Nothing to install, nothing that breaks when a PC is replaced, and the screens are built for the job each person does rather than for whoever built the forms in 2006.

The signs it is time

You do not have to replace an Access database just because it is old. These are the signs that it is costing you more than it saves.

• People keep a spreadsheet or a notebook beside it because the database cannot do something they need.
• Anyone outside the office is phoning in for information that is sitting in the database.
• You have had a corruption or a lost day in the last year.
• You want customers, drivers or engineers to see or enter their own information.
• You want it to talk to the accounts package, the website or the phones, and it cannot.
• Nobody left in the business can safely change a report.

Two or more of those and it is worth a conversation. One, and it is probably worth leaving for now.

What carries across

More than people fear. The data carries across in full: customers, jobs, history, the lot, cleaned on the way. The logic carries across too, because an Access database is a very good specification of how your business works. The tables tell us what you keep, the forms tell us how people enter it, and the reports tell us what you need out. We read all three before we design anything.

What also carries across, and matters more than it sounds, is the words. If the database calls something a "docket" or a "round" or a "ticket", so does the new system. Nobody has to learn a new vocabulary for the work they already do.

What changes

The screens are rebuilt rather than copied. An Access form was designed for a mouse and a big monitor, and often grew extra buttons over the years. The new screens are built for the three or four things each person does most, and for a phone where that is where the work happens.

Several people work in it at once without locking each other out. A history is kept of who changed what and when. Things that are overdue flag themselves instead of waiting to be spotted in a report. And it joins to the systems round it: a finished job can raise the invoice in the accounts package, an enquiry from the website can arrive as a record, a text can go to the customer when the driver is on the way.

Reports that mattered are rebuilt and usually improved. Reports that nobody has run since 2014 are left behind, deliberately, after checking.

How the move actually goes

1. We look at the database with you. An hour or two, usually on a shared screen, going through what it holds, who uses which forms, and which reports are printed every week. We take a copy to read the structure properly.
2. A fixed price in writing. Scope, dates and what is not included, written plainly. Nothing starts until you have agreed it.
3. The data comes across early. We import a copy of the real data into the new system while it is being built, so every screen is reviewed with your own customers and jobs on it rather than made-up ones.
4. Your team reviews it before go-live. The people who use it every day try it with real work while there is still time to change how it works.
5. Final import and switch. On go-live day we import the latest data again, so nothing typed into Access in the meantime is lost. The old database is made read-only and kept.
6. Training and the first week. In your office with your own data on the screen. We stay close for the first week while the habits change.

There is more on the order of things in how we work, and the same approach for spreadsheets is in moving off spreadsheets.

How long it takes

A typical Access database that runs one part of a business, jobs or customers or stock, is weeks rather than months to replace, and the data import is the part that decides where in that range it lands. A database that has grown into the whole business goes live in pieces, the most-used part first, with Access kept running for the rest until each piece is ready. There is a longer answer in how long bespoke software takes.

Keeping the old data

The Access file is never deleted. It is made read-only and kept, so anyone who needs to check something from 2011 can still open it. Everything that matters is in the new system anyway, with a history, and your data can be exported in a standard format whenever you ask.

What this means for you

If the database is working and nobody is losing sleep over it, leave it. If it has become the thing that stops people working from the van, locks two people out of each other, or only one person understands, it has done its job and it is time to let it retire gracefully. The replacement should feel familiar on day one, work everywhere, and be looked after by people you can ring.