This white paper discusses how to solve the problem of Microsoft Access becoming unsuitable for business processes with a cost-effective and practical approach.
It is easy to see how Microsoft Access can start off as a tactical choice for a specific business purpose and quickly become part of a strategic business process. However, where a solution is likely to grow in scope or size beyond the initial need for a tactical fix used by a handful of people importing low data volumes, then this agile tool quickly becomes unsuitable.
The Ascent of Access
For over 20 years Microsoft Access has been used by end users and IT departments as a desktop tool for small database applications. The graphical form designer and query builder make this tool accessible to users without any programming knowledge. Access is sold as part of the higher end versions of Microsoft Office and many knowledge workers are likely to have this tool installed on their desktops – ready to use without any further approval needed from IT. A combination of its ease of use and ready availability make Access an obvious choice for business users needing an upgrade path from spreadsheet to database when it is difficult to solicit help from an overstretched IT department.
Typically, an Access application consists of a small database schema together with the functionality to capture data via forms or via bulk import from csv or Excel files. Once the data has been captured it is available for reporting. Calculations on the data can be performed as part of the import or reporting function.
Access is able to export data in many different formats. This can result in its use as a key component in a longer chain of components creating a business process. For example, Access can be used to collate data from several core strategic systems; transform, aggregate and export that data into another format; and then pass on that data to another system such as an enterprise planning or financial forecasting system.
As a result of their agility and ease of use, Access (and Excel) are commonly used to develop IT solutions where businesses do not yet know what the detailed functional and non-functional requirements might be. This lack specificity does not fit nicely into traditional IT development approaches (such as the SDLC and Waterfall methodologies) and the business need for speed to market.
It is therefore easy to see how this agile tool can start off as a tactical choice for a specific business purpose, but then surprisingly quickly become part of a strategic business process.
Limitations and Problems
So, why the concern? Surely a tool that gets the job done with minimal cost and quick delivery times is fit for purpose?
The answer of course is that, in many cases, Access is an adequate option. However, where a solution is likely to grow in scope or size beyond the initial need for a tactical fix used by a handful of people importing low data volumes, then Access quickly becomes unsuitable.
A few common limitation and problems are;
- Will the system only be understood by one person? And what happens if the person who built the system leaves the team?
- Is there evidence of control and security?
- Has the report been written by someone who understands SQL?
- Does the technical architecture support the ability to replicate data?
- Is the system likely to be rolled out to more than a few users?
Below are some of the items to consider that are recognised as the Achilles heel of Access database solutions:
If any of the above are a concern and you have Access databases being used in your department then you would be wise to start considering your options for an upgrade path.
A few short term fixes exist. These include splitting the database into several instances, e.g. by region or product type – assuming the data lends itself to being split. Another option is to archive old data. These splits will however take some effort to accomplish and will create other problems down the line, e.g. for consolidated reporting across several databases or reporting prior to the archive cut-off date.
For a longer term solution, you will need to migrate your Access database to an existing or new system where data is stored in an enterprise grade RDBMS, such as Microsoft SQL Server, with good support in place through either your company’s internal IT or a third party supplier.
Microsoft provides a free tool for migration of database schema, data and queries to SQL Server. This is a step forward but still leaves a gap for functionality provided by the front-end forms built in Access. Be careful not to see this as a magic wand – a badly designed schema and queries in Access will get converted as-is into SQL Server.
Ultimately, the tasks of building the new database, adding functionality (be it in forms or web pages) and migrating the data and queries across is likely to be an expensive exercise once accounting for the cost of provisioning the hardware, software licenses and time spent by developers and infrastructure experts. Some of our clients have been quoted more than a half a million dollars for the in-house development of a bespoke system.
Cloud based solutions also exist. Fundamentally these might be similar solutions to the option of migrating to SQL – indeed it might even be SQL Server behind the scenes. A key difference however is that the provisioning of the hardware and software licenses is already taken care of and passed down to you in a very cost effective way because of the economies of scale involved, charged in monthly instalments rather than a large capital expenditure up-front. In addition to the cost benefits these cloud based platforms also provide highly productive environments for building the functionality which drove you to use Access in the first place: ability to capture data files in multiple formats, form building for capture of data from users, ability to export data in multiple formats and a user friendly analytical toolset. So, you don’t need to lose agility but you do gain the scalability and enterprise grade features that are missing from Access.
Cloud based solutions worth considering are: SolveXia, Office 365 (Access Apps for Sharepoint), Caspio and Zoho Creator.
Over the last few years, we have conducted several migrations from Access to SolveXia’s hosted SQL Server for clients ranging from local travel agents to large multi-national banks. Based on this experience, if you are considering the replacement of an Access database and looking for alternatives to an expensive in-house development project, successful projects will have the following attributes:
Whilst “raw” cloud options such as Azure offer some of these attributes such as a much lower initial cost of hardware and software, SMEs would still need to navigate company security hurdles, understand the technical side of the database implementation, ensure there is adequate backup and version control, and write programs to get data into and out of the database. “Raw” cloud is therefore unlikely to prove a suitable replacement for Access databases where SMEs wish to retain control.
Hosted systems like SolveXia provide a combination of SQL Server “made easy”, process automation using integrated data “robots”, automatic version control and backup, along with integrated end-user business intelligence (BI) tools to address all of these requirements in a cost-effective manner.
Such platforms are targeted at SMEs who wish to automate the repetitive and data intensive aspects of their work. No programming knowledge is required – even SQL Server tables can be constructed by a designer with no knowledge of SQL Server syntax. Data is held in an encrypted form on a secure architecture that has been designed to include redundancy, clustering and data replication.
Rest assured, viable and cost-effective technologies to help SMEs migrate Access databases and related processes to better and more scalable options do exist. Access is no longer denied!