Creating an offline Data Entry form

[Starlight]

We have a client who is using an Access 2013 DB (Office 2013 Pro Plus) with the Windows 10 operating system. They are working on implementing the infrastructure for using Office 2013 under the Office 365 subscription. The Access DB they are using is setup as 2 .accdb files, one that contains the back end data, and the other the objects for the UI.

They will soon be using MS Surface Pro tablets for their field personnel, and want a form that can be used for data entry while in the field.

When my team mate and I originally discussed the best solution for this task (we are both most familiar with Access versions 2007 and earlier), we talked about creating a form that could be used offline (basically a new FE and BE) and routine that would do data synchronization back to the network database when submitted by the field personnel.

From online research, it looks like this may be better solved with the use of Office 365, SharePoint, and Access Services. Unfortunately, since they haven't yet migrated to Office 365, we have no way to test it and need to determine if it is a viable option, or if we will need to do the "custom routine" option. Since they are planning to migrate to 365 anyway, if it works, it would obviously be a more cost effective solution for them.

Does anyone know if this is a viable solution? Here are a few questions we have when considering this solution:

1) The Internet is not always available in the field, so the solution must be one that can run offline. Is this possible with 365?
2) They are working to get Sharepoint Services working soon. It looks like we will also need them to implement Access Services. What do SPS and Access Services look like, how do they interact, and what features do they offer and how can we leverage them?
3) What does the web-based Access application look like? What is the path to convert/migrate an Access 2013 DB to a web app? Does all development need to be done (or redone) in a new dev environment for 2013 web apps?
4) The "hydration" of an offline copy of the form is what we would need to distribute to an offline tablet device. What does this involve? Is a small footprint .accdw created? What happens after that when first accessed by the user? Is there a hydrated .accdb for the Front End and/or Back End?
5) If we must hydrate the entire application, we may need to implement security at that time. From our research, it appears that once the Access web app is published to SPS, the objects (lists, etc.) in SPS are lockable at the SPS level, and we may have to use that security layer instead of Access security, correct?



Any input that can help us determine the best course of action and what tools may be necessary would be greatly appreciated!

[June7]

Sorry, I don't know much about web db, never used Office 365 or SharePoint, but will offer some thoughts.
1. then I suspect you would have to do the 'custom' synchronization for the data collected while off-line
2. no idea
3. you can download some web templates and find out - they are harder to design and edit and the web objects cannot use VBA
4. no idea - what does hydrate mean
5. Access has very little built in security, at least since Access 2003 - requires a lot of customization and code to secure Access db

[ItsMe]

Limited experience here, too.

1) The Internet is not always available in the field, so the solution must be one that can run offline. Is this possible with 365?
SharePoint offers bidirectional synchronization for Libraries, just like an Office 365 pro plus subscription would. The way I understand it, Libraries have things like Word Docs, Excel spreadsheets, and Lists. I am not aware of intrinsic features for bidirectional synchronization of Access databases using SharePoint and Access Services. I have the book, 'Extend Microsoft Access Applications to the Cloud', and a quick search of 'bidirectional' and 'offline' produced no results.

2) They are working to get Sharepoint Services working soon. It looks like we will also need them to implement Access Services. What do SPS and Access Services look like, how do they interact, and what features do they offer and how can we leverage them?
I have no experience with this but my research indicates that if you host SharePoint on premises and want to use Access web apps in conjunction with SharePoint Lists, you would want to have Access Services enabled. I believe an Office 365 pro plus subscription would default to include Access Services. But I am really just guessing here. When I read about SharePoint's role with relational database, I am always left with questions. If you work with an Access Web App and SharePoint, the backend will be SQL server. I do not know what sort of role SharePoint plays with respect to SQL server or SQL Databases. There may not be any role. However, I believe the following to be fact

• An Access Web App depends on SharePoint. An on premises version requires Access Services.
• An Access Web App uses SQL server and or SharePoint Lists as the backend. If it is an Office 365 subscription that does not depend on on-premises SharePoint, the BE is Azure SQL Database.

3) What does the web-based Access application look like? What is the path to convert/migrate an Access 2013 DB to a web app? Does all development need to be done (or redone) in a new dev environment for 2013 web apps?
I do not know what an Access Web App looks like. It seems like a developers nightmare, to me. However, I believe it fills a void when power users need a way to query relations. Access Web Apps and Access Desktop Databases are two different animals. Starting with the 2010 publication of Microsoft Press' Access Inside Out, there is a definitive distinction between the two and curriculum is divided. I have not created a single Access application that would have to be entirely redone in order to function as a Web App. But I do not use Macros or rely on the Wizard (I use the Wizard on occasion). The apps I create use a lot of VBA.

4) The "hydration" of an offline copy of the form is what we would need to distribute to an offline tablet device. What does this involve? Is a small footprint .accdw created? What happens after that when first accessed by the user? Is there a hydrated .accdb for the Front End and/or Back End?
For the most part, I do not have any answers to these questions. I will say, however, that an Access Web App is going to be talking to an SQL database. or a SharePoint List. I do not believe it uses the Ace database engine. So, might as well use a desktop DB app and connect directly to the SQL DB using ODBC. The problem now is bidirectional synchronization. So for the tablets, it may be best to build a Windows Forms (or whatever else) app and use .Net's Sync Framework Database Synchronization.
https://msdn.microsoft.com/en-us/sync/bb887608.aspx

5) If we must hydrate the entire application, we may need to implement security at that time. From our research, it appears that once the Access web app is published to SPS, the objects (lists, etc.) in SPS are lockable at the SPS level, and we may have to use that security layer instead of Access security, correct?
I have no idea about security with Access Web Apps. You might want to purchase Extend Microsoft Access Applications to the Cloud and see what they have to say about it. A quick search indicates much of the security is Server side and focused on Public facing endpoints vs. Private facing endpoints. Of course, using SQL server offers security. There is also a section within the book that talks about connection strings and using ODBC. So file DSN's may be one approach.

[peiyezhu]

If you use hybrid SQL server and access client, no problem with security for you just link table from SQL server.