Access Database to Modern System: A Real Migration Story

This is the story of Miller Manufacturing (name changed), a 30-person precision parts manufacturer that spent 18 months migrating from a Microsoft Access database to a modern web-based system.

This isn't a case study designed to sell you something. It's an honest account of what worked, what didn't, costs that were expected, costs that weren't, and lessons learned the hard way.

If your business runs on Access (or similar legacy system) and you're considering migration, learn from Miller's experience.

The Starting Point: Access Database That Ran the Business

Company profile:

• 30 employees
• $6M annual revenue
• Manufacturing precision parts for aerospace industry
• Family-owned, 35 years in business
• Tech-forward for their industry but not a tech company

The Access database:

• Built 12 years ago by owner's nephew (who had since moved on)
• Ran on shared network drive
• Managed: Customer orders, inventory, scheduling, quality control, shipping
• It worked. Business had grown 3x while using it.

The Access setup:

• Frontend: Access application (.accdb) on each computer
• Backend: Access database on file server
• 8 concurrent users typically
• 45,000 customer records
• 250,000 order line items
• 15,000 inventory records

What worked about it:

• Forms were intuitive (evolved over years based on actual use)
• Reports were exactly what business needed
• Everyone knew how to use it
• Zero downtime in 12 years

What didn't work:

• Single user could lock entire database with certain operations
• Slow when multiple users querying simultaneously
• No mobile access (stuck at desk)
• Couldn't access from home during COVID
• Hard to generate new reports (required Access skills nobody had)
• No audit trail (couldn't see who changed what when)

The Decision to Migrate

The trigger: COVID pandemic

The problem:

• Owner needed to access system from home
• Accounting needed to work remotely
• Remote access to Access database over VPN was painfully slow
• They limped through COVID with VPN and workarounds

Post-COVID reflection:

• Remote work proved valuable
• Access was blocking flexible work
• Business wanted to grow another 2-3x
• Access performance was degrading with data volume

The owner's reasoning: "We've grown as much as we can on Access. To get to $15M revenue, we need something better. Let's do this right."

Budget approved: $150,000 Timeline expected: 12 months

Spoiler: Actual cost was $280,000, actual timeline was 18 months. But they succeeded.

Phase 1: Discovery and Planning (Months 1-2)

What They Did Right: Hired a Consultant to Map Current State

Instead of jumping to solutions, they documented what they had.

The consultant spent 6 weeks:

• Interviewing every department
• Documenting every workflow
• Mapping every report
• Understanding every form
• Cataloging every piece of functionality

Key discoveries:

Discovery #1: Hidden complexity

• What looked like "simple order management" had 43 different workflows
• Different order types: Standard, Rush, Custom, Prototype, Rework
• Each type had different approval flows, pricing rules, scheduling logic

Discovery #2: Institutional knowledge in the database

• Customer-specific pricing rules (78 custom rules)
• Machine-specific scheduling constraints
• Material compatibility matrices
• Quality control checklists that varied by customer and part type

Discovery #3: The reports were critical

• 67 different reports actively used
• Many reports were how people actually did their jobs (not just occasional summaries)
• Daily production schedule generated from Access report
• Weekly customer shipment report sent to 40 customers

This discovery phase cost $18,000. It was the best money they spent.

Without it, they would have:

• Underestimated complexity
• Missed critical workflows
• Built system that didn't match how business actually worked

What They Did Right: Involved Users from the Start

User committee formed:

• Production manager
• Quality control supervisor
• Customer service lead
• Accounting manager
• Shipping coordinator

Met biweekly throughout project.

Their role:

• Validate requirements
• Review designs
• Test prototypes
• Champion the project to other employees

This prevented: Building a system that technically worked but nobody wanted to use.

The Plan That Was Created

Technology choices:

• Web application (accessible anywhere)
• PostgreSQL database (reliable, scales well)
• Python/Django backend (consultant's expertise)
• React frontend (modern, responsive)
• Hosted on AWS (managed infrastructure)

Migration approach: Phased

1. Build new system
2. Parallel run (both systems simultaneously)
3. Gradual cutover (one module at a time)
4. Decommission Access

Timeline: 12 months

• Months 1-2: Discovery ✓
• Months 3-8: Build new system
• Months 9-10: Parallel running and testing
• Months 11-12: Cutover and stabilization

Budget: $150,000

• Consultant/development: $110,000
• Project management: $15,000
• Infrastructure: $10,000
• Contingency: $15,000

Everything seemed reasonable.

Phase 2: Development (Months 3-10)

Note: This took 8 months instead of planned 6 months.

Month 3-4: Database Design and Core Architecture

The work:

• Design PostgreSQL database schema
• Build authentication and user management
• Set up AWS infrastructure
• Create basic CRUD operations

Early wins:

• Modern infrastructure working
• Users could log in from anywhere
• Fast (way faster than Access over network)

Early challenges:

Challenge #1: Access database structure was... creative

The nephew who built it 12 years ago wasn't a professional developer.

Examples of "creative" structure:

• Dates stored as text ("1/5/2023" and "01-05-2023" and "Jan 5 2023")
• Customer names with inconsistent capitalization
• Order status stored as numbers in one table, text in another
• Calculated fields stored (violating normalization) because nephew didn't know about views

Impact: 3 weeks of data cleanup before migration could even start.

Lesson: Legacy systems evolved organically. Structure reflects learning curve of original builder.

Month 5-7: Building Core Modules

Development priority:

1. Customer management (foundation)
2. Order entry (most-used feature)
3. Inventory (critical for operations)
4. Production scheduling (complex, high-value)

What went well:

Customer management (Month 5):

• Straightforward
• Cleaned up duplicate customers during migration
• Better search than Access ever had
• Users loved it

Order entry (Month 6):

• Initial version matched Access forms closely
• Users comfortable with it
• Actually improved on Access (better validation, prevented errors)

What didn't go well:

Inventory (Month 7):

• Underestimated complexity
• Access had 12 years of "special cases" encoded
• Example: Certain materials auto-adjusted quantities based on historical waste rates
• These adjustments weren't documented, just "in the system"
• Had to reverse-engineer logic from Access macros and VBA code

Time overrun: 4 weeks (discovering and rebuilding hidden logic)

Production scheduling (Month 7-8):

• Most complex module
• Access used complex VBA to schedule machines
• Logic considered: Machine capabilities, current workload, due dates, material availability, operator certifications, maintenance schedules
• Took 2 months instead of planned 1 month

Month 8-10: Reports and Integrations

The report challenge:

Remember those 67 reports? Each one needed to be rebuilt.

Original estimate: 2 weeks for all reports Reality: 8 weeks

Why it took longer:

• Each report had subtle logic that wasn't obvious until you tried to reproduce it
• Users would look at new report and say "that's wrong"
• Turns out Access report had filter or calculation they didn't realize was there
• Had to go back to Access, figure out what it was actually doing, rebuild correctly

Example: "Weekly revenue report" seemed simple. But Access version excluded certain order types, applied different date ranges to different customers, and adjusted for returns in a specific way. None of this was documented. Users just knew "the report is always right."

Lesson: Reports aren't just formatting. They encode business logic.

Integrations:

Two external integrations:

1. QuickBooks (for accounting)
2. UPS/FedEx shipping APIs

Access integration:

• QuickBooks: Manual export/import via CSV
• Shipping: Manual entry of tracking numbers

New system integration:

• QuickBooks: Automated sync
• Shipping: Direct API integration, auto-tracking

These worked well and provided clear value.

Phase 3: Parallel Running (Months 11-13)

Note: This took 3 months instead of planned 2 months.

The plan: Run both systems simultaneously, compare outputs, build confidence.

How it worked:

Month 11: Dual entry

• Orders entered in both systems
• Compare results daily
• Fix discrepancies

Unexpected challenge:

• Taking 2x time to enter everything twice
• Employees frustrated ("why are we doing twice the work?")
• Had to hire temp worker to handle extra data entry

Cost overrun: $12,000 for temp worker (not in original budget)

Month 12: Found the bugs

When systems disagree, which is right?

Example bug: New system calculated order total differently than Access.

Investigation revealed:

• Access applied customer discount, then shipping, then tax
• New system applied discount, then tax, then shipping
• Result: $0.13 difference on $1,000 order

Seems trivial. But multiply by 200 orders/month across 12 months = $300 difference.

And customers might notice. Their system expects specific calculation order.

Found 37 such discrepancies. Each required investigation and fix.

Month 13: Building confidence

Finally, systems agreeing consistently.

The data that convinced them to proceed:

• 30 consecutive days of matching results
• Users comfortable with new system
• Speed improvement measurable (2-3x faster for most operations)
• Remote access working perfectly

But timeline now 13 months (1 month over original plan) and still not cutover.

Phase 4: Cutover (Month 14-15)

The original plan: Big-bang cutover. One weekend, switch from Access to new system.

The actual plan: Phased cutover.

Why they changed approach:

Big-bang felt too risky after seeing how many subtle issues parallel running revealed.

The phased approach:

Phase 1: Read-only reporting (Week 1)

• New system becomes source for reports
• Order entry still in Access
• Build confidence that reports are correct

Phase 2: Customer service (Week 2-3)

• Customer service team switches to new system
• Can access from anywhere
• Other departments still on Access

Phase 3: Order entry (Week 4-6)

• New orders go into new system
• Old orders still in Access (for reference)
• Integration with QuickBooks cut over

Phase 4: Production and shipping (Week 7-10)

• Production scheduling in new system
• Shipping integration goes live
• Access now read-only

Why this worked:

• Smaller bite-size changes
• Each phase validated before next
• Could roll back if needed (never did)
• Team adjusted gradually

What didn't work:

Week 3: Production emergency

Machine scheduler broke. Production stopped for 4 hours while developers debugged.

Root cause: Edge case where part required two sequential operations on same machine, but new system only checked general machine availability, not sequential availability.

Access handled this (somehow - probably via VBA macro nobody remembered).

Cost: 4 hours of downtime, emergency developer call (premium rate)

Lesson: Legacy systems handle edge cases you don't know exist until they break.

Month 15: Full cutover complete

Access officially retired. Everyone on new system.

Timeline: 15 months (3 months over estimate)

Phase 5: Stabilization (Months 16-18)

Wishful thinking: "Once we cut over, we're done."

Reality: "Now we find all the real problems."

Month 16-17: The bug reports

First month after cutover:

• 47 bug reports
• 12 were actual bugs
• 35 were "works differently than Access" (not bugs, but required adjustment)

Examples of "not bugs but differences":

Report #1: "Inventory report is wrong"

• Investigation: Report was correct
• Difference: Access rounded to nearest whole unit, new system showed decimals
• Solution: Added rounding to match expectations

Report #2: "Can't find customer XYZ"

• Investigation: Customer was in system
• Difference: Access search was fuzzy (matched partial names), new system required exact match
• Solution: Improved search with fuzzy matching

Report #3: "Order totals don't match QuickBooks"

• Investigation: They did match
• Difference: QuickBooks integration now real-time instead of daily batch, so timing looked different
• Solution: User education

Developer time: 80 hours in Month 16-17 handling post-cutover issues

Month 18: Actually stable

Metrics:

• Bug reports down to 2-3 per week (mostly minor)
• Users proficient with new system
• Performance excellent
• Remote work fully enabled
• Customer satisfaction maintained

Project officially closed Month 18.

The Real Costs: Full Accounting

Original budget: $150,000

Actual costs:

Direct Development Costs

• Discovery and planning: $18,000
• Development (8 months vs 6 planned): $132,000 (overrun)
• Project management: $22,000 (overrun)
• Infrastructure (18 months): $15,000 (overrun)
• Subtotal: $187,000

Unexpected Costs

• Temp worker during parallel running: $12,000
• Emergency developer calls: $8,000
• Extended consultant time (post-launch support): $18,000
• Training materials and sessions: $5,000
• Subtotal: $43,000

Internal Costs (often forgotten)

• User committee time: 200 hours × $50/hour = $10,000
• Testing time: 150 hours × $50/hour = $7,500
• Data cleanup: 80 hours × $50/hour = $4,000
• Dual entry period inefficiency: $18,000
• Subtotal: $39,500

Opportunity Costs

• Owner's time: 120 hours managing project
• Delayed initiatives: New product line launch pushed 3 months
• (Hard to quantify but real)

Total all-in cost: $280,000

vs. Original budget: $150,000

Overrun: 87%

This is actually typical. IT projects commonly run 50-100% over budget.

What They Got for $280K

Tangible benefits:

1. Remote access

   • Work from anywhere
   • Value: Flexibility for key employees (owner values at $30K annually in avoided commute time and ability to work evenings)

2. Performance

   • System 3x faster
   • Value: ~30 minutes/day saved per user × 8 users = 4 hours daily = $20K annually

3. Better reporting

   • Self-service reports
   • Value: ~5 hours/week of "can you run this report?" requests eliminated = $13K annually

4. Integration automation

   • QuickBooks sync automated
   • Shipping integration automated
   • Value: 10 hours/week of manual work eliminated = $26K annually

5. Scalability

   • Can now handle 3x data volume
   • Can add users without degradation
   • Value: Enables growth (hard to quantify)

6. Mobile access

   • Owner can check orders from anywhere
   • Production can update status from shop floor
   • Value: Responsiveness, customer service improvement

Measurable annual benefit: ~$89K

Break-even timeline: 3.1 years

Intangible benefits:

• Modern, professional system
• Easier to hire (younger employees want modern tools)
• Better audit trails
• Improved data quality
• Foundation for future growth

What Worked: The Success Factors

Success Factor #1: Realistic Timeline

They gave themselves 12 months and ended at 18 months.

If they'd pressured for 6 months, they would have either:

• Cut corners (caused problems later)
• Stressed the team (caused quality issues)
• Failed outright

Lesson: Plan generously for migration timelines.

Success Factor #2: Phased Cutover

Original plan was big-bang. They pivoted to phased.

This saved them:

• Could test each phase before next
• Issues found early, when easier to fix
• Team learned gradually
• Could roll back if needed

Lesson: Phased beats big-bang almost always.

Success Factor #3: User Involvement

User committee prevented: Building wrong thing

User testing caught: Issues before production

User champions helped: Others adopt the new system

Lesson: Users must be involved, not just "told what's happening."

Success Factor #4: Parallel Running

Running both systems simultaneously was expensive (time, temp worker).

But it caught 37 discrepancies that would have been disastrous in production.

Lesson: Parallel running is worth the cost for critical systems.

Success Factor #5: Good Discovery Phase

$18,000 spent understanding what they actually had.

This prevented: Building incomplete system.

This enabled: Accurate estimates (well, more accurate).

Lesson: Don't skip discovery to "get started faster." You'll pay for it later.

What Didn't Work: The Mistakes

Mistake #1: Underestimated Reports

Estimated: 2 weeks for 67 reports Actual: 8 weeks

Why: Reports encode hidden business logic

Lesson: Reports are not "just formatting." Budget significant time.

Mistake #2: Assumed Access Was Simple

Because Access interface is simple, they assumed logic was simple.

Reality: 12 years of VBA macros, hidden calculations, and institutional knowledge.

Impact: Constant "wait, Access did what?" discoveries

Lesson: Legacy systems are more complex than they appear.

Mistake #3: Didn't Budget for Stabilization

Original plan ended at cutover.

Reality: 3 months of post-launch support needed.

Impact: Budget overrun

Lesson: Budget 20-30% of project cost for post-launch stabilization.

Mistake #4: Inadequate Change Management

Assumed: "New system is better, people will love it."

Reality: Change is hard. People resist.

What helped: User champions, but should have done more formal training and change management.

Lesson: Technical migration is 50% technical, 50% people/change management.

The Alternative: What If They'd Stayed on Access?

Honest counterfactual:

They could have stayed on Access by:

• Moving Access backend to SQL Server (keeps Access frontend, better backend)
• Cost: ~$30K
• Would solve performance and remote access issues
• Timeline: 2 months

Or even:

• Built web frontend, kept Access as database
• Cost: ~$80K
• Timeline: 6 months

Why they chose full migration anyway:

• Wanted modern platform for future growth
• Wanted to own their roadmap (not constrained by Access limitations)
• Willing to invest for long-term value

The decision was defensible, but they could have solved immediate problems for much less.

The Bottom Line

Miller Manufacturing spent $280K and 18 months migrating from Access to modern web system.

It worked. They're happy with the result.

But:

• Cost was 87% over budget
• Timeline was 50% longer than planned
• Process was harder than expected
• Edge cases kept surprising them

Would they do it again?

Owner's answer: "Yes, but I'd budget $300K and 24 months from the start. We got there, but it was stressful thinking we were over budget and behind schedule when actually we were experiencing normal IT project reality."

Key lessons if you're considering similar migration:

1. Budget 2x what you think (they thought $150K, actually $280K)
2. Timeline 1.5-2x what you think (they thought 12 months, actually 18)
3. Invest in discovery (understand what you actually have)
4. Involve users early and often (prevents building wrong thing)
5. Phase the cutover (reduces risk dramatically)
6. Plan for parallel running (catches discrepancies before production)
7. Budget for stabilization (post-launch support is not optional)
8. Consider alternatives (full migration isn't always necessary)

Access databases (and similar legacy systems) run many successful businesses.

Migration can work, but go in with eyes open about real costs, timelines, and complexity.

Miller's story is a success story—but success required realistic expectations, good planning, user involvement, and 87% more budget than originally planned.

If you can't commit to that, maybe keep Access running for now.

It's been working for 12 years. It can probably work a while longer.