The Pros and Cons of the Access App for SharePoint 2013

Published 11/05/2018 07:33 AM   |    Updated 11/14/2018 08:38 AM
This article originally appeared on Dec. 10, 2013.
 

Why are Access 2013 Apps great? Here are a few reasons:

 
  • They have a SQL Server back end.
  • You can use SQL Server Reporting Services, Excel or any other tools that support SQL Azure or SQL Server over Open Database Connectivity (ODBC) to generate reports on the Access App data.
  • Views and navigation are created for you when you use app templates or tables.
  • New related item controls make building views easy, and they have a consistent look and feel.
  • One-click launch is a reality.
  • 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 where users can download them to their SharePoint sites. Each app, whether custom or out of the box, targets a specific set of features and is 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’ll cover in a bit more detail in the following sections. The purpose of the app is to provide a more reliable, faster, 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 feature of the Access App is that its back end is SQL Server, or SQL Azure if you're using Office 365. This design allows data to display faster, is more reliable and robust, and it's more manageable long-term. 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, but it also allows outside SQL Server and SQL Azure supported tools to gain access to the data.

 

How it works

 
  1. When you create the app in Microsoft Office Access 2013, you choose the site where it will live.
  2. In the process launching the app to SharePoint, a SQL database is provisioned that will house all the objects and data the app requires.
  3. The database that’s created is specific to your app and, by default, not shared with other apps.
  4. When you create a table in your app, a table is created in the database.
  5. 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.
  6. When you create a Standalone Macro in your app, a Stored Procedure is created in SQL Server.
  7. 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’re HTML and JavaScript rather than SQL objects.
 

Other 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 and 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 such as 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. SharePoint doesn't have a mechanism that can get notified when items change in the external data source, so the workflow can’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 doesn’t 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 premises 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 new opportunities for SQL developers to extend and work with the data. It has the potential of being a really great app. Add built-in features such as full read/write when connecting to SharePoint lists and workflows, and you've got a truly awesome out-of-the-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 back end.

 

These web databases are compatible with SharePoint 2013 and Access 2013, but you can’t create new web databases in Access 2013. You’re only able to manage existing ones and publish them to either SharePoint 2010 or 2013. Also, there’s no way to automatically convert the web database into an Access App. You’ll have to do that manually, which consists of importing data from the web database into the new Access App and recreating the interface and business logic.

Is this answer helpful?