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% ( ? )

 

[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!)

 

[cheekybuddha]

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

 

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

 

[Gasman]

@Gasman I hope you don't think this person knows what he is talking about.

I do not. I have already said I think the statement is BS here and also said so on that other site

Plus he is a she, and from what @Edgar_ mentioned appears to be a reasonable Excel user.
Then she has the cheek to ask if I would be prepared to tutor her.

 

[NauticalGent]

Morning Paul, your post comes at a very interesting time for me. The agency that contracts the company I work for has asked if there was a way to move the application from Access to a more "robust" (his words, not mine) web-based platform. His concerns are all the tired arguments that I will not recapitulate here.

We have a "summit" (again, his term) this month where I will do my best to assure him that at this time, with what we have, Access is the best tool for the job. The real challenge I have is that my immediate supervisor just recently retired from the same agency that contracts my company - and what is worse, she was a big-wig for that agency's IT department. In other words, his desire to move away from Access is music to her ears. She in enamored with this thing called Qlik - Qlik sucks.

But, another individual who works for the agency has been dabbling with a company/platform called ServiceNow. I did some research on it and it appears that it may be a viable replacement. The agency does have some site licensees and ServiceNow even offers a multitude of free courses (up to and including application developer) and will even provide you a free instance on their server for the coursework.

I am one week into it and I haven't gotten to the good stuff yet but so far, it looks promising. If interested, I will keep you informed with my progress...

 

[Gasman]

One thing people do not seem to take into account, is that whilst web access is very nice to have, (I use it for the Xero Accounting system), that it opens it up to everyone, not just the company people when internal, so that could be a huge problem, security wise, and there are plenty out there trying to exploit any crack in the security.

In my last place of work, we used Access over the net with a company that supplied an IFA package. I was constantly contacting the developer to report the odd bug or two and knowing Access helped to identify them. The developer always appreciated the feedback.
We just had access to a Windows server where our DB was available to us. I have no idea as to whether it was FE & BE or a single DB, as we only got the forms/reports, though thinking about that now, I would suspect FE/BE so the same FE could be used pointing to the respective client BE db's?