Sharepoint SIG

Thoughts and ideas from your SharePoint support team

Reporting on Lists and Libraries in SharePoint using Microsoft Access

| 0 comments

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

Open With Access button

  • 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

NSID and password prompt

  • 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

External Data tab

  • In the “Import & Link” section at the left, click on the “More” button

More button in Import section

  • 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

    Get External Data dialogue box

  • Enter your NSID and password
  • The lists and documents libraries will appear; this may take a few seconds

List of lists and libraries to link

  • 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

Disadvantages

  • 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

    Strip ID from Managed Metadata

  • 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 sp_support@usask.ca.  I will be happy to assist you.

Sheila ffolliott

Analyst, SharePoint team

Leave a Reply