Get user information from AD.

[UserInterface]

I have an asset register that I am creating in MS Access and I would like to put in the users login name on a form and have it look up AD and get the information for Firstname, Lastname, email, title, state..
I thought I could use the information from this site but I have been unable to modify it or even get it to work.
I am sorry for such basic questions but I have never used access before and can only sort of read VBS at least so far as to get an idea of what it does..



I have read a lot of guides on how to use access and I have my 2 tables for users and equipment and now I am at a stage where I wish to input data but would like it to be able to pull the required information as I have hundreds of systems to enter in.

Could someone please help me?

[June7]

I am not sure what is meant by 'Active Directory'. Is this email service like Outlook Exchange?

[UserInterface]

I am not sure what is meant by 'Active Directory'. Is this email service like Outlook Exchange?

Active Directory is a MS Application that stores all users details such as usernames\passwords etc for users on a domain.

Better explanation here -> http://en.wikipedia.org/wiki/Active_Directory

[June7]

This is probably beyond my means to even try. I can't imagine that just anyone on the network would be able to do what you want, surely would need network administrator permissions to read those files. As for passwords, seems even administrator can't retrieve those. When I forget mine the admin has to reset.

[UserInterface]

This is probably beyond my means to even try. I can't imagine that just anyone on the network would be able to do what you want, surely would need network administrator permissions to read those files. As for passwords, seems even administrator can't retrieve those. When I forget mine the admin has to reset.

I stupidly missed the fact that there was a sample file that could be downloaded. I now have that and the relevant code from it. when I change the LDAP string to point to my domain it then looks up my UID and returns a popup with the details that I want in it (name\department etc). Can you help me with working out how to get that information into a form instead of in a pop up?

This is the code that I pulled from the sample database.

Private Sub cmdSample3_Click()
'Note: Code to search Active Directory given the user login name.
'Note: Change 'LDAP://DC=Fabrikam,DC=COM' to your domain.
Dim varInfo As Variant
varInfo = ""
Dim LN As Variant
LN = InputBox("Enter Login:")
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,AdsPath,givenName,SN,title,telephonenumber,Department,OU, CN, initials, displayname, " _
    & "sAMAccountName, distinguishedName, physicalDeliveryOfficeName, mail,wWWHomePage,homePhone,pager,mobile,ipPhone,Info  " _
        & "FROM 'LDAP://DC=Fabrikam,DC=COM' WHERE " _
            & "objectCategory='user' And sAMAccountName = '" & LN & "'"

Set objrecordset = objCommand.Execute
If objrecordset.EOF And objrecordset.BOF Then
    objrecordset.Close
    MsgBox "No Records match."
    Exit Sub
End If
objrecordset.MoveFirst

'Return Active Directory information.
Do While Not objrecordset.EOF
varInfo = "Name = " & objrecordset.Fields("Name").Value & Chr(10) _
    & "AdsPath = " & objrecordset.Fields("Adspath").Value & Chr(10) _
    & "givenName = " & objrecordset.Fields("givenName").Value & Chr(10) _
    & "SN = " & objrecordset.Fields("SN").Value & Chr(10) _
    & "title = " & objrecordset.Fields("title").Value & Chr(10) _
    & "Telephonenumber = " & objrecordset.Fields("telephonenumber").Value & Chr(10) _
    & "Department = " & objrecordset.Fields("Department").Value & Chr(10) _
    & "CN = " & objrecordset.Fields("CN").Value & Chr(10) _
    & "OU = " & objrecordset.Fields("OU").Value & Chr(10) _
    & "Displayname = " & objrecordset.Fields("Displayname").Value & Chr(10) _
    & "Initials = " & objrecordset.Fields("Initials").Value & Chr(10) _
    & "PhysicalDeliveryOfficeName = " & objrecordset.Fields("PhysicalDeliveryOfficeName").Value & Chr(10) _
    & "Mail = " & objrecordset.Fields("Mail").Value & Chr(10) _
    & "wWWHomePage = " & objrecordset.Fields("wWWHomePage").Value & Chr(10) _
    & "HomePhone = " & objrecordset.Fields("HomePhone").Value & Chr(10) _
    & "Pager = " & objrecordset.Fields("Pager").Value & Chr(10) _
    & "Mobile = " & objrecordset.Fields("Mobile").Value & Chr(10) _
    & "ipPhone = " & objrecordset.Fields("ipPhone").Value & Chr(10) _
    & "Info = " & objrecordset.Fields("Info").Value & Chr(10) _
    & "distinguishedName = " & objrecordset.Fields("distinguishedName").Value & Chr(10) _
    & "sAMAccountName = " & objrecordset.Fields("sAMAccountName").Value & Chr(10) _

MsgBox varInfo
objrecordset.MoveNext
Loop
End Sub

Below is the link where I found the sample database if anyone wants to see it.

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

[June7]

Instead of building a string with the recordset fields, populate textboxes.

Me.tbxName = objrecordset!Name
Me.tbxAdsPath = objrecordset!Adspath
etc.

But that will save only the last set of data of the recordset from the loop. If you want to save all the records must commit each record then move to New Record on form, read next set of data, save, move, repeat.

Another approach is to run INSERT action and save all records directly to table, like:

CurrentDb.Execute "INSERT INTO tablename SELECT ... FROM 'LDAP://DC=Fabrikam,DC=COM' WHERE ..."

Then requery the form.

I have used the Environ() function to pull some info about the logged on user but this code seem to go well beyond. Interesting, I will have to try this at work tomorrow.

[UserInterface]

Instead of building a string with the recordset fields, populate textboxes.

Me.tbxName = objrecordset!Name
Me.tbxAdsPath = objrecordset!Adspath
etc.

But that will save only the last set of data of the recordset from the loop. If you want to save all the records must commit each record then move to New Record on form, read next set of data, save, move, repeat.

Another approach is to run INSERT action and save all records directly to table, like:

CurrentDb.Execute "INSERT INTO tablename SELECT ... FROM 'LDAP://DC=Fabrikam,DC=COM' WHERE ..."

Then requery the form.

I have used the Environ() function to pull some info about the logged on user but this code seem to go well beyond. Interesting, I will have to try this at work tomorrow.

Thanks I think that gives me enough to work it out now.
I don't need to loop anything as there is only one record for each UID, this is how I am going to ensure that it must get the right record. Hopefully with the information you have given I can piece it together from bits on the web. I have not used VBS or Access so this should be a real challenge

[UserInterface]

Lol stuffed it up so badly then decided to start again and got it working first go! Thanks heaps for your help could not have done it with out you.
Still has the pop up when I select the text box but I can live with that.

I do have one question left.
What would be the best way to manage if a computer comes back to be reassigned to someone else i.e. sometimes a laptop comes back to us then gets reimaged (xp -> win7) it would keep the same information for all the computer fields but the user information would change.

[June7]

What popup?

I suppose could find record that has that laptop ID and replace the user info.

[UserInterface]

What popup?

I suppose could find record that has that laptop ID and replace the user info.

Ah didn't realise that I could change the info like that. Told you I was new to Access.

The pop up is because I still have this in the code.
I couldn't work out how to get it to just read what is in the text box, so when I click in the textbox it brings up the popup to enter UID.. not a big deal still works well.

LN = InputBox("Enter Login:")

[June7]

Try:

LN = Me.textboxname

Use the actual name of textbox in place of textboxname.

Be sure to enter value in textbox before clicking button.

Could even have a conditional so code only runs if value present:

If Not IsNull(Me.textboxname) Then
...
End If

Or set the button Enabled property as No then enable the button after value entered in textbox. In textbox BeforeUpdate or AfterUpdate event: Me.cmdSample3.Enabled = True

[UserInterface]

Try:

LN = Me.textboxname

Use the actual name of textbox in place of textboxname.

Be sure to enter value in textbox before clicking button.

Could even have a conditional so code only runs if value present:

If Not IsNull(Me.textboxname) Then
...
End If

Or set the button Enabled property as No then enable the button after value entered in textbox. In textbox BeforeUpdate or AfterUpdate event: Me.cmdSample3.Enabled = True

So cool.. wish I had been using access for last 10 years!

I changed it to run "after update"
and changed it to LN = Me.textboxname so simple. I think when I was trying I put it as LN = (Me.txtname) and the brackets might have been stopping it.

Could you tell me what this section does? I Googled it but I am unsure what it means.

Set objrecordset = objCommand.Execute
If objrecordset.EOF And objrecordset.BOF Then
    objrecordset.Close
    MsgBox "No Records match."
    Exit Sub

Every time I have something wrong the debugger stops on this line.

If objrecordset.EOF And objrecordset.BOF Then

From what I could work out it is trying to determine if it is empty field or not..??

Now I get to play with reports, hopfully these go a bit smoother


Thanks again!

[UserInterface]

I hate asking so many questions but is there a way to have it open the form on a blank line when you open the database? At the moment it opens to the top record so i have to scroll down to find the bottom (blank line) then i can enter information.

[June7]

That code is making sure the recordset actually has records before running the subsequent lines of code.

Open form to just new record (will not display existing:

DoCmd.OpenForm "form name", , , , acFormAdd

If you want existing records available, put code in form Load event:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

[UserInterface]

Sorry for mega delay, I broke it and took me a while to repair.

That code works perfect, and it opens to a new line each go.

Thanks again for all your help!