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?

Thank you for any help,

You may want to check out this post:

DBForums Code Bank

I played around with some Active Directory coding (it's not simple). The code isn't documented very well but it does a good job of giving you the basics along with some advanced coding.

I used it to query the Active Directory parameters for the city within MSAccess. You'll need to change the connection string for your environment.

I hope it helps. It's a tad sloppy and not commented very well (mostly just pieces of coding info I found from different websites.)

I won't be able to answer any questions though on it since I did it years ago and haven't really followed up on it.

Again, I hope it helps in some way.

PK,
Thanks I did use your method as a start and shared it with others.

I thought I would share with you what they told me.

I only use a few parts of the entire thing specifically the part about getting user information from AD.

I quote another fellow
"realistically to get your import to run a bit faster , you'd probably be better off running an INSERT INTO SQL statement:"

INSERT INTO AD_User (UserName, FirstName, LastName, BusinessPhone, DisplayName, LogonName)
SELECT Name,givenName,SN,telephonenumber,displayname,sAMA ccountName
FROM 'LDAP://OU=,DC=,
WHERE objectCategory='user'"

It was true the way you wrote it worked but this did help it speed up. In my case I have about 2000+ users I am importing.

Non the less thanks. for that.

Also I did figure out how to query AD from a combo/listbox
Again folks were digesting what i gathered from you and had some sugestions.

So here is my end result:
The Code below is added to a combo box called lstUsers on a form in the OnGotFocus Event. When you run the form it will query AD when you put your mouse in the box. After the query runs you can see the names of users in your AD in the combo box.

NOTE:********
I excluded the LDAP just know that you need to add in your own domain.

A few extra notes:
1. This could not work for me because the Value list in a combo box
is limited to like 30 something thousand characters.
and for 2000+ users this is reached.

But if your users are less than lets say 100 this should work fine for you.

2. I wanted to see 6 columns in AD but you can change that to whatever
suits. You can also and change the SQL to get other info that you need from AD.

So whats the point? I had to see if it was possible wether it worked for my scenario or not.

In the end I hope this helps someone.

Private Sub lstUsers_GotFocus()
'*****************************************
'*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


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 Name,givenName,SN,telephonenumber,displayname,sAMA ccountName " _
& "FROM 'LDAP://OU=,DC=' WHERE " _
& "objectCategory='user'"

'*************************************
'Adds records to list box
'*************************************
With Me!lstUsers
.RowSourceType = "Value List"
.ColumnCount = 6
End With

Set objrecordset = objCommand.Execute
With objrecordset
.MoveFirst
Do While Not .EOF
Me!lstUsers.AddItem .Fields("Name").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

End Sub

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

grafiksinc,

Thank you very much for posting your response! I added your code to the AD example in the code bank for others to see (with your credit and a link to these posts for additional details).

I struggled with tapping into AD for many months gathering as much code as I could from different sources to get it to work and you really made my day today by replying that it helped in 'some' way with your response. I thought no one would ever look at my example or even care.

Thank you again!! It's responses like yours that makes development life easier for others (and again, really cheered me up.)

Feel free to add any additional comments you wish to the code bank. (it would be great to see your final product mdb posted in the code bank.)

Best wishes.