Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14

    Question Unanswered: Use form List Box to query Active Directory

    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,

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Active Directory

    You may want to check out this post:

    http://www.dbforums.com/6296643-post48.html

    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.
    Last edited by pkstormy; 11-29-09 at 22:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14

    Thumbs up Thanks - Used your method

    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

  4. #4
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14

    Thumbs up On final breakthrough on this

    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


  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    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.
    Last edited by pkstormy; 12-04-09 at 00:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    May 2013
    Posts
    1
    I am sorry for digging up an old thread but this is the only information I could find that might do what I want.
    I am trying to create an asset register in MS Access and I want to have the admins select the UID of the user and then have MS access look up the name etc from active directory. This way I can ensure that assets are marked to the right person and also that we can not have people with duplicate assets. I have never used MS access before so I apologise if I am asking stupid questions.
    I would greatly appreciate any information anyone can give me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •