Moving Away from MS Access

Since the 90s MS Access has been one of the ‘go to’ product for database development. You can find multitudes of systems throughout the world that even to now use Access. However, times are changing. In 2017 Microsoft announced its plan to officially stop supporting Access and whilst as of 2021 the application remains available signs of its depreciation are readily apparent.  So, what does this mean for groups that are still dependent on MS Access?

Relying on a depreciating product

Hardware and software are rapidly evolving. In this environment, if a product is not consistently supported it will quickly find itself plagued by numerous issues.

Malware is the most readily identifiable threat and can cause extensive damage through the stealing of private information as well as the lockout or deletion of data. Whilst the types of malwares that aim at a product like Access will decrease as its usage does, those malicious pieces of code that are developed will find themselves unchallenged and difficult to address for the companies that are afflicted by them. In a similar vein new bugs and glitches that can appear naturally with the introduction of new hardware and operating system whilst not intentionally malicious will similarly cause drops in efficiency and potentials profits.

The other significant issue ties into expansion. Depending on the type of database MS access is being used for, new more efficient techniques or government regulations may require your system be modified or integrated with other products. Whilst this is currently quite achievable the number of programmers skilled in Access can be expected to decrease over the years whilst the cost of those available increase with their niche speciality.  Very quickly you will find the cost of upgrading such an outdated system is better used to instead replace it.

Replacing MS Access

The most immediate option is to replace your existing system with readily available products. If your Access database is similar to those, you should be able to export and import to the new system without too much difficulty. There will be costs involved in setting up the new product as well as the training that will be required. That said if this option is available to you, it is well worth considering. However, the trouble comes about when your access system does not have a ready-made alternative on the market. It is in these situations we recommend replacing your system with a custom-built web front.

Moving to the Web

The issue with moving whatever existing MS Access you have to a web platform is that the base languages do not directly convert. Access uses Visual Basic which is engineered for the web. There are applications available which can automatically convert VBA to something usable online, but this is often plagued with bugs and broken functions which can be a nightmare to fix. We highly recommend you rebuild your system from scratch. You will find it saves more money and effort in the long run.  

Below is a plan that we have prepared for how to transition for our clients and may be useful to you.

This development plan can occur while you are using your current system.  When items are considered complete, they can be rolled into live, and be used by your operators to make sure that they work correctly, while other areas are prepared and setup.  What this means, is you do not have to do all the work in one hit – it can be a logical step by step process.

The other ideal of this process is that your staff do not need to be retrained, the way the work now, will basically be the same with the web environment.

DATABASE

If your original MS Access system was developed by a database specialist, you should have a separate system to Access which holds the data. We typically use MSSQL or MySQL. If you are similar situation good news, nothing needs to happen here.

If not, your existing Access forms will need to be evaluated and a new backend will need to be developed. This is often the most expensive part of database development, and you should source a local specialist to do so. This is one of our primary services so please feel free to reach out if you require it.  

FORMS

When creating a web front each form needs to be recreated in HTML – this means that a copy of your original form is taken and then remade from scratch in a web language. The work itself is not too difficult, just tedious, and can be outsourced to overseas coders for costs as low as $10 per hour. That said testing should always be done locally by a specialist. Whilst basic coders can write they often lack the thoroughness and consideration for a client that database specialists are expected to have. This difference becomes most obvious in the number of bugs and frustration in the communication which are common with basic coding houses. Having a local specialist act as an intermediary will save you a lot of headaches.

Once the HTML form is created, then the data needs to be linked to the form.  There are a couple of languages that can do this, we encourage PHP as this is specifically what the language is designed for and from our experience as a development house is very reliable. This part of a rebuild should be done by a specialist as it is a core factor in determining the speed, performance, and reliability of your overall system.

REPORTS

One of MS Access greatest strengths is its reports generators. We have researched several alternatives but found that the most cost-effective solution is to simply take what forms you already have on Access and rebuilt custom in PHP. Who should do this depends on the nature of the report and we always encourage a specialist first evaluate the reports to determine whether a specialist is needed or the work can be outsourced.

PROCESSES

Processes are the pieces of code that mass updates, exports, transfers your data.  They fall into two areas. One is what we call a stored procedure, and the other is VBA code.

If you have a database separate from MS Access you will typically have a combination of the two types as certain processes are better handled in different ways. That said, if your system is entirely within MS Access, you will likely be working exclusively with VBA.

If you have stored procedures little work needs to be done on them they can be directly applied to your new web platform with limited effort. However, all VBA processes need to be rewritten as they will not work well on a web environment. This specific section of work should be done by a specialist as functions are some of the most complex pieces of code within a databases. At Accede we use someone with over 40 years of experience to cover this.

CONCLUSION

In closing, there are number of reasons you should seriously consider transitioning from MS Access if you’re still using it. If you have a system that is custom built and not easily replaceable by products available on the market you should reach out to a specialist to help. At Accede we offer these services but always encourage them to evaluate alternatives if we are not offering the best option. We believe in win-win relationships.

Facebooktwitterredditpinterestlinkedinmail

Leave a comment