SharePoint: Find out About Site Users within Microsoft Access (Screencast)
Author: Laura Rogers, Birmingham, AL
SharePoint 911
In her previous screencast, Set up SharePoint List to Auto-Populate User Information, Laura Rogers demonstrated how to create a custom form in order to automatically put pertinent information about the logged in user into a form as they are filling it out. In a lot of organizations, though, customizing list and library forms in SharePoint Designer is not allowed or is not feasible. There is a way to pull SharePoint data into Microsoft Access in order to not only view the data in lists and libraries, but view detailed information about who has created or modified the items. This is done by creating an Access query.
In this screencast, Laura Rogers demonstrates a way to open a SharePoint list in Access, along with the UserInfo table from the site. In the organization, this task of using an Access query to obtain user information is usually done by one or two people who are managing the list. This can be done with any version of Office or SharePoint (2003 and on).
Speaking of joined tables, if you’re looking to create a join between two different SharePoint lists, here’s Laura’s screencast on Join View of SharePoint Lists.
Here is some reference information from Microsoft. There are several different integration points between Office and Access, and a few different ways to connect the SharePoint data to Access.
Import from or link to a SharePoint list
How to Link SharePoint Server 2007 Lists with Microsoft Access 2007
For Admins: If you’re wondering where the list of user fields (properties) comes from, this is something that the SharePoint administrator can set up. In 2007, it was in the SSP “User Profiles and Properties”, and in 2010 it’s called the User Profile Service Application. Read Professional SharePoint 2010 Administration for in-depth information on both Office integration and social computing (which includes the user profiles and properties).
For Developers: Tobias Zimmergren has written a good post about this user information list, and a developer’s perspective on it.
SharePoint’s hidden user-list – User Information List
Catch Mark and Laura at SPTechCon, at their half-day workshop on Office integration, with more on what can be done with Access and SharePoint.
Author: Laura Rogers, Birmingham, AL
SharePoint 911
Thought this was a great idean and decided to try it out. I decided to join an announcement list to the User Information List (UIL). I get a “type mismatch” error when I run the query. When I look at the Name field in the UIL it is defined as TEXT whereas Created By is defined as a number (long integer) in the Announcement list.
BTW, I am using MOSS 2007 and Access 2007.
Any ideas?
Hi Mike,
The Name field works in both my 2010 and 2007 environments. Go ahead and try out the different fields to troubleshoot.
Also, go ahead and open up the userinfo table to look at it, to see what each of the fields looks like. Then, open up the table of your sharepoint list, and look at the “created by” field.
In my environments, the Name field is the one that matches.
Hi Laura,
I’m like Mike : I don’t understand why in your case that’s work.
The type of the Created by field is Long (Integer), and the type of the Name field : String.
The Createated by field display the Name because of the choice list used in this fiels.
In Access, you must (for integrity reason) used the primary key (Id in this vase) to create link. It’s a many to one relation between this two tables…
In my case, when i add one list in Access like table, the User info came automatically, and with all the relations (in good form : primary key – external key) between this two tables…
for all readers : excuse my poor english (I never learned…), I’m better in french…