Chapter 32
Integrating Access with SharePoint
IN THIS CHAPTER
- Getting familiar with SharePoint sites
- Understanding Access and SharePoint integration
- Linking to SharePoint lists
- Importing SharePoint lists
- Exporting Access tables to SharePoint
- Upsizing Access databases to SharePoint
- Using SharePoint list templates
Throughout this book you've read about the many features that Microsoft has added to Access over the years. As exciting and interesting as these new capabilities are, they pale in comparison to the ability to upsize Access applications to Windows SharePoint Server. Each recent version of Access has demonstrated greater and greater ability to integrate with SharePoint. The most exciting aspect of this integration is the ability to actually run your Access application as a SharePoint website.
In this chapter and the next, we'll explore the various techniques that allow you to upsize your Access databases to SharePoint. This chapter gives you a base understanding of what SharePoint is and how it helps organizations share and collaborate data.
Introducing SharePoint
SharePoint is Microsoft's collaborative server environment, providing tools for sharing documents and data across various organizations within your company network.
SharePoint is typically deployed on a company's network as a series of SharePoint sites. A SharePoint site is configured as an intranet site, giving various departments the ability to control their own security, workgroups, documents, and data. These sites can be nested within other sites in a hierarchical fashion.
As with any other website, the pages within a SharePoint site are accessible through a URL that the user can access via a standard web browser.
Although SharePoint is most frequently used for sharing documents, data tables, and other content management tasks, SharePoint is frequently applied to many other applications—for example, to handle the documentation required for product development. A SharePoint site devoted to a development project easily handles the project initiation, tracking, and progress reporting tasks. Because SharePoint easily handles virtually any type of document, project drawings, videos, schematics, photographs, and so on can be added to the project's SharePoint site for review and comment by project members.
Companies often use SharePoint for distributing human resource and policy documents. Because SharePoint provides user- and group-level security, it's quite easy to grant a particular department access to a SharePoint page while denying other users access to the same site.
SharePoint also logs changes to documents and supports a check-in/check-out paradigm for controlling who is eligible to make changes to existing documents and who is allowed to post new documents and files.
Some of the most common SharePoint deployments are storing of version-controlled documents, such as Word documents and Excel spreadsheets. In many environments, e-mail is used for passing documents back and forth between users. The potential for mixing up different versions of the same document is considerable. Also, storing multiple copies of the same document takes up a lot of disk space. Because SharePoint provides a single source for storing, viewing, and updating documents, many of these issues are eliminated entirely.
Understanding SharePoint Sites
Before going into the detail of describing how Access integrates with SharePoint technology, it's helpful to picture a typical SharePoint site. In this section, you'll get a brief overview of the two most common aspects of a SharePoint site: documents and lists.
SharePoint Documents
Perhaps the most common use of SharePoint is to store shared documents and other files. SharePoint keeps track of files from the moment they're added to a list until they're removed or deleted. Anyone with write-access to a SharePoint site can upload a document for sharing. Figure 32.1 shows a short list of several different types of files uploaded to a SharePoint document library.
Figure 32.1 A SharePoint document library.
In Figure 32.1, notice that the library contains several different types of documents. Each row in the document list includes an icon indicating the document's type, the document's name, the size of the document, and the name of the person who added the document to the list.
SharePoint document libraries support a check-in/check-out paradigm. Only one person at a time is able to check out a document for changes. Although not shown in Figure 32.1, SharePoint records when a document is checked in or out and keeps track of the individuals making changes. SharePoint can even be instructed to roll back document changes to an earlier version, if necessary.
Again, this document-sharing paradigm is most commonly used to share information across organizations, allowing for collaboration between the users of the SharePoint site.
SharePoint lists
In addition to storing and tracking entire documents, SharePoint users can store and share data via SharePoint lists. SharePoint lists are conceptually similar to database tables in that each list consists of rows and columns of data. Each column holds a particular type of data, such as text, a date, or an object (such as a photo). From this simplistic perspective, SharePoint lists are analogous to Access tables.
Figure 32.2 illustrates a typical SharePoint list. As you can see, the relevant information is presented in a single screen. You add a new item, edit an existing item, or delete an item through this same screen.
Figure 32.2 A SharePoint list allows for the storage and tracking of data in a table format.
SharePoint can manage virtually any type of data you want to share with other people. Although the site shown in Figure 32.2 is designed for a specific purpose, SharePoint is suitable for many other scenarios. For example, an HR department could use a SharePoint list for sharing and tracking required training courses. An IT department could maintain a list of assets, including status and locations. Even smaller organizations like a local bowling club can share and maintain tournament schedules and player rankings as SharePoint lists.
SharePoint easily supports multiple lists, allowing an organization to add as many lists as needed. Unfortunately, unlike Access tables, SharePoint lists are not relational; there is no way to directly relate data in two different SharePoint lists or to query multiple SharePoint lists to find related data. However, SharePoint lists can be linked or imported into Access. Linking to SharePoint lists makes data stored on a SharePoint website appear as linked tables in Access.
For the most part, linking to a SharePoint list is no different from linking to a SQL Server database table or other remote data source. However, an important difference is that linked SharePoint lists in Access 2016 are read-only; you can't edit or update the linked SharePoint list.
When linked to an Access application, SharePoint data is available to all the queries, forms, and reports in that application. This means that data entered into SharePoint lists can be viewed in real time and utilized through your Access application, making Access a feature-rich front-end platform for displaying SharePoint content.
Sharing Data between Access and SharePoint
Building Access applications with SharePoint data simply means going into an Access application, linking to SharePoint lists, and then writing forms and reports based on those linked tables. A linked SharePoint list appears (to Access) as any other linked data source.
In addition to linking to SharePoint lists, you may find it helpful to import SharePoint data directly into local Access tables. Imported data is no longer connected to the SharePoint site, and is therefore useful in those situations when you need to work with static snapshots of SharePoint lists.
Linking to SharePoint lists
The most fundamental data sharing between Access and SharePoint is for Access to link to a SharePoint list and use the data as with any other linked data source. Follow these steps to link to a SharePoint list:
- Click the More drop-down button in the Import & Link group on the External Data tab. The list of more advanced import and linking options (see Figure 32.3) appears.
Figure 32.3 Preparing to link to a SharePoint list.
- Select SharePoint List from the list of import and linking options. The Get External Data – SharePoint Site dialog box (see Figure 32.4) appears. Here, you can specify your target SharePoint site by entering the site's URL. This dialog box will remember any URL you enter and, when activated again, will show you a selectable list of the recently used URLs.
Figure 32.4 The Get External Data – SharePoint Site dialog box.
You must have appropriate permissions to link to a SharePoint list. In fact, without proper permissions, Access can't even display the SharePoint lists on the designated site. SharePoint users are recognized by their membership in Windows Active Directory services and their inclusion in designated SharePoint groups. These topics are beyond the scope of this book, but you should be aware that access to SharePoint sites and SharePoint data is protected by processes similar to any other Windows application.
- Enter your username and password. Once you successfully log in to the SharePoint site, you will be presented with a list of SharePoint lists in the designated SharePoint site. Each item in the list is accompanied by a check box.
- Place a check next to each list you want linked, and then click OK. In Figure 32.5, only the Customers SharePoint list is selected for linking, but you can select multiple lists as well.
Figure 32.5 Selecting a SharePoint list for Linking.
Figure 32.6 shows the linked SharePoint list. The icons indicating linked SharePoint lists look very much like Access table icons. Each linked list is accompanied by an arrow, and the color of the icon has changed to a yellowish-orange color.
Figure 32.6 A linked SharePoint list appears much like any other Access table.
The data in the linked table is compatible with Access, and you can build queries, forms, and reports against this data if needed. It's important to remember that the data in the linked list is read-only, meaning you can't update the data in the SharePoint list via Access 2016.
Importing SharePoint lists
Instead of having a live link to SharePoint lists, you may want to import a list. Importing a SharePoint list allows you to simply take a snapshot of the list and bring the data into Access as a stand-alone disconnected table. Unlike a linked SharePoint list, an imported list will not be automatically updated with new SharePoint data.
The steps for importing a SharePoint list are similar to those for linking:
- Click the More drop-down button in the Import & Link group on the External Data tab. The list of more advanced import and linking options appears.
- Select SharePoint List from the list of import and linking options. The Get External Data – SharePoint Site dialog box appears.
- In the top portion of this dialog box, either select a recently visited SharePoint site or enter a new destination SharePoint URL; then select the Import option, as shown in Figure 32.7.
Figure 32.7 Selecting a SharePoint list for importing.
- Enter the appropriate permissions to link to a SharePoint list. You're presented with a list of SharePoint lists in the designated SharePoint site. Each item in the list is accompanied by a check box.
- Place a check next to each list you want imported, and then click OK. Access imports your chosen lists into a table that can be viewed and utilized like any other standard table through the Navigation pane.
Exporting Access tables to SharePoint
Sometimes you need to transfer data from Access to SharePoint so that SharePoint users have access to the same data as Access users. The following steps export a table from Access to a SharePoint list. Remember, you will need to have proper Write access on the SharePoint site in order to perform this action.
- Right-Click the table you want to export, and then select Export
SharePoint List (see Figure 32.8). The SharePoint Site dialog box activates.
Figure 32.8 Exporting a table to SharePoint.
- Enter or select the target SharePoint site URL (see Figure 32.9). In addition to pointing to the URL, you have the option of specifying a name for the soon-to-be-created list.
Figure 32.9 Select the destination SharePoint site and specify a name for the exported list.
- Click the OK button. If you're prompted for a login, enter a valid SharePoint username and password.
When the export is complete, the new SharePoint list will be displayed with the other lists on the SharePoint site.
- Click the Close button in Access to dismiss the Export to SharePoint dialog box.
Note that after exporting data from Access to SharePoint, the two data tables are not connected, so if changes are made in either location, the other application doesn't see the change.
Certain fields don't export well to SharePoint. For instance, an OLE Object field is simply left empty on the SharePoint side and contains no data. However, most other field data types are properly translated into compatible SharePoint columns and populated with data from the Access table. Table 32.1 shows how Access data types are translated to compatible column types in SharePoint. Notice that far fewer types of data are available in SharePoint lists than in Access tables. The data types in Table 32.1 are applied any time an Access table is exported to SharePoint.
Table 32.1 SharePoint Data Type Conversion
| Access Data Type | Converted Type in SharePoint |
| AutoNumber | Number |
| Text | Single line of text |
| Memo | Multiple lines of text, limited to 8,192 characters |
| All Number Types (Byte, Integer, Long Integer, Single, Double, Decimal) | Number |
| Date/Time | Date and Time |
| Currency | Currency |
| Yes/No | YesNo |
| OLE Object | Single line of text |
| Calculated | Calculated |
| Hyperlink | Hyperlink or Picture |
Moving Access tables to SharePoint
Instead of simply exporting Access tables to SharePoint, another approach to data sharing is to move all the tables in an Access application to SharePoint as a single export operation and link the new SharePoint lists back to the Access application. All the tables in the Access database are moved to SharePoint and linked back to Access in a single process.
The advantage of moving Access tables to SharePoint is that you can build out your data model in Access, using all convenient tools for table creation, and then upsize the data model to SharePoint. Once the data is in SharePoint, any changes made in SharePoint will be immediately seen in Access.
This level of integration allows SharePoint to be used as the data collaboration and tracking portal, while giving users the benefit of Access's superior user-reporting tools.
Moving Access tables to SharePoint is not one of the import/export features of Access 2016. Instead, the commands necessary to move the entire set of Access tables to SharePoint are on the Database Tools tab of the Ribbon (see Figure 32.10).
Figure 32.10 The Move Data group on the Ribbon contains the wizard to upsize to SharePoint.
Clicking the SharePoint command in the Move Data group on the Ribbon opens the Export Tables to SharePoint Wizard dialog box (shown in Figure 32.11). The only information that you need is the URL of the destination SharePoint site; Access handles the rest.
Figure 32.11 The Export Tables to SharePoint Wizard dialog box specifies the destination SharePoint site.
The Next button initiates the export process, which may take more than a few minutes, depending on the number of tables in the Access database, the volume of data in each record, and the efficiency of the SharePoint server hardware and software. You may also be asked for your SharePoint username and password because SharePoint must verify that you have the proper permissions to create objects in the destination SharePoint site.
The newly created SharePoint lists are given the same name. At the conclusion of the process, all the tables in the Access database have been moved to SharePoint and linked back to the Access application. The tables and their data are now stored and managed by SharePoint Services. All that's left in the Access database are logical links to the SharePoint website; the tables and data are no longer stored in the Access database.
Just as with other export processes, you can save the export steps for future use. Access also makes a backup of the Access database file prior to the export process so that you can revert to the prior state, if necessary.
Using SharePoint Templates
In this chapter, we examine the options available when an Access database already exists and users require the same data on a SharePoint website. In its attempt to solidify the connection between Access and SharePoint, Microsoft has provided yet another approach to integrating Access applications with SharePoint.
Instead of exporting existing Access tables to SharePoint or linking to SharePoint lists, this alternative technique involves building entirely new SharePoint lists within the Access environment. Access 2016 provides SharePoint list templates, which contain all the details necessary to build SharePoint lists, including column names and data types and other list properties. This is essentially meant to be a time-saver for anyone who wants to quickly stand up a new list on SharePoint.
The SharePoint templates in Access 2016 cover a number of important business functions: Contacts, Tasks, Issues, and Events, as shown in Figure 32.12. In addition, the Custom list template (near the bottom of the list) allows you to add virtually any combination of SharePoint-compatible columns to an otherwise blank list. The last item in the drop-down list (Existing SharePoint List) provides the same linking capability discussed in the “Linking to SharePoint lists” section, earlier in this chapter.
Figure 32.12 SharePoint list templates available in Access.
Selecting an item from the list of SharePoint list templates opens the Create New List dialog box (shown in Figure 32.13). You'll have to provide a SharePoint URL and a name for the new list.
Figure 32.13 The Create New List dialog box when creating a new SharePoint list from an Access template.
Notice that you don't have an option to modify the template before you create it in SharePoint. This means, of course, that the list will include a predetermined set of columns, each set to a particular data type required for the list's operations.
You may be asked to provide SharePoint credentials as the new list is created. You need administrative rights to add lists to a SharePoint site, so even if you can link to a SharePoint list, you may not be entitled to create an entirely new list.
The newly created list will be automatically added to Access as a linked table that behaves like any other linked SharePoint list.