Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    California
    Posts
    11

    Unanswered: Importing Active Directory into Access 2007

    Hello to all,

    I have found the following code that claims to be able to import AD info into a table in Access 2007. I am just unsure where to put it... Should it be on a click event or something. What would be the best practices if I would want to link to the AD to use that info within my databases...???

    Thanks for all your help and advice!!!

    Code:
    Dim dbTableName
    Dim dbPath
    Dim LastLogin
    
    Set objRoot = GetObject("LDAP://RootDSE";)
    strDNC = objRoot.Get("DefaultNamingContext")
    Set objGroup = GetObject("LDAP://"; & strDNC)
    dbPath = "Z:\jms_be.accdb"
    dbTableName = "AD Users"
    Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "Microsoft.ACE.OLEDB.12.0"
    conn.Open dbPath
    On Error Resume Next
    SqlCommand = "CREATE TABLE [" & dbTableName & "] (SamAccountName 
    Text(255),
    CN Text(255), FirstName Text(255), LastName Text(255)," & _
    "Initials Text(255), Descrip Text(255), Office Text(255), Telephone
    Text(255), Email Text(255), WebPage Text(255)," & _
    "Addr1 Text(255), City Text(255), State Text(255), ZipCode Text(255),
    Title Text(255), Department Text(255)," & _
    "Company Text(255), Manager Text(255), Profile Text(255), LoginScript
    Text(255), HomeDirectory Text(255)," & _
    "HomeDrive Text(255), Adspath Text(255), LastLogin Text(255), OU
    Text(255), Disabled Text(255))"
    conn.Execute (SqlCommand)
    On Error GoTo 0
    
    SqlCommand = "DELETE * FROM [" & dbTableName & "]"
    conn.Execute (SqlCommand)
    Call enumMembers(objGroup)
    MsgBox "Done"
    Sub enumMembers(objGroup)
    For Each objMember In objGroup
    If objMember.Class = "user" Then
    Set Container = GetObject(objMember.Parent)
    OU = FixValue(Mid(Container.Name, 4), "OU")
    SamAccountName = FixValue(objMember.SamAccountName, "SamAccountName")
    Cn = FixValue(objMember.Cn, "CN")
    Disabled = objMember.AccountDisabled
    FirstName = FixValue(objMember.GivenName, "FirstName")
    LastName = FixValue(objMember.sn, "LastName")
    initials = FixValue(objMember.initials, "Innitial")
    Descrip = FixValue(objMember.Description, "Descript")
    Office = FixValue(objMember.physicalDeliveryOfficeName, "Office")
    Telephone = FixValue(objMember.telephonenumber, "Telephone")
    Email = FixValue(objMember.mail, "Email")
    WebPage = FixValue(objMember.wwwHomePage, "WebPage")
    Addr1 = FixValue(objMember.streetAddress, "Addr1")
    City = FixValue(objMember.l, "City")
    State = FixValue(objMember.st, "State")
    ZipCode = FixValue(objMember.postalCode, "ZipCode")
    Title = FixValue(objMember.Title, "Title")
    Department = FixValue(objMember.Department, "Department")
    Company = FixValue(objMember.Company, "Company")
    Manager = FixValue(objMember.Manager, "Manager")
    Profile = FixValue(objMember.profilePath, "Profile")
    LoginScript = FixValue(objMember.scriptpath, "LoginScript")
    HomeDirectory = FixValue(objMember.HomeDirectory, "HomeDirectory")
    HomeDrive = FixValue(objMember.HomeDrive, "HomeDrive")
    AdsPath = FixValue(objMember.AdsPath, "AdsPath")
    On Error Resume Next
    LastLogin = FixValue(objMember.LastLogin, "LastLogin")
    On Error GoTo 0
    If IsDate(LastLogin) = True Then
    LastLogin = FixValue(LastLogin, "LastLoginConverted")
    Else
    LastLogin = "1/1/2000"
    
    End If
    If not(Disabled = "True" or CN = "TLCADMIN" or OU = "Training" or OU =
    "Visiting Teachers" or OU = "Disabled" or OU = "Restricted Access" or OU =
    "Service Accounts" or OU = "TEST" or OU = "Users" or OU = "corporate" or 
    OU =
    "GPO Tests" or OU = "Mandatory Wallpaper" or Descrip = "Generic_Login") 
    Then
    QryInsert = "INSERT INTO [" & dbTableName & "](SamAccountName, CN,
    FirstName, LastName, Initials, Descrip, Office, Telephone, Email, WebPage,
    Addr1, City, State, ZipCode, Title, Department, Company, Manager, Profile,
    LoginScript, HomeDirectory, HomeDrive, AdsPath, LastLogin, OU, Disabled)
    VALUES ('" & SamAccountName & "','" & Cn & "','" & FirstName & "','" &
    LastName & "','" & initials & "','" & Descrip & "','" & Office & "','" &
    Telephone & "','" & Email & "','" & WebPage & "','" & Addr1 & "','" & City 
    &
    "','" & State & "','" & ZipCode & "','" & Title & "','" & Department & 
    "','"
    & Company & "','" & Manager & "','" & Profile & "','" & LoginScript & 
    "','" &
    HomeDirectory & "','" & HomeDrive & "','" & AdsPath & "','" & LastLogin &
    "','" & OU & "','" & Disabled & "')"
    conn.Execute (QryInsert)
    End If
    SamAccountName = "-"
    Cn = "-"
    FirstName = "-"
    LastName = "-"
    initials = "-"
    Descrip = "-"
    Office = "-"
    Telephone = "-"
    Email = "-"
    WebPage = "-"
    Addr1 = "-"
    City = "-"
    State = "-"
    ZipCode = "-"
    Title = "-"
    Department = "-"
    Company = "-"
    Manager = "-"
    Profile = "-"
    LoginScript = "-"
    HomeDirectory = "-"
    HomeDrive = "-"
    LastLogin = FormatDateTime("1/1/2005")
    OU = "-"
    
    End If
    If objMember.Class = "organizationalUnit" Or objMember.Class =
    "container" Then
    enumMembers (objMember)
    End If
    Next
    End Sub
    Function FixValue(sValue, PropertyName)
    If Err.Number <> 0 Then
    WScript.Echo "Error Code: " & Err.Number & " in reading " & PropertyName &
    "property"
    End If
    If VarType(sValue) = 10 Or VarType(sValue) = 1 Then
    FixValue = "-Null-"
    End If
    FixValue = Replace(sValue, "'", "''")
    End Function

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Please elaborate on, "link to the AD to use that info within my databases".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2009
    Location
    California
    Posts
    11
    Kinda exactly what it states... there are a variety of functions that I would need to use the information in the AD for... I was just curious if I would be able to set this up as a direct link to the AD or simple imports at random times.

    Preferably, I would like to have a linked table but no sure how to accomplish this...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    AD can be used for a TON of things.

    Also, active directory is NOT a flat structure. It is a hierarchical structure by definition. How would you propose to flatten an entire AD structure in to a linked table?


    I'm getting the impression that AD is a much broader subject than you may believe...


    To answer your question directly, I don't believe there is a way to persist an LDAP query as a linked table. If there is, that's specifically what you should be searching for.
    Last edited by Teddy; 07-31-09 at 13:15.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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