MS Access to SQL Server migration

Recycling company solves data corruption issues through cloud database integration

Influential Software performed a Microsoft Access to SQL Server migration that empowered remote workers to access data with full stability and security.

Executive brief

Building resilience with Microsoft Access to SQL Server migration

Our client is a recycling company specialising in paper recovery. Influential Software delivered a Microsoft Access to SQL Server migration solution that enabled the company’s staff to work remotely. This Azure-based SQL Server database brought the following benefits:

Curious about how we achieved this through an MS Access to SQL Server migration? Read on to learn more.

The full story

The challenge

Corrupted MS Access database was wasting time

Years previously, the company had paid someone to create a MS Access database for customer, order, supplier, and sales data. But over time it became clear that the database was not behaving properly, as the data was regularly being corrupted. The cause of this corruption was the use of an inefficient fileshare connection which staff were using to access the database remotely.

 

As a result of the corrupted MS Access database, the company had to restore the database several times a week. This meant that all work since the last backup was lost, and no-one could use the system during the restore. And this contributed to the company losing around a day of work time per month.

 

Overall, the negative effects of the corrupted Access database were:

 

• inefficiency caused by a lack of trust in company data
• possible security breaches due to the unencrypted fileshare
• loss of productivity with hours wasted on database restores

 

In brief, these poorly integrated technologies were costing the company time and money. It was clear that a modern and well-implemented database solution was needed.

The solution

Performing MS Access to SQL Server migration

Initially, the recycling company approached us to support its ailing MS Access database. On investigation we discovered that the database had not been split into two, as per MS Access best practice. Making this split — with the tables separate from the queries, forms, and reports — gave an improvement in performance, reliability, and security.

 

However, the database was still experiencing some corruption. The cause, we determined, was the fileshare connection used to interact with the database remotely. We addressed this issue by replacing the fileshare with a new router that supported a virtual private network (VPN). Because the VPN was stable and end-to-end encrypted, this solved the connectivity and security issues of the old fileshare.

 

In the short term, this solution worked perfectly — the staff now had uninterrupted access to reliable company data.

 

But Access also had long-term disadvantages, such as corruption, compliance issues, and lack of scalability. Our client’s management recognised this as a barrier to the company’s agility and growth.

 

For this reason, we proposed moving the company database to an enterprise-grade SQL Server in the Azure cloud. By keeping the Access application as the interface for the new database, we maintained the same user experience. This guaranteed full adoption and mitigated the time and cost of user training.

The benefits

An Azure cloud database for long-term growth

While this project started with a short-term goal (solving database corruption), it turned into an opportunity for long-term IT modernisation. By recommending that our client move from an MS Access database to a SQL Server one in Azure, we achieved both goals in one solution. This “two-birds-with-one-stone” approach was ultimately more economical for the client and gave them a cost-effective foundation for future IT projects.

 

Overall, the benefits of the decision to migrate a Microsoft Access database to SQL Server in Azure were:

 

• enabling staff to access data remotely at all times
• restoring faith in the accuracy of company data
• regaining the hours lost to restoring corrupted data
• providing a scalable and flexible cloud-based platform

Get help from the data experts

This recycling client is one of dozens that benefit from our data integration expertise. Whatever your industry or size, we’ll work to maximise the value of your business data. To find out more, visit our integration solutions page.