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