Modernizing a Legacy MS Access System into a Full Web-Based ERP Solution

Client Profile                                                        

• Industry: Chemical Manufacturing
• Company History: 40+ years in operation
• Legacy Platform: MS Access application (20+ years old)
• Primary Users: Operations, production, sales, accounting, and warehouse

Background

For decades, the client relied on a large MS Access application to manage its core business operations. What began as a departmental database gradually expanded into the central system for inventory management, production, purchasing, quoting, invoicing, and accounting.

Although the application functioned as an informal ERP, it was never designed to support long-term growth. Over time, the system became increasingly difficult to maintain. There was no documentation describing the database structure or embedded business logic, the original developer was no longer available, and even minor changes introduced risk. Despite these challenges, the system remained mission-critical to daily operations.

This created a growing need to convert MS Access to web architecture without disrupting the business.

The Challenge

The client faced a growing set of technical and operational risks:

• A large, undocumented MS Access database with complex embedded business rules
• Increasing instability and performance issues
• No practical path for scaling or extending functionality
• Dependence on outdated technology
• Multiple disconnected systems that could not be unified

Compounding the problem, the business could not tolerate downtime. Any effort to convert MS Access to web technology had to preserve day-to-day operations while a replacement system was designed and built.

Initial Engagement

The engagement began with a request for MS Access support. Rather than immediately modifying the system, a structured discovery and engagement process was used to define scope, responsibilities, and expectations.

Key stakeholders, including the company owner and controller, were directly involved from the outset. Early discussions focused on understanding business rules, operational dependencies, and the level of collaboration required to safely modernize a system that the business depended on daily.

Stabilization and Parallel Planning

Before replacing the legacy platform, the existing MS Access application was stabilized to reduce operational risk. Critical bugs were resolved, system reliability was improved, and workflows were observed and documented in real time.

At the same time, planning began for a full migration to a modern, web-based ERP system backed by SQL Server. This parallel approach ensured continuity of operations while laying the groundwork to convert MS Access to web architecture successfully.

System Discovery and Reverse Engineering

Because no documentation existed, the MS Access application had to be reverse engineered in full. This process included:

• Analyzing table structures, relationships, queries, and dependencies
• Tracing event code, macros, and startup logic
• Identifying proprietary algorithms used in formulas and inventory management
• Mapping business rules embedded across forms and processes

This discovery phase ensured that all functionality was fully understood before any data or logic was migrated, reducing the risk of lost or altered behavior.

Database Migration: MS Access to SQL Server

Once the system was fully mapped, the database was migrated from MS Access to SQL Server. The migration focused on creating a stable and scalable foundation for the new ERP system.

Key efforts included schema analysis and documentation, data cleanup and normalization, redesigning the database for performance and scalability, and migrating queries and logic into SQL. Stored procedures and synchronization routines were implemented to support a controlled transition.

The result was a secure, high-performance backend capable of supporting a modern web-based ERP.

ERP System Design and Development

High-level specifications were created for each ERP module and reviewed collaboratively with the client. This process clarified business rules, improved data integrity, defined security and access controls, and established a framework for future expansion.

The ERP system was developed as a web application using C# and the .NET framework, following an Agile methodology aligned with SDLC best practices. Development was delivered in stages, beginning with core modules and expanding iteratively based on user feedback. This approach minimized risk and ensured the effort to convert MS Access to web aligned with real operational needs.

System Consolidation and Integration

Previously disconnected applications, including QuickBooks, were consolidated into a single ERP platform. Accounting data was integrated directly, eliminating duplicate data entry and improving financial visibility across the organization.

The ERP was designed to address industry-specific challenges unique to chemical manufacturing, including rapid turnover of finished goods, sample tracking tied to sales opportunities, multi-level drop shipments, and complex formula and batch management.

A visual warehouse management component was introduced to provide real-time inventory visibility through floor-plan layouts, barcode scanning, and automated updates. An integrated CRM module connected sales, customer service, inventory, and production data into a unified workflow.

AI-Driven Reporting (“Ask ERP”)

To reduce reliance on predefined reports, an AI-based natural language reporting feature was introduced. Users can query ERP data using plain English, and the system automatically interprets the request, identifies relevant modules, generates secure SQL queries, and presents results in interactive, exportable grids.

This capability significantly expanded reporting access without requiring technical expertise or IT involvement.

Results

The legacy MS Access system was successfully replaced with a fully web-based ERP platform.

Key outcomes included:

• Stable operations throughout the migration process
• Consolidation of multiple systems into a single ERP
• Improved inventory control and formula management
• Faster order processing and quote-to-order workflows
• A secure, scalable architecture designed for long-term growth

Ongoing Support

Following implementation, ongoing support continues through system monitoring, performance optimization, security updates, user training, and feature enhancements as business needs evolve.

Client Perspective

The client reported measurable efficiency gains in order processing, improved inventory control, stronger visibility into historical data, and easier management of formulas, raw materials, and shipping operations compared to the legacy MS Access system.

Work with The Farber Consulting Group Inc.

If you are considering converting MS Access to a web-based system, The Farber Consulting Group Inc. brings decades of experience modernizing mission-critical databases and building secure, scalable ERP solutions. Their team focuses on minimizing risk, preserving business logic, and ensuring smooth transitions with no disruption to daily operations.