The question I get a lot is if Access is still relevant to
build applications in a corporate environment.
I reached to my LinkedIn group MS Access VBA Developers and with all the feedback and suggestions I wrote this article (see resources at the end).
I understand that my long-time experience working with Access (almost 25 years) might cloud my judgment but still, I think there is a place for desktop-based applications build in any company.
I will begin with the reasons IT doesn't like Access (but they should).
1. It grew.
It's common to use Access for small tasks even if larger, more robust systems
(like OutSystems) are available. On some cases, an Access application grows beyond
its original purpose and can become vital to the organisation.
At this point IT faces a challenge – revamp Access database or upgrade,
both requiring a lot of work.
Some people just think that the original developer should have predicted the future and use a "more sophisticated platform". I think this attitude is counterproductive. Successful databases evolve over time.
If an Access database works its way up to the department or enterprise level, celebrate its triumph.
Some IT people dislike that Access, being part of Office, is available to most of the users
in the office. Then databases are created, and IT must begin support.
In an effort to be proactive, some IT departments have banned Access from their organisation.
Unfortunately, that drastic step doesn’t benefit end users. They'll use Excel, or worse,
call IT when they need a database, even if it's something small.
The real solution is to understand how Access fits into an organisation.
3. Access isn't a professional database and VBA isn't a pure language.
Access has the reputation of being a toy and not a real database. It's undeserved.
Access uses Visual Basic for Applications (VBA) as its development language. Being a subset of Visual Basic (VB), also considered a minor programming language, so many IT people believe Access has less value than VB.
Professionals have many tools and they apply them efficiently, if they are smart. VBA used appropriately is a powerful language and can be used across all Office programs.
4. File-server applications are inferior.
Technically, Access is a file-server application and not a client-server application.
This means that Access does all its processing on one server.
Client-server applications process on both sides of the network.
File-server applications aren't inferior, they are different. Used appropriately, they're efficient and capable, in certain situations, of outperforming client-server applications.
5. Access is hard to deploy.
Access applications need Access (even if it's only the runtime),
just as an Excel spreadsheet requires Excel. Still, we will run into these problems with almost
any application, not just Access.
If we want to maintain Access and avoid deployment issues, we should consider turning Access databases in Web-based applications (for example in a Citrix environment).
6. Poor security. This one it's difficult to counter argument. While most Access developers swear by its security model, Access security simply isn't as robust as we might need. We can password-protect an encrypt data, but Access doesn't offer the same level of security as SQL Server. This said, in 25 years I never had any problems with Access security.
Access applications build properly (I try) and using remarks along the code (I do), are valuable assets for any organisation.
Access applications are cheaper to build and specially to maintain than more sophisticated productions of SQL Server, Oracle or OutSystems. That doesn't mean that Access being cheaper is the best database for every project, but other solutions must convince Management that a high-end system and ongoing support are necessary. Otherwise, Access just might be the right solution for the job.
In terms of applications, on one end of the spectrum is something like Excel, which is ubiquitous and widely used in the business world. At the other end are high end server databases and platforms, like SQL Server, Oracle and OutSystems.
The main problem is that Excel is not a true relational database. The main job of a database is to allow us to ask questions to the data. The main job of a spreadsheet is to report the data.
High end databases and platforms, on the other hand, can handle millions of records have robust security features. The two main issues are their steep learning curve and their high cost.
Access fits comfortably in the middle:
- Costs are more reasonable, compared with other systems.
- It is a true relational database management system
- It contains many of the components that many other solutions don't have, such as forms and reports.
- It is possible to write down pretty sophisticated code in VBA to get Access to do everything you need it.
- Being a Microsoft product and part of Office, its ubiquity beats any other desktop database out there.
When formulating the database/application strategy of an organisation, it's helpful to think of individual databases evolving over time. Healthy database applications are not just created once but change and grow. Bad one's stop being used, and sometimes even good ones disappear because their purpose changes.
In a business environment, lot of "databases" are created in Excel spreadsheets each year, but only a small percentage "graduate" to the next level – Microsoft Access. The same way, only a small percentage of Access applications are upsized to a more sophisticated solution. In the meantime, a huge number of database needs are solved completely by Access. Access is simply the best at what it does.
An IT Manager needs to understand and use Access tactically and anticipated that some Access applications will migrate over time. This is not an indictment on Access, but rather the natural process of database evolution, as an organisation needs change.
Even when Access applications evolve to another platform, Access can scale to SQL Server while preserving the application development investment. Most of the features developed in Access can be rolled into new platform guaranteeing the success of the new system.
The savvy IT manager knows when Access is effective and when it's not. If it can be done in Access, the ROI is superior to alternate technologies. Taking advantage of the strengths of Access gives our organisation a significant competitive advantage both financially and in response to user, market and customer conditions.
Some databases are critical to the survival of an organisation while others are simply a quick way to do data analysis. Below the main reasons for using databases in all levels of an organisation:
These are mission critical, that the organisation needs for its survival. Examples include accounting systems. CRM systems, ERP systems or other critical systems vital to organisations.
Not to be built in Access.
Applications build for departments are less critical for the survival of the organisation. Although these may still include important data center applications, other applications may be managed by local IT.
Can be built in Access.
These applications focus on the needs of a smaller group of people working together. These applications can change rapidly to meet the needs and challenges the workgroup faces.
Tend to be PC based, involving professional developers, but also in some instances, created by power users and non-developers. Often retrieve data from data center systems but never send data back.
Should be built in Access.
On individual computers, some people create their own databases in Excel or Access. Tend to be single user applications that have normally short life spans. Their purpose is to simplify the work of individuals or small groups who created it.
Should be create in Excel or if necessary in Access.
Maximizing ROI is more critical than ever. Management demands tangible results for the investments in database application development. Choosing the technology and approach for each level in our organisation is critical to maximising ROI.
Organisations face a variety of database challenges. No tool solves every issue.
We have several tools and approaches available, each with their own strengths
and weaknesses. Some manage large amounts of data in a structured and secure manner.
Other tools manage small amount of data in a (un)structure, minimally secure,
yet flexible manner.
Depending of the objectives, one tool may be superior to the other.
This article is based on:
- end -
Comments, suggestions and questions are always welcome - firstname.lastname@example.org.