Microsoft Access: Is it still relevant in 2022? (1 Viewer)

[georg0307]

Dear All,

Microsoft Access: Is it still relevant in 2024? - 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 in 2024? - 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.

 

[Pat Hartman]

Two applications I created for large corporations became so mission critical that IT was charged with recreating them using web tools. Each were fully functioning interfaces with properly normalized schemas and each had taken less than three months initially and had gone through a year to two of occasional additions as I developed other apps. Both took over two years using teams of 3-5 developers. Either I'm that good or they're that bad or Access had more than a little to do with it. In one case, THEY were that bad. They took a properly normalized "survey" type schema with questions and categories and path logic and flattened the schema. They hard coded the text of the questions on the web pages so the users couldn't modify them and had to get IT help to add a period and divided the hundreds of questions into flat tables instead of a single table with a list so that each web page was tied to a single table. When I objected, I was informed that I was not the web developer so what did I know So, a normalized app that actually served MULTIPLE surveys with a single schema was crippled to do only ONE survey that totally depended on IT for support. I could go on and I have several times over this fiasco especially because it was MY tax dollars at work.

 

[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.

 

[Pat Hartman]

There is no doubt, that at some point, some Access applications need migrating onto a larger or more specific platform.

Not necessarily. UNLESS you need a web application and in that case, Citrix might work just fine.

Once you separate Access from using Jet/ACE as the BE, you are freed from the old limits of 255 users and 2G of data. Your user count is limited to the number of seat licenses the company has for its RDBMS. If it's installation can support 1000 concurrent users, then the Access FE supports 1000 concurrent users. Same with data. i've had a number of applications that used multi-million row tables successfully. The key is understanding how Access works with ODBC and ensuring you work with the server rather than against it. For example, you never bind a form to a table or a query with no criteria. That just acts like a straw sucking potentially hundreds of thousands of records from the server to the local PC. This will send your DBA into a rage and he may threaten to hurt you

Access' big weakness for working with enterprise wide applications are.
1. Security. Even delivered as an .accde, there is a risk that the FE can be cracked and information about the server exposed. It's not a huge risk and it doesn't expose data directly. One solution I have used is to never allow the user to know his password to the server-side database. He logs into the Access app by whatever credentials we use but the Access app logs into the server using the UserID but with a calculated password that only the DBA and the Access developer have access to the algorithm. There are a number of ways to do this but it takes the cooperation of your DBA. That isn't easy given the bad press "Access" gets from people who can't distinguish "Access" from Jet/ACE and their limitations.
2. Given its one file structure, it is difficult to run a project where multiple developers need to work at the same time. Difficult but not necessarily impossible.