Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
What are my options to share data with field staff
I have a need to make a small subset of data, currently stored on a MS Access database, available to field staff. I can fit the data on a single table of about 20 fields and a maximum of 500 records. I don't want staff to add or delete records and I am happy for data updates to come back to us via email. It would be nice if they could also return photos with the email but that's not imperative. Realistically the interactivity requirements will increase over time but not too much as my users are not tech-savvy.
So far have been looking at PowerApps as a possible solution. However I have found that a PowerApp connected to an SQL Azure backend only has an effective 1 year lifespan before MS withdraw that option. I tried looking at a SharePoint List as a backend but it is unreliable and therefore unworkable. So I am after some suggestions for alternative approaches that are not going to cost a fortune, are going to be relatively easy to integrate with MS Access and will not force me into months of learning a new language.
So far have been looking at PowerApps as a possible solution. However I have found that a PowerApp connected to an SQL Azure backend only has an effective 1 year lifespan before MS withdraw that option. I tried looking at a SharePoint List as a backend but it is unreliable and therefore unworkable. So I am after some suggestions for alternative approaches that are not going to cost a fortune, are going to be relatively easy to integrate with MS Access and will not force me into months of learning a new language.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
alternative approaches that are not going to cost a fortune, are going to be relatively easy to integrate with MS Access and will not force me into months of learning a new language.I think you've boxed yourself in with those requirements, since you're also not considering PowerApps (and I fully understand why you'd do that). Access can't do web stuff very well (and they're doing away with Web Apps), so you're effectively asking how you can run Access in a web app (and you can't do that).
You could use www.eqldata.com, but there are ongoing monthly fees for that.
If this is a read-only sort of thing, you could simply create an Access database the user could run on their laptop in the field, and deploy that as needed.
However, if you're going to do updates, then you must be concerned with replication issues, like data mashups, conflicts, etc. This can be a HUGE headache, and one that is not easy to overcome. If users have their own subset of records - like UserA "owns" records 1 - 50, and UserB "owns" records 51 - 100 then it's fairly simple, but if not then you're back to replication issues.
Scott,
<<However I have found that a PowerApp connected to an SQL Azure backend only has an effective 1 year lifespan before MS withdraw that option.>>
Think he meant Access Web apps there...Power Apps are certainly not going away in a year.
and I would agree; www.eqldata.com is probably the best bet at the moment.
Jim.
<<However I have found that a PowerApp connected to an SQL Azure backend only has an effective 1 year lifespan before MS withdraw that option.>>
Think he meant Access Web apps there...Power Apps are certainly not going away in a year.
and I would agree; www.eqldata.com is probably the best bet at the moment.
Jim.
<<However I have found that a PowerApp connected to an SQL Azure backend only has an effective 1 year lifespan before MS withdraw that option.>>
Not entirely correct. An access web app (AWA) has it data storage in a limited Azure SQL server, and it is true, that THAT option is going away April 2018. However, Azure SQL server is NOT going away.
So you can have your data in Azure, your client side frontend in Access (But be realistic about performance, considering the remote backend), and connect a different technology to that Azure Frontend, e.g. Power Apps.
A third option is to keep your access data however it is now, push the data in question into excel on Onedrive, and connect your power app to that (If read-only access is what you want). There are really quite many ways to go about it.
A fourth option is again, to just put the relevant data to sharepoint, and access the sharepoint remotely. I wouldn't put the entire backend in sharepoint, but if you only want a subset of information, then sharepoint might just fit the bill for you.
Not entirely correct. An access web app (AWA) has it data storage in a limited Azure SQL server, and it is true, that THAT option is going away April 2018. However, Azure SQL server is NOT going away.
So you can have your data in Azure, your client side frontend in Access (But be realistic about performance, considering the remote backend), and connect a different technology to that Azure Frontend, e.g. Power Apps.
A third option is to keep your access data however it is now, push the data in question into excel on Onedrive, and connect your power app to that (If read-only access is what you want). There are really quite many ways to go about it.
A fourth option is again, to just put the relevant data to sharepoint, and access the sharepoint remotely. I wouldn't put the entire backend in sharepoint, but if you only want a subset of information, then sharepoint might just fit the bill for you.
Thanks for your comments. I will look at your suggestions tomorrow.
What I meant though is that after a year the sql database that I create with Access will be gone and I will need to subscribe to an alternative sql database.
As for replication, I need up to date data to flow to the field but I don't want them updaing the database, just letting the office know the status so auto emails are adequate. Office staff can then process the info an update the database. Field staff only do between 1 and 5 jobs per day so its small amounts of data flowing
What I meant though is that after a year the sql database that I create with Access will be gone and I will need to subscribe to an alternative sql database.
As for replication, I need up to date data to flow to the field but I don't want them updaing the database, just letting the office know the status so auto emails are adequate. Office staff can then process the info an update the database. Field staff only do between 1 and 5 jobs per day so its small amounts of data flowing
Sorry Anders I didn't see your message before my last post.
From a performance perspective keeping the data on our local server in an Access database is as good as necessary. The subset of data required for the field is very small. We have teams of electricians who do 1 or 2 jobs a day. Some do maintenance and the highest conceivable is 10 jobs. They need to know basic data like:
Where is the job, when does it need to be done and specific instructions (a memo field).
They start work between 6 and 7 AM and need to have the latest data available when they arrive at the job.
Office staff start at 8:30. Updates to the special requirement field occur randomly during the day so it's impractical to send an SMS or email every time a minor tweak is made the the special instructions. All the office need to know is:
Has the job been started Y/N?
When was it finished?
Who did the job?
Any Comments?
Who did the QA check?
When was it done?
Any Comments?
We don't want updates punched straight to the database because office staff need to perform various functions as each answer arrives. Therefore the Excel option may be as good a solution as any. I will look at all the suggestions tomorrow.
Thanks again
From a performance perspective keeping the data on our local server in an Access database is as good as necessary. The subset of data required for the field is very small. We have teams of electricians who do 1 or 2 jobs a day. Some do maintenance and the highest conceivable is 10 jobs. They need to know basic data like:
Where is the job, when does it need to be done and specific instructions (a memo field).
They start work between 6 and 7 AM and need to have the latest data available when they arrive at the job.
Office staff start at 8:30. Updates to the special requirement field occur randomly during the day so it's impractical to send an SMS or email every time a minor tweak is made the the special instructions. All the office need to know is:
Has the job been started Y/N?
When was it finished?
Who did the job?
Any Comments?
Who did the QA check?
When was it done?
Any Comments?
We don't want updates punched straight to the database because office staff need to perform various functions as each answer arrives. Therefore the Excel option may be as good a solution as any. I will look at all the suggestions tomorrow.
Thanks again
Access doesn't work well with Excel in a "live" situation. Access needs complete control over the file in order to push any updates and this will likely conflict with users who are reading the file.
If you have a SharePoint server, the SharePoint list to share just this set of data will work well. Add additional columns so field people can make updates to this shared dataset. Office personnel can decide when to take the record and apply it to the main database.
I like the idea of eqldata but I have no idea what it would cost you. My clients who need remote access use either Remote Desktop or Citrix. When you need only a small set of data to be exchanged live, SharePoint works well as the intermediary. I would never use SharePoint to hold an application's data. I would only use it as a way to exchange live data interactively.
If you have a SharePoint server, the SharePoint list to share just this set of data will work well. Add additional columns so field people can make updates to this shared dataset. Office personnel can decide when to take the record and apply it to the main database.
I like the idea of eqldata but I have no idea what it would cost you. My clients who need remote access use either Remote Desktop or Citrix. When you need only a small set of data to be exchanged live, SharePoint works well as the intermediary. I would never use SharePoint to hold an application's data. I would only use it as a way to exchange live data interactively.
Thanks Pat for your comments. Your comments have ruled out Excel.
I tried a SharePoint List but it was very problematic. Every time I tried pushing an update, and I only did 3 or 4 updates during development, it came up with records that wouldn't update and needed user intervention which makes it impractical.
I want to explore the other options that were suggested today but I'm beginning to think that I should just persevere, for the moment, with the SQL back-end. I've got a year to see how it works and see how it evolves and is used. Then next year I can switch to a $5 per month SQL backend with MS or explore other options then
I tried a SharePoint List but it was very problematic. Every time I tried pushing an update, and I only did 3 or 4 updates during development, it came up with records that wouldn't update and needed user intervention which makes it impractical.
I want to explore the other options that were suggested today but I'm beginning to think that I should just persevere, for the moment, with the SQL back-end. I've got a year to see how it works and see how it evolves and is used. Then next year I can switch to a $5 per month SQL backend with MS or explore other options then
I don't have a lot of experience with SharePoint lists but never had a problem with them. Perhaps security is not set up correctly. Use a standard Access database NOT a Web database and just link to the SharePoint list.
I would not spend any time at all on an Access Web apps application and linking to Azure from desktop Access, while you can do it, will be too slow for practical use. If you do it, use a local SQL database for the main data and just use the Azure database for the interchange table where you communicate with the field. That will minimize your exposure to Azure.
I would not spend any time at all on an Access Web apps application and linking to Azure from desktop Access, while you can do it, will be too slow for practical use. If you do it, use a local SQL database for the main data and just use the Azure database for the interchange table where you communicate with the field. That will minimize your exposure to Azure.
Hi Pat, well now I am getting more confused, no doubt due to my own misunderstanding. I could not successfully copy all of my data to the List without user intervention when I tried, but perhaps I wasn't persistent enough. When I tried, for some reason, I had trouble with it and a comment from an Expert (which I probably mis-interpreted) made me suspect that it wouldn't be a good idea to persevere with a SharePoint List.
Can I just clarify the following.
if I link to a SharePoint list from MS Access should I be able to run an Access query to
a) detect what fields on the list are different to those in the Access tables?
b) Add/Edit/Delete records in the SharePoint List using a Query to re-align them with the MS Access tables?
If I can achieve the above then I should be able to convert my app to run from a SharePoint list quite easily and then I will have achieved my immediate goal. I would just run a small MS Access program every now and then to update the data.
Sorry you're having to deal with such a Dummy but I am just not getting what the best way forward will be.
Can I just clarify the following.
if I link to a SharePoint list from MS Access should I be able to run an Access query to
a) detect what fields on the list are different to those in the Access tables?
b) Add/Edit/Delete records in the SharePoint List using a Query to re-align them with the MS Access tables?
If I can achieve the above then I should be able to convert my app to run from a SharePoint list quite easily and then I will have achieved my immediate goal. I would just run a small MS Access program every now and then to update the data.
Sorry you're having to deal with such a Dummy but I am just not getting what the best way forward will be.
I just had another look at my existing attempt at linking Access to a SharePoint list and it seems to only create a local copy and then tries to keep it synchronized. That means I end up with messages across the top every now and then asking if I want to reconnect and synch and if I say Ok I keep getting failures. I don't know what I am doing wrong but if I can't overcome that then it's not a viable option for me
You need to create the list using SharePoint. Then you can link to it from Access. As a linked table, you can do pretty much anything you can do with a local table.
I can't tell you how to set up security but I'm going to guess that is your SharePoint issue. I don't currently have access to a SharePoint site so I can't give you any specific instructions.
I can't tell you how to set up security but I'm going to guess that is your SharePoint issue. I don't currently have access to a SharePoint site so I can't give you any specific instructions.
That's interesting because I set it up using the approach recommended on the notice from MS telling me how to convert from SQL to SharePoint List.
Based on what you've said I will now have to start from scratch - build a new SharePoint List that is identical to the one created using the instructions from MS, then try to link to it and see if I still have the same problems I do now with it.
In Australia our next 3 weeks are short weeks (3 long weekends) so I am going to have limited time available to try this while in the office and I have limited ability to do anything at home. I will leave the question open for now but will probably need to close it and start a new one if it doesn't work because EE will want a close out.
Thanks to all of you for your patience with me on this.
Based on what you've said I will now have to start from scratch - build a new SharePoint List that is identical to the one created using the instructions from MS, then try to link to it and see if I still have the same problems I do now with it.
In Australia our next 3 weeks are short weeks (3 long weekends) so I am going to have limited time available to try this while in the office and I have limited ability to do anything at home. I will leave the question open for now but will probably need to close it and start a new one if it doesn't work because EE will want a close out.
Thanks to all of you for your patience with me on this.
because EE will want a close out.Just post a comment every 5 - 7 days, and you won't get the closeout notice. EE only prompts you for "abandoned" questions (i.e. questions without any activity in the past 14 days).
I won't be able to get back to this till Wednesday or Thursday but which is going to be quicker to respond, a SharePoint List or an SQL Azure backend. I am currently using a link to SQL on my PowerApp under development and it is painfully slow on my phone
which is going to be quicker to respond, a SharePoint List or an SQL Azure backend.Just a guess, but I'd think a properly designed system that hit a SQL backend would be better. If you're viewing this on your phone, then of course you have cellular connections to contend with (or networks connections, if you're connecting via wifi).
Thanks for your comments. I think I will run with Pat's suggestion - build a SharePoint List, if I can get it to reliably update, and add fields for staff updates. The other comments have helped me understand the options better.
Thanks again to all of you
Thanks again to all of you
Just an update on this, in case anyone else is using it as a basis for their decisions.
YOU CAN NO LONGER BUILD A LINK FROM MS ACCESS TO A SHAREPOINT LIST: https://support.office.com/en-us/article/Access-Services-in-SharePoint-Roadmap-497fd86b-e982-43c4-8318-81e6d3e711e8
YOU CAN NO LONGER BUILD A LINK FROM MS ACCESS TO A SHAREPOINT LIST: https://support.office.com/en-us/article/Access-Services-in-SharePoint-Roadmap-497fd86b-e982-43c4-8318-81e6d3e711e8
YOU CAN NO LONGER BUILD A LINK FROM MS ACCESS TO A SHAREPOINT LIST:The link you provided refers to Access Web Apps, and specifically states that "Access Desktop databases (.accdb) are not impacted by this decision". So if you're using a desktop Access database, you can still use links from Sharepoint.
Interesting point Scott. The reason I made this post is because I was having trouble linking a Desktop app to a SharePoint List. I ended up contacting Microsoft tech support and, after a day of research into the reason, they came back to me with that link telling me it was no longer possible and that's why I couldn't do it. I've now gone back to them for further clarification.
Thanks for the clarification. I don't use Sharepoint very often, so I don't have any way to test, but please let us know what they say. If it turns out you cannot use Sharepoint links to a desktop app, it would be nice if MSFT support would point to a document stating as much!
Just to add a bit, that article is talking about Access Services in SharePoint, which is what Access Web Apps and Databases depended on. There is no doubt that is going away.
But like Scott I have not heard where SharePoint was no longer supported for Desktop databases. I don't have a SharePoint server up either, but Access 2013, which is the latest I have installed at the moment, certainly still has a menu entry for linking to a SharePoint list. A2016 has changed very little over 2013 and should be able to link to SharePoint as well.
I think Microsoft gave you a bum steer.
Jim.
But like Scott I have not heard where SharePoint was no longer supported for Desktop databases. I don't have a SharePoint server up either, but Access 2013, which is the latest I have installed at the moment, certainly still has a menu entry for linking to a SharePoint list. A2016 has changed very little over 2013 and should be able to link to SharePoint as well.
I think Microsoft gave you a bum steer.
Jim.
but Access 2013 which is the latest I have installed at the moment, certainly still has a menu entry for linking to a SharePoint list.Access 2016 does too.
I am using Access 2016 and it seemed odd to me that they had the link but I simply couldn't get it to work. I tried various ways to find out an in the end contacted MS who gave me that answer, both by phone first and then by email. I have now gone back to them for clarification but I can't expect an answer over the weekend. I will post a reply here when I hear back from them
While Microsoft continued looking for a solution I realised that I had logged into MS Access using my personal account and the SharePoint List was on the work account. I didn't realise that the Account login functionality had been added into MS Access so I added the work account to MS Access and I could immediately login to the SharePoint list.
Thanks for your support on this one and for correcting the incorrect advice given by MS Access
Thanks for your support on this one and for correcting the incorrect advice given by MS Access
Premium Content
You need an Expert Office subscription to comment.Start Free Trial