Back
Access

Connect your Access 2013 Web Apps to SharePoint Lists

This post was written by Lois Wang, a Program Manager on the Access team.

Access 2013 web apps are great places to centralize your data. Whether you’re tracking people, events, products or something else, storing data in an Access app allows you to easily collaborate with others while keeping things organized.

Sometimes, though, the stuff you care about is already stored somewhere else. Although you could import the data into Access, those external sources may be maintained by other people or processes. In these cases, you want to make sure that as these sources are updated, you’re always seeing the latest version in Access. Wouldn’t it be great if you could simply link to these data?

Access 2013 makes this easy. The web apps you create with Access 2013 can connect to and display real-time data from SharePoint lists. That way, you can easily supplement or combine external data sources with the things that your app uniquely tracks.

Imagine a scenario where a small business owner named Ryan is trying to manage a party planning company. He and his five employees use Office 365 for sharing information. His accountant manages all the suppliers of his business in a SharePoint list. Ryan has built an Access 2013 web app to manage all the parties that he is in charge of planning. He wants to pull supplier information into his app, but he doesn’t want to have to worry about manually keeping his app in sync with the accountants list. How can he do that?

The PartySuppliers SharePoint list.

He opens up his Access 2013 web app in the Access designer and clicks the Create Table button in the ribbon. Then, under the heading “Create a table from an existing data source,” he chooses “SharePoint List.”

Add a new table from an existing data source.

The next step is to provide the URL of the SharePoint site where the PartySuppliers list lives. Since he wants to link to rather than import his data, Ryan selects “Link to the data source by creating a linked table.”

The External Data wizard.

Access will go fetch the names of the lists on that site, and Ryan selects the one he wants—the PartySuppliers list. In order for Ryan set up this link, his account’s permission level needs to be “Full Control” for the PartySuppliers list in SharePoint.

Assign permissions to allow your Access app to read the items in the SharePoint list. 

Now, the supplier data show up in Ryan’s project management app. Access automatically creates a List view and a Datasheet view for displaying the suppliers. It looks and feels just like the rest of his Access web app.

 Access automatically creates List and Datasheet views to display the data in linked SharePoint lists.

Ryan can further integrate this SharePoint list into his app by adding a lookup field in his Events table to show which PartySupplier is working on which Event. Even though the PartySuppliers “table” is actually stored externally, setting up this relationship works exactly as it would if the data were stored in a local table.

Two things are worth noting about this external data feature in Access 2013 web apps. First, Access currently only supports read-only connections to SharePoint lists. In our example, that would mean that in order to change information about a supplier, Ryan would have to edit the SharePoint list directly.

Second, in order to set up a connection to an external List, your user account has to be allowed to change permissions to the List. This is because when you set up the connection, you need to give the Access web app itself the right to read the data. The right to grant other accounts or apps access to a List is usually included with the “Full Control” or “Owner” SharePoint permission groups. If you have trouble, check with the person who is in charge of your SharePoint site.

Access 2013 web apps can easily integrate with external SharePoint lists. You can try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

Join the conversation

8 comments
  1. Unless I’m mistaken, I think an important "gotcha" to mention is that since the new Access App are stored in Azure SQL instead of SharePoint lists, there’s currently no way link two Access App together.

    In other word, any table that has a remote chance to be reused by more than one Access App (i.e: Employee, Clients, Products, Assets, etc.) should be stored in an SharePoint list… where sadly it’ll live only as a read-only object in the Access App.

    To be honest, I’m still a bit puzzle on how to best use the new Access Apps because of these limitations. I was thrilled to learn that the new web DB would be backed by Azure which I personally believe to be the way of the future, but their present implementations have, in all respect, trun this into a real head-scratcher for me.

  2. OK, I’ll bite.

    What if the SharePoint List is backed by a BCS External Content Type?

    Will the Access Web App still be able to link to the SharePoint List?

    You didn’t specify that this wouldn’t work in this post, but you did in answers to comments in a different post on this blog in August.

  3. I’ll be honest….the complicating factor in all of this from my point of view is the whole "web app" part. Correct me if I’m wrong but if I can move my native tables to either SP lists or SQL Azure tables, I will just post my Access FE to SharePoint and let user save a copy locally and then all my VBA code will still work and I can be linked to SP & SQL Azure ?? I am just playing around with Access/SharePoint 2010 now and creating web apps with all macros is not worth the effort. You got us to the web Microsoft but right now I’ll stick with accdb format & all my VBA code I’ve written over the years.

  4. I am unable to link to any SharePoint 2010 lists.

    Are SharePoint 2010 lists supported?

    What happens with Attachment fields?

  5. If I try to link my existing SharePoint list with an Access-App I get following error (translated from German error message):
    Access-Apps can only connect to lists which have the some cultural (regional?) settings. Try to change the cultural settings of the list or connect to a different list.
    Correlations-ID: a7b6e09b-7e57-3004-53fe-d5df664f172c
    Any tips how to solve the issue. I changed the settings anywhere possible but nothing solved the issue. I use SharePoint 2013 in Office 365. Any advice?

  6. Since all Access Web Apps tables are stored on SQL Azure and not anymore in SharePoint lists, what options do I have to manage individual user permissions for my Access Web App? Example: A user should have edit rights to Customers, but only read rights to Suppliers.

    Any input would be appreciated.

  7. I am in a group that builds applications using InfoPath and Workflows to automate business processes. It looks like there are some limitiations on the forms that are created by Access 2013 and I am not sure how workflow technologies can be tied in to Access Data? I could imagine connecting Access to a SharePoint list and having the workflows run on the list, but this seems very cumbersome. Can you share any wisdom about how Access 2013 dovetails with InfoPath and Worklflow technolgies?

Comments are closed.