Pros and Cons of the Access App

Pros and Cons of the Access App for SharePoint 2013

Why are Access 2013 Apps Great? Here are a few reasons…

• They have a SQL Server backend
• You can use SQL Server Reporting Services, Excel or any other tools that support SQL Azure or SQL Server over ODBC to generate reports on the Access App data
• Views and navigation are created for you when you use App Templates or Tables
• There are some new Related Item controls that make building views easy and they have a consistent look and feel
• One Click Launch!
• The Search functionality is built in and is intuitive

What are SharePoint 2013 Apps?

Before diving into the details on this new Access App, we should do a quick overview on SharePoint 2013 Apps in general. Included in this most recent version of SharePoint is the App Model. It enables developers to create custom apps that can be published to the Office Store for public download or to the Corporate Catalog which is an organization's internal App Catalog Site then users can download them to their SharePoint sites. Each app, whether custom or out-of-box, targets a specific set of features and are lightweight and easy to use. Included in the out-of-box apps is an Access App that enables Access 2013 databases to be added to SharePoint 2013 sites.


What is the Access App?

This out-of-box, no-code app enables us to put Access databases into SharePoint and includes some really great features (listed above) that I will get into a bit more detail in the following sections. The purpose of the app is to provide a more reliable, faster and robust solution for putting relational data into SharePoint without the hassle of designing and developing something from scratch. Microsoft Office Access 2013 includes a few templates for Access Web Apps and tables that will get you started.


The Best Access App Feature

The favorite is that its backend is SQL Server, or SQL Azure if you're using Office 365. This design allows data to display faster, is more reliable and robust plus long-term it's more manageable. It's a great alternative to creating a list in SharePoint when you know it will grow to be a "large list". Not only does it help manage large lists and provide quick access to the data, it also allows outside SQL Server and SQL Azure supported tools to gain access to the data.


Want to know how it works?

• When you create the app in Microsoft Office Access 2013, you choose the site where it will live.
• In the process launching the app to SharePoint, a SQL database is provisioned that will house all the objects and data that the app requires.
• The database that is created is specific to your app and by default not shared with other apps.
• When you create a table in your app, a table is created in the database.
• When you create a query in your app, a SQL Server View is created or if your query takes a parameter, a table-valued function is created.
• When you create a Standalone Macro in your app, a Stored Procedure is created in SQL Server.
• Views in Access are the parts of your app that display the data in the browser. These are also stored in the database but as text since they are HTML and JavaScript rather than SQL objects.

Other Really Great Features that are Worth Mentioning

When creating the Access App, you can select from one of the quick and easy templates or start from scratch with a custom app. When using one of the templates, Access automatically creates tables and related views around those tables. Also, the navigation is created for you so your database is ready to use. You would only need to add your customizations if you require any then click Launch App. That's it, in just a few clicks you have a working SharePoint App. Even if you're going with the Custom option, you still get a lot of automatic features like table templates that include multiple tables with relationships, related views and navigation. Either way, once you've designed your database, click Launch App and you have a no-code app in SharePoint that includes a search tool.


Wondering about Workflows?

The data in the Access App is stored in a SQL database and SharePoint doesn't have a mechanism that can get notified when items change in the external data source so the workflow couldn't be directly associated with one of the tables in your app. Using the linked table feature in Access to connect to a SharePoint list isn't going to help because that creates a read only connection. A possible solution would be to consume the external data in a workflow. You could create a site workflow or a list workflow and have it read or update from an external list. Basically it can be done but isn't going happen without some difficulty.


Wrap it up…

With this app, you can create web-based applications that use the power of SQL Server on-premise or in the cloud. You don't have to worry about deployment challenges, software installation issues or operating system compatibilities. You just build your app and share it across the web with SQL Server or SQL Azure. This new architecture increases performance and scalability and opens up new opportunities for SQL Developers to extend and work with the data. It has potential of being a really great app. Add built-in features like full read/write when connecting to SharePoint lists and workflows and you've got a truly awesome out-of-box solution.


A Note on SharePoint 2010 vs. SharePoint 2013 Access Services

SharePoint 2010 also has Access Services but the process around how the Access databases are put into SharePoint is very different. The tables in the 2010 database are converted into SharePoint lists so you don't get the SQL backend. These Web Databases are compatible with SharePoint 2013 and Access 2013 but you cannot create new Web Databases in Access 2013, you are only able to manage existing ones and publish them to either SharePoint 2010 or 2013. Also, there is no way to automatically convert the Web Database into an Access App. You will have to do this manually which consists of importing the data from the Web Database into the new Access App then recreate the interface and business logic.