How to Integrate MS Access 2010 with SharePoint Foundation 2010

By now, you may have heard about the new integration and web database publishing features possible with Microsoft Access 2010 and Microsoft SharePoint Server 2010 Access Services. However, you might find yourself asking, “What about SharePoint 2010 Foundation?”

Here’s some good news. MS Access 2010 and SharePoint Foundation 2010 have some integration points as well (these apply to SharePoint Server 2010 too). In this article, we’ll cover all of the Access 2010 Integration features as they pertain to hosted SharePoint Foundation 2010.

Microsoft Access 2010 Integration Features

Export Data to a SharePoint 2010 List from Access Tables

This nice integration point will create SharePoint lists that correspond to your Access tables and populate them with the Access table data.


NOTE: This has also piqued the interest of our SharePoint 2010 engineering team because it creates a potential migration path from WSS 3.0 to Foundation 2010. Stay tuned for more about this. You can also get ahold of us if you would like more information about our continuing work to ease the SharePoint 2010 migration pains of our clients.


Ok… on to the features!

Let’s say we have an Access 2010 table with some columns and data.

How to automatically link Access Databases to SharePoint Lists

ADDITIONAL NOTES:

If you try to export to a SharePoint list that already exists, a “_1” will be appended to the list name.

The foreign key relationships are automatically converted to lookup columns between the newly created SharePoint lists. Also, Cascade deletes are kept intact in both Microsoft Access and your SharePoint 2010 site.

To move all your Access 2010 tables to SharePoint and create lists for them, use the “SharePoint” button on the Database Tools Tab (shown below).

How to move all your Access 2010 tables to SharePoint 2010


How to Import SharePoint 2010 List Data Into Access 2010 Tables

This feature allows you to do one of two things:

1. Do a one-time import of SharePoint 2010 list data into a new table in a MS Access 2010 database.

2. Create a linked table so that changes to the data in SharePoint or on Access 2010 will be synced.

Here’s an example list of companies in our SharePoint 2010 Foundation site:

An example list of companies in our SharePoint 2010 Foundation site

We want to work with this data in Access 2010. We can import or link the data between SharePoint Foundation and Access 2010 from 2 different places; SharePoint’s UI or the Access 2010 Client.

Linking SharePoint data to Access 2010 from the SharePoint UI

When viewing our list in a SharePoint Foundation site, click on the “List” tab under “List Tools”, you’ll see the ribbon pictured below – Click on “Open with Access”.

SharePoint Foundation List Tools List Tab

Access dialog box asking how you would like to use the SharePoint dataA dialog box pops up (pictured right), asking how you would like to use the data. Choose “Link to data on the SharePoint site”.  This will ensure that data changes from either location are synchronized.

Click “OK”. Access 2010 will be launched (you must have this installed on your workstation).

Access will prompt you for your SharePoint credentials. Once you correctly enter them, you should see your newly created Access table that is being generated from the data in your SharePoint 2010 site.

Now that you’ve linked the data, you have the ability to save a local copy of the Access Database to your workstation for further use.

ADDITIONAL NOTES:

If you are exporting the table, SharePoint cannot overwrite an existing table. You will need to delete the old one and then export the SharePoint list again.

Linking SharePoint Data to Access 2010 from the MS Access Client

Pretty cool, eh’?  You can even create an Access 2010 application which runs on your local machine with custom forms and use it to update multiple SharePoint lists simultaneously. The data would then be available to all users of your hosted SharePoint 2010 site!

How can I tell which tables are linked to SharePoint 2010 from inside the MS Access Client?

The tables with the orange icon and a blue arrow are linked. (pictured below)

How can I tell which tables are linked to SharePoint 2010 from inside the MS Access Client?

While all of these Microsoft Access 2010 integration features for SharePoint 2010 work well in SharePoint Foundation, they are truly supercharged when you step up to SharePoint Server 2010 hosting plans with Access Services from Fpweb.net. SharePoint Server 2010 enables the creation of full Access 2010 applications in the Access client with forms and reports that can be published to your SharePoint server, giving you a user-friendly scalable Access web database that is accessible from anywhere in the world.








2017-11-27T12:42:32+00:00July 28th, 2010|

26 Comments

  1. Connie Evans August 2, 2011 at 12:28 am - Reply

    Thanks for this guide!!!

    I was able to get the link to work with the Sharepoint list in two different sites in a site collection. I first created the SP list, and then linked it in Access using the MS Access Client. Then, I went back to SP and in SP Designer created a global view of the list which I saved to the gallery. In a subsite, I created a webpart (again in SP Designer) using the global list. All three sites are now linked and changes made in any one show up in the others.

  2. CSigler October 18, 2011 at 8:14 am - Reply

    Andy –

    I have a SP 2010 site with various custom lists and the newitem forms were modified in InfoPath 2010. I have an Access 2010 table that has data pulled from our AS/400. I need to be able to link SP to Access so that I can display the Access data in my form and write back to the Access table. There are so many posts out there and I am very confused which seems to be my constant state lately trying to implement SP. )

  3. aru October 31, 2011 at 3:21 am - Reply
  4. beatnikthedan November 28, 2011 at 2:06 pm - Reply

    I am trying to link a list that has more then 5,000 items in it and access won’t let me open it because it says my server doesn’t let me view this many items??? i can’t change it cause we’re using office 365. is there another way to fix this???

  5. shruti November 29, 2011 at 7:58 am - Reply

    Hi,

    i don’t know how to auto update sharepoint table in access. i know that if you right click on table and select refreshtable but i want auto update every 1 min or when we open next time.

    shruti

  6. Rx_ January 16, 2012 at 4:07 am - Reply

    Converting MS Access 2010 to Sharepoint 2010
    My first step will be to move the back end database to SQL Server.

    The things missing in most articles are the business rules written in VBA. The forms use VBA to validate business process, limit list box choices, and otherwise guide users through a complex set of regulatory processes across dozens of forms. Any advice would be very appreiciated.

  7. musique gratuitement February 28, 2012 at 4:18 am - Reply

    Thanks for the information

  8. David Breitstein May 14, 2012 at 5:27 pm - Reply

    Great article!
    I have a backend DB written in Access 2003, and a front end using Access 2010. I want to push the recoeds into a Foundation 2010 list. Do we need to convert/import the 2003 DB into Access 2010 first? Is that possible?
    Thanks in advance-

  9. Feroz May 15, 2012 at 9:46 am - Reply

    Hi Thanks for your article.

    I have a sharepoint site (2003) and in that I have different views for many lists, previously in MS Access 2003 I could link to whatever view I want but I could not find a way to link to a view of a list in MS Access 2010……please could you help me on this…

    Many thanks for your help.

    Feroz.

  10. SIDDIQALI November 19, 2010 at 1:01 am - Reply

    Thanks it works very simply specified

  11. RJ November 19, 2010 at 3:33 pm - Reply

    What if we want to use sharepoint lists externally through odbc for ms access linked to sharepoint lists? If we create list items using sharepoint, would we need to close and open Access to see the changes or the changes to list in sharepoint be available in ms access?

  12. Jim Parker February 5, 2011 at 12:21 pm - Reply

    @RJ: You woudn’t need to close MS Access, but you would need to refresh the table/query/form/report if you already have it open. Hit the F5 key and presto, you’ll see any new data entered into Sharepoint now appear within MS Access.

    On the flip side, it’s even better. Sharepoint 2010 supports auto-ajax refreshes, so any data you entered in MS Access, would automatically appear in your Sharepoint view/webpart in just a few seconds (or whatever polling setting you have).

    Hope this helps.

  13. Microsoft Access training March 17, 2011 at 8:57 am - Reply

    Great article – I’ve been testing some of these features with Sharepoint Foundation this morning. Do you know if Sharepoint 2010 can connect directly to SQL databases or if it is possible to create data connections? Thanks

  14. Anna April 26, 2011 at 9:14 pm - Reply

    Great article. I have a security requirements that users will not be able to save a local copy of Access database. I have existing Access application and Access clients installed for each user. I linked tables to the SharePoint lists and created forms. I need to be able to prohibit users from saving copy of lists data locally. Is it possible with Microsoft Access 2010?

  15. Xeno May 6, 2011 at 7:32 pm - Reply

    What if when updating the text, you get a notice…

    You cannot update this field because the value you’re trying to apply is not valid or would break a data integrity rule. Please correct and try again.

  16. Mai Omar Desouki August 6, 2012 at 2:15 am - Reply

    Hi,

    That was a great one..

    Thanks.

  17. Pam November 6, 2012 at 5:49 pm - Reply

    I have created macros in MS Access that pull data into a table from Sharepoint. One of the fields in the Sharepoint database is linked to another db in Sharepoint and shows as a number in MS Access instead of the supplier name when I pull it into a MS Access table. How can I get it to show the actual name of the supplier instead of a numeric value?

  18. Pam November 6, 2012 at 5:56 pm - Reply

    Everytime I pull data from a Sharepoint database into MS Access to create a new table, there is a field that shows up as a number. This field happens to be a linked field in Sharepoint. How do I get the supplier name to show up in the MS Access table instead of a number when I am importing this list?

  19. http://tresbellepeau.com/cleanser.html January 13, 2013 at 7:13 pm - Reply

    Hi there, You’ve done a great job. I will definitely digg it and personally recommend to my friends. I’m confident they’ll be benefited from this web site.

  20. Jeff Simpson August 2, 2013 at 12:08 pm - Reply

    I am running on 2010 office, 2010 Access and 2010 Sharepoint SBS

    I ran into a problem recently. When I would click “Open with Access” I would get “Export to database failed”.

    Not much on the internet on how to fix this problem but what I had to do was remove “Service Pack 2 for Micrsoft Office 2010” KB2687455.

    The last few days were frustrating to say the least. Just wanted to share so others could avoid the frustration.

  21. Jeff Simpson August 2, 2013 at 12:15 pm - Reply

    I have simple forms that I use to edit SP content. It works fine. However I have a form that is populated with fields from two different linked lists. The information displays just fine but I am not able to edit any of the comment fields from the Access form. I have looked at the field attributes to make sure they are not locked. Any idea how to fix this problem?

  22. Joe DeLeon August 9, 2013 at 5:09 am - Reply

    @Jeff Simpson
    Did you fix the “export to database failure” issue by removing the SP2 for Micrsoft Office 2010 KB2687455?
    I’m having the same problem.
    thanks,
    Joe

  23. Chris October 11, 2013 at 6:32 am - Reply

    Hi Joe,

    The workaround with uninstalling SP2 of Office 2010 worked for me. But it’s not a solution for me, because we’re working with Citrix and I don’t want to uninstall SP2 on all Citrix servers just because of this. Btw: when I installed SP2 again, the error showed up again.

    If someone has a real solution, please let me know!

    Regards,
    Chris

  24. Shahid January 8, 2014 at 6:56 am - Reply

    Thanks. Its good alternative for SP error “Export to database failed. To export a list, you must have a Microsoft SharePoint Foundation-compatible application”.

  25. Chetan May 9, 2014 at 1:52 pm - Reply

    Resolution1: Apply Hotfix
    Link: http://support.microsoft.com/kb/2553170/en-us

    Resolution 2: Import a SharePoint List OR Link to a SharePoint List from MS Access:
    Link: http://office.microsoft.com/en-in/access-help/import-from-or-link-data-to-a-sharepoint-list-HA010341758.aspx

  26. MaddamX November 4, 2016 at 9:05 pm - Reply

    Digg

    While checking out DIGG today I found this

Leave A Comment