If you have been using SharePoint for a while, you have looked for a way to report on the data in your lists and document libraries. You have figured out how to filter and sort the entries , either using the drop-downs in the header row or by creating views. But this type of reporting is limited. For example, it doesn’t provide the ability to:
- Change fonts, font sizes, font weight etc
- Format the information, such as centering the title
- Add graphics or lines
- Create headers and footers
- Specify pagination
- Use linked master-detail sections
If you want to create formatted reports, SharePoint provides the ability to connect your lists and libraries to an Access database. Once the database is created and lists and libraries are linked to it, they are available in the left panel in MS Access. From here, you can edit list entries, create queries based on the list or library, and create reports based on the queries.
To connect a SharePoint list to MS Access
- Enter the list that you want to connect
- Click on the List tab in the Ribbon
- Click on the “Open with Access” button in the “Connect & Export” section of the ribbon
- SharePoint will ask you to choose a location for the new Access database. Click Browse and choose a different location if you don’t like the default.
- Leave the selection set to “Link to data on the SharePoint site”; this will ensure that when the data in your list changes, the changes will be visible in the MS Access database.
- Click OK
- Enter your NSID and password
- MS Access will launch and create a new database
- The name of the list will appear in the database
- If the list had other lists related to it, the related lists will be linked to Access as well
To connect to a SharePoint list or library from MS Access
Connecting to a document library requires a different process because the “Open with Access” button is not available in the List ribbon in SharePoint. But it is possible to connect to a document library from within MS Access itself.
This method is also required if you have an existing database and want to add a link to a SharePoint list to it.
- Start up MS Access
- Create a new, blank database or open the existing database
- Click on the “External Data” tab in the ribbon
- In the “Import & Link” section at the left, click on the “More” button
- Choose “SharePoint List” from the drop-down list
- In the dialogue box
- Type in the URL of the SharePoint site that contains the document library, or choose it from the list of site addresses if it is already available
- Make sure that the “Link to the data source by creating a linked table” option is chosen
- Click Next
- Enter your NSID and password
- The lists and documents libraries will appear; this may take a few seconds
- Click the checkbox beside each list and library that you want to view in Access
- Note: do not uncheck any lists or libraries that are already linked to the database; if you do, they will be removed (from the Access database, not from SharePoint)
- You can relink them later but any queries or reports that include the deleted list or library will be messed up
- Click OK
Refreshing Lists and Libraries in MS Access
If you make changes to a list’s or library’s structure in SharePoint (add or delete columns, or change a column’s definition), these changes are not automatically propogated to the MS Access database. This is the same behaviour that is seen if tables in other types of linked databases are changed. The links must be refreshed before the changes are available in Access. With other types of databases, the Linked Table Manager is the tool to use, but it is not available for SharePoint lists.
To refresh the links, use the same method detailed in the section above. Instead of checking or unchecking links, just click the OK button at the end. Access will automatically update the links and the changes to columns will be revealed when you next open the lists or libraries.
Advantages of Reporting in MS Access
- More powerful reporting than in SharePoint lists and views
- Can update most data fields (except Managed Metadata, the Name field, or fields that are automatically populated), thus providing for a method to perform bulk updates via Update Queries
- MS Access is only available on Windows computers; Macintosh and Unix/Linux clients will not be able to use the database
- Managed Metadata fields appear with the ID appended to the front (e.g. 4;#2012-2013), so the ID has to be stripped out manually
- Create a new column in the query with the code “Columnname: mid([fieldname],instr([fieldname],’;#’)+2)” replacing “fieldname” with the actual name of your field
- Use the calculated column for sorting, grouping, etc rather than the original managed metadata field
- It is possible to store the MS Access database in a document library, but you must download the database from SharePoint to use it
- The changed database must be re-uploaded into the document library for others to see the changes
- Turning on the required check out feature causes the “Save to SharePoint Site” button in Access to stop functioning properly, although it is still possible to upload the changed database manually, so requiring check out is not a solution
I have had quite a bit of previous experience using MS Access, so if you have questions about how to configure it, please contact me at firstname.lastname@example.org. I will be happy to assist you.
Analyst, SharePoint team