Replacing a department spreadsheet or Access database with a web app

Signs the file has outgrown itself

Spreadsheets and Access databases are good tools. Departments reach for them because they can start today without asking anyone. The trouble starts when the file becomes a shared system without being treated like one: several people depend on it, other processes read from it, and nobody can change it safely.

• People wait for each other. The file is locked, copies are emailed around, or someone merges versions at the end of the week.
• One person understands it. The macros, the hidden sheets or the Access queries only make sense to whoever wrote them.
• Mistakes are silent. A pasted value replaces a formula, a filter hides rows, or a lookup quietly returns the wrong match, and nobody notices until a report is wrong.
• It's hitting hard limits. An Access database file can't grow past 2 GB, and an Excel worksheet stops at 1,048,576 rows. Long before either, large shared files on a network drive can become slow and prone to corruption.
• Audit and access questions have no answer. Who changed this value, when, and who is allowed to see the file at all?
• Other systems depend on it. Reports, mail merges or other workbooks link to it, so a renamed column breaks something elsewhere.

Before committing to a custom build, check whether something you already own fits. Microsoft Lists, Power Apps or a module in an existing system can be the right answer for a simple list with a form. Our guide to custom web apps vs. low-code walks through that decision. A custom web app tends to earn its place when the logic is real, other systems need the data, or the process needs permissions and an audit trail that a shared file can't give you. For documents approved by email from a shared drive, see our guide to a simple document approval workflow app.

Inventory what the file actually does

The file's visible purpose is rarely all of it. A workbook that "tracks supplier corrective actions" may also calculate due dates, color-code overdue items, feed a monthly chart that goes to leadership, and hold a hidden sheet of lookup values another workbook reads. Anything missed here turns into a surprise after cutover.

Sit with the people who use the file and watch them do the job. Ask what they do when something goes wrong, what they check before sending a report, and what they wish the file did. The workarounds they describe are requirements.

Design the data model, then clean the data

A spreadsheet usually stores one wide row per thing, with repeated columns such as "Action 1", "Action 2", "Action 3". A web app's database should store those as related records, with lists of values in their own tables. That change is what makes reporting, permissions and history possible later.

• Free text to picklists. A "Supplier" column with twelve spellings of the same company becomes a reference to one supplier record.
• Repeated columns to child rows. "Action 1" to "Action 5" become a list of actions, each with an owner and a date.
• Dates and numbers stored as text. Values such as "TBD", "3/4" or "approx 40" need a rule: convert, flag or leave blank with a note.
• Meaning in color or comments. Turn it into a real field, such as a status or a reason.
• Duplicates and orphans. Decide which record wins, and what happens to rows that reference something that no longer exists.
• Identifiers. Keep the old row number or record ID on each migrated record, so anyone can trace it back to the file.

The new database is usually SQL Server or PostgreSQL, often on infrastructure you already run. If the app also needs data from an existing system, our guide to integrating two business systems when one vendor's API is poor covers keeping the two in step.

Treat the migration as repeatable code

The worst migrations are done once, by hand, on the night of cutover. A better migration is a script that reads the file, applies the cleanup and mapping rules, loads the new database and produces a reconciliation report. It runs as many times as needed: on a copy early in the project, again after each round of feedback, and a final time at cutover.

Rebuild formulas and VBA as tested rules

A formula copied into a new app is still a formula nobody can see. Rebuild each calculation as a named rule on the server, where it applies to every screen, import and API call the same way, and write down what it does in plain language.

• Use the old file as the oracle. Take a set of real rows, run them through the file and through the new rule, and compare the results. Differences are either a bug in the new rule or a bug in the old file that the department needs to decide on.
• Watch for spreadsheet behavior. Rounding, blank cells treated as zero, text that looks like numbers, and date serial numbers all behave differently in a database and code. Decide each one explicitly.
• Replace macros with features. A VBA button that emails a report becomes a scheduled report or a notification; a macro that copies rows to an archive sheet becomes a status and a filter.
• Keep validation, and make it stricter at entry. Required fields, allowed values and ranges are checked when the record is saved, not discovered at month end.

Choosing a cutover approach

1. 1Rehearse the full migration on a recent copy, and time it.
2. 2Agree a go or no-go check: the reconciliation report is clean or its exceptions are accepted.
3. 3Announce the freeze time, and who to call on the day.
4. 4Stop edits to the file, run the final migration and reconcile.
5. 5Open the app, and make the old file read-only rather than deleting it.
6. 6Fix links: bookmarks, shortcuts, and any workbook or report that read from the old file.
7. 7Archive the old file with a note saying where the data now lives.

Our sample written scope shows how the data sources, the migration and the acceptance checks for a project like this are written down before a fixed price.

Making the change stick

• Involve the file's owner from the start. The person who built the spreadsheet knows its rules best and becomes the new app's best advocate if they help shape it.
• Make the app faster than the file for the common task. Sensible defaults, remembered filters and fewer clicks for the thing people do fifty times a day.
• Sign in with company accounts. Single sign-on and roles replace the shared file password. Our guide to SSO and least privilege for internal apps covers the design.
• Give the app an owner after launch. Someone decides on changes to picklists, rules and reports, instead of the department starting a new side spreadsheet.
• Watch for shadow copies. If people export and keep working in Excel, find out what the app is missing.

Checklist before you start

Our experience includes database design and optimization on SQL Server, PostgreSQL and MySQL, VBA and Visual Basic, and full-stack web applications in React, Next.js, TypeScript and Node.js. See web application development for what we build, and the sample handover package for the documentation and runbooks your team would receive.

Sources

• Access specifications (Microsoft)
• Excel specifications and limits (Microsoft)