Microsoft Access modernisation and replacement

Somebody in the office built it to track one thing. It worked, so it grew. Years later it holds the customers, the orders, the stock and the invoicing, half the office opens it every morning, and the person who built it has moved on.

That's the normal history of an Access system, and it's not a failure. Access is a good tool for the job it was designed for: a handful of users on one office network. The trouble starts when the business outgrows that design, and the symptoms are always the same.

• It's slow, and slower still for anyone working from home over a VPN.
• It corrupts. Compact and repair has become a weekly job, and everyone knows the phrase "unrecognised database format".
• Two people editing the same record produces a locking error, or silently loses one edit.
• The file is near the 2 GB limit, or has been split into several files that don't quite agree.
• Nobody dares change a query because nobody knows which reports depend on it.

Three things, and they're worth different amounts.

The tables. The data model is the most valuable part and usually the soundest. It reflects what the business tracks. It often needs tightening (missing keys, free-text fields doing the work of a lookup, dates stored as text), but it's a head start on any replacement.

The queries and the VBA. This is where the business rules live: the pricing logic, the status transitions, the "if the customer is on hold, don't allow despatch" checks. It's also where the undocumented behaviour hides. Reading it is most of the assessment.

The forms and reports. Useful as a specification for what the screens need to do. Rarely worth carrying forward as they are, because they're the part tied to the desktop.

Move the data to SQL Server, keep the Access front end. Access links to SQL Server tables and carries on looking exactly as it did. The 2 GB limit, the corruption and most of the speed problems go, because the data is no longer a file on a share being read by thirty PCs. Multi-user locking becomes SQL Server's job, which it does properly. Queries that ran in Access can be moved into views and stored procedures so the heavy work happens on the server. This is the first stage for almost every Access system, and for some it's the whole answer for years.

Replace the front end with a web application, in stages. When people need the system outside the office, on a phone, or shared with customers, the desktop front end is the limit. With the data already on SQL Server, a web screen can replace one Access form at a time while the rest of the system carries on in Access against the same tables. The VBA rules move across as they're understood. The old form stays until the new one has proven itself.

Replace the whole thing. Right when the data model itself is the problem, or when the business has changed so much that the system's shape is wrong. Even then, replace alongside, with the data moved and reconciled before anything switches, and the Access tables and VBA read as the specification.

Which path fits is what the assessment decides, from the tables, the queries and the code rather than from a description.

Less than people fear, done in the right order.

1. Read the schema and clean what has to be clean. Every table needs a primary key SQL Server can use. Dates stored as text, Yes/No fields with nulls, and lookup values typed by hand all have to be resolved before they move, or they'll break on the way.
2. Move the tables, using the Microsoft migration tools where they fit and hand-written scripts where they don't. Attachment and multi-value fields, which SQL Server doesn't have, get redesigned.
3. Re-link the front end. The Access forms now point at SQL Server. Queries that were doing joins across linked tables get rewritten as views or pass-through queries so the work happens on the server.
4. Test against a copy, then run in staging alongside the old file with the same data, reconciling record counts and totals until they match.
5. Switch, at a quiet moment, with the old file kept read-only as a fallback.

At the end the users see the same screens, and they're faster, and the weekly compact and repair is over.

Nothing changes during the assessment. When the data moves, it moves once, and it never meets a change until the change has proven itself somewhere else: built and tested on a copy in a test environment, then run in staging alongside the live database with record counts, totals and a sample of records reconciled. Only when the tests have shown it's safe does anything connect to live, and the Access file stays available as a fallback until you're confident.

Yes, and Access is unusually well suited to it. Because the front end and the data can be separated, the data move is a stage on its own with an immediate result, and every form after that is a stage of its own. The office keeps working in Access throughout. There is no cutover weekend and no leap.

The assessment is from £395 + VAT, sized on a free one-hour consultation, with an exact price before you commit. For an Access system it reads the tables, queries, VBA and reports, tests the file's health, and prices each path as a fixed number. The data move is usually the first stage and the cheapest. What drives the price and the payment terms each have a page.

Corruption is the one that ends badly. An Access file on a network share, opened by many people, will corrupt eventually, and the question is whether the last backup was tested. The others are slower: the 2 GB limit arriving, the one person who understands the VBA leaving, and remote working turning a slow system into an unusable one.

Understand first, then move the data, then modernise the screens one at a time. Marc Allington has built business systems on Access and SQL Server as well as on the web, so the queries and the VBA are familiar ground. Everything we build afterwards runs on open, widely used technology, in your own accounts, with full source code and ownership transferring to you on final payment.