Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Retrieving Active Directory contents in vba

    Does anyone have some good, simple code on retrieving the Active Directory contents in vba (preferably ADO)?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Disregard. I found some great code here....

    http://www.microsoft.com/technet/scr....mspx?mfr=true
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    Hello pkstormy,

    I look at the script. Not quit understand it. But my first question is how to implement this script into my access database. Or it is possible at all? I have a text control box in my access form which has some VBA codes to get the user login name. On the top of my Form I have this ---- Welcome: LeeM

    I like to say Welcome: Mary Lee

    I am thinking if I can get a hold on the window active directory then I can compare this LeeM to the Active directory and should find that it is corresponding to Mary Lee. When that is done, I can say Welcome: Mary Lee.

    By the way, I user notepad to create a AD.exe file. Here it is

    On Error Resume Next

    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

    objCommand.CommandText = _
    "SELECT Name FROM 'LDAP://dc=ss1 ' WHERE objectCategory='user'"
    Set objRecordSet = objCommand.Execute
    objRecordSet.MoveFirst

    Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields("Name").Value
    objRecordSet.MoveNext
    Loop

    I then try to run it, it said
    "The NTVDM CPU has encountered an illegal instruction.
    CS:0577 IP:0211 OP:65 63 74 69 6f Choose 'Close' to terminate the application.

    Do you know why this error message?

    Thanks for your help in advance.
    Last edited by sweetmail; 04-21-08 at 14:36.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey Paul,

    If you're still having trouble with this then let me know; I've done a fair amount of work on this in the last 6 months and am more than happy to provide you with some code.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2007
    Posts
    148
    Hello georgev,

    This is sweetmail. Could you point me to the right direction to accomplish this problem.

    I have a text control box in my access form which has some VBA codes to get the user login name. On the top of my Form I have this ---- Welcome: LeeM

    I like to say Welcome: Mary Lee

    I am thinking if I can get a hold on the window active directory then I can compare this LeeM to the Active directory and should find that it is corresponding to Mary Lee. When that is done, I can say Welcome: Mary Lee.

    Thank you

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies sweet - I didn't notice the dates on this thread! Well done for using the search functionality before deciding to start a new thread

    The project in which I linked to AD is at work; if I have time tomorrow I will dig you out some code!

    But in the mean time have a scout in the code bank, memory serves that Paul actually did post something about this in there that should get you started (heck, that's where I did)!
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2007
    Posts
    148
    I will look in the code bank.

    Thanks

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The AD code in the code bank I posted is a bit sloppy. I'm hoping to clean it up sometime. The AD project I was working on for the city ended up being dropped so I posted what I had which worked pretty well for it's purpose on getting information from LDAP (the main goal was finding user login names but I found you can get a wealth of other information.)

    Basically, once you navigate through some of the sloppiness, you need to change LDAP://dc=cityofmadison to what your LDAP is and then you can return the fields of information stored. I also left in some of the other code and commented it out which again, was a work in progress.

    If you happen to find more information, please post it as I'm sure someone will find it useful. Most of the stuff I found was piecing bits and pieces of code together I had found online and then experimenting with what worked and what didn't. Since I was learning as I went and most of the stuff I found was undocumented, I didn't fully understand some of the code to actually document it myself.
    Last edited by pkstormy; 04-22-08 at 00:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Private Function queryAD()
    
    Dim rs As Object
    Dim uName As String
    
    Dim extensions() As Variant
    Dim i As Integer
    
    Public Const adOpenStatic     As Integer = 3
    Public Const adLockReadOnly   As Integer = 1
    Public Const adCmdUnspecified As Integer = -1
    
        'Instantiate recordset
        Set rs = CreateObject("ADODB.Recordset")
        
        'Open LDAP recordset
        strSQL = "SELECT userPrincipalName, sAMAccountName, mail, telephoneNumber, otherTelephone " & _
                 "FROM 'LDAP://DC=xxxxxx,DC=pri'" & _
                 "WHERE objectClass='user' AND objectCategory='Person'"
        
        rs.Open strSQL, "Provider=ADSDSOObject;", adOpenStatic, adLockReadOnly, adCmdUnspecified
        
        'Iterate through recordset
        If Not rs.EOF And Not rs.BOF Then
                
                'If userPrincipalName is null, then uName = "@"
                uName = Nz(rs.fields("userPrincipalName"), "@")
                'Trim everything after, and including, the "@" symbol from uName
                uName = Left(uName, InStr(1, uName, "@") - 1)
                'If uName is blank then use sAMAccountName
                uName = IIf(uName = "", Nz(rs.fields("sAMAccountName"), ""), uName)
                
                'Assign username value to textbox
                Me.txtNTUsername.Value = uName
                
                'Assign email and telephone to textboxes
                Me.txtEmail.Value = Nz(RTrim(rs.fields("mail")), "")
                Me.txtTelephone.Value = Nz(RTrim(rs.fields("telephoneNumber")), "")
                
                'otherTelephone accepts multiple values; and as such it has to be treated differently
                'i.e. as an array
                
                If IsNull(rs.fields("otherTelephone").Value) Then
                    Me.txtMobExtension.Value = ""
                Else
                    'Assign recordset value to our array variant
                    extensions() = rs.fields("otherTelephone").Value
                    
                    'If there is more than one value in the array
                    If UBound(extensions) > 0 Then
                        'Iterate through and append each value to create a semi-colon separated string
                        For i = 0 To UBound(extensions)
                            Me.txtTelExtension.Value = Me.txtTelExtension.Value & extensions(i) & "; "
                        Next i
                    Else
                        'Single value
                        Me.txtTelExtension.Value = extensions(0)
                    End If
                End If
            
        End If
        
        'Close connection and tidy up
        rs.Close
        Set rs = Nothing
        
    End Function
    George
    Home | Blog

  10. #10
    Join Date
    Sep 2007
    Posts
    148
    Thanks pkstormy and georgev. I will try the code today. Thanks Georgev.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    if you don't understand any of the above code (it's not heavily commented) then just ask; I'd be happy to explain
    George
    Home | Blog

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    George - I'm just wondering on how your code differs from mine regarding returning AD information (other than changing null values in some of the returns and it's written a little differently.) One of the problems I faced was that not all the values in the fields were populated consistently (the sAMAccountname field for example would sometimes have an email address but most of the time it had just the user login name.) The city though did a poor job of keeping the AD updated with good consistent information so it was difficult to produce anything but a return of information only type of procedure and not use any of the actual values returned to reliably automate anything else.

    Another main goal I had on this project was to find user permissions to folders via the AD. Did you happen to encounter anything which showed user permissions to folders? I wasn't able to find that information in a returnable format.
    Last edited by pkstormy; 04-22-08 at 11:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can query the memberOf field to get what groups a user is part of - if you set permissions to folders using membership then this method will be your allie

    My code doesn't differ much at all, just a bit tidier and possibly easier to follow - as I've said before it was you who helped me with this in the first place!

    The big difference is that it just throws a few nicities like handling those pesky multiple valued fields and trims the username using the sAMAccount name, which, in theory, is the best place to get it from (or so I am led to believe anyway!).
    George
    Home | Blog

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the info George. I was just curious on looking through your code if there was something I was missing. I like your code (mainly since mine was just copied and thrown together and yours is easier to follow.) I recall trying the MemberOf query without success but I think that's about when the project was cut-off. I had a lot of difficulty though with getting any help from the networking guys who really didn't know themselves - go figure.

    I do remember there was one thing which bugged me - I couldn't do a "like" statement. So if for example, I wanted to see AD info on all users whose last name was like Smith (i.e Smithson, Smithers, etc..) it wouldn't work. For some reason the method I was using to construct the select statement didn't like utilizing the word Like.

    Your code interests me and when I get back to working at a company using AD, I want to give your code a try. Thanks for posting it! You should put it in the code bank once you've finished it (or even as you're working on it). I can't understand why more developers out there don't find this kind of information and coding valuable.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Sep 2007
    Posts
    148
    Hello George,

    I am running Access 2003

    I create a new form (frmAD) have ONE control ---- txtNTUsername

    I put the function under Modules and it named -- UserNameAD

    I change the LDAP to

    'Open LDAP recordset
    strSQL = "SELECT userPrincipalName, sAMAccountName, mail, telephoneNumber, otherTelephone " & _
    "FROM 'LDAP://DC=ss, DC=cobbroot, DC=local'" & _
    "WHERE objectClass='user' AND objectCategory='Person'"

    First, I don't think access think that queryAD is a function.

    I then put the whole function under inside the Form (frmAD) but then access compliant about the following

    Public Const adOpenStatic As Integer = 3
    Public Const adLockReadOnly As Integer = 1
    Public Const adCmdUnspecified As Integer = -1

    Sorry if this is a simple question but I really don't know what to do.

    Thanks
    Last edited by sweetmail; 04-22-08 at 22:22.

Posting Permissions

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