MS Access is a great tool to quickly and simply fill a business need. As your database matures and grows, it may start to suffer from performance and/or reliability issues. Learn best practices on how to move your current database to an enterprise-grade solution.

12844122413 7051abd646 k

MS Access is a great tool to quickly and simply fill a business need. As your database matures and grows, it may start to suffer from performance and/or reliability issues. It is important to recognize when your database starts to outgrow its original design and know when to move to an enterprise-grade solution.

Once you’ve identified that your MS Access solution has run its course, it’s time to take the next step. How do you build on what you already have? In this guide, I’ll walk you through everything you should know prior to migrating away from MS Access.

Is the grass really greener on the other side?

Before we get too far, it is important to decide whether moving away from Access is the right decision for you. In many cases, upgrading your current database won’t make sense. Databases that are accessed by just a few people or are infrequently used might not be worth upgrading. There are several ways an MS Access databases can outgrow their original purpose.

Plan your project

Once you’ve identified the need to partially or fully move away from MS Access, it’s important to formulate a plan.

Current vs Future usage

To begin with it’s important to understand and record the current status of your database.

Some key information:

  • Database size (number of tables, number of records, total size)
  • Total users accessing the database. Number accessing it at the same time.
  • Process to backup the database
  • What kind of integrations with corporate data (automated, import, export)
  • What other applications are integrating with the database

As you are recording this information, it is important to consider the future. What is the end goal of the database, and what is the scale of the end product (e.g. how many users, how much data will it hold)?

Requirements

In addition to understanding the current usage, it is important to record what the system’s function should be. How should the system behave, what are the key features, what features are not longer in use, and what new features are required?

What are your options?

Now that you have decided that MS Access is no longer ideal, it’s time to decide on a replacement tool. What is the best alternative?

SQL Server Backend

The lowest entry level involves keeping the existing MS Access front end (forms, reports, logic etc) and linking to an SQL Server for the data. This is a good balance between cost and benefits. This is a quick and effective move because it doesn’t impact your application’s logic. With a small amount of work, SQL Server can provide greater reliability and maintenance compared to an MS Access database. It is also a step towards making your data available from outside the office and through mobile devices. Once this is in place, other applications can now interact with this data. Imagine you want field workers to fill out a simple form while requiring access to some information held within the database; with the SQL Server is in place, a simple web application can be created to provide this functionality.

Custom Web Application

The main reason most people are drawn to Access is its simplicity and customizability. Web applications give you the ability to create something unique by taking the previous solution one step further. This involves upgrading both the data layer and the application. Upgrading the application will further improve performance, scalability and security. The major drawback in this approach is the development effort to rewrite the application. Web applications offer a lot of other benefits such as easier deployments (no more redistributing new versions of the database), easier maintenance/bug fixes, high flexibility in User Interface design, and accessibility through various platforms such as mobile.

Software as a Service Tools

If you are lacking the skills to create a web application, it’s not within your budget to hire help, and you would like a do-it-yourself solution, there are reliable SaaS tools like Quickbase which will let you create cloud-based applications fairly easily. These type of platforms will be limited in what you can do, but depending on your business they may be flexible enough to work for you. These type of applications are hosted on the cloud and have monthly fees.

Moving Forward

Now that you have a vision for the end goal of the database it’s time to decide on a solution and formulate a plan.

It’s always important to have a strong vision and plan, especially when implementing software. Software development is complex and ambiguities should be avoided to prevent unexpected requirements from creeping in.

Here are some things you may want to include in your plan:

  • Current and future requirements
  • Areas that require improvement or need to be rewritten
  • Data migration plan
  • Risks and mitigation strategies
  • Implementation and testing processes
  • Deployment process (e.g. stages, beta users, handling issues and feedback)

Have questions or unsure about the process? Get in touch! We will gladly point you in the right direction and clarify any questions you might have.

Image: Flickr