Build a SharePoint App with Microsoft Access

1. Jacqueline van der Holst j.van.der.holst@avanade.com Build a SharePoint App with Microsoft Access

2. About me Manager at Avanade 13 years of experience in development Last seven years SharePoint and development for SharePoint Started career with Access applications Borrowed some slides from SPC 2014

3. Presentation Objectives Knowledge objectives • Background and purpose • Architecture • Highlights and limitations Practical objectives • Create and update Access apps • Integration with SharePoint • Packaging • Add additional value to the Access app

4. Access Apps The Story

5. Access overview Desktop Access Apps • Used by millions today • 20 years old • Data stored in a local file • Requires Access software • VBA • Supported in SP2013 Access 2013 Web Apps • Brings Access DB’s to the Web • Data stored in SQL • Accessible everywhere • Easy sharing – No code

6. What kind of app is an Access app? S Fully functioning apps that run on the web within SharePoint Hosted on SharePoint sites in the cloud Access apps are SharePoint Apps Apps for SharePoint “Add-ins” of functionality for some Office programs Placed within the document or in the task pane Access apps can contain Apps for Office Apps for Office

7. Target groups Access Services 2013 “NAPA” SharePoint Apps Office Apps Pro Dev Power User Visual Studio

8. Pro’s to SharePoint WebApps • You can build them directly in Access 2013 • No coding required • Support for relational data • The backend is SQL Server 2012 • Easy security • Data table templates • Ease of use in designer tools • You can package and sell • You can use SQL Server Reporting Services, excel or other tools to generate reports

10. What about the con’s • Records cannot trigger or be part of Access workflows • Security setup is basic and somewhat limited • The apps own the database • The only supported SP object is the List object and can only be added in readonly mode • Scope is the site collection you are in • You cannot write any VBA code for the app

11. Architecture

12. Architecture SharePoint on Office 365 Web Browser SharePoint on premises Access Services Consumes SQL DB Tables & Data 2012+ Access Client Creates Azure

13. Access Services

14. Search Search is leveraging the full-text search capabilities within SQL 2012 The search server in SharePoint is not aware of the content that is hosted within Access Services

15. What about security? • Many people can work at the same app at once • No additional security needed to the DB • No need to authenticate when using the app • Permissions are handled by adding people to SP groups (and/or AD groups) in the site that hosts the app

16. What cannot be done in security? • A user cannot own records • There is no way of separating who can add vs. Who can edit records • No way of applying edit vs. readonly permissions to specific tables within the app

17. Updating your app

19. What happens during upgrade? • Current database (V1) read-only and copy created(V2) • Applies schema changes in DACPAC to V2 • If there runs on-deploy macro

20. What happens during upgrade? If Success then Deletes V1 Renames V2 to the same name as V1 Else Marks V1 back to read/write Deletes V2

21. Locking a table Markes the table readonly All data in a Locked table is packed for deployment Even locked in development mode Used for sample data, informational tables etc.

22. Packaging

23. Packaging and deploying Save as app package Install on another SharePoint site Publish to the SharePoint store

24. Add value

26. Wrap-up

27. Related links On-premise installation: http://www.microsoft.com/en-us/download/details.aspx?id=30445 Access for developers: http://msdn.microsoft.com/access Download SPC 2014 presentations from channel 9: http://gallery.technet.microsoft.com/PowerShell-Script-to-all-04e92a63