Microsoft Access: Is it still relevant in 2022?

[georg0307]

Dear All,

Microsoft Access: Is it still relevant? - Explore Alternatives

Is it worth learning Access's foibles to set up a personal database? We investigate the Best MS Access Alternatives - both Free & Paid.

www.comparitech.com

I love Ms Access,

After reading this article I was a little disconcerted.
Where I work, I have created many Access Databases that have become part of the work Iter.
In many ways it simplifies our work by making it faster and more efficient.

But is this true?

I need positive advice, also on how to proceed in the coming years.

Thank you very much in advance.

Microsoft Access: Is it still relevant? - Explore Alternatives

Is it worth learning Access's foibles to set up a personal database? We investigate the Best MS Access Alternatives - both Free & Paid.

www.comparitech.com

 

[Ranman256]

We use MANY databases daily. It is the workhorse for our company.
We will not be abandoning it.

 

[Minty]

That is not a very well written article.
It looks like it has been edited/hashed out from when Microsoft announced they were dropping the ADP/Web-based projects from Access, which if memory serves me correctly was nigh on 10 years ago.

In fact, after reading the post replies, Richard Rost commented on it 2 years ago as being a load of rubbish.

 

[The_Doc_Man]

The dilemma is that, for all its shortcomings, Access is still better than its competitors. I don't know that I can predict the future because I appear to have mislaid my crystal ball, but several projects intended to replace Access (even some by Microsoft) have fallen flat due to their introduction of other problems.

The article contained at least some chimeras. For instance, the "proprietary format" comment is a "so what?" case. Sybase, Oracle, and SQL Server files are not directly interchangeable either. I helped in a project to convert from a now-defunct DB called ShareBase to an ORACLE DB. Had to reverse-engineer a backup file to do it, but we converted a 240+ table database over one weekend, got everything up and running. The point here is that the accusation of format issues is common to all of the big boys.

Another factor is that Access is now doing things far and above its original intention. It was intended as a desktop and small business tool. It just proved to be more than that. The discussion about interfaces is significant, too, since it glosses over the fact that you can develop with native Access and then upgrade the back end when your data outgrows Access capacity.

 

[NauticalGent]

Some light reading...

Microsoft Access's Multiple Personality Disorder

The dual nature of Access–and the ignorance of this reality–is a major source of the shade that "real programmers" throw its way.

nolongerset.com

 

[Cronk]

Access has its niche. It's the nimble pickup truck but can't handle Mac truck situations like an international booking flight booking system but can bring great productivity to small/medium work groups.

I used to try to estimate the productivity in every system I developed. Probably the best one was a system that where 5 people spent 2 weeks every month on, was replaced with a system that did more, and with increased accuracy in running unattended for 30 minutes every month.

I've found the IT management in larger organizations both government and business hate Access and do everything they can to prevent the use of Access. This is because someone with varying Access skill, starts working in an area and recognizes the gain to the work group by developing an Access system. After refinement and issue fixing, that person leaves which exposes the organizations lack of skill to support Access.

I'll bet Ranman (@2) works for a smaller organization where management recognizes the gains from Access systems

 

[Minty]

We predominantly build Access FE systems on the back of Azure SQL databases.
Some of our clients are 2-man bands, some are multinationals, and most are somewhere between the two.

Access is a fantastic FE RAD tool. Add in the ability to leverage SQL / enterprise-level backend processing as well, you can build some very complex tools with it.
You can add new functionality in a matter of hours or a few days to most well-written databases without a ton of development work.

It interfaces with multiple data sources and can create PDF reports, Emails, Excel reports, Powerpoint Slides and other outputs relatively simply.

I know of nothing else on the market that has that level of usefulness coupled with the speed of development.

 

[NauticalGent]

I know of nothing else on the market that has that level of usefulness coupled with the speed of development.

Exactly. No one disagrees that Access is NOT perfect and could use a lot of love - problem is, there is NOTHING that can replace it.

 

[NauticalGent]

I've found the IT management in larger organizations both government and business hate Access and do everything they can to prevent the use of Access.

I work for the largest agency in the US Department of Defense: The Defense Logistics Agency. There are hundreds of Access applications that we "Citizen Developers" have written to bridge the gap between the Enterprise Level Program(s) of Record - usually Oracle/SQL/SAP - and the users daily reporting requirements.

They used to use individual Excel spreadsheets but the middle and upper management needed something more dynamic, flexible and most importantly, real-time - enter Access. The J6 (IT department) HATES it and has done everything it can to force us to get rid of them. The good thing is that there at lot of high-ranking miltitary officers and civilian employees that will not allow it until there is a viable replacement.

It has been 5 years so far and the J6 even brought in some MS technicians to educate us on PowerApps/Dataverse. There were given a simple app to duplicate and they failed miserably. And what is even more telling is that even IF they had been successful, the added cost to provide the means to all the users was prohibitive. Why pay the cost when an even better solution is already bundled with the suite on every computer already?

Yep, MS is a victim of it's own success and it is amusing as hell watching them try to solve this puzzle.

 

[gemma-the-husky]

The most ridiculous thing is that Excel is allowed/even encouraged to be used throughout organisations, whereas Access is not.

 

[jdraw]

See this recent video by Daniel Pineault

Microsoft Access - What You Need To Know Before You Get Started​

 

[CJ_London]

looks like a fair assessment to me

 

[tmyers]

I agree with Gemma that it is horrifying that companies encouraged to be the main tool used even in a multi-user environment. My company is one and I have been learning my way through Access the last few years and have made a few simple apps to remove the need for several Excel sheets and it has been massively well received here at my location due to the increase in efficiency and the tools I have been able to build. My IT/Help Desk however HATES that we use it and have actually begun restricting it to some extent so not everyone can use it.

 

[Cotswold]

I've been looking for an alternative to Access for 20 years. The only thing I've managed to do is waste time.

Also agree with GTH #10. In fact of all the spreadsheets I've checked virtually all have had errors. Some fairly serious. Mainly because they aren't usually checked and are written by people who do not test their work. But presume calcs are all correct.

 

[gemma-the-husky]

Regarding the Daniel Pineault article referred to by @jdraw , the one point I would say reference to code, is that in my experience you need a lot of code in a database. A lot of the code is defensive to stop people entering poor data, and to prevent users doing whatever they want with the data.

 

[jdraw]

Dave,
I agree that validating data entry and keeping "users" from experimenting and/or damaging an operational data base does take more code. But as Daniel points out, Access is a 2 part animal- dbms (jet/ace) and a rapid application tool that can interface with several other databases. I think his assessment from his years of working with Access and other software, and the many articles on his website is a solid review and great reference. Definitely more bugs are appearing unexpectedly, and it seems Microsoft is fully occupied in cloud efforts and not on Access. It is a very versatile front end and a proven multiuser desktop solution even with some shortcomings.

 

[The_Doc_Man]

I work for the largest agency in the US Department of Defense: The Defense Logistics Agency. There are hundreds of Access applications that we "Citizen Developers" have written to bridge the gap between the Enterprise Level Program(s) of Record - usually Oracle/SQL/SAP - and the users daily reporting requirements.

They used to use individual Excel spreadsheets but the middle and upper management needed something more dynamic, flexible and most importantly, real-time - enter Access. The J6 (IT department) HATES it and has done everything it can to force us to get rid of them. The good thing is that there at lot of high-ranking miltitary officers and civilian employees that will not allow it until there is a viable replacement.

It has been 5 years so far and the J6 even brought in some MS technicians to educate us on PowerApps/Dataverse. There were given a simple app to duplicate and they failed miserably. And what is even more telling is that even IF they had been successful, the added cost to provide the means to all the users was prohibitive. Why pay the cost when an even better solution is already bundled with the suite on every computer already?

Yep, MS is a victim of it's own success and it is amusing as hell watching them try to solve this puzzle.

Before I retired I worked for the U.S. Navy's SPAWAR division, which was among other things their hi-tech branch for data processing. (They only had 5 branches and our function didn't fit in with any of the other branches.) I took a project to streamline reporting issues for my boss.

At the time we used Excel to report security compliance status for a growing number of servers and projects and a relentless stream of security notices. At some point as the number of projects and servers grew, the time it took to finish the "weekly roll-up" even with Excel spreadsheets was too much for my boss and he could not create a weekly report in 40 hours - but as a civilian contractor was not authorized for frequent overtime. And the detailed reporting requirements COULD not go away because they were mandated by Congress. Yep, sometimes you have to deal with an act of Congress in that line of work, and there WAS no way of getting a waiver.

Though it took me more than a couple of months to get it working, the Access app that I developed using RAD methods dropped his 40-hour weeks of overtime-based reporting to less than 10, which meant he could be our boss again and not have to "call in" any legit supervisory issues. At least two other times, a Civil Service person with ORACLE knowledge tried to develop something else but the interface was killing him and he was an "anti-Access" type who didn't understand databases as well as he thought. Just as I retired, a new commercial product became available for status tracking but it wouldn't do everything that they wanted either because it was Windows-oriented. We had at least 9 flavors of UNIX and a couple of other "mainframe" cases that were neither Windows nor UNIX.

They also looked into getting it tracked with Solar Winds "Orion" software. Those of you who remember that name and wonder why you do might remember that somehow Russian hackers had penetrated the core and were able to siphon data from those machines. I had retired by then and was glad to no longer be in the middle of that nightmare.

 

[GSTEEL320]

This is all refreshing to hear, and very much mirrors my own experience. I agree with the above sentiments, whole-heartedly.

The other factor which makes Access ridiculously attractive to a non IT professional like me, is that I can build a proof-of-concept BE and FE in Access, with the required logic and the required GUI interaction. This ensures that the guts of the project are understood at the very conception, saving time and money.

There is no doubt, that at some point, some Access applications need migrating onto a larger or more specific platform. However, rather than create an 18,000 page specification document, followed by a 18 month consultation meeting, I can get the whole lot pretty much done with the Access model. It allows everyone to mark-to-market where I need to be with the commercial requirements of the project.

I will also say, Access is also an excellent bridge between SQL and Excel, in so far as you can take a 15 stage calculation to get to what you actually need, much more transparently. Normally, in excel, you would attribute each stage to a new column and break down the calculation slowly, left to right. This can be done in Access quite readily, whereby SQL, its less intuitive. This provides me with excel functionality and ease, with SQL type "big data" management. Once it looks good in Access, I can just copy and paste most of the "SQL view" of the query into a SQL view.

 

[Lightwave]

I see IT departments disliking MS Access applications often but consider this an illogical argument.

What they actually dislike is the fact that there is only a single developer and they don't know how the business logic works. So if that single developer goes no one knows how to fix or adjust the system. Its not really an argument against the technology.

This is not a problem with the method of deployment and would be just as much an issue if the solution was implemented with the latest greatest web technology and your given enterprise grade database engine. As Pat points out simply transferring the application to another platform isn't necessarily a solution.

Citizen Domain developers are super hard to replace irrespective of the technology they use.
They know the domain better than any new developer..
They are super motivated to make an optimal solution..
They develop over years not months..

MS Access just happens to be the way they have delivered their solutions. In fact MS Access is absolutely an optimum - microsoft is a stable company (it makes plenty of money) and Access has a huge user base. Visual Basic is possibly the most popular language in the world with infinite articles on every aspect of it built over decades and there are probably more available visual basic programmers than possibly any other language. Migrating from previous versions to the latest version of Access is nearly always painless..

What's not sustainable about that????

Some IT directors really need to get their logic sorted and stop being so short sighted. By all means force the backend into sql express / postgress / oracle (and appoint a dba to double down on solid backups) but if there's no outside vendor providing an alternate UI there is no legitimate reason to deny MS Access. Nice UI design is really really difficult and MS Access is amazing at UI design it still does the nicest master details forms that I have ever seen and its possible to do amazing colour work and conditional formatting in Access. Master details design for the web by comparison is really poor.

And as for difficulty in multiple developers on a single project -- that is a big problem with all platforms I've used.

 

[GPGeorge]

In my experience, one of the biggest contributing factors to IT's well known dislike of Access is, ironically, it's very accessibility to "citizen" developers who have no idea what they are doing. We have all been there, or most of us. We see Access, we love Access, we build a garbage relational database application with Access. And then, if we are lucky, we get a chance to rebuild it correctly.

Too many times, though, IT gets called in to rescue the garbage application when it's already become mission critical. And they see the garbage application, not as a product of an inexperienced citizen developer, but as a "typical Access database" and rail against letting anyone use Access because it leads to that sort of thing.

In the one place where I didn't run into that, there was a long-term Access developer working as a consultant. He'd been there for many years as a consultant. His job was to take over those Access relational database applications and bring them up to standard. He had no desire to be hired full-time for some reason I never really got out of him. Nonetheless, that IT department didn't wash their hands of such databases; they turned them over to him to rescue.

Ironically, that was a quasi-government agency, where one normally expects layers of bureaucracy to prevent progress. Maybe that's why this developer preferred to remain a consultant, come to think of it.