To make user directory information available in your team solution, you must create links to a view of the user directory from within the solution. You cannot create an actual relationship link between the information in the user directory and a table in your database. The user directory information in your team solution is stored in a view called modUserList. SQL Server does not support the creation of a relationship between a table and a view.
To create lookups for the user directory, use the information in the modUserList view as the rowsource for a combo-box or list-box control or the ListRowSource for a drop-down list control. The SAMAccountName column in the modUserList view is the column you should target as your primary key. The data type should be nvarchar with a character length of 64.
For example, you create an employee suggestion table that includes four columns: SuggestionID, SuggestionDate, SuggestedBy, and Suggestion.
If you want the SuggestedBy column to display only solution users, define this column as a nvarchar data type with a character length of 64. If you are using Microsoft Access 2000, you can create a form and add a combo box using the Form wizard. Select the modUserList view as the source for this control, and select two columns: SamAccountName and CN (contains the user's full name). Hide the SamAccountName column (column width of zero), so only the full name column is displayed. If you have database users who do not have full name entries (such as groups), you can modify the row source of the combo box to hide records where CN is Null.
The following is an example of how the row source for your combo box might appear:
SELECT SAMAccountName, CN FROM modUserList WHERE (NOT (CN IS NULL))
If you create a data access page instead of a form to display information for the employee suggestion table, follow the same steps, except use a drop-down list control rather than a combo box. In Access 2000, the Page wizard automatically limits the row source to non-Null entries.
If you have not worked with data access pages and drop-down list controls, search for "drop-down list boxes," "data access pages," and "strategies for creating data access pages" in the Access 2000 online Help.