Link to home
Start Free TrialLog in
Avatar of Rob4077
Rob4077Flag for Australia

asked on

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?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

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.
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

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.
Avatar of Rob4077


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?
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 :-)
Avatar of Rob4077


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.
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.
Avatar of Rob4077


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?
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 ...
Avatar of Rob4077


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 ( 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
, 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?
"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.
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob4077


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.
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 :-)
Avatar of Rob4077


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.
"they will provide and maintain some other mechanism to create/update/link to SQL Azzure database."
Something ... like that ... but not exactly ... stay tuned.
Avatar of Rob4077


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!!
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.
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:

Pat Wood ( ) has dug more into permissions since Brent demonstrated and wrote a blog about adding Write, for data anyway --  there may yet be a way ...
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 ?
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
Avatar of Rob4077


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
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.
Sorry I had to be vague until now ... but it's official:

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.

Avatar of Rob4077


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.
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.
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."
Avatar of Rob4077


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.