Solved Your opinions please

[Gasman]

Hi everyone,
A member on another site posted a comment

But Access is flakey and really only supports VBA, an archaic and error-prone programing language, for macros and user-defined functions (UDFs)

Now they joined that site back in 2012.
They then posted

Interesting. The only reason I chose Access is because I already have an Office 365 subscription, so I already have Access and it's free.

I have looked into other databases from time to time, especially ones that allow me the option of having multiple users access the database. Is there a database that is better than Access (pretty low standard), is well supported (user forum), and doesn't cost a fortune? It would be nice if it is free, but I am happy to pay for quality and support.

Now I am not as experienced as a good few of you here, but it served me well when I needed to use it in work. It serves me well personally still to this day.
I fully expect that a good few of you have also created robust systems which your clients rely on heavily.

So I am interested in your views/opinions of the above.

For myself, I believe it is pure BS. and for what it is worth, this user is considering Python in it's place?

 

[gemma-the-husky]

The trouble is, everyone and their dog writes excel
Spreadsheets, some ok, some not. They never use code, and businesses make them mission critical documents.

The same users think they can build a database in the same way as they build their spreadsheets with no conception of the completely different paradigm.

An access table looks like a spreadsheet, but users need to grasp there's no next row, or previous row to help them, and to consider their data as sets of records is actually hard. So to sit down and "hack" a database (hack in the sense of build as you go) just isn't possible. You need to spend time designing the data structure before you start.

The set of excel users who can develop access databases is very small.

And there is no "easier" database. Access is as easy as it comes once you put in the 1000 hours experience, as well as having a logical mind, some programming understanding, and for developing "work" databases, a decent understanding of business practices.

 

[Gasman]

I did advise that Access is capable of having multiple users access the database

 

[Josef P.]

VBA, an archaic and error-prone programing language

Is it the language or the programmer using the languages?

I myself prefer to program in C# because it is more "powerful".
For example, I would sometimes prefer strong typing or a better interface implementation in VBA. However, this is not an argument against VBA when you consider how widely JavaScript is used, for example.

But you can also live the Clean Code idea with VBA.
Perhaps not as elegant as in C#, but a few SOLID principles can also be implemented in VBA.
There is also no reason not to test VBA code automatically and to use source code management (Git & Co.) to maintain quality.

/edit:

.. for macros and user-defined functions (UDFs)

Perhaps someone should take a closer look at VBA if they only know "(Excel?) macros" and functions.

 

[DickyP]

My experience with spreadsheets in general and Excel in particular, is that they are business critical in many organization, but have no quality assurance whatsoever. The are, I am told my a Management Accountant friend of mine, the biggest single source of CAB (Computer Aided Bankruptcy).

Despite Microsoft's best efforts Access remains a simple, robust, and easy to use product. It has always seemed to me that the majority (a view reinforced by this forum) of Access problems is that databases are unplanned and not-designed in any systematic way. This is exacerbated by the modern trend away from documentation, which means that users just start building and coding before any serious analysis of what they want to do, and how they want to do it.

Once the comprehensive documentation ceased to be part of the Access product, I always recommended the O'Reilly book 'Access Database Design and Programming' by Steve Roman as a starting point. This, however, was published in 1997 and there is not an equivalent available now that is as complete and comprehensive in just 244 pages,

The problem with on-line documentation is that you need to know what it is you don't know and need to look up.

 

[DickyP]

I myself prefer to program in C# because it is more "powerful".
For example, I would sometimes prefer strong typing or a better interface implementation in VBA. However, this is not an argument against VBA when you consider how widely JavaScript is used, for example.

Just remember that Bill Gates described C# as VB for snobs.

Personally, despite decades of using C, C++, Javascript, and C#, I much prefer Basic as it is far easier to write readable code, whereas the C coding paradigm leads so easily to unmaintainable 'write only' code.

 

[Gasman]

I read the macros to actually be Access macros, not what Excel calls Subs/Functions.

 

[Edgar_]

I've provided solutions for the member you're talking about on MrExcel. As far as I could tell, that person is just trying to find a reliable database software. No idea what background they have, but they're probably read quite a few bad opinions about Access/VBA already. I don't think that person has ever tried to make anything remotely robust, but we have to be honest that Access is not secure, and that's a big let down for a lot of developers. In areas where security does not need to be too tough, Access is still a great option until you need a lot of responsivity and/or pretty GUIs.

 

[gemma-the-husky]

Yes it's not secure, but you can still use an access front end with a secure backend, but then you need IT people to help manage the BE instance. (or you need more skills)

 

[cheekybuddha]

Access is still a great option until you need a lot of responsivity and/or pretty GUIs.

And as long as you are happy being tied to Windows, and also a reliable LAN.

 

[Isaac]

or myself, I believe it is pure BS

For myself too. Access is a development tool. Error prone made me laugh out loud. Have that person start trying to write in anything else they're not familiar with and let's watch the errors pile up LOL

I like David's comment about the reliable LAN though.............this is a big deal as a majority of people are using Wifi, not a lan in the first place, as it is 2024! This is a bit of a problem for the Access world..

 

[gemma-the-husky]

My stuff isn't error prone. I have very few service calls, touch wood.

 

[Edgar_]

And as long as you are happy being tied to Windows, and also a reliable LAN.

Oh yes, those things too.

Anyway, heres' a few results from stack overflow's survey.

Most used languages. VBA went from 3.55% to 3.7%. Increased 0.15% (whoa)

Most desired languages, VBA went from 1.1% to 1.3%. Increased 0.2% (yay)

Most used databases. Went from 4.25% to 4.2%. (Oh no)

Most desired databases. Access went from 1.29% to 1.4% ( ? )

 

[Pat Hartman]

@Gasman I hope you don't think this person knows what he is talking about. He is clearly ignorant when it comes to what Access is and what it is best used for. He doesn't know the difference between the Access RAD tool and Jet and ACE the database engines. He is also making the mistake of assuming that every tool should be able to do everything that any other tool can do. Can his car ride on railroad tracks? Can his car pull an 18-wheeler? Can his car cross an ocean under its own power? No? Does that make his car a piece of crap?

Access is not a tool for all uses but it is the best damn small development platform ever developed. The fact that complete novices can actually create something useful says a lot about its ease of use. Most professionals think it is too easy and too forgiving and would prefer that it be tightened up somewhat to prevent newbees from making such total messes. It is these messes that give "Access" such a bad name in addition to the general lack of ability for other IT users to distinguish between Access the RAD tool and Jet/ACE the database engines. Pretty much every bad thing you read about "Access" is actually about Jet/ACE and has nothing to do with Access the RAD tool.

Access serves as a platform for developing a certain class of applications. If your needs fit within its capabilities, there is nothing even close to it available today. Clearly you cannot use Access to develop web applications so if you insist on a web app, find a different tool. If you need something multi-user that can work over a LAN or you have a Citrix environment which allows WAN use, then Access is a potential platform. Not guaranteed but potential.

I cut my teeth developing very large, multi-year, multi-million dollar COBOL/CICS/IMS/DB2 applications that had potentially thousands of users (my insurance and banking clients). Most of those applications could have been developed quite easily (and significantly faster) using Access. The one single issue is that Access doesn't have a multi-DEVELOPER interface. So, we have a time/people problem. Can one person develop the application in a couple of months? Access can do the job (with the help of Citrix if necessary) If it would take more than one person to develop the project in a reasonably time frame, then I wouldn't even consider Access.

Once you separate Access from Jet/ACE and once you get past the WAN problem using Citrix and then past the multi-developer issues, Access is powerful enough to have been used to create every single mainframe application I ever designed and built. Even the ones with thousands of users and multiple multi-million row tables. Once your BE is SQL Server, you are not limited to 255 hard or ~ 50 soft concurrent users. You are limited by the number of seat licenses you have for SQL Server.

The final limitation is with the .accdb itself. Even compiled it isn't secure and so would not be a wise choice for a shrink wrapped product sold to the public. But, if your clients are big corporations and you have a good contract, you are safe enough to distribute compiled databases as .accdr's.

Would I develop a billing system for SNET (Southern New England Telephone - now part of ATT) using Access? probably not. Who wants to fight city hall. But I could and that is the point. There is nothing that application did that Access couldn't do very easily, including processing many millions of individual phone calls to calculate a bill.

 

[GPGeorge]

I don't think I've ever read a more on-point statement regarding the role of Access in the late 20th and early 21st centuries.

 

[cheekybuddha]

I don't think I've ever read a more on-point statement regarding the role of Access in the late 20th and early 21st centuries.

The unfortunate situation for Access is that Windows-only and wired LAN based environments are becoming rarer and rarer in this part of the 21st century. (in fact, they are virtually extinct!)

 

[Pat Hartman]

The unfortunate situation for Access is that Windows-only and wired LAN based environments are becoming rarer and rarer in this part of the 21st century. (in fact, they are virtually extinct!)

You are aware that Citrix solves that problem, right? In fact, once companies go to the "cloud", Citrix becomes the de facto desktop rather than the one they had when their office computer was connected to the LAN so the "cloud" is even easier to implement Access apps with.

Perhaps I should also have included "running on a phone" as a limitation of Access. There is a class of applications that is suitable for Access and those that need to use only a phone don't fit. Because of Citrix, you can make some functionality available to phone users though. You just need to design the screens very carefully and limit the data that is displayed. But, they are great for doing quick lookups for customer data when you're out on the road.

 

[cheekybuddha]

Not everyone is going to pay for citrix just so they can keep using Access

 

[Pat Hartman]

Not everyone is going to pay for citrix just so they can keep using Access

That isn't what I said. My clients are using Citrix to publish their web hosted desktops. It has nothing to do with Access. All it means is that it gives Access new life because the clients are already using Citrix once they move their operation to the cloud so having Access apps is no big deal and no extra cost.

 

[DickyP]

A few random thoughts.

Perhaps I'm missing the point here but WiFi is just another way to connect to a LAN. Even at a personal level I have a mapped drive on my laptop which is wirelessly connected to my network. People seem to somehow equate WiFi with just being web based/cloud based.

Also it is typically the medium sized organization that are going WiFi only: small users in a single office have the ability to be both with just a couple of cheap cables and large users with main frame systems will continue to use wired LANs for efficiency.

The comment about being Windows based only is valid one , but we should not forget the most popular DBMS on the Mac remains Access running on a Windows emulator.

As for multi user development - it's simple enough but requires more rigorous co-ordination than other platform. Not as easy as it was in VSS days, but as with any multi developer project it is a management problem not a software problem.

I would love a multi platform replacement for Access but I don't see it happening.

And lastly I get irked by the assumption (especially prevalent on this site) that the only relevant BE alternative for enhancing Access based storage is SQL Server. My personal favourite was always Oracle as it was so much more powerful than SQL Server and the administration of the database was much simpler and more coherent than SQL Server. I have to admit that my experience is now several years out of date.

To misquote Monty Python "Access (I'm) not dead yet".