How can I "link" to Active Directory from within Access 2003? I work in a very large enterprise and I can successfully link to the Exchange Server for some information. I need to link to AD and retrieve the "Display Name", "Description", and "User Logon Name (pre-Win2k)" field for all users. I need help establishing the Data Source / DSN.

I have searched Google and this forum and the closest I can find is this dead post: http://p2p.wrox.com/topic.asp?TOPIC_ID=13740.

A CSV/TXT export from AD would also be acceptable as long as the solution allows a non-admin on a workstation to perform it. I do NOT have ldifde available to me.

Hiya,

 You need to be using VBScript to do this, which Access VBA will support.

   Try this from the Microsoft Scripting Guys ADScriptomatic tool free on Microsoft.com. You can see how this works and scavenge the parts you need:
'---------------------------------------------
strContainer = ""
strName = "EzAdUser"

On Error Resume Next

'***********************************************
'* Connect to an object *
'***********************************************
Set objRootDSE = GetObject("LDAP://rootDSE")
If strContainer = "" Then
  Set objItem = GetObject("LDAP://" & _
    objRootDSE.Get("defaultNamingContext"))
Else
  Set objItem = GetObject("LDAP://cn=" & strName & "," & strContainer & "," & _
    objRootDSE.Get("defaultNamingContext"))
End If
'***********************************************
'* End connect to an object *
'***********************************************

WScript.Echo VbCrLf & "** General Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strname = objItem.Get("name")
WScript.Echo "name: " & strname
strgivenName = objItem.Get("givenName")
WScript.Echo "givenName: " & strgivenName
strinitials = objItem.Get("initials")
WScript.Echo "initials: " & strinitials
strsn = objItem.Get("sn")
WScript.Echo "sn: " & strsn
strdisplayName = objItem.Get("displayName")
WScript.Echo "displayName: " & strdisplayName
strdescription = objItem.Get("description")
WScript.Echo "description: " & strdescription
strphysicalDeliveryOfficeName = objItem.Get("physicalDeliveryOfficeName")
WScript.Echo "physicalDeliveryOfficeName: " & strphysicalDeliveryOfficeName
strtelephoneNumber = objItem.Get("telephoneNumber")
WScript.Echo "telephoneNumber: " & strtelephoneNumber
strmail = objItem.Get("mail")
WScript.Echo "mail: " & strmail
strwWWHomePage = objItem.Get("wWWHomePage")
WScript.Echo "wWWHomePage: " & strwWWHomePage

WScript.Echo VbCrLf & "** General Properties Page**"
WScript.Echo "** (MultiValued Attributes) **"
strotherTelephone = objItem.GetEx("otherTelephone")
WScript.Echo "otherTelephone:"
For Each Item in strotherTelephone
 WScript.Echo vbTab & Item
Next
strurl = objItem.GetEx("url")
WScript.Echo "url:"
For Each Item in strurl
 WScript.Echo vbTab & Item
Next

WScript.Echo VbCrLf & "** Address Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strstreetAddress = objItem.Get("streetAddress")
WScript.Echo "streetAddress: " & strstreetAddress
strl = objItem.Get("l")
WScript.Echo "l: " & strl
strst = objItem.Get("st")
WScript.Echo "st: " & strst
strpostalCode = objItem.Get("postalCode")
WScript.Echo "postalCode: " & strpostalCode
strc = objItem.Get("c")
WScript.Echo "c: " & strc

WScript.Echo VbCrLf & "** Address Properties Page**"
WScript.Echo "** (MultiValued Attributes) **"
strpostOfficeBox = objItem.GetEx("postOfficeBox")
WScript.Echo "postOfficeBox:"
For Each Item in strpostOfficeBox
 WScript.Echo vbTab & Item
Next

WScript.Echo VbCrLf & "** Account Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
struserPrincipalName = objItem.Get("userPrincipalName")
WScript.Echo "userPrincipalName: " & struserPrincipalName
strdc = objItem.Get("dc")
WScript.Echo "dc: " & strdc
strsAMAccountName = objItem.Get("sAMAccountName")
WScript.Echo "sAMAccountName: " & strsAMAccountName
struserWorkstations = objItem.Get("userWorkstations")
WScript.Echo "userWorkstations: " & struserWorkstations

WScript.Echo VbCrLf & "** Account Properties Page**"
WScript.Echo "** (The userAccountControl attribute) **"
Set objHash = CreateObject("Scripting.Dictionary")
objHash.Add "ADS_UF_SMARTCARD_REQUIRED", &h40000
objHash.Add "ADS_UF_TRUSTED_FOR_DELEGATION", &h80000
objHash.Add "ADS_UF_NOT_DELEGATED", &h100000
objHash.Add "ADS_UF_USE_DES_KEY_ONLY", &h200000
objHash.Add "ADS_UF_DONT_REQUIRE_PREAUTH", &h400000
intuserAccountControl = objItem.Get("userAccountControl")
For Each Key in objHash.Keys
  If objHash(Key) And intuserAccountControl Then
    WScript.Echo Key & " is enabled."
  Else
    WScript.Echo Key & " is disabled."
  End If
Next
If objItem.IsAccountLocked = True Then
  WScript.Echo "ADS_UF_LOCKOUT is enabled"
Else
  WScript.Echo "ADS_UF_LOCKOUT is disabled"
End If

If err.Number = -2147467259 OR _
  objItem.AccountExpirationDate = "1/1/1970" Then
  WScript.Echo "Account doesn't expire."
Else
  WScript.Echo "Account expires on: " & objItem.AccountExpirationDate
End If

WScript.Echo VbCrLf & "** Profile Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strprofilePath = objItem.Get("profilePath")
WScript.Echo "profilePath: " & strprofilePath
strscriptPath = objItem.Get("scriptPath")
WScript.Echo "scriptPath: " & strscriptPath
strhomeDirectory = objItem.Get("homeDirectory")
WScript.Echo "homeDirectory: " & strhomeDirectory
strhomeDrive = objItem.Get("homeDrive")
WScript.Echo "homeDrive: " & strhomeDrive

WScript.Echo VbCrLf & "** Telephone Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strhomePhone = objItem.Get("homePhone")
WScript.Echo "homePhone: " & strhomePhone
strpager = objItem.Get("pager")
WScript.Echo "pager: " & strpager
strmobile = objItem.Get("mobile")
WScript.Echo "mobile: " & strmobile
strfacsimileTelephoneNumber = objItem.Get("facsimileTelephoneNumber")
WScript.Echo "facsimileTelephoneNumber: " & strfacsimileTelephoneNumber
stripPhone = objItem.Get("ipPhone")
WScript.Echo "ipPhone: " & stripPhone
strinfo = objItem.Get("info")
WScript.Echo "info: " & strinfo

WScript.Echo VbCrLf & "** Telephone Properties Page**"
WScript.Echo "** (MultiValued Attributes) **"
strotherHomePhone = objItem.GetEx("otherHomePhone")
WScript.Echo "otherHomePhone:"
For Each Item in strotherHomePhone
 WScript.Echo vbTab & Item
Next
strotherPager = objItem.GetEx("otherPager")
WScript.Echo "otherPager:"
For Each Item in strotherPager
 WScript.Echo vbTab & Item
Next
strotherMobile = objItem.GetEx("otherMobile")
WScript.Echo "otherMobile:"
For Each Item in strotherMobile
 WScript.Echo vbTab & Item
Next
strotherFacsimileTelephoneNumber = objItem.GetEx("otherFacsimileTelephoneNumber")
WScript.Echo "otherFacsimileTelephoneNumber:"
For Each Item in strotherFacsimileTelephoneNumber
 WScript.Echo vbTab & Item
Next
strotherIpPhone = objItem.GetEx("otherIpPhone")
WScript.Echo "otherIpPhone:"
For Each Item in strotherIpPhone
 WScript.Echo vbTab & Item
Next

WScript.Echo VbCrLf & "** Organization Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strtitle = objItem.Get("title")
WScript.Echo "title: " & strtitle
strdepartment = objItem.Get("department")
WScript.Echo "department: " & strdepartment
strcompany = objItem.Get("company")
WScript.Echo "company: " & strcompany
strmanager = objItem.Get("manager")
WScript.Echo "manager: " & strmanager

WScript.Echo VbCrLf & "** Organization Properties Page**"
WScript.Echo "** (MultiValued Attributes) **"
strdirectReports = objItem.GetEx("directReports")
WScript.Echo "directReports:"
For Each Item in strdirectReports
 WScript.Echo vbTab & Item
Next

WScript.Echo VbCrLf & "** Environment Properties Page**"
WScript.Echo "** (The ADSI Extension for Terminal Services interface) **"
WScript.Echo "TerminalServicesInitialProgram: " & _
  objItem.TerminalServicesInitialProgram
WScript.Echo "TerminalServicesWorkDirectory: " & _
  objItem.TerminalServicesWorkDirectory
WScript.Echo "ConnectClientDrivesAtLogon: " & _
  objItem.ConnectClientDrivesAtLogon
WScript.Echo "ConnectClientPrintersAtLogon: " & _
  objItem.ConnectClientPrintersAtLogon
WScript.Echo "DefaultToMainPrinter: " & _
  objItem.DefaultToMainPrinter

WScript.Echo VbCrLf & "** Sessions Properties Page**"
WScript.Echo "** (The ADSI Extension for Terminal Services interface) **"
WScript.Echo "MaxDisconnectionTime: " & _
  objItem.MaxDisconnectionTime
WScript.Echo "MaxConnectionTime: " & _
  objItem.MaxConnectionTime
WScript.Echo "MaxIdleTime: " & _
  objItem.MaxIdleTime
WScript.Echo "BrokenConnectionAction: " & _
  objItem.BrokenConnectionAction
WScript.Echo "ReconnectionAction: " & _
  objItem.ReconnectionAction

WScript.Echo VbCrLf & "** Remote Control Properties Page**"
WScript.Echo "** (The ADSI Extension for Terminal Services interface) **"
WScript.Echo "EnableRemoteControl: " & _
  objItem.EnableRemoteControl

Select Case objItem.EnableRemoteControl
  Case 0
    WScript.Echo "Remote Control disabled"
  Case 1
    WScript.Echo "Remote Control enabled"
    WScript.Echo "User permission required"
    WScript.Echo "Interact with the session"
  Case 2
    WScript.Echo "Remote Control enabled"
    WScript.Echo "User permission not required"
    WScript.Echo "Interact with the session"
  Case 3
    WScript.Echo "Remote Control enabled"
    WScript.Echo "User permission required"
    WScript.Echo "View the session"
  Case 4
    WScript.Echo "Remote Control enabled"
    WScript.Echo "User permission not required"
    WScript.Echo "View the session"
End Select

WScript.Echo VbCrLf & "** Terminal Services Profile Properties Page**"
WScript.Echo "** (The ADSI Extension for Terminal Services interface) **"
WScript.Echo "TerminalServicesProfilePath: " & _
  objItem.TerminalServicesProfilePath
WScript.Echo "TerminalServicesHomeDirectory: " & _
  objItem.TerminalServicesHomeDirectory
WScript.Echo "TerminalServicesHomeDrive: " & _
  objItem.TerminalServicesHomeDrive
WScript.Echo "AllowLogon: " & _
  objItem.AllowLogon

WScript.Echo VbCrLf & "** COM+ Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
WScript.Echo "msCOM-UserPartitionSetLink: "
WScript.Echo " " & objItem.Get("msCOM-UserPartitionSetLink")

WScript.Echo VbCrLf & "** Member Of Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strprimaryGroupID = objItem.Get("primaryGroupID")
WScript.Echo "primaryGroupID: " & strprimaryGroupID

WScript.Echo VbCrLf & "** Member Of Properties Page**"
WScript.Echo "** (MultiValued Attributes) **"
strmemberOf = objItem.GetEx("memberOf")
WScript.Echo "memberOf:"
For Each Item in strmemberOf
 WScript.Echo vbTab & Item
Next

WScript.Echo VbCrLf & "** Object Properties Page**"
WScript.Echo "** (Single-Valued Attributes) **"
strwhenCreated = objItem.Get("whenCreated")
WScript.Echo "whenCreated: " & strwhenCreated
strwhenChanged = objItem.Get("whenChanged")
WScript.Echo "whenChanged: " & strwhenChanged

objItem.GetInfoEx Array("canonicalName"), 0
WScript.Echo VbCrLf & "** Object Properties Page**"
WScript.Echo "** (MultiValued Attributes) **"
strcanonicalName = objItem.GetEx("canonicalName")
WScript.Echo "canonicalName:"
For Each Item in strcanonicalName
 WScript.Echo vbTab & Item
Next

'---------------------------------------------

Did that help?





mmcdonal

Thank you for that. It apparently does connect, and I can figure out how to export to a proper CSV. One issue with it is that while it does give accurate responses for keys being enabled and disabled, just about everything else had an empty response.

I will tinker with it, unless you know what is going on. Maybe I am just using the wrong username or format for it.

I think you will want to substitute the actual username you are trying to get info for. The script uses a fictional name, I think. Try your own username and see.

Did that help? If I had my 24X7 books on Wrox (coming soon) I could look up a definitive answer for you.

mmcdonal

Thank you for pointing me in a direction. I ended up having to use a different type of query, but it seems that it will work. The only thing remaining will be to place this in a loop and pull all users, then export everything to a CSV.

If anyone could tell me how to change "strQuery" to a "*" that would be great so that I can maintain some consistency. However it proly isn't possible without binding since it would pull too much data.