Chapter 14. Using SharePoint Foundation with Excel 2010 and Access 2010
Chapter at a Glance
✓ | Import data from an Excel spreadsheet to a list in SharePoint. |
✓ | Export a SharePoint list to an Excel spreadsheet. |
✓ | Export an Excel table to a SharePoint site. |
✓ | Export data from an Access database to a list in SharePoint. |
✓ | Import a list to an Access database. |
✓ | Link an Access database to a SharePoint list. |
✓ | Link a data-centric application to a SharePoint list. |
✓ | Work offline. |
Microsoft SharePoint Foundation 2010 provides the collaborative backbone to the Microsoft Office 2010 System. This chapter focuses on the integration of SharePoint Foundation with Microsoft Excel 2010 and Microsoft Access 2010.
Excel 2010, similar to Excel 2007, allows you to export and import data to and from SharePoint lists and provides one-way synchronization from SharePoint lists to Excel spreadsheets, so that you can take the data offline and then synchronize with the SharePoint lists when you reconnect.
The combination of SharePoint Foundation and Access 2010 makes it easy for you to build client–server databases against SharePoint Foundation. In doing so, users who do not possess the skills or privileges to be a Microsoft SQL Server database administrator can still obtain the manageability and stability benefits of storing data on the server while retaining the ease of use of Access 2010. The level of server functionality integrated into Access 2010 increases when used in combination with Access Services in SharePoint Server 2010 Enterprise Edition.
Access 2010, like Access 2007, Excel 2010, and Excel 2007, allows you to export and import data both to and from SharePoint lists. Access also provides two-way synchronization between Access databases and SharePoint lists, so that you can work with lists offline and then synchronize the changes when you reconnect.
Although you can complete the tasks documented in this chapter using Excel 2007 and Access 2007, the steps and screenshots in this chapter were created using Excel 2010 and Access 2010. If you use Excel 2007 or Access 2007, your steps and screenshots will look slightly different. See the book Microsoft Windows SharePoint Services 3.0 Step by Step by Olga Londer, Bill English, Todd Bleeker, and Penelope Coventry (Microsoft Press, 2007) for detailed steps on using Excel 2007 and Access 2007 with SharePoint.
Practice Files
Before you can complete the exercises in this chapter, you need to download the Chapter 14 practice files from the book’s catalog page to the following folder on your computer: DocumentsMicrosoft PressSBS_SPFChapter14. See Using the Practice Files at the beginning of this book for more information.
Important
Install and activate the Office 2010 suite before beginning any of the exercises in this chapter. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercises.
Importing Data from an Excel Spreadsheet to a List in SharePoint
In many situations, you might already have data within a spreadsheet, but later you find that you need to share the data with other members of your team. SharePoint provides the ability to import data from an Excel 2010 spreadsheet into a SharePoint list. Those users who have appropriate permissions may read the SharePoint list, while others may even revise the list or enter additional data. You can choose to import all the data held on a worksheet, a range of cells, a named range, or an Excel 2010 table.
In the following exercise, you will use your browser to create a SharePoint Custom list that contains data imported from an Excel 2010 spreadsheet.
Practice Files
You will use the practice file Furniture_Price.xlsx, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Set Up
Open the SharePoint site to which you would like to import data from the Excel spreadsheet. Remember to use your SharePoint site location in place of http://wideworldimporters in the exercises. If prompted, type your user name and password, and click OK.
Note
BE SURE TO verify that you have sufficient rights to create a list. If in doubt, see the Appendix A at the back of this book.
Click Site Actions, and then click More Options.
The Create dialog is displayed.
In the left pane, under Filter By and then under All Categories, click Blank & Custom, and then, in the middle pane, click Import Spreadsheet. In the right pane, click Create.
The New page is displayed.
In the Name box, type FurniturePrice.
In the Description box, type This list contains the furniture items in stock together with their unit prices.
Important
If you import a spreadsheet into a site based on the Meeting Workspace template, an option appears on the New page to share the same items for all meetings. If you choose not to share the same items for all meetings, then each meeting displays the list with only the items added for that date. Once items become series items for a list, you cannot change the setting back to list items for a specific date.
Click Browse.
The Choose File To Upload dialog box appears and displays your Documents folder (or the last folder that you accessed).
If the Documents folder is not displayed in the Choose File to Upload dialog box, under Libraries, click the Documents icon. Navigate to the Chapter14 practice folder, and then double-click the Furniture_Price.xlsx file.
Troubleshooting
If a dialog box opens, stating that the specified file is not a valid spreadsheet or contains no data to import, then add your SharePoint site as a trusted site or local intranet. If you are using Internet Explorer to add a site as a trusted site, click Tools and then click Internet Options. In the Internet Options dialog box that opens, click the Security tab. Click Trusted Sites, and then click Sites. In the Trusted Sites dialog box that opens, click Add and then Close. Click OK to close the Internet Options dialog box. Once you have added your SharePoint site as a trusted site, repeat this exercise from step 1.
The Choose File to Upload dialog box closes.
On the New page, click Import.
Excel 2010 opens Furniture_Price.xlsx and displays the Import to Windows SharePoint Services List dialog box.
From the Range Type drop-down list, check that Table Range is selected.
Click in the Selected Range drop-down list, choose Stock!FurniturePriceTable, and then click Import.
The All Items view of the FurniturePrice list is displayed, and the URL in the Address box is http://<siteURLname>/Lists/FurniturePrice/AllItems.aspx, where <SiteURLname> is the URL of your site, such as http://wideworldimporters.
To change the title of the list, click the List tab on the Ribbon, and then click List Settings in the Settings group. The List Settings page appears.
Under General Settings, click Title, description and navigation.
The General Settings page appears.
In the Name and Description section, in the Name box, type a user-friendly name such as Furniture Price List.
In the Navigation section, click the Yes option to display this list on the Quick Launch.
Click Save at the bottom of the web page. The List Settings page appears.
On the breadcrumb, click the Furniture Price List link. The All Items view of the Furniture Price List appears.
The title of the list has changed to “Furniture Price List,” but the URL remains as http://<siteURLName>/Lists/FurniturePrice/AllItems.aspx.
Exporting a SharePoint List to an Excel Spreadsheet
You can export the contents of SharePoint lists, results of a survey, or document libraries to an Excel 2010 spreadsheet. The exported list or library is a web query that stays updated with changes to the original list in your SharePoint site. The Excel 2010 spreadsheet maintains a connection to the SharePoint list and therefore becomes a linked object.
In this exercise, you will export a list from a SharePoint site to an Excel 2010 spreadsheet. You will add data to the spreadsheet and then synchronize the data in the spreadsheet with the contents of the list on the SharePoint site.
Set Up
In the browser, open the SharePoint site, if not already open, where you have a list whose contents you want to export to an Excel 2010 spreadsheet. This exercise uses the list that you created in the previous exercise.
Note
BE SURE TO verify that you have sufficient rights to edit items in the list. If in doubt, see the Appendix A at the back of this book.
In the Quick Launch, in the Lists area, click Furniture Price List.
Tip
The export process exports only the columns and rows contained in the list’s current view; in this exercise, that is the All Items view. If none of the views contain the data that you want to export, then you must create a new view to meet your needs. Alternatively, you can choose one of the existing views, export the list to a spreadsheet, and then delete the unwanted data.
On the Ribbon, click the List tab, and then click Export to Excel in the Connect & Export group.
SharePoint generates an Excel web query file, and the File Download dialog box opens.
Click Open.
Excel 2010 opens a new workbook that contains one worksheet named owssvr(1). An Excel Security Notice dialog box is displayed, warning you that data connections have been blocked.
Click Enable.
The Excel 2010 query results are displayed in the owssvr(1) worksheet in an Excel 2010 table. Each column in the list contains an AutoFilter arrow in the header row, and the Design contextual tab is active. Excel 2007 names your table _owssvr_1.
Tip
When you export a SharePoint library, Excel 2010 represents the documents in the list with hyperlinks that point to the documents on the SharePoint site. Similarly, attachments on list items are replaced with a hyperlink. In the Excel 2010 spreadsheet, click this link to open the file.
Tip
You should make a habit of renaming your tables so that you recognize the data they contain. This helps make formulas that summarize table data much easier to understand. To rename your table, first ensure that the Design contextual tab is active, and then, in the Properties group, edit the value in the Table Name field.
Click cell A10, type Antique, and press TAB. Type Bi, and press TAB again.
IntelliSense completes the word Bianca for you.
Type wood, and press TAB. Type 5, and press TAB. Type 10, and then press ENTER.
Excel 2010 places a dollar ($) sign before the number 10.
Click the Data tab, and click Refresh All in the Connections group.
The spreadsheet is updated with a copy of the data from the Furniture Price List on the SharePoint website. Your changes to data in the Excel 2010 spreadsheet are lost, unlike what happens with Excel 2003. In Excel 2007 and Excel 2010, changes that you make to data in your Excel worksheet do not synchronize with the list on the SharePoint website; that is, only a one-way synchronization occurs from the SharePoint site to Excel 2010 or Excel 2007. When using Excel 2003, two-way synchronization is still available.
Click cell A2, and then, in the Connections group on the Data tab, click Properties. The External Data Properties dialog box appears.
You can use this dialog box to alter the behavior of the refresh activity.
Click Cancel.
See Also
You can also initiate the exporting and linking of a SharePoint list to Excel 2010 by using the Access Web Datasheet task pane, which contains four options: Query list with Excel, Print with Excel, Chart with Excel, and Create Excel PivotTable Report. For more information on the Datasheet View and task pane, see Chapter 9.
Exporting an Excel Table to a SharePoint Site
Creating a SharePoint list from within Excel 2010 or Excel 2007 is known as exporting an Excel table. In Excel 2003, this was known as publishing an Excel list. Once the table data is placed on the SharePoint site, users can see the Excel data without opening Excel. As in the first exercise of this chapter, you can maintain a link between the SharePoint list and the Excel data, but any changes in the Excel spreadsheet are not reflected in the SharePoint list. You can only synchronize changes in the SharePoint list to the Excel spreadsheet.
In the following exercise, you will export a spreadsheet to a SharePoint list by using Excel 2010 and a two-step wizard.
Practice Files
You will use the practice file Sales_Figures.xlsx, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Note
BE SURE TO verify that you have sufficient rights to create a list. If in doubt, see the Appendix A at the back of this book.
In Excel 2010, click any cell within the data.
On the Home tab, in the Styles group, click Format as Table, and then choose a table style.
Tip
By selecting one cell in the data, Excel 2010 automatically selects a range of cells that contain data; however, you can select a different range of cells to use when creating a table. In addition, if your data does not contain headers, Excel 2010 creates them for you and labels them as Column1, Column2, and so on.
When the Format As Table dialog box appears, click OK.
Excel 2010 converts the data in the workbook into a table. Each column header contains an AutoFilter arrow and a black border surrounding the data, which means that the table is active on the worksheet.
The Design tab on the Table Tools contextual tab appears.
On the Design tab, in the External Table Data group, click Export, and then, from the drop-down menu, click Export Table to SharePoint List.
The first step of the two-step Export Table to SharePoint List wizard is displayed.
In the Address box, type the name of the site where you want to export the data, such as http://wideworldimporters.
Select the Create a read-only connection to the new SharePoint list check box.
Important
If the Create A Read-Only Connection To The New SharePoint List check box is selected, the spreadsheet is linked to the SharePoint list and you can synchronize updates from the SharePoint list to the spreadsheet. However, once the SharePoint list is created, you cannot link the spreadsheet to the SharePoint list. Therefore, if you wish to synchronize updates between the list and the spreadsheet, be sure to select this check box now.
In the Name box, type SalesFigures, and in the Description box, type This list contains furniture sales for this year.
Click Next.
Excel checks the data in each column to ensure that the data belongs to a data type supported by SharePoint Foundation. If it doesn’t, Excel usually applies the Text data type to each column. Excel also checks whether each column contains only one type of data. If a column contains a mixture of data types, such as numbers and text, then Excel chooses Text as the data type. Once Excel completes its check, the second step of the two-step Export Table to SharePoint Site wizard is displayed.
In the Key Cell column, notice that cell $A$5 in the Product column and $C$4 in the Q2 column contain a different data type from the rest of the cells in their columns. Also, the formulas are removed from the Total column. If you have the region and language format (also known as the date, time, or number format) of your operating system set to English (United States), then columns Q1, Q3, Q4 and Total have a data type of Currency; otherwise, they will have a data type of Number.
Tip
At this point, you can click Cancel, correct the erroneous data, and then restart the export process. Also, because Excel removes formulas during the export process, you may consider deleting the Total column and creating a calculated column once you have completed the export process and the data is on your SharePoint site.
Click Finish. A SharePoint Foundation dialog box is displayed with the URL of your new SharePoint list.
Click the URL of your new SharePoint list. A new browser window opens, displaying the new SharePoint list.
Important
Before you close Excel, notice that the spreadsheet contains two extra columns. When you export a spreadsheet that is linked to a SharePoint list, Item Type and Path columns are added to your spreadsheet. On the Design contextual tab, use the External Table data group to alter the properties of a range of cells, open the connected SharePoint list in a browser, or unlink a list.
Exporting Data from an Access Database to a List in SharePoint
Traditionally, Access uses client database objects to manipulate and display data, tables, reports, and queries, where the Access database is stored on a file system or in a SharePoint library and requires the use of Access on the computer. When you create such a database with Access 2010, the same file format that was used with Access 2007 is created—an accdb file. However, Access 2010 has introduced a new database object—an Access web object where tables, queries, and reports are displayed with a browser using Access Services, a component of SharePoint Server 2010 Enterprise Edition.
See Also
An Office visual instruction on creating web databases with Access 2010 and Access Services can be found at http://msdn.microsoft.com/en-us/library/ff402351(office.14).aspx.
Tip
When creating new database solutions in Access 2010, use a web database template, and check that it is compatible with SharePoint Access Services. Therefore, should you want to create a Web Access service site from your Access database in the future, you will not need to convert your database.
When you integrate data between Access 2010 and SharePoint Foundation, you are limited to using similar methods that were used with Access 2007. However, Access 2010 contains SharePoint connectivity improvements and a wizard that allows you to use the new SharePoint 2010 list improvements, such as enforcing cascade delete and restricting the delete relationships between two lists.
See Also
More information on changes in Access 2010 can be found at http://technet.microsoft.com/en-us/library/cc179181.aspx. Information on SharePoint connectivity performance improvements can be found at http://blogs.msdn.com/b/access/archive/2010/02/05/access-2010-performance-improvements-against-sharepoint-lists.aspx, and details of data platform improvement can be found at http://blogs.msdn.com/b/access/archive/2010/02/15/data-platform-improvements-in-sharepoint-2010.aspx.
Access 2010 consists of a number of tabs, many of which provide a quick way to work with SharePoint websites and lists, as summarized in the following table.
Tab | Group | Description |
Create | Tables | Use the SharePoint Lists drop-down menu to create a list on a SharePoint site and a table in the current database that links to the newly created list. |
External Data | Import & Link | Use the More drop-down menu to import from or link to data on a SharePoint list. |
Export | Use the More drop-down menu to export the selected object as a SharePoint list. | |
Database Tools | Move Data | Use the SharePoint command to move your tables to a SharePoint list and create links to those tables in your database. |
Access allows you to export a table or other database objects to a number of formats, such as an external file, a dBase database, an Excel workbook, a Rich Text Format (RTF) file, a text file, a PDF or XPS file, an email attachment, an Extensible Markup Language (XML) document, an Open Database Connectivity (ODBC) data source, or a Hypertext Markup Language (HTML) document. Beginning with Access 2003, you can also export a table to a SharePoint site where a new list is created.
Tip
Access 2010 does not support linking, importing, or exporting using the Installable Sequential Access Method (ISAM) for Lotus 1-2-3, Paradox, and Jet 2.x.
In the following exercise, you will export a table from within an Access 2010 database into a SharePoint site by creating a new SharePoint list.
Practice Files
You will use the practice file ExpImpWideWorldImporters2010.accdb, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Set Up
Start Access 2010 and open the ExpImpWideWorldImporters2010.accdb database. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise.
Note
BE SURE TO verify that you have sufficient rights to create a list. If in doubt, see the Appendix A at the back of this book.
Under Tables, click FurniturePrices, if it is not already selected, and then, on the Access ribbon, click the External Data tab. In the Export group, click More, and then click SharePoint List.
The Export – SharePoint Site dialog box is displayed.
In the Specify a SharePoint site area, choose the site where you want to export the table, such as http://wideworldimporters.
In the Specify a name for the new list box, type exportFurniturePrices.
Leave the Open the list when finished check box selected.
Click OK. The Exporting Table to SharePoint List dialog box opens, displaying the progress of the import.
The browser opens and displays the newly created list, exportFurniturePrices, in All Items view.
Return to the Save Export Steps page of the Export – SharePoint Site dialog box in Access.
Select the Save export steps check box. The Save As and Description text boxes, as well as the Create Outlook Task areas, appear.
Click Save Export. The Export – SharePoint Site dialog box closes.
To export the data to a SharePoint list, Access queries the Access table for data, which it then copies to the SharePoint list. By saving the export query, you can now repeat the above steps without using the wizard. Your saved exports can be found under the External Data tab in the Export group. Similarly, you can save your export as a Microsoft Outlook task, which you can then configure to remind you to run the export query.
Importing a List to an Access Database
By using Access 2010, you can create a new table by importing data from an external data source, such as a dBase database, an Excel workbook, an Outlook or a Microsoft Exchange Server folder, an XML document, an ODBC data source, or a SharePoint website. The new table becomes an integral part of your database, and the data is not affected by subsequent changes made to the data source after it is imported.
In this exercise, you will import data from a SharePoint list.
Practice Files
You will use the practice file ExpImpWideWorldImporters2010.accdb, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Set Up
Start Access 2010 and open the ExpImpWideWorldImporters2010.accdb database that you used in the previous exercise. You can also use your own list if you want. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise.
Note
BE SURE TO verify that you have sufficient rights to read list items. If in doubt, see the Appendix A at the back of this book.
On the Access ribbon, click the External Data tab. In the Import & Link group, click More, and then click SharePoint List.
The Get External Data – SharePoint Site dialog box appears.
In the Specify a SharePoint site area, choose the site that contains the list with the data that you want to import into the database.
Select the Import the source data into a new table in the current database option.
Click Next. The Import Data From List page of the Get External Data – SharePoint Site dialog box appears.
Select the check box to the left of the list from which you want to import the data, such as exportFurniturePrices.
Tip
When you import data from a SharePoint list, the imported data is based on a view, and only those columns and rows shown in the view are imported. You can select the required view from the Items To Import drop-down list.
The Import page displays values instead of IDs for lookup values stored in another list, and allows you to maintain the lookup relationship that this list may have with other SharePoint lists.
Click OK. The Save Import Steps page of the Get External Data – SharePoint Site dialog box appears.
Click Close. The Get External Data – SharePoint Site dialog box closes, and the exportFurniturePrices table appears under Tables in the Access navigation pane.
Double-click the exportFurniturePrices table. Access opens the exportFurniturePrices table in Datasheet view.
Troubleshooting
If a table is not created from your SharePoint list, then a table called Web Compatibility Issues is created, which lists incompatibilities. Correct the issues and repeat this exercise.
You can now edit the value in the cells of the table. Such changes will not be reflected back in the expFurniturePrice list on the SharePoint website.
Linking an Access Database to a SharePoint List
Data was copied in the previous two sections so that the same data could be stored in both an Access database and on a list on a SharePoint site. However, no data synchronization between these two data locations occurred. If you do not want to maintain two copies of that data but do need to refer to the data within Access, then Access provides methods of accessing external data that are physically located outside an Access database. The easiest way to reference a SharePoint list externally is to use linked tables, which were known as attached tables prior to Access 95. A linked table stores only a connection to the SharePoint list. You should use linking rather than importing if the data is maintained by either a user or a separate application on the SharePoint website.
Access 2010 contains improvements when the link table data is cached in local tables when online with SharePoint, thereby improving large list performance. When server connectivity is lost, the database automatically goes into offline mode. When connectivity is restored, Access automatically synchronizes data changes for you.
See Also
Microsoft Business Connectivity Services (BCS), originally called the Business Data Catalog, is now available in both SharePoint Foundation and SharePoint Server 2010. The definitions that allow BCS to connect to the back-end applications can also be used to reveal external data in Office 2010 applications, including Access 2010, Outlook 2010, Microsoft Workspace 2010, Word 2010, Microsoft InfoPath 2010, and Excel 2010. Information on how to create a BCS definition and how to use the definition in Access 2010 can be found in Chapter 7, “Using Business Connectivity Services,” in the book Microsoft SharePoint Designer 2010 Step by Step, by Penelope Coventry (Microsoft Press, 2011).
In this exercise, you will link a table to a SharePoint list.
Practice Files
You will use the practice file ExpImpWideWorldImporters2010.accdb, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Set Up
Start Access 2010 and open the ExpImpWideWorldImporters2010.accdb database that you used in the previous exercise. You can use your own Access database if you wish. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise.
Note
BE SURE TO verify that you have sufficient rights to edit items in the list. If in doubt, see the Appendix A at the back of this book.
On the Access ribbon, click the External Data tab. In the Import & Link group, click More, and then click SharePoint List. If an Access dialog box opens, stating that all objects must be closed prior to continuing this operation, click Yes to close the objects.
The Get External Data – SharePoint Site dialog box appears.
In the Specify a SharePoint site area, choose the site that contains the list to which you wish to link.
Check that the Link to the data source by creating a linked table option is selected, and then click Next.
The Choose The SharePoint Lists You Want To Link To page of the Get External Data – SharePoint site dialog box is displayed.
Select the check box to the left of the list to which you wish to link, such as exportFurniturePrices.
Click OK.
The Get External Data – SharePoint site dialog box closes. Access has a linked table: exportFurniturePrices1. To the far right of the Access status bar, the text “Online with SharePoint” is displayed.
Right-click the linked exportFurniturePrices1 table, and then select More options.
The browser opens and displays the exportFurniturePrices list in All Items view.
Switch back to Access. Under All Tables, in the exportFurniturePrices1 group, double-click exportFurniturePrices1 to open the linked table in Datasheet view, and then, click the cell in the first row under the Furniture Name column, and type Base Unit.
Click the cell in the second row under the Furniture Name column.
Switch back to the browser, click Refresh, and then verify that the first row has been modified.
On the Ribbon, click the Items tab, and then click New Item in the New group.
The exportFurniturePrices – New Item dialog is displayed.
In the StockNo box, type W0033, and then click Save.
The W0033 item is added to the list.
Switch back to Access. On the Home tab, click Refresh All.
Linking a Data-centric Application to a SharePoint List
Many Access applications grow from the need to manage and aggregate data. These data-centric applications often prove useful to more than one person in an organization, and thus the need to share them increases. However, Access is not truly meant for concurrent use. As Access database applications grow and become more complex, it is necessary to consider upsizing them to a data repository that can support more users while increasing availability, reliability, and manageability. Beginning with Access 2000, various tools and wizards have helped with this process. Starting with Access 2007, you can now upsize your Access database to SharePoint, which is known as moving your Access database.
Note
In Access 2007, moving to and then storing a database in a SharePoint library was known as publishing. In Access 2010, publishing a database is used when moving to and storing your Access database in a special SharePoint site, which is known as a web database site. You can use web database sites only with SharePoint Server 2010.
When you move data from an Access database to a SharePoint site, this process creates a SharePoint list for each Access table. Data from Access tables is moved into these SharePoint lists, and each data row becomes a list item in a SharePoint list. Tables in the Access database are replaced with linked tables that point to the newly created SharePoint list or lists. The Access database now becomes a user interface to the data by retaining views, reports, and relationships between tables.
Tip
Unlike in Access 2007, saving the Access database in a SharePoint library cannot be completed as part of the move process. When an Access database is saved to a SharePoint list, and users open the database in Access to make design changes, the last person who uploads the changed database back to the SharePoint library overwrites changes by other users. Therefore, use the checkout and check-in functionalities of SharePoint libraries when making database design changes.
Because the data is now in SharePoint, you can use SharePoint functionality. For example, you can restore deleted list items from the Recycle Bin and apply workflow rules to data items. If you choose to save the database in the document library, users who want to use the database can navigate to the document library in a browser, where the database can be opened in Access.
Prior to Access 2007, multiple users kept their own copies of an Access database and amended it separately, often not viewing others’ amendments until they were included in official documents and the need to amalgamate the changes was recognized. To allow users to keep their own copy of a database, a business process would need to be introduced to maintain the data integrity of the database and distribute updates to the appropriate users. By using the process outlined here, Access 2007 and Access 2010 users can add and modify data by using either SharePoint or the linked tables within the Access database. New views, data relationships, and reports maintained in the Access database file can be managed as any other document when saved in SharePoint, including check-in and checkout facilities. Security on the data and the Access database can be maintained using SharePoint security. To take advantage of these new features, you must move your data from your Access database to SharePoint.
See Also
More information on developing Access 2010 hybrid applications can be found at http://blogs.msdn.com/b/access/archive/2010/07/20/the-access-show-developing-access-2010-hybrid-apps-with-dick-moffat.aspx.
In the following exercise, you will move data from within an Access database to a SharePoint site.
Practice Files
You will use the practice file MoveWideWorldImporters.accdb, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Set Up
Start Access 2010 and open the MoveWideWorldImporters.accdb database. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise.
Note
BE SURE TO verify that you have sufficient rights to create lists. If in doubt, see the Appendix A at the back of this book.
On the Access ribbon, click the Database Tools tab. In the Move Data group, click SharePoint.
The Export Tables to SharePoint Wizard is displayed.
In the What SharePoint site do you want to use? text box, type the name of your SharePoint site, such as http://wideworldimporters.
Click Next.
The Moving Data to SharePoint Site dialog box displays the progress of the move operation, eventually stating that the tables are shared successfully and noting whether any issues were encountered.
Select the Show Details check box.
The Move to SharePoint Site Wizard dialog box displays the tasks that it completed during the move operation. Note that a backup of the database is made.
Click Finish. In the left navigation pane in the Supporting Objects group, the three Access tables—Customers, Employees, and Opportunities—are now linked tables.
Click File to display the backstage view of Access.
In the left navigation pane, click Save & Publish.
Under File Types, verify that Save Database as is selected and then, under Save Database As, select SharePoint.
Click Save As. The Save to SharePoint dialog box is displayed.
In the File name box, type the URL of the SharePoint site that contains the document library where you wish to save your Access database, such as http://wideworldimporters, and then press ENTER.
The All Site Content of the SharePoint site is displayed in the Save to SharePoint dialog box.
Double-click Shared Documents. The contents of the Shared Document library are displayed.
Click Save. The Saving dialog box displays the progress of the save operation. A yellow toolbar labeled Save Changes appears below the Ribbon.
Working Offline
In the previous section, when you moved an Access 2010 data-centric application to a SharePoint site or imported data from a SharePoint list, you created linked tables where you can view and update the data using Access. In these tables, the data is stored outside Access. However, you might still like to access the data in a disconnected environment. When using Access 2007 or Access 2010, you are able to cache SharePoint list data locally in an offline mode. The data that is held locally is not independent of the data in the SharePoint list. You may synchronize changes back to the SharePoint site any time you want.
Tip
When an Access database has many linked tables pointing to large SharePoint lists, it may be advisable for performance and storage reasons to create a Access database specifically for users who are constantly taking data offline and are required to synchronize that data over slow network connections.
In this exercise, you will synchronize data and a metadata column with a table linked to a SharePoint list when working offline. This exercise uses the linked tables that were created during the move operation in the previous exercise. You can also use your own tables that are linked to a SharePoint list if you want.
Practice Files
You will use the practice file MoveWideWorldImporters.accdb, located in the DocumentsMicrosoft PressSBS_SPFChapter14 folder.
Set Up
Start Access 2010 and open the MoveWideWorldImporters.accdb database that you saved in the Shared Documents library in the previous exercise. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise.
Note
BE SURE TO verify that you have sufficient rights to edit items in the lists linked to the Access tables. If in doubt, see the Appendix A at the back of this book.
On the Access ribbon, click the External Data tab, and, in the Web Linked Lists group, click Work Offline.
The text “Offline With SharePoint” appears on the status bar. On the External Data tab, in the Web Linked Lists group, the Synchronize and Discard Changes commands are enabled. All data is cached within the Access database, and links to the SharePoint lists are cut temporarily.
Under the Supporting Objects group, double-click Opportunities.
Access opens the linked Opportunities table in Datasheet view. The application behaves much as it did online.
Right-click Opportunities, select More Options, and then click Open Default View. The browser opens and displays the Opportunities list in All Items view.
Switch back to Access. In the Datasheet view of the Opportunities table, click the cell in the first row under the Title column and type Bianca Corner Unit. Click a cell in the second row.
Troubleshooting
If an Access dialog box opens, stating that a value must be greater than 1/1/1900, click OK to close the dialog box. Press the ESC key to discard your changes. On the External Data tab, in the Web Linked Lists group, click Work Online, and then repeat steps 1, 2, and 4.
A dimmed pencil icon in the first column of the first row indicates that you have made changes to this row. When you are online with the SharePoint website, moving out of the row that you are editing causes Access to synchronize changes. The dimmed icon indicates that this has not occurred here.
Switch back to the browser. Click Refresh, and then verify that the first row has not been modified.
In the browser, click the cell in the first row under Title, type Woodland Bench, and then click a cell in the second row.
Switch back to Access. On the External Data tab, in the Web Linked Lists group, click Synchronize.
Access temporarily connects to the SharePoint list to synchronize changes. The Resolve Conflicts dialog box appears.
Click Discard My Changes. The Access database remains offline, and the Datasheet view of the Opportunities table closes.
Under Supporting Objects, double-click Opportunities.
Access opens the linked Opportunities table in Datasheet view, and the Title column of the first row contains the text “Woodland Bench.”
In the Datasheet view of the Opportunities table, click the cell in the first row under the Title column and type Bianca Corner Unit. Click a cell in the second row.
On the External Data tab, in the Web Linked Lists group, click Work Online.
The text “Online with SharePoint” appears in the status bar, and the Datasheet view of the Opportunities table closes.
Switch back to the browser. Click Refresh, and then verify that the first row contains the text “Bianca Corner Unit.”
On the Ribbon, click the List tab, and then, in the Manage Views group, click Create Column.
In the Name and Type section, in the Column name box, type Advertisement and select the Yes/No option. Click OK.
The Opportunities page is displayed with the Advertisement column at the end of the list.
Switch back to Access. Under Supporting Objects, right-click Opportunities, click More options, and then click Refresh List.
Open the Opportunities table, if necessary, and check that the Advertisement column is visible.
Key Points
You can create a custom list from the browser by importing data from an Excel 2007 or Excel 2010 spreadsheet.
You can create an Excel 2007 or Excel 2010 spreadsheet from the browser and export data into it from a SharePoint list.
From within Excel 2007 or Excel 2010, you can export data from an Excel table into a newly created SharePoint list.
You can synchronize changes between a SharePoint list and an Excel 2007 or Excel 2010 spreadsheet. This is a one-way synchronization process.
Integration with SharePoint Foundation makes Access 2010 a great collaboration tool, while also enabling data to be stored on enterprise servers for better manageability.
Access 2010 allows you to export and import data to and from SharePoint lists. Data in the Access table is not affected by subsequent changes made to the SharePoint list because there is no synchronization process between Access 2010 and a SharePoint site.
When using Access 2010, you should create a new table that’s linked to a SharePoint list where data is maintained by users on the SharePoint website and you want Access 2010 to use the most current data. This functions well for data that are shared between people and enables you to take advantage of SharePoint features such as workflow, security, and searching.
You can use linked tables in Access 2010 to enter data into SharePoint lists, where two-way synchronization is provided, and you can maintain a cache to use when working offline.
In Access 2010, you can move a database to a SharePoint site. Data is moved into SharePoint lists, and then, if you want, you can save the Access database in a document library.