Is Access the right solution?

[zeroaccess]

So as I was writing the original post here it appears Microsoft has been hard at work on this problem.

 

[Isaac]

So as I was writing the original post here it appears Microsoft has been hard at work on this problem.

Agreed! I'm waiting for my corporate admins to unlock Power Apps for us and then going to have a go. They say it is coming soon.

 

[gemma-the-husky]

It's not a question so much as whether "access" or indeed any relational database is the right solution.
If you are only used to using spreadsheets, then you have to appreciate that database development is a world away from a spreadsheet. In a spreadsheet you can produce a good tool without a line of code. You just can't do this with a database. You need a lot of defensive code to make it fool proof. You need to prevent all users, including yourself doing things that would be ill-advised, especially as there is no "undo" feature. You insulate yourself and other users from some of the more dangerous things that can be done to data. So there are potentially 2 new skills to learn. Database development, and Web development. As @Galaxiom pointed out earlier in his posts, it's still hard even if you know what you are doing.

 

[CJ_London]

In a spreadsheet you can produce a good tool without a line of code. You just can't do this with a database. You need a lot of defensive code to make it fool proof.

you need defensive code in excel as well! I've lost count of the number of times I see data where 'required' fields are left blank or fields are filled with inappropriate data - typically text in what is supposed to be a numeric type field

 

[zeroaccess]

Agreed! I'm waiting for my corporate admins to unlock Power Apps for us and then going to have a go. They say it is coming soon.

I discovered an unfortunate limitation yesterday. Apparently using Power Apps via the MS 365 license comes with some limitations to data connectors, one key one being SQL server. Therefore creating a Power App for mobile use to connect to the same back end db you're using with your desktop Access db is not possible without a separate and costly "Premium" Power Apps license.

I must find a workaround so I can have my desktop and mobile talk to the same db.

 

[Isaac]

I discovered an unfortunate limitation yesterday. Apparently using Power Apps via the MS 365 license comes with some limitations to data connectors, one key one being SQL server. Therefore creating a Power App for mobile use to connect to the same back end db you're using with your desktop Access db is not possible without a separate and costly "Premium" Power Apps license.

I must find a workaround so I can have my desktop and mobile talk to the same db.

Interesting. That's disappointing. I'd probably be overlaying them to sharepoint lists to start with, since my company makes us suffer without SQL server currently.

 

[gemma-the-husky]

I discovered an unfortunate limitation yesterday. Apparently using Power Apps via the MS 365 license comes with some limitations to data connectors, one key one being SQL server. Therefore creating a Power App for mobile use to connect to the same back end db you're using with your desktop Access db is not possible without a separate and costly "Premium" Power Apps license.

I must find a workaround so I can have my desktop and mobile talk to the same db.

How much is the "premium" license, out of interest?

 

[zeroaccess]

How much is the "premium" license, out of interest?

$10 per app per user per month (one app used at a time), or
$40/user/month for unlimited apps

 

[zeroaccess]

Interesting. That's disappointing. I'd probably be overlaying them to sharepoint lists to start with, since my company makes us suffer without SQL server currently.

I've heard there can be performance issues when relying on Sharepoint instead of SQL, which worries me. I'm also not sure how we would create a relational database in SP.

 

[zeroaccess]

Relevant:

Can Power Apps replace Access?

https://www.reddit.com/r/PowerApps/comments/h7zdz3/_/fup2yl7

 

[Isaac]

I've heard there can be performance issues when relying on Sharepoint instead of SQL, which worries me. I'm also not sure how we would create a relational database in SP

Assuming modest amounts of data, I've had no trouble using Sharepoint lists as tables. As to their "relational"-ness, you simply enforce it in forms and code. You represent the relations with tables just like you would in Access, really.

 

[zeroaccess]

Assuming modest amounts of data, I've had no trouble using Sharepoint lists as tables. As to their "relational"-ness, you simply enforce it in forms and code. You represent the relations with tables just like you would in Access, really.

I suppose you don't have to define relationships at the table level, though I do so I have things mapped out for myself and to provide defaults throughout the database.

Power Apps can't do code, though. So I'm unsure how you'd go about building a Power App for mobile that reads and writes to the same database. I suppose a lot of that is taken care of for you. I would like to get my hands on it to try but the licensing might be a showstopper.

 

[Isaac]

Power Apps can't do code, though

Then it either sucks, or there has to be a LOOOOOT of configurable properties. Either way doesn't sound like a fun afternoon to me.

 

[zeroaccess]

Then it either sucks, or there has to be a LOOOOOT of configurable properties. Either way doesn't sound like a fun afternoon to me.

It actually looks like they took PowerPoint and Excel and Access and blended them together. No VBA. But there are "formulas" used in a similar-looking bar as Excel. I wouldn't be able to criticize much without getting my hands on it but I know that for many, no VBA or query editor means it can't replace Access.

 

[The_Doc_Man]

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.

 

[zeroaccess]

Having no experience with SharePoint I had to look further. It turns out you can create relationships:

Create list relationships by using unique and lookup columns

Still, there will be limits depending what you want your apps to do – which means if you want a desktop Access front-end and a mobile Power App to connect to the same SharePoint, it will place limits on its complexity.

 

[gemma-the-husky]

I'm not sure about clunky

I have used the RAD facilities of Access to do quite a few things that have had nothing to with databases. Just a way of getting a programming language that I am very familiar with.

 

[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