In Microsoft SharePoint terminology, a table is referred to as a list that stores information about a single subject. In a list you have columns (fields) that contain the different kinds of information about the subject. Similar to how you work in Access 2010, you can work with lists in different views for adding and editing records. In order to import or link to a list from a SharePoint site into an Access database, you need to have appropriate permissions to the SharePoint site. Contact your SharePoint administrator to give you permissions if you are having trouble accessing the SharePoint list.
Importing a list from a Microsoft SharePoint site works in much the same way as importing a table from another data source such as text files, spreadsheets, other Access databases, or SQL databases. In this case, you are downloading data from a Microsoft SharePoint site and saving a local copy of the data in an Access table. After Access creates the table and imports the records, you can use all the powerful tools at your disposal in Access—queries, forms, and reports—to analyze the data.
To begin the import process in Access, click the More button in the Import & Link group on the External Data tab and then click SharePoint List, as shown in Figure 8-56.
Access opens the first page of the Get External Data – SharePoint Site wizard, as shown in Figure 8-57. You can use this wizard to either import or link to SharePoint Services lists. We’ll discuss linking in the next section. Under Specify A SharePoint Site, enter a valid address to a SharePoint Services site or subdirectory. Any SharePoint Services sites that you have previously imported from, linked to, or exported to are displayed in a list box. If one of these sites is the location from which you want to import the table, you can click that address and Access fills in the address text box below the list with that link. Enter a valid SharePoint Services address in the text box, or select a previously visited SharePoint Services address from the list box. Select the first option, Import The Source Data Into A New Table In The Current Database, to import the list and records to a local table and then click Next.
The second page of the wizard displays all the lists found in the SharePoint Services site directory that you specified on the previous page, as shown in Figure 8-58. Select a check box in the Import column to specify which list to import to Access. The Type column displays icons representing the different types of lists. The Name column displays the names of the lists on the SharePoint site. The fourth column, Items To Import, shows a list of views. If the list has more than one view defined in SharePoint, you can select which specific view you want to import. The default view, All Contacts, is the only view defined in our example. The last column, Last Modified Date, displays the date the list was last modified.
Near the bottom of this page is an option to import the display values from any lookup fields instead of the actual lookup field ID. If you think a list has one or more related lookup lists, and you want to fetch the linking ID instead of the lookup value, clear this check box so that you fetch the actual ID value. For example, if an Orders list is related to a Customers list, clearing this check box fetches the Customer ID instead of the customer name that might be defined in a lookup. If you leave this item selected, you’ll see the customer name imported in the Customer ID field. In this case, there are no related or lookup tables for Contacts, so this option does not apply. Note that if you are unsure whether a list has more than one related lookup list, you can browse to the SharePoint site and check the field properties for the list columns.
Select the check box for the list you want to import, leave the other options set to their defaults, and then click OK to begin the import process. Access creates a new local table in your database and then imports the records. After the import process is complete, Access displays the last page of the wizard, as shown in Figure 8-59. A message at the top of this page indicates whether the import process was a success or if any problems were encountered. The wizard also displays an option to save your import steps in case you want to perform the exact import procedure again in the future. You can execute saved imports by clicking the Saved Imports button in the Import & Link group of the External Data tab on the ribbon. Click Close to dismiss the wizard.
Access now displays the new table you imported in the Navigation pane. In our import example, you can see the two records we imported from the SharePoint list displayed in Datasheet view, as shown in Figure 8-60. You can now analyze the data using queries and reports or build data entry forms for adding records to the table or editing them. Note, however, that you’ve made a copy of the data stored on the SharePoint site. Any changes you make to the local copy won’t be reflected in the SharePoint site list. If you want to be able to update the data in the list directly from Access, continue to the next section.
Troubleshooting
Why doesn’t my imported SharePoint list include all the records?
You can create different views of a list in SharePoint sites. You can define filters, include only certain columns, and assign sort orders to a custom view. If your custom view restricts the number of records returned, Access follows those rules and imports only those specific records. So, for example, if you define a custom view that shows only contacts whose last name equals Conrad and then import that view into Access, the only records imported are ones where the last name equals Conrad. If you need to import all the records into Access, make sure you import a view that returns all the records in the list.
As you might recall from earlier in this chapter, we discussed the differences between deciding to import from and deciding to link to another data source. If you need to share your data with other users or if the data changes frequently, you should consider linking to instead of importing from another data source. You just imported a sample list from a SharePoint site to an Access database. If you add new records, edit existing records, or delete records in this table, these changes are not reflected in the list on the SharePoint site. This can be problematic if all users need to have the most up-to-date data available to them. You could make changes to your local table and then export the table to the SharePoint list, but what if another user had also made changes to the records in the list? You can see the dilemma this causes when trying to keep accurate data.
Fortunately, with Access, you can link to a SharePoint site just as you can to other data sources. If you export an Access table to SharePoint and then link it back, this allows both your desktop application users and authorized members of your SharePoint site team to work with and update the same data. To link to a SharePoint list from Access, click the More button in the Import & Link group on the External Data tab, and then click SharePoint List, as shown in Figure 8-61.
Figure 8-61. Click the SharePoint List button to start the Get External Data – SharePoint Site wizard.
Access opens the first page of the Get External Data – SharePoint Site wizard, shown in Figure 8-62. This particular wizard is the same one you used for importing lists from a SharePoint site in the previous section. Enter a valid SharePoint address in the address text box below the list of previously visited sites or select a previously visited SharePoint address from the list box. Select the second option, Link To The Data Source By Creating A Linked Table, to link to an existing list on a SharePoint site and then click Next.
The second page of the wizard displays all the lists found in the SharePoint site directory that you specified on the previous page, as shown in Figure 8-63. Select a check box in the Link column to specify which list you want to link to Access. The Type column displays icons representing the type of list. The Name column displays the names of the lists on the SharePoint site. The last column, Last Modified Date, displays the date the list was last modified. Select the Link check box next to the list you want to link to and then click OK to start the linking process.
Note
You’ll notice in Figure 8-63 that you cannot select any views on a SharePoint site as you can when you are importing a list. Access allows you to link only to the full list, as opposed to views created from lists.
Access creates a link to the SharePoint list you selected and marks the icon for linked SharePoint tables in the Navigation pane with a blue arrow, as shown in Figure 8-64. If Access finds a duplicate name, it generates a new name by adding a unique integer to the end of the name, as described earlier. Because objects such as forms, reports, macros, and modules might refer to the linked table by its original name, you should carefully check name references if Access has to rename a linked table.
On the status bar at the bottom of the Access window shown in Figure 8-64, you’ll notice that Access displays Online With SharePoint. This message appears on the status bar whenever you have any active links to a SharePoint site.
You can now use this list just like a local table in your application. You can create data entry forms in Access and use this linked list as a record source. If you add a new record to the linked table, the list on the SharePoint site is also updated. Note that using data from a SharePoint list as a linked table in Access requires a high-speed Internet connection or local area connection to your intranet server. Performance will be significantly slower over a dialup connection.