Is Access the right solution?

[zeroaccess]

So far, in the time I have worked with Access as a dabbler and later as a user for corporate and government functionality, I have never seen anything in the same price-class that had quite the utility of Access. Particularly considering the added feature of having lots of "hooks" into Excel, Word, Outlook, and other Office-class apps. Every time someone comes up with an alternative, it is sorely deficient in some way. Though Access is clunky and has many limitations when not using an active SQL back-end, nonetheless I've never seen anything that could beat it overall.

It's possible that many of those interactions with other Office apps (and even non-MS apps) can be handled by Flows in Power Automate:

That may very well be the intention of Microsoft as you work within the MS 365 ecosystem. Including Power Apps.

I agree Access doesn't have a replacement but I think us devs are going to need to learn some new tricks to keep up with these developments.

 

[Isaac]

Yes @zeroaccess , According to microsoft "the way" to create relationships in Sharepoint is by using Lookup columns, but I never do that...

 

[Isaac]

Well, coincidentally to this thread, my company is basically opening up the door to full PowerApps environment for us. I've taken the first few tutorials on creating canvas apps and have to say, I'm less anguished now than I first was upon learning that a "no-code" tool might become popular.

I'm a strong proponent of Access, but where I work, we have a global userbase and we generally end up with at least some of our users who are on an actual different network than we are. Hence, the standard Access setup - FE on each user's local, and BE as (say) an Access file on a shared network folder.........Won't work.

Add to that, like most large companies during COVID, we're almost 100% working from home on VPN (and surely most people use WI-FI). So then you have the oft-quoted warning that Access gives a lot of trouble in that scenario - yet that is, and will be, our scenario.

Add to that, my department can't get access to a SQL Server (long story there....)

What it all comes down to is two options: Access FE Sharepoint BE, (which I'm beginning to advance--but even then, there isn't much auto-distribution/auto versioning options available), OR, PowerApps.

Learning about PowerApps Dataverse capabilities excites me. There's a back end I can create with constraints of all kinds to enforce business rules - and has no indecent exposure (i.e., insecure exposure) to end users, as Sharepoint lists would, but rather, will ONLY be available through my PowerApp. We also will have Power Automate and Flow, which assures me I can freely schedule cloud-hosted batch jobs, notifications, emails, and other workflows.

If I lived in my perfect world, I'd crawl in my Access-and-SQL-Server world and stay there, making it my lifetime study (as many people have proven can easily be a lifetime study, just ask any SQL Server professional). But given that I am forced out, I must say, I am enjoying learning about the immense possibilities PowerApps opens up.

It's basically all about configurable properties with formula-type expressions that contain 5, 10, even 20 parameters and can "do" things just like code. Granted, there will be times when I wish I could write free flowing code - but for now, so far this has been a fairly pleasurable learning experience.

Like any experienced developer, I worry about the fast-and-sloppy environment that no-code tools tend to promote, people deploying apps that just aren't reliable and that they don't understand. But I choose to take the optimistic view: I can study PowerApps carefully, academically, from the ground up, and be the 'stable' force, and resource, that encourages and assists quality development. I don't have much of a choice. And I'm starting to enjoy it just a bit

 

[zeroaccess]

Well, coincidentally to this thread, my company is basically opening up the door to full PowerApps environment for us. I've taken the first few tutorials on creating canvas apps and have to say, I'm less anguished now than I first was upon learning that a "no-code" tool might become popular.

It certainly looks promising, but at the same time I worry as developers who really know Access, we may find some limitations. I have to be fair because I haven't tried it yet.

I'm a strong proponent of Access, but where I work, we have a global userbase and we generally end up with at least some of our users who are on an actual different network than we are. Hence, the standard Access setup - FE on each user's local, and BE as (say) an Access file on a shared network folder.........Won't work.

SQL Server.

Add to that, like most large companies during COVID, we're almost 100% working from home on VPN (and surely most people use WI-FI). So then you have the oft-quoted warning that Access gives a lot of trouble in that scenario - yet that is, and will be, our scenario.

Front-end on each (work at home) computer and back-end in SQL Server.

Add to that, my department can't get access to a SQL Server (long story there....)

That is a problem. MySQL? PostgreSQL? Amazon RDS? Microsoft Azure? Of course those vary wildly in cost; choose accordingly.

What it all comes down to is two options: Access FE Sharepoint BE, (which I'm beginning to advance--but even then, there isn't much auto-distribution/auto versioning options available), OR, PowerApps.

Watching this video helped me understand better where Dataverse and Power Apps fits in. Link starts at 6:45 with a little back story regarding Access then gets to where Dataverse fits in at 11:00.

 

[Isaac]

Yeah - like I said, I've exhausted all those handy dandy back end options. Unfortunately in my current job I don't work in I.T. or even Systems / Development, but rather am embedded in a business unit and am considered something along the lines of what you might call a glorified business analyst who just happens to be technical. We have to go through XYZ approvals to get any new technology, and they really won't budge. You either contract with the I.T. dept to create an enterprise-level software system for you, or you use local Office type of tools. Until now.....Or, PowerApps.

 

[Lightwave]

Its a bit of a problem if you can't get authorisation for a proper backend like mysql / sql server or postgres.. I'm probably not going to take on projects that won't let me get the back end into one of these three. And most of the new tools are starting to drop support for MS Access backends so that is going to be a big issue for you maybe not now but in the future. Once you have your backend into one of these databases you have a lot of choice in the UI tools that you can use which is just great. I completly understand your predicament though where I work they won't give me priviledges to servers or proper databases so everything ends up a bit of a hack.

Thus in my spare time I experiment with cloud platforms and cloud tools.

My research suggests there are more and more generators for web UIs both expensive and cheap but pretty much all require you have the data in Mysql / Postgres or SQL Server (most work with Oracle as well but don't go there if you don't have to). I like the ones that generate non proprietary code that can be hosted anywhere... and I am moving towards focusing on either PHP or ASP.net

Two cheap options that fit that bill are

1)PHPRad (which is windows based and is RESPONSIVE) Link to PHPRad site
2)NuBuilder (MySQL only but SQL Server support is coming / Unix and windows support - But is NOT RESPONSIVE) Nubuilder site

I would recommend you choose one and just try and implement something/ anything. You will quickly start to see issues you thought were going to be bad aren't a problem but other things are a major problem which you probably never saw coming..!

Quite often configuration at the start is the biggest issue.

A good option is to keep the Access front end for desktop users and have a separate web responsive setup for more remote / mobile users... This works well if you have an Access front end that you have sunk years of work into and is pretty optomised already. Access still does Master Details forms better than web uis.

I've also had a lot of success with ASPrunner.net which is here - https://xlinesoft.com/index.htm - but it is more expensive but for me was worthwhile for what it gives me.. easy 2FA authenication - a basic WYSWIG designer - support for all database backends and easy install on MS Azure.

 

[Lightwave]

Like any experienced developer, I worry about the fast-and-sloppy environment that no-code tools tend to promote, people deploying apps that just aren't reliable and that they don't understand. But I choose to take the optimistic view: I can study PowerApps carefully, academically, from the ground up, and be the 'stable' force, and resource, that encourages and assists quality development. I don't have much of a choice. And I'm starting to enjoy it just a bit

If someone uses a tool that improves what they are doing over what they did before it is a benefit. The fact that it is sloppy or you would have implemented a better option may not be as relevant because you cannot be there to implement that solution. People critisize MS Access for the same reasons but I don't accept their arguments as they usually cannot suggest a legitimate alternative and aren't on the ground being the person responsible for delivery...

 

[Isaac]

If someone uses a tool that improves what they are doing over what they did before it is a benefit.

I would agree with that statement, but only if it were amended as follows:

- if someone uses a tool that improves what they are doing over what they did before, and doesn't introduce an even greater risk or other problems that might offset the benefit, then it is a net benefit.



The problem with low- or no-code tools is that they are often used by people who simply can't or won't approach the development paradigm thoroughly and critically enough to eliminate the reality that they are also, along with their newfound toy, introducing significant amounts of risk to the organization. It is quite possible to introduce a tool that comes with 10 units of efficiency, and 15 units of un-appreciated risk. I know because I've been the person who did that to an organization at least once or more when I was early learning.

 

[Pat Hartman]

The #1 most important thing in the development of an application is keeping out bad data. If PowerApps does not provide methods for that to happen, it is not the right tool.

I am mostly retired so I haven't even played around with Power Apps but it seems to me that PowerApps and Access sharing the same SQL Server BE might be an optimal choice for a lot of apps.

 

[zeroaccess]

I would agree with that statement, but only if it were amended as follows:

- if someone uses a tool that improves what they are doing over what they did before, and doesn't introduce an even greater risk or other problems that might offset the benefit, then it is a net benefit.



The problem with low- or no-code tools is that they are often used by people who simply can't or won't approach the development paradigm thoroughly and critically enough to eliminate the reality that they are also, along with their newfound toy, introducing significant amounts of risk to the organization. It is quite possible to introduce a tool that comes with 10 units of efficiency, and 15 units of un-appreciated risk. I know because I've been the person who did that to an organization at least once or more when I was early learning.

One thing the new MS 365 universe has that did not exist with Access is that all of these apps and functions can be turned on/off for users by admins. So if you are in your team in MS Teams and you don't see Power Apps, it's probably because your admin decided to turn it off for you.

 

[Isaac]

One thing the new MS 365 universe has that did not exist with Access is that all of these apps and functions can be turned on/off for users by admins. So if you are in your team in MS Teams and you don't see Power Apps, it's probably because your admin decided to turn it off for you.

So far I've been lucky. Not only do they have power apps and flows turned on, but even some of the premium flow Operations, like Populate A Word Template, are enabled. Although my Adobe premium connector, was not.