MICROSOFT (MS) ACCESS IS AN IDEAL TOOL FOR CREATING A DESKTOP APPLICATION TO BE ACCESSED BY SINGLE USERS. ANYONE CAN EASILY CREATE SIMPLE DATA-DRIVEN APPLICATIONS THAT REALISE IMMEDIATE EFFICIENCIES. MS ACCESS HAS USEFUL QUERY AND REPORTING FUNCTIONALITY, ALLOWING FOR QUICK AND EASY ANALYSIS OF YOUR DATA.
However when multiple users need to concurrently access the application or users need remote access, the limitations of MS Access become apparent.
This article highlights the signs that your business has outgrown an MS Access application and provides options of what you should do next.
The 5 signs
SIGN 1 – More than 2-3 people need to access the application
SIGN 2 – Users need external access to the application
SIGN 3 – Interface files become easily corrupted
SIGN 4 – The application database houses sensitive information that needs to be protected
SIGN 5 – The application has many limitations and users need additional functionality
So you’ve outgrown your MS Access application, now what?
Sign 1 MORE THAN 2-3 PEOPLE NEED TO ACCESS THE APPLICATION MS Access was never intended to be used for a multiuser (more than a few users) enterprise-wide application.
When multiple users need to access the application the database files need to be separated from the interface files. However even if the files are split, MS Access does not fully support transactional record handling. Meaning if two people make changes to the same record at the same time, only the last saved changes will be kept. This could result in one person losing their changes and having to re-enter the information.
To enable multiple user access, the database structure needs to be moved to a more robust database backend that can support concurrency, such as a Microsoft SQL server. However, even with an SQL backend database, the front-end application can easily become corrupted and will have limited capabilities, along with a host of deployment and licensing issues.
Sign 2 USERS NEED EXTERNAL ACCESS TO THE APPLICATION MS Access does not support external access. You can not simply take an MS Access application and make it available via a browser.
External users would need to be facilitated by remote access software such as remote desktop or terminal services, e.g. Citrix Online. This results in additional software licences and, more likely than not, increased costs.
Sign 3 INTERFACE FILES BECOME EASILY CORRUPTED Even if the problem of concurrency is solved with an SQL backend database and remote access is enabled through additional software, the access interface files are highly inflexible and can easily become corrupted.
It only takes a few bytes to go wrong and MS Access will not be able to recover. This means having to continuously restore interface files on users’ desktops.
Sign 4 THE APPLICATION DATABASE HOUSES SENSITIVE INFORMATION THAT NEEDS TO BE PROTECTED Although some security measures can be put in place, compromising security policies can be quite simple for those with malicious intent.
Because the database is simply one file, it can easily be emailed along with all the data to external sources.
Moving to an SQL backend database will prevent such a security risk.
Sign 5 THE APPLICATION HAS MANY LIMITATIONS AND USERS NEED ADDITIONAL FUNCTIONALITY Even when you overcome the capacity problem with SQL, the inflexible nature of the MS Access interface will become a burden to expanding functionality requirements. As the need for additional interface functionality becomes unavoidable, using MS Access to cater to the requirements often becomes impractical.
So you’ve outgrown your MS Access application, now what?
Organisations using both an MS Access interface and MS Access database find they can achieve efficiencies by moving the backend database to an SQL server.
But in time the limitations of this scenario start to become apparent.
As more and more users access the system and as the need for additional functionality increases, a custom application becomes a real cost-saving option.
A custom application will ensure concurrency is no longer an issue, security is always maintained and additional functionality is catered for.
Furthermore, remote access software licences can be eliminated by designing the application to be accessed via a web browser.
MS Access, even with SQL in the backend, should never be used for a business critical application.
This could pose a serious continuity risk to an organisation should the application go down.