Is Access the right solution?

[CJ_London]

if you want a web based database with a variety of different front ends doing different things, take a look at sql azure

 

[Minty]

We have a couple of clients that have a hybrid solution on Azure.

Access FE for production and office based, DB intensive operations, and a Web client for road based sales warriors for reports and simple expense tracking, and some of their client portal data.

 

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

Comment
by from discussion
in PowerApps

 

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

 

[Pat Hartman]

The Relationships you create in SharePoint result in table level lookups and you have NO choice. It was from Sharepoint that MS got the idea to do the same thing in Access. Sharepoint is a poor substitute for even Jet but if you have small tables (< 5,000 rows) and you need to have remote users without using Citrix or RD, then it is an option. Not one I like but we don't always have control over decisions made by our clients or employers.

Just FYI, Sharepoint emulates tables by using a skinny table with an ID, tblID, FK, FieldName, FieldValue organization. This is essentially an entity/attribute schema. So, if your "table" has 30 columns, each logical record takes 30 rows (the data is actually stored in SQL Server) to store it. And I think all "tables" are stored in the same physical SQL Server Table. So couple of rows might look like

1, tblCust, 245, CustName, Microsoft
2, tblCust, 245, Addr, One Microsoft Way
3. tblCust, 245, City, Redmond
4, tblCust, 245, St, Washington