Access Web App or PowerApp

We use an MS Access based system in our office to manage most work functions with the backend stored on a local server . We now have a need to make a small subset (about 500-600 records in a single table) available to field personnel to enable them to look up details, record when jobs are completed and make brief comments.  

PowerApps. I could use MS Access to keep a spreadsheet up to date and read user inputs to that spreadsheet but is SharePoint "smart  enough" to update just the updated record or does it replace the entire spreadsheet every time the spreadsheet is updated.

Access Web Apps. These use a SQL Azure database on the SharePoint server and can be linked to MS Access. It seems like this would be a more efficient solution but perhaps opportunities to increase features over time may be more limited.

My question is: Am I making the right choice - a web app instead of a PowerApp, or is there something about PowerApps that make them a better choice for my application?
LVL 1
Rob4077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Power Apps. Period.  Between the two ... Microsoft if putting ALL their effort in Power Apps. Period.  I would not spend *any* time on Access Web Apps.  Trust me on this :-)
Also ... you will need to convert your Access back end to SQL Server (which is what happens with Web Apps anyway).  At this time, Power Apps cannot directly connect to an Access back end, unless this has changed in the last couple of months.
Just say No to Access Web Apps.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Power Apps do not currently have programmability like Web Apps do, but, as Joe said, PA is where Microsoft is putting their development. Therefore, PA is a better platform for longevity -- and even though it is currently lacking automation abilities, it sure has fancy graphics.
0
Rob4077Author Commented:
That's what I thought, but the problem is that means I have no alternative but use a spreadsheet. Isn't that a very inefficient approach? Updating a spreadsheet with Access means I need to open an instance of Excel, open the spreadsheet, find the line I want to change, edit it and save it. Then I think SharePoint will save the whole spreadsheet again. If data changes every 5 minutes that's a lot of traffic, isn't it? Is that still what you recommend?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
We get it and we feel your pain. There is only so much we can say about this (Web Apps) ...  but you will be doing yourself a disservice by investing *any* time in an Access Web App scenario.  We know stuff, but can only say this much :-)
0
Rob4077Author Commented:
Ok, I can only assume that you're telling me that there is no practical solution at the moment and I should either be satisfied with the high traffic scenario of a spreadsheet or wait for MS to release their next solution.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Not exactly.  We are telling you not to spend any time on Access Web Apps ... that's all.
"available to field personnel to enable them to look up details"
Power Apps are geared toward Mobile Apps.  Web App are not.  So, you should ... be able to leverage PAs.  Microsoft's 'next solution' is Power Apps.
1
Rob4077Author Commented:
Ok, I understand "spend any time on Access Web Apps".

So is there any viable alternative to using a spreadsheet as a data source form my PA even though it means saving the entire spreadsheet every time the data changes, or is there a more efficient alternative - at the moment?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry, I'm not understanding the spreadsheet part exactly.
Is there any chance you could commission a SQL Sever back end, say on the Azure cloud?
This would open up soooo many opportunities, including connection Power BI dashboards 'live' via DirectQuery to the SQL Server backend to visualize data in ways never before possible.
And of course, there could be other solutions beyond AWAs and PAs ... but those are what you mentioned.

I will say this about AWAs ... you could get away with a Web App SQL backend on Azure for a while .. but ... your days in doing so would *definitely be numbered ...
0
Rob4077Author Commented:
Joe, sorry but you'll have to excuse my ignorance on this.

I thought I had created a single table on an SQL Server on the cloud when I created my first attempt at a Web app. I've also managed to connect to that table from my MS Access accdb using an ODBC link. I can read from it but I haven't tried writing to it yet.

Based on your comments, and those in this link (https://powerusers.microsoft.com/t5/PowerApps-Forum/PowerApp-Connect-to-access-web-app-data/td-p/1668) I have been trying to connect PowerApps to that database. At this stage I have gone into PowerApps, selected New, selected SharePoint but the only things on the list are things I don't recognise, namely
  "Content type publishing error log",
   "Project Policy Item List",
   "SharepointHomeOrgLinks" and
   "TaxonmyHiddenList"
, none of which were created locally so I can only assume they're system files or something.

If I could figure out how to connect to the same SQL Azzure database as my web app works then I could do what I wanted.

The only other options I can find are ways to connect my new PA to an Excel spreadsheet, something I am trying to avoid. Of course I could use one of the posted templates that is connected to a SQL Azure database but then need to figure out how to link to it.

Is my problem beyond the scope of EE? Perhaps I should be posting it on another site?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I thought I had created a single table on an SQL Server on the cloud when I created my first attempt at a Web app. "
Yes ... that should be the case and I have done that. And I have a single Web App SQL Azure table as well. And I was able to link to an Access front end in the same manner.

I have not yet tried to connect a Power App to it ... oh wait ... yes I did in fact ... and I was able to do it ... but that was about 2 months ago. I will have to look and see what I did.
I can tell you - which you must already know ... that ... to connect to the Azure db, you need Server Name, Database Name, User Name and Password, all of which are created automatically with you create the Web App ... and the SQL Server db is created on Azure.
Also ... I connected a couple of Power BI (PRO) dashboards to this same SQL Server Azure db as well ... and it works also.

"At this stage I have gone into PowerApps, selected New, selected SharePoint"
Well ... you need to select SQL Azure as the data source, then you should be able to enter all four parameters I mentioned. Pretty sure that is what I did.  I definitely did not connect anything to Sharepoint.

RE: "These use a SQL Azure database on the SharePoint server "
I don't think that is correct.  The SQL db is on the Azure Server. Sharepoint is only the admin piece for AWAs.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... this is how I connected to the SQL Azure AWA table - in Power Apps:
(Nothing to do with Sharepoint

1
2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Got it. Thanks. I'm connected. I can write to the database from MS Access and my first attempt at a PA worked. You could certainly not accuse the link to the cloud of being fast, can you? Anyway it works and if it does what I need then I think it will give them the info they need faster than a phone call. Thank you very much for your help getting it to work.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
No problem Rob.  I have a vested interest in getting all of this working ... and necessary understanding as such.
Just keep this in mind please ... the days of your db remaining on SQL Azure - in the web app domain - are *definitely* limited ... and I really mean that.  I suspect both of us (et al) will have to look at getting a regular Azure account at some point :-)
0
Rob4077Author Commented:
Thanks Joe.

As per your recommendation, I am now working on creating the PA and have already raised a separate question to find an efficient way to sync my Access database with the small subset of data I need on the PA. So now I am only using the Web App component to create/define the table and provide the link. I hope that if MS drop support for Web Apps, they will provide and maintain some other mechanism to create/update/link to SQL Azzure database.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"they will provide and maintain some other mechanism to create/update/link to SQL Azzure database."
Something ... like that ... but not exactly ... stay tuned.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
FYI  here is something that just came across my way ... FFWIW:

https://powerapps.microsoft.com/en-us/tutorials/connection-azure-sqldatabase/
0
Rob4077Author Commented:
Thanks Joe.

I've managed to connect my app to the SQL database I created using MS Access. The main concern I have now is that the only way I know to modify the database tables is via MS Access so if MS drop support for that I will be in trouble.

Creating the App seems easy enough. I only need a really basic setup so a single flat table is an adequate starting point. I can try to make it more efficient when I find out what MS end up doing with this. Most of my data travels one way and I will look at Data Flows as an alternative to communicate data back to the office - we don't want field staff modifying real data.

Really appreciate your help!!
1
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm going to install  SQL Management Studio ... and point to the Azure db  .... and see what it allows me to do in terms of table mods, etc.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Joe,

You may not be able to. Brent discusses exposing the programmability for Access Web Apps using SSMS -- that part starts at about 35 minutes:

https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps

Pat Wood ( http://gainingaccess.net/ ) has dug more into permissions since Brent demonstrated and wrote a blog about adding Write, for data anyway --  there may yet be a way ...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
35 minutes?? darn. I'm at work.  BTW ... I went to install SSMS 2014 from MSDN last night ... and it seems to want to install SQL Server !! Doesn't seem to be an option just to install SSMS - even though that's the EXE. What am I missing ?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
no, AT 35 min in -- his part is only about 15 min

> "What am I missing ?"
 I just did this myself ... I'll send a PM
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
thx. Will watch vid tonight
0
Rob4077Author Commented:
Hi Joe, I asked the question of on another question as to whether I could use SQL Server Express on a local server as a datasource for a PowerApp instead of SQL Azzure on the cloud and the answer from Gustav Brock was "No. Not heard of that option." You seem to be 'in the know' from MS on these things. Is that likely to be an option soon?

You've made it clear that I shouldn't waste time on Web Apps but given I am trying to link to a MS Access front end I am trying to work out what is going to be my best alternative.

Sorry to keep raising issues on a closed question. If you like I can raise this as a new question on EE
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... not sure about that. I assume other users w/b using the Power App?
I don't recall an option to connect to Express. Seems unlikely because Express free ... so with PA & Express ... there is no revenue source for Microsoft.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry I had to be vague until now ... but it's official:

https://techcommunity.microsoft.com/t5/Office-Retirement-Blog/Updating-the-Access-Services-in-SharePoint-Roadmap/ba-p/57148

https://support.office.com/en-us/article/Export-Access-web-app-tables-to-SharePoint-ff9d9058-14cf-40a2-89c8-ec46cf5cd67c?ui=en-US&rs=en-US&ad=US&fromAR=1

We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.

:-(
0
Rob4077Author Commented:
Thanks for keeping me in mind and letting me know.

The main thing I need to ascertain now is whether MS Access can connect to SharePoint lists. I have created a first draft of my PowerApp using SQL Azure which links to MS Access but I am not sure whether that's ultimately the best way to go, or whether a connection to a SharePoint list will work. A whole new learning curve trying to figure out what's best.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... Access can link to Sharepoint lists for sure.  But most developers avoided this because of assorted limitations.
I don't know right of if PAs can connect to SP list or not ... but s/b simple to check by open a new PA and see what data sources are available now.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I guess based on this ... PAs will connect to SP lists:

"We know that many of you have come to depend on Access custom web apps and we are working to make the transition to PowerApps as smooth as possible. We have added a feature to   export your data to SharePoint lists where you can create PowerApps and Microsoft Flows. We have also published guidance on how to port your custom web app to PowerApps here."
0
Rob4077Author Commented:
Thanks Joe, I think I should be away now that I know what is happening. A SharePoint list may have its limitations but it should suffice for my limited needs, for now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.