The case for Microsoft Access?

Rod O’Doherty, April 2019

Microsoft Access Services

At Salar IT we have years of experience developing and supporting Microsoft Access solutions.

New application development
Migration to Cloud
Upgrade from Access 2007
Migration to SQL Server
Interface Development
Business Intelligence
Consolidation of multiple data sources
Support and Maintenance

See below for contact details.

If you need custom software developed to address a business need, it can be challenging to understand which route to take. There are so many options, so much advice, so many pros and cons to consider. Particularly if you are challenged by technical considerations and perhaps apprehensive about making the right decision. This article provides information on how Microsoft Access may be the answer to your needs. We examine the strengths of Access, what types of resources you should use to develop it, what are alternatives, what are the limitations and most importantly - when not to use Microsoft Access.

Microsoft Access has been around for almost 30 years. It was originally released when there were few alternative database development tools in circulation. At a time when more mainstream solutions were far too costly for all but the largest of enterprise, Access addressed the needs of the smaller organisation. Compared with other database solutions, Access is in the top 3 database solutions used by businesses around the world [11% of businesses worldwide use Microsoft Access 1 ]. This is because Access has the benefit of being inexpensive and relatively easy to use. It provides a rich visual and intuitive developer experience, which makes it a welcoming solution to power users as well as the experienced developer community.

Unfortunately, the success of Access has also led to some bad press, particularly in Information Technology circles. Its ease-of-use permits inexperienced users to develop solutions which can be far from technically perfect. It is not uncommon to find circumstances where a well-meaning gifted user has developed an Access business solution which creates more problems that it solves. This is particularly troublesome where the business depends on the solution to administer some business critical processes. It is usually an organisation’s IT department which is left to resolve such issues – sometimes fire-fighting the shortcomings in order to keep a critical business process in motion. It’s important therefore to control the use of Access in the user community and ideally seek professional development help, particularly when using Access to develop the more critical business solutions.

It is the visual nature of the Access development environment which makes it such a productive development platform. Its intuitive table, form, query, and report design tools assist the developer with implementing comprehensive data integrity, strong validation and rich reporting. At all stages, the developer can preview their work and gain early appreciation of how the end product will look. The platform also supports the implementation of complex business logic through both its pre-defined functions (macros) and its powerful programming language (Visual Basic for Applications).

In the right hands, compared to other development platforms, Access’s ease-of-use supports more rapid development of solutions. This quicker development route, can permit developers to quickly provide users with usable skeleton or prototype previews of the solution – this helps users to understand at an early stage what the end product will look like. This can be of great value, particularly where the users accountable for defining requirements have never been involved-in, or are apprehensive of their role. During the development process and before delivery, the rapid development of queries in Access supports pre-delivery testing. Using the visual queries developers and testers can quickly sense-check data quality to improve the reliability of the software.

While Microsoft Access has its own somewhat limited built-in database engine (size and performance limits), its real abilities come to the fore when it leverages the capacity of powerful server based database platforms. Access has facilities which allow it to simultaneously connect to a wide range of on-premises and cloud resident 2 data sources. It can seamlessly connect to Microsoft’s SQL Server, SharePoint and even Excel workbooks. In addition it can also connect to wide range of non-Microsoft databases such as Oracle, My SQL & IBM’s DB2. The advantage of this approach is that you can exploit the rapid development experience of Access while also benefiting from the capacity, security and resilience of mainstream enterprise data storage. Simultaneous database connectivity makes Access a very useful tool for combining and interfacing data between multiple separate systems.

Microsoft Access - Selection of connectivity Options

When business cannot find a suitable off-the-shelf application to address its needs, Access is ideal for developing unique, one-off and small to medium scale back-office database solutions. Because Access has been around for quite a long time, it has become a very stable and easy-to-use development tool. So it is perfectly possible for non-developers to produce simple non business-critical applications. However for more complex business requirements, there are many software development companies will be able to provide professional Access development resource. Due to its maturity and widespread use, there also is a wealth of independent knowledge and expertise available from Access development contractors. One needs also to note that Access only runs on Microsoft PC’s, laptops or tablets with Microsoft Office or an Office 365 subscription. 3

Microsoft Access is not usually recommended for large-scale (50+ users) or geographically widespread solutions. This is because deployment of Access to a large number of user’s PC’s can be burdensome for IT departments to handle. Other tools such as .NET are more suited to large scale deployments. Having said that there are methods which professional developers will be able to employ to automatically deploy updates in such cases. In addition very long term (10 years +) deployments may require re-visiting when Microsoft next upgrade their Office suite. The last upgrade which affected Microsoft Access was Office 2010. In this case, Microsoft re-engineered the Access platform and the new application was not entirely backward compatible with previous versions. It is also important to be aware that Microsoft Access is not at all suitable as a web forms development tool. It cannot be accessed by browser over the internet. In this case, it is best to use a web content management solution or use a combination of development tools incorporating Java, PHP, HTML, XML along with the services of professional web developer resources.

Over the last 30 years, Microsoft has displayed strong support for continued development of Microsoft Access. The last major refurb was in Office 2010. This version saw significant technical improvements and improvements to both the user and developer experience. There were however some features removed, specifically around support for statistical graphs. Microsoft already has such functionality consolidated into it’s Excel and Power BI products which can be used to address the removal of graphs in Access. With Microsoft’s focus now firmly on the provision of Azure Cloud services, it is promoting Access as a tool which can easily connect to the cloud and continue to be a valuable business tool. As Microsoft Access in the top 3 databases used in businesses worldwide, it is likely to continue to be a valued element of Microsoft's future development offerings.

In conclusion, Microsoft Access is a stable, flexible and popular platform for businesses to create custom in-house database applications. It is a product which Microsoft has and continues to show strong commitment to. Compared to other database application development tools, it can quickly and less expensively provide a broad range of benefit to business processes. Provided it is used in a controlled manner, it can also be used by technically savvy users to develop simpler non-critical applications. For complex and business-critical application development, it is best to use the services of a professional developer. For businesses which already use Microsoft tools, it will integrate seamlessly with existing infrastructure, Microsoft applications and databases. It is not however suitable as a solution for large-scale, long-life or web facing business database solutions.

Notes:

1 Source: iDatalabs, Bellevue, WA, USA (Sample size 589,209 companies)

2 Typically a Microsoft Azure SQL Server, Azure SharePoint or Azure SQL database. Other options available via open database connectivity tools.

3 Microsoft Office Professional and most Office 365 subscriptions. Standalone MS Access licence is about £100