Replacing an Access Database with a Web Application

Signs You've Outgrown Access

• Your .accdb file is approaching 2 GB. Microsoft documents this as a hard limit, and performance drops and corruption risk rises as you near it.
• Several users hit locking errors or slowdowns at once. Access allows 255 connections on paper, but a shared file database becomes unreliable with far fewer active users.
• The database has corrupted before. Access uses a file-share model, so one dropped network connection can corrupt the file for everyone.
• People outside the office need to use it, or you need user accounts, an audit trail, and backups that do not require kicking everyone out first.

Two Paths Forward

The right first move depends on how much pain the file is causing today and how much the forms and reports have drifted from how the business works. Both paths end in the same place; they differ in how soon the Access front end goes away.

What Carries Over

More than most people expect. Your table structures, relationships, and years of data migrate with verification at every step. Forms and reports do not port automatically, but they are the best specification the new interface could have, because they encode exactly how your business works, including the exceptions nobody wrote down.

Three things need deliberate attention because Access let them slide. Data types: text fields holding dates and numbers, and lookups stored as display values, need to become proper columns. Keys: tables without primary keys, or with duplicate rows, must be cleaned before SQL Server will accept them. Logic: validation rules, macros, and VBA behind the forms are business rules, and each one needs a home in the new application or database.

The Migration in Stages

1. 1
   Inventory the application

   List every table, query, form, report, macro, and VBA module, and note which ones people still use. Old Access files carry years of abandoned objects, and you should not pay to rebuild them.

2. 2
   Design the SQL Server schema

   Model the target schema deliberately instead of copying the Access shape. Fix data types, add the missing keys and constraints, and normalize the tables that grew columns over the years.

3. 3
   Migrate and reconcile

   Move the data with scripts you can rerun, then reconcile row counts and totals against the Access file. Repeat until the run is clean, because you will run it again at cutover.

4. 4
   Rebuild the interface

   Turn the forms into screens and the reports into reports, using the Access versions as the specification. This is where the business rules from macros and VBA get a proper home.

5. 5
   Run in parallel, then cut over

   Let the new application run alongside Access on the same data long enough to catch what the inventory missed, then switch the Access file to read-only and keep it until nobody has opened it in months.

What It Takes

A focused Access-to-web migration is usually a small-to-mid-size project rather than an enterprise one. Scope depends on the number of forms and reports still in use, the quality of the data, and the systems the application exchanges data with. This is one of our most common engagement types; see our legacy modernization and database design services for how we run it.

The forms and reports you already have are the specification. The job is to move the data safely and give that specification a platform that will not corrupt.

Keep Reading