Farber Consulting Group | Custom Software Development NY | Software Development Company | Sql Consulting

How We Converted an MS Access Application into a Full Web-Based ERP Solution

Introduction: From Legacy MS Access to Modern ERP and CRM

For over 40 years, a leading chemical manufacturer relied on a massive MS Access application to manage its daily operations. Built around 20 years ago, the system became the backbone of the company, supporting everything from inventory management to accounting. While it wasn't officially recognized as an ERP system, the MS Access application effectively functioned as a partial ERP, integrating multiple key business functions under a single platform.

However, as the company grew, so did its need for a more scalable, reliable, and future-proofed solution. The MS Access system, which had not been properly maintained for years, became increasingly unstable. The original developer was no longer available, bugs piled up, and the system could no longer meet the evolving needs of the business. As a result, the manufacturer faced risks to productivity, scalability, and overall operational efficiency.

Our Solution 

This is where our expertise in C# development, a key component of the .NET framework, along with our custom software development and database design capabilities, came into play. Our job was to help the client streamline their entire production process, from raw material inventory control all the way through to the finished product.

We began by stabilizing the existing MS Access application, ensuring it was functional enough to support the company's immediate needs. We then migrated the backend to a more robust and scalable MS SQL database, dramatically improving system performance, security, and long-term scalability.

This transformation allowed us to deliver a fully scalable, web-based ERP solution, one that could meet the company's current and future business needs. The modernized system not only supports the client’s growing demands but also integrates seamlessly across all business functions, streamlining everything from procurement to production management.

It was a large and complex MS Access application, yet no documentation existed to explain the database structure or database design.

Step 1: First Contact and 10-Step Engagement: 

The journey began with a single email request for an MS Access developer. Rather than jumping directly into fixes, we asked the client to review our process, the first step we take to engage with the client during the initial phase of a custom software development project.
As part of this step, we shared our article: Custom Software Development – 10 Steps to Engage With the Client which outlines our proven process for ensuring alignment and clarity before development begins.
 
The client responded with an email that included detailed answers based on the guidelines in the article. These responses showed their commitment to investing the necessary time, which was essential for the success of this software development process.

In our first meeting, we discussed the following:

  • Their business rules in details.
  • The time commitment required from their team.
  • Expectations for meetings, reviews, and approvals.

This structured approach helped us avoid wasted efforts and set a clear foundation for collaboration. We also learned that the company’s owner, a Ph.D. in Chemistry, and the controller would take part in the initial meetings. Their active participation gave the project strong direction from the start and created confidence in achieving the desired outcomes.

Step 2: Maintaining While Preparing for Migration:

The client couldn’t afford downtime. Before replacing the old system, we agreed to maintain and stabilize their MS Access application. Our team fixed bugs, ensured continuity, and learned the system’s workflows while simultaneously preparing for a modern solution. This double task strategy allowed us to keep operations running smoothly while planning the database migration, to convert MS Access database to SQL Server and begin building a scalable, web-based ERP through custom software development in C# and .NET.

Step 3: Comprehensive Feature Discovery:

We conducted a series of deep-dive meetings with stakeholders.

  • With the Owner: We explored operational features like inventory control, formula management, label creation, raw material tracking, warehouse management, quotes, purchase orders, and batch processing. 
  • With the Controller: We reviewed financial features such as invoicing, accounts receivable, accounts payable, and raw material payments. We also examined how QuickBooks was integrated into their accounting workflows. 

It was a large and complex MS Access application, yet no documentation existed to explain the database structure or database design. Multiple algorithms were embedded across different processes, but not a single note had been left behind. Our discovery sessions were essential to uncover these details and ensure nothing was missed before planning the migration.

Step 4: Reverse Engineering the MS Access Application:

Because the original developer was no longer available, we needed to reverse engineer the MS Access system from the ground up. No documentation existed to describe the database structure or design, and many processes were hidden in code.

We followed a methodical reverse-engineering approach: we examined relationships, exposed hidden objects, traced startup sequences, reviewed event code, and worked through query logic and table-to-query flows. 

We investigated:

  • Proprietary algorithms embedded in formulas
  • Inventory and raw material management logic
  • Relationships between tables and data dependencies
  • Custom workflows and business logic embedded in forms and macros

This step-by-step approach allowed us to fully map the undocumented MS Access application.

Step 5: Migrating MS Access Database to SQL Server:

The next major milestone was to convert the MS Access database to SQL Server, also known as upsizing an MS Access database to MS SQL.

We carried out a structured migration plan:

  • Analyzed and mapped table structures, relationships, and keys to document the existing schema.
  • Cleaned and normalized data to improve integrity and performance.
  • Optimized the SQL Server database design for scalability and future modules.
  • Reviewed and migrated MS Access queries and expressions into SQL.
  • Created stored procedures and sync routines to manage data during transition.

By migrating to MS SQL Server, we removed the limitations of MS Access, ensured a secure and scalable database design, and established a strong foundation for the next phase of custom software development, delivering a robust web-based ERP system.

Step 6: Specifications and Database Improvements: 

We created high-level specifications for each ERP module, shared them with the client, and incorporated their feedback. 


Key benefits of this step: 

  • The client clearly understood planned features before development began. 
  • We enhanced existing modules based on business rules. 
  • We improved backend design beyond what MS Access Application could provide. 

We followed a rigorous Software Requirements Specification (SRS) process: defining functionality, creating a data dictionary, modelling entity relationships, and specifying expected performance, security, and external interfaces. This collaborative step helped ensure the ERP system was tailored to the client’s real-world workflows. 

Step 7: Agile Development with C# and .NET: 

Using C Sharp or C# as part of the dot NET. framework, we applied our expertise in C Sharp Development and adopted an Agile development approach,  grounded in proven SDLC - Software Development Life Cycle principles. This helped us increase quality, accelerate delivery, and reduce cost through structured iterations.

The function of the SDLC is to save time and money during the process of developing custom software while also ensuring top quality. Really, most software projects fall victim to the same series of mistakes.

  • We started with essential modules such as Suppliers and Customers. 
  • We delivered working increments early, gathering client feedback at each stage. 
  • We iteratively refined features to match unique business requirements. 

By aligning with SDLC best practices, and using Agile as our methodology, we kept the client involved, minimized errors, and ensured efficient progress toward the final ERP solution.

Step 8: Consolidating Multiple Systems Into One Web Solution as ERP: 

The client had been juggling several disconnected applications, including QuickBooks. We consolidated them into a single web-based ERP system. With seamless QuickBooks integration, we eliminated double data entry and gave the business a unified view of operations. 

Step 9: Advanced Inventory and Warehouse Features 

Inventory posed unique challenges for a chemical manufacturer. Our ERP modules accounted for: 

  • Finished goods that had to leave the warehouse quickly. 
  • Samples tracked and shipped to prospects or customers. 
  • CRM integration, enabling sales teams to track samples and seamlessly convert a prospect into a customer.
  • Drop shipment chains where Customer A resold to Customer B, sometimes extending to five levels deep. 

These features gave the client precise control over inventory, warehousing, and customer fulfilment. 

ERP-Integrated Visual Warehouse Management

To extend the advanced inventory features, we introduced a visual warehouse management platform fully integrated with the ERP system. This solution provides a floor-plan style view of warehouse operations, delivering live visibility and automation for every movement of goods to streamline inventory control.

  • Real-time tracking of inventory across the warehouse
  • Location-specific details displayed on a visual floor plan
  • Color-coded indicators for quick status updates
  • Barcode scanner integration for automated updates and error reduction

Step 10: Developed an Integrated CRM Features for Stronger Customer Relationships

As part of the ERP solution, we implemented a fully integrated CRM (Customer Relationship Management) module built with C# and .NET. This CRM connects sales, customer service, and inventory management into a unified workflow, providing real-time visibility into every customer interaction.

Key CRM Capabilities:

  • Centralized Customer Data: Every client, prospect, and vendor record is stored in a single, searchable database integrated with the ERP. This allows teams to access order history, invoices, and communications instantly.
  • Lead and Opportunity Management: The CRM tracks sales leads from the first contact through to conversion. Each lead can be linked to quotes, sales orders, and sample shipments for complete visibility.
  • Activity and Communication Tracking: All client interactions, emails, calls, meetings, are logged in one place. This ensures that every team member has the full context before engaging with a customer.
  • Integration with Inventory and Production: The CRM connects directly to ERP modules such as inventory and production. When sales reps update an opportunity, they can immediately check available stock, product details, and shipping schedules.
  • Automated Follow-Ups and Reminders: The system includes task reminders, automated email notifications, and follow-up prompts, ensuring no sales opportunity slips through the cracks.
  • Reporting and Analytics: Management dashboards provide insights into lead sources, conversion rates, and customer lifetime value. These analytics help improve forecasting and sales performance.

Step 11:  Added  Natural Language Data Finder - Ask ERP - As AI Automation

What it does:

  • We used AI Automation to allow the client to have un-limited reports. This feature let users query ERP data in plain English no report setup or SQL knowledge needed. Simply type what you’re looking for, like:

    "Show all batch tickets using ingredient IBA between October 10 and November 6, 2025."

  • The system uses AI to interpret the request, identify which ERP modules (Sales, Production, Batch Tickets, Inventory, etc.) are relevant, and automatically generate a secure SQL query behind the scenes. It then presents the data in a dynamic, grid-style view that supports sorting, filtering, grouping, and exporting.

  • With this capability, clients can instantly access information from any part of the ERP sales trends, production runs, ingredient usage, purchase history, and more and without needing to rely on IT or pre-defined reports. Users can easily export the results into spreadsheets (Excel or CSV), PDF documents, or other supported formats for sharing, record keeping, or deeper analysis.

  • It’s a powerful way to turn complex ERP data into quick, actionable answers in seconds.

Step 12: Delivering a Scalable ERP Solution 

In the final phase, we transitioned the business from Access to a future-proof ERP system:

  • Backend powered by SQL Server - A secure, reliable, and scalable database foundation that eliminated the limitations of Access.
  • Web application built with C Sharp Development on the .NET framework - We used a commercial .NET framework, which provided a full suite of pre-built features before development began. This included advanced security controls that allowed us to manage access down to individual menu features. Implementing these capabilities out of the box saved months of development time and significantly reduced risks.
  • Agile process for continuous improvement - We engaged closely with the client in short iterations, reviewed deliverables regularly, and adapted quickly to feedback. This minimized mistakes, reduced rework, and ensured the ERP solution aligned perfectly with real business needs.

Results: 

  • Business operations stabilized during transition. 
  • Multiple applications consolidated into one ERP. 
  • Scalable, secure architecture designed for decades of use. 

If you’re facing the same challenges, or your software/app is outdated—and you want to upgrade to a cloud-based ERP, contact us. We’ll migrate your existing database, improve your system, and convert it into a secure, cloud-based web application. 

In addition, if you’re dealing with complex customer tiers or drop shipping across levels, we can help. We’ll migrate your data, improve your system, and deliver a secure cloud-based web ERP that supports multilevel fulfilment. 

Step 13: Ongoing Support and Continuous Improvement:

After successfully implementing the new web-based ERP system and migrating from MS Access to MS SQL, we didn’t just walk away, we continue to provide comprehensive support and ensure that the solution remains aligned with the client’s evolving needs.

Our support extends beyond just system maintenance. We work closely with the client’s team to optimize processes, address any challenges, and make enhancements as new business requirements emerge. This ongoing relationship ensures the system not only performs efficiently but also adapts to their changing business dynamics.

Key areas of ongoing support include:

  • Proactive monitoring to identify and address potential issues before they affect performance.
  • Regular updates to ensure the system stays current with the latest technological advancements and security measures.
  • User training and support to maximize the team’s productivity and help them fully leverage the system's capabilities.
  • Customizations to the ERP system as new processes or functions arise within the business.

Client Feedback: Measuring Productivity, Profitability, and Efficiency Gains

“Once we figured out how to use the software, or rather, once we got accustomed to it, it significantly reduced the time it took for the office to process orders. The software doesn’t change production time, since chemical compounding takes as long as it takes, but the improvement in order-processing speed is where we’ve seen the biggest efficiency gain.

We’ve also seen major advancements in how electronic orders are translated directly into the ERP system, as well as the one-click conversion from customer quote to sales order. The addition of a global shipping calendar, where dated orders can be viewed and accessed by shipping and receiving, has been extremely helpful for organizing freight.

There is now much better organization of raw material quotes, quote history, and control over how materials are costed into formulas, compared to the old MS Access system. We also now have a true inventory control system, which didn’t exist in the old setup.

On the formulation side, working with existing formulas is much easier, and raw material substitutions can be handled with far more precision, whether applied across all formulas or just specific formula classes.

The search features and historical tracking are also much better overall, whether you’re looking up a customer’s order history, checking which formulas use a particular raw material, or reviewing the costing history of an item. All of it is extremely useful.

Finally, yearly inventory is much easier to complete in the ERP than it ever was in the old MS Access system.”

Frequently Asked Questions

How long does it usually take to convert an MS Access application to a web-based ERP?

Project timelines depend on the complexity of your database and required features. A typical conversion and rebuild process, including migration to SQL Server, database design, and web ERP development, can take a few months. We start with an evaluation to provide a clear project schedule.

Will we lose any of our existing data during migration?

No. Our process ensures complete data preservation. We back up and validate your Access data, then migrate it safely to SQL Server with full integrity checks to make sure nothing is lost or corrupted.

Can you modernize only part of our MS Access system instead of rebuilding the whole thing?

Yes. We often start with critical modules first, then expand as needed. This phased approach helps reduce cost, minimize disruption, and deliver results faster while planning for full system modernization.

Do you offer ongoing support after the migration?

Absolutely. After deployment, we provide maintenance, optimization, and feature updates to ensure your new C#/.NET ERP system continues to perform at its best.

How secure will the new web-based system be?

By rebuilding your application in C# and .NET on SQL Server, we implement advanced security features like role-based access control, data encryption, and secure authentication. This ensures your ERP system meets modern security standards.

What kind of businesses benefit most from this service?

Companies that have outgrown their MS Access applications, especially manufacturers, distributors, and service-based organizations, benefit most. We specialize in helping businesses move from desktop-based systems to secure, cloud-ready ERP solutions.

How can we get started?

You can contact us for a free evaluation. We’ll review your existing MS Access database, discuss your requirements, and outline the steps to migrate it to SQL Server and rebuild it as a scalable web-based ERP system.

Got questions about unleashing the full potential of your project?
We’ve got the answers!

Contact Us

Search