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

asked on

Sharepoint list to Access database

I have been told that MS are dropping support for SQL Azure table connections to MS Access so I have started developing an app that uses a SharePoint list instead. I am trying to figure out a seamless way to keep the data on the list updated.

Our full set of data is saved on a MS Access BE on our server in the office. I have created a SharePoint list that only contains a small subset of the data from our back end and linked that SharePoint table to our FE.

The plan was to run a timer event every now and then to keep the back end up to date. However I don't understand the behavior. The update query runs fine in less than a second but then after a few more seconds I am getting a message on my Access FE that says "RECONNECTED TO SHAREPOINT SERVER The connection to one or more SharePoint servers has been restored. Click synchronize to close the opened objects, send pending changes to the server, and synchronize with server data". This makes the whole process impractical unless I can automate the synchronize.

In addition, I am now getting messages that say "An unexpected error has occurred. Changes to your data cannot be saved. For this error you can retry or discard your changes". Again this is impractical - I can't expect users to have to deal with this kind of semi-manual update process.

Is there a way of seamlessly updating to a SharePoint list, or is there some other way I should be storing data on SharePoint that will be reliable and seamless and can still be connected to a PowerApp?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
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

ASKER

Cool! Therefore all I need to find out is how to modify the table structure of an SQL Azure table on SharePoint. At present the only way I know how to do that is via MS Access.
There seems to be some confusion as to what sharepoint and SQL azure is.

The soon to be obsolete access WEB apps (AWA for short) consisted of a SQL Azure backend for data, whereas the interface was designed in Access, and displayed to users through Sharepoint. The idea was good, but the adaptation poor and Microsoft recently announced that the platform would receive no further development.

That said, the access DESKTOP Client is alive and well, and used many, many many places. The desktop client can connect to Excel, Access databases, SQL databases, MYSQL, Oracle, SQL azure and even sharepoint.

The key point to understand is the seperation of the presentation layer from the data layer. An access file (accdb) can contain both layers, and is rather unique in that aspect. That said, many professional access applications use SQL server for the data storage, and only use Access for the presentation layer.

Hope that clarifies.
Avatar of Rob4077

ASKER

Thanks Anders Ebro for your clarification.

As a result of your clarification I am proceeding with development using SQL Azure database as datasource for the PowerApps and MS Access to maintain the table structure.

However, FYI,  I just tried changing a field name and adding a new field to my table from MS Access and neither will work. It just keeps coming up with "Internal error encountered by Access Services. Action cancelled" and then it gives some technical details. I've tried closing and reopening everything but it doesn't seem to want to allow me to change table structures.  I opened a new question yesterday (https://www.experts-exchange.com/questions/29015365/how-do-I-modify-the-table-structure-of-an-SQL-Azure-table-on-SharePoint.html) that I was going to delete after your comments but in light of the problem I am having now I will add some comments and wait to see if anyone can help.
Use SSMS (SQL Server Management Studio) to manage the Azure database.
I think I see what is going on. You have created an access WEB database. You might not be using the access/sharepoint site of it, however that database is still going to be turned off April 2018.

Creating an access WEB database does give you a SQL server database in the cloud, which you can connect to from other sources such as Power Apps. However, you cannot create your own stored procedures or functions on that database, and as mentioned, they will be turned of April2018.

You can create a "stand-alone" SQL server+database, and use that, from both Access and Power Apps.
It will come with a cost of course, (~5$/month for the cheapest)
You won't be able to use Access to manage the table structure, but could use SSMS instead as Hartman suggested.
Avatar of Rob4077

ASKER

Thanks for the added clarification.

Yes I did create the SQL Server database using WebApp and I have connected to it via a PowerApp. What you've told me now makes me realise that I have probably wasted a couple of weeks work. First I tried converting the SQL database to a SharePoint list but that is unworkable. Then I rebuilt the app using the SQL backend thinking it would last, but and only the WebApp part would disappear. Now I will have to start all over again using SSMS.

It is frustrating that MS don't offer a transition method, except to a SharePoint list, and what's worse is that I am discovering that PowerApps still seems to be in its infancy. I've pursued many an option for a very basic task only to be told that it is either a known issue, a suggestion under review, or a new suggestion. I wish I knew of a simpler option.
I can only say I agree and feel for your loss of work/time. The announcement that Access Web services were being retired was only made public 2 weeks ago. If you can recreate the database in Azure, and just point your Power app to that, you might be up and running again. Just something to consider.
The easiest way to copy an entire database is to use SSMS to create a backup and then restore the backup in your new environment.

Anders, I didn't think that Azure was going away from SharePoint.  I thought only the Access Web Apps were being deprecated.
Avatar of Rob4077

ASKER

In case anyone is looking at this question to assist with a solution, YOU CAN NO LONGER USE MS ACCESS TO CONNECT TO A SHAREPOINT LIST https://support.office.com/en-us/article/Access-Services-in-SharePoint-Roadmap-497fd86b-e982-43c4-8318-81e6d3e711e8