For organizations choosing a public tenancy SharePoint Online solution, via Office 365, publishing Access 2013 databases as SharePoint 2013 Apps certainly poses a problem. As I wrote in a post to this blog published yesterday, once the Access 2013 database has been published to SharePoint Online as an App, the data layer will no longer be retrievable for local back up and data storage. However there is a workaround.
The workaround is to select a “Datasheet” view of the App, rather than the “List” view. Once the “Datasheet” view has populated, all of the table rows and columns should be exposed on the web page. Simply copy all of the exposed data, and then paste it into an Excel spreadsheet. Once the spreadsheet has been correctly configured to display all of the information in a workable form, save it. Open Access 2013, select a local blank database and import your speadsheet. In all likelihood, all of the data should be present in the new Access 2013 table, which, of course, can then be saved locally. Once the local backup procedure can be satisfied, it will be safe to simply publish the Access 2013 database as a SharePoint list, rather than as an App.
It may be worth repeating the problem I presented in yesterday’s post to acquaint new readers. As an article published to TechNet and titled SharePoint 2013: Access Services explains, “Access apps are SharePoint apps, therefore for Access Services to run Access app, SharePoint Server 2013 needs to be configured as a SharePoint app server. In addition, Access Services requires SQL Server 2012 to run.” But a public tenancy on Office 365, SharePoint Online, E3 plan does not include any management features for the SQL Server 2012 back end. In fact, as I found out via several posts to an Office 365 Community Forum, and several calls to Office 365, SharePoint support, once the Access 2013 App is published as a SharePoint 2013 App, via Access Services, the data layer will be reposed in SQL Server running on Azure, and, thereby, completely removed from the user’s control.
The workaround I provided at the top of this post appears to work fine, albeit, it is completely distinct from the SharePoint 2013 App built with the Access 2013 database published as an App. Going forward, for my particular requirements, I plan on maintaining the “master” database on premise and will publish periodic updates to a SharePoint list. Once I verify everything is working fine, I then plan on removing the SharePoint 2013 Access Services App for this database.
Organizations using SharePoint 2013 on premise, will not have the same problem (although they will have to provide their own SQL Server 2012 back end, and should follow the procedures detailed in the TechNet article mentioned above). With SharePoint 2013 on premise, a back up procedure can be applied to the table supporting the SharePoint 2013 App, but run on a SQL Server 2012 (or 2014) table.
©Rehmani Consulting, Inc. & Ira Michael Blonder 2014 All Rights Reserved