Use form List Box to query Active Directory

[grafiksinc]

Scenario: Have an Employee Roster database. Would like to intergrate this with Active Directory (AD)



How: Would like to have the Human Resources (HR) user select a new employee's name from a list box.
The list box would query AD and list all the active user names.

Goal: Due to our check in process all new employees must see the Helpdesk to get a network account, I would like limit as many discrepancies between the Employee roster and AD as possible.

What have I done:
I can import AD information in to the Employee roster that works.
However, there is a performance lag when running the new import " Takes about a minute to complete ".
Also sometimes there is a timing issue. For example Account Created in AD but import has not run yet.
So, I thought I would ask this question.

Can I use and Access form with a list box to query AD for user information?

Edited by: grafiksinc on Sun Nov 29 20:59:45 EST 2009.

[SFonseca]

Sorry I don't have an answer. But I have a similar question.

I would like to make a form that serves as an input to a query.

Filling in the blanks and clicking "Search Button" would run the query. How do I make and link the from?

Example:
BETWEEN Sales Date: ________ AND Sales Date: _______
Sales Rep ___________
Type of car __________ [Button"Search Now"]

[grafiksinc]

My scenario was to search only the comments field of my DB.

The solution was to create a select query with and on the comments column the Criteria I used is as follows:

Like "*" & word([forms]![searchdatabase]![txtcriteria],1) & "*" Or Like "*" & word([forms]![searchdatabase]![txtcriteria],2) & "*" Or Like "*" & word([forms]![searchdatabase]![txtcriteria],3) & "*" Or Like "*" & word([forms]![searchdatabase]![txtcriteria],4) & "*"


The Search button the form just ran this query and useing the values that user put in the comments field.

So, what does the user do? -
On the form that has the search they enter text in the field next to the search button. When the click the search button I run the query above. It looks for any words that match what was enter on the search form.


You could possible use the same method to do your between search.

Hope this helps you.

[SFonseca]

Thanks, I used your ideas and it works. I made a form that was not connected to a data source, added the button, and the text boxes. The button runs a macro that opens the query, that accepts the txt in the forms. Works great!

[grafiksinc]

So, I made some changes.
Here is my scenario that meets my requirement.
Here is what this code will do,
On the form that you have now Add a text box for testing and name it
"last".
What the code does is, it looks at that field for the last name.
So, when you select the Combo it will search AD for only the users that
match the value that is in the text box names last.
It's faster and it only gets the data that is in use at the time.
There-fore no need to import AD users.
With all that said this is just one scenario but you could expand on
this potentially rather than have it say look at another text box but
make it look at itself possibly.
Forgive the comments.


Code-------------------------------------------------------------------------------------------------------------
'*****************************************
'*Connects To AD and sets search criteria*
'*****************************************
'On Error Resume Next
Dim rs As ADODB.Recordset
Dim strSql As String
Const ADS_SCOPE_SUBTREE = 2
'On Error GoTo ADImportError
'Screen.MousePointer = 11
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
'************************************************* *********************
'*SQL statement on what OU to search and to look for User Objects ONLY*
'************************************************* *********************
objCommand.CommandText = _
"SELECT SN, GivenName, sAMAccountName " _
& "FROM 'LDAP://OU=DC=' WHERE " _
& "objectCategory='user'" _
& "AND SN='" & Me!last & "'"

'*************************************
'Adds records to list box
'*************************************
With Me!lstUsers
.RowSourceType = "Value List"
.ColumnCount = 1
End With
Set objrecordset = objCommand.Execute
With objrecordset
.MoveFirst
Do While Not .EOF
Me!lstUsers.AddItem .Fields("SN").Value
'& ";" & _
'.Fields("GivenName").Value & ";" & _
'.Fields("SN").Value & ";" & _
'.Fields("telephonenumber").Value & ";" & _
'.Fields("DisplayName").Value & ";" & _
'.Fields("sAMAccountName").Value
.MoveNext
Loop
End With
objrecordset.Close
Set objrecordset = Nothing
Exit_ADImport:
Screen.MousePointer = 0
Exit Sub
ADImportError:
MsgBox Err.Description & " - " & Err.Number & Chr(13) & Chr(13) _
& "Unable to produce picklist. Report the above error to ITD."
Resume Exit_ADImport
End Sub