I tried and failed, so I blogged.
I have recently discovered the magic of using MS Access on top of SharePoint and I have found that it works like an absolute charm. It allows you to do a nice front end quick and easy and allows you to interact with your data through Access, SharePoint and SQL. This fits a nice gap we found between InfoPath and BCS. Our turnaround time time for a project is now days and changes can be made on the fly. Great stuff Microsoft!
However I have found it difficult doing a simple People Picker from SharePoint.
For those of you who do not know, the data stored when using an Access Web App in SharePoint is stored as SharePoint lists, which you can still access as you any normal SharePoint list. The Web App handles the eye candy but the data is still in lists. Now when adding column as a People Picker you might find that as soon as you open the app you cannot select anything at all. Or if you have imported the user table, you can select everything and more, but I am looking for that sweet middle ground that only allows you to select your active users from the http://<your site herer>/_catalogs/users/detail.aspx list.
So how can you do this?
Well here is what I did.
1.You have an existing Access Web App. 2. You have at least a basic understanding of what you want to do. 3. You have a good supply of coffee. 4. We will not be using a normal user lookup/people picker from SharePoint but we will fake a similar function using lookup tables.
Step 1. Open your Web App
Step 2. Create Linked Table
Now link this to your current site and look for the UserInfo table.
It is a good idea to save and sync right about here.
Step 3. Create the column
I created a column from SharePoint by accessing the list and adding a People Picker Loockup with multiple value select enabled.. This will create the link we need.
When syncing the web app now I find that in the table I have the lookup I can see the people listed in the lookup.
Step 4. Get the data and filter it
There are always the odd entry you don’t want users to see, so let’s filter this.
You can either do a normal select, but I am choosing to write a query that looks something like this:
SELECT UserInfo.ID, UserInfo.Name FROM UserInfo WHERE (((UserInfo.Office) Is Not Null)) ORDER BY UserInfo.Name;
I am using the where clause to filter out all you service accounts and the likes, as only employees have and Office assigned in AD.
SELECT UserInfo.Name FROM UserInfo WHERE (((UserInfo.Office) Is Not Null)) ORDER BY UserInfo.Name;
Step 4. Pray, Save, Sync and Test
I am seeing the list pop up in my local Access while editing this file.
Now after as small victory dance I feel a slightly better person than I did before getting this to work.
Your mileage may vary, drop a comment if you don’t get the same results.