MS Access 2010 integration 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
| 1.
Click the External Data Tab > More > SharePoint List |
 |
|
|
|
| 2.
Next, choose the SharePoint Foundation 2010 site where the new List should be created. Click OK. |
 |
|
|
|
| PRESTO! Your default browse view should now open up right to your new, automatically-populated SharePoint list full of the data from your MS Access tables! | |
 |
|
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).
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:
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”.
A 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
| 1. Now, we’re going to open Microsoft Access 2010 and go to File > New > Blank Database Give the database a name and click Create. |  |
|
|
|
| 2.
Next, go to the Create Tab, and go to SharePoint Lists > Existing SharePoint List |
 |
|
|
|
| 3.
Enter in your site’s domain name and select the “link” option. This way, changes we make within Access 2010 will be reflected in our SharePoint Foundation 2010 site. Important: If you are working with lists in a SharePoint 2010 sub-site, you must enter in the full URL to the sub site. |
 |
|
|
|
| 4.
Select the lists you’d like to work with. For this example, I’m going to use the “list in SP2010”, a custom list I created. |
 |
|
|
|
| As you can see below, a new table was created in our MS Access 2010 DB called “List in SP2010”, reflecting the column structure of the existing SharePoint list. All of the data was imported. | |
 |
|
| You can also add a new row in the table, click save, go back to the SharePoint 2010 list in your browser, and hit refresh. You will see the row that was added via Access 2010. | |
 |
|
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)
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.