Windows Vista Forums

Reading from Access to update Active Directory

  1. #1


    Glenn Guest

    Reading from Access to update Active Directory

    I am trying to use the script below to read from an Access database and to
    update Active Directory. When I run it I get an error that "The server is
    not operational" Code 8007203A. It happens on the "Set obbUser = Get Object
    _ line.

    If I change the line below that, to ("LDAP://...") (filling in the "..."
    with the distinguished name of a given user, it works fine.

    Can someone please help me out with how to write the LDAP line?

    Thanks!

    ---script:

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")

    Const adOpenStatic = 3
    Const adLockOptimistic = 3

    objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source = \\myserver\myshare\users.mdb"

    objRecordSet.Open "SELECT * FROM QRY_STAFF" , _
    objConnection, adOpenStatic, adLockOptimistic

    objRecordSet.MoveFirst

    Do Until objRecordset.EOF

    Const ADS_PROPERTY_UPDATE = 2

    Set objUser = GetObject _
    ("LDAP:// & objRecordSet(""distinguishedName"") & ")

    objUser.Put "facsimileTelephoneNumber", "" &
    objRecordSet("facsimileTelephoneNumber") & ""

    objRecordset.MoveNext
    Loop

    objRecordSet.Close
    objConnection.Close



      My System SpecsSystem Spec

  2. #2


    Glenn Guest

    Re: Reading from Access to update Active Directory

    Please disregard. I figured out what I need to do.


    "Glenn" <nospam@xxxxxx> wrote in message
    news:u56ngdVGKHA.3708@xxxxxx

    >I am trying to use the script below to read from an Access database and to
    >update Active Directory. When I run it I get an error that "The server is
    >not operational" Code 8007203A. It happens on the "Set obbUser = Get
    >Object _ line.
    >
    > If I change the line below that, to ("LDAP://...") (filling in the "..."
    > with the distinguished name of a given user, it works fine.
    >
    > Can someone please help me out with how to write the LDAP line?
    >
    > Thanks!
    >
    > ---script:
    >
    > Set objConnection = CreateObject("ADODB.Connection")
    > Set objRecordSet = CreateObject("ADODB.Recordset")
    >
    > Const adOpenStatic = 3
    > Const adLockOptimistic = 3
    >
    > objConnection.Open _
    > "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    > "Data Source = \\myserver\myshare\users.mdb"
    >
    > objRecordSet.Open "SELECT * FROM QRY_STAFF" , _
    > objConnection, adOpenStatic, adLockOptimistic
    >
    > objRecordSet.MoveFirst
    >
    > Do Until objRecordset.EOF
    >
    > Const ADS_PROPERTY_UPDATE = 2
    >
    > Set objUser = GetObject _
    > ("LDAP:// & objRecordSet(""distinguishedName"") & ")
    >
    > objUser.Put "facsimileTelephoneNumber", "" &
    > objRecordSet("facsimileTelephoneNumber") & ""
    >
    > objRecordset.MoveNext
    > Loop
    >
    > objRecordSet.Close
    > objConnection.Close
    >


      My System SpecsSystem Spec

  3. #3


    Richard Mueller [MVP] Guest

    Re: Reading from Access to update Active Directory


    "Glenn" <nospam@xxxxxx> wrote in message
    news:u56ngdVGKHA.3708@xxxxxx

    >I am trying to use the script below to read from an Access database and to
    >update Active Directory. When I run it I get an error that "The server is
    >not operational" Code 8007203A. It happens on the "Set obbUser = Get
    >Object _ line.
    >
    > If I change the line below that, to ("LDAP://...") (filling in the "..."
    > with the distinguished name of a given user, it works fine.
    >
    > Can someone please help me out with how to write the LDAP line?
    >
    > Thanks!
    >
    > ---script:
    >
    > Set objConnection = CreateObject("ADODB.Connection")
    > Set objRecordSet = CreateObject("ADODB.Recordset")
    >
    > Const adOpenStatic = 3
    > Const adLockOptimistic = 3
    >
    > objConnection.Open _
    > "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    > "Data Source = \\myserver\myshare\users.mdb"
    >
    > objRecordSet.Open "SELECT * FROM QRY_STAFF" , _
    > objConnection, adOpenStatic, adLockOptimistic
    >
    > objRecordSet.MoveFirst
    >
    > Do Until objRecordset.EOF
    >
    > Const ADS_PROPERTY_UPDATE = 2
    >
    > Set objUser = GetObject _
    > ("LDAP:// & objRecordSet(""distinguishedName"") & ")
    >
    > objUser.Put "facsimileTelephoneNumber", "" &
    > objRecordSet("facsimileTelephoneNumber") & ""
    >
    > objRecordset.MoveNext
    > Loop
    >
    > objRecordSet.Close
    > objConnection.Close
    I would try:
    =========
    Do Until objRecordset.EOF
    Set objUser = GetObject _
    ("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
    objUser.Put "facsimileTelephoneNumber", _
    objRecordSet.Fields("facsimileTelephoneNumber").Value
    objUser.SetInfo
    objRecordset.MoveNext
    Loop
    =========
    An error will be raised if there is no value for facsimileTelephoneNumber,
    so you might want to test for that. For example:
    =======
    Do Until objRecordset.EOF
    strUserDN = objRecordSet.Fields("distinguishedName").Value
    strFaxNumber = objRecordSet.Fields("facsimileTelephoneNumber").Value &
    ""
    Set objUser = GetObject("LDAP://" & strUserDN)
    If (strFaxNumber <> "") Then
    objUser.Put "facsimileTelephoneNumber", strFaxNumber
    objUser.SetInfo
    End If
    objRecordset.MoveNext
    Loop
    =========
    I concatenate the blank string, "", to the fax number in case the value is
    Null, so the variable is a blank string in that case. I don't do this for
    distinguishedName, as it is required (or should be).

    --
    Richard Mueller
    MVP Directory Services
    Hilltop Lab - http://www.rlmueller.net
    --



      My System SpecsSystem Spec

  4. #4


    Glenn Guest

    Re: Reading from Access to update Active Directory

    Incredible - I discovered the problem you mentioned last night (about
    blanks), and found a work around (using a query to find records only with
    fax numbers), but wanted to find a better solution for the problem long
    term. I had figured it needed to be something similar to what you
    suggested, but wasn't sure how to code that. I just came on with the intent
    to ask the question, and you had already answered it. Thank you! I haven't
    asked questions on here in a while, but when I have you've always been
    extremely helpful.

    One more, hopefully quick question - what if I actually wanted to delete the
    value of what was in the field? Is there something I could put in my
    database that would accomplish this?

    Thanks.



    "Richard Mueller [MVP]" <rlmueller-nospam@xxxxxx> wrote in
    message news:urDEylWGKHA.2832@xxxxxx

    >
    > "Glenn" <nospam@xxxxxx> wrote in message
    > news:u56ngdVGKHA.3708@xxxxxx

    >>I am trying to use the script below to read from an Access database and to
    >>update Active Directory. When I run it I get an error that "The server is
    >>not operational" Code 8007203A. It happens on the "Set obbUser = Get
    >>Object _ line.
    >>
    >> If I change the line below that, to ("LDAP://...") (filling in the "..."
    >> with the distinguished name of a given user, it works fine.
    >>
    >> Can someone please help me out with how to write the LDAP line?
    >>
    >> Thanks!
    >>
    >> ---script:
    >>
    >> Set objConnection = CreateObject("ADODB.Connection")
    >> Set objRecordSet = CreateObject("ADODB.Recordset")
    >>
    >> Const adOpenStatic = 3
    >> Const adLockOptimistic = 3
    >>
    >> objConnection.Open _
    >> "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    >> "Data Source = \\myserver\myshare\users.mdb"
    >>
    >> objRecordSet.Open "SELECT * FROM QRY_STAFF" , _
    >> objConnection, adOpenStatic, adLockOptimistic
    >>
    >> objRecordSet.MoveFirst
    >>
    >> Do Until objRecordset.EOF
    >>
    >> Const ADS_PROPERTY_UPDATE = 2
    >>
    >> Set objUser = GetObject _
    >> ("LDAP:// & objRecordSet(""distinguishedName"") & ")
    >>
    >> objUser.Put "facsimileTelephoneNumber", "" &
    >> objRecordSet("facsimileTelephoneNumber") & ""
    >>
    >> objRecordset.MoveNext
    >> Loop
    >>
    >> objRecordSet.Close
    >> objConnection.Close
    >
    > I would try:
    > =========
    > Do Until objRecordset.EOF
    > Set objUser = GetObject _
    > ("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
    > objUser.Put "facsimileTelephoneNumber", _
    > objRecordSet.Fields("facsimileTelephoneNumber").Value
    > objUser.SetInfo
    > objRecordset.MoveNext
    > Loop
    > =========
    > An error will be raised if there is no value for facsimileTelephoneNumber,
    > so you might want to test for that. For example:
    > =======
    > Do Until objRecordset.EOF
    > strUserDN = objRecordSet.Fields("distinguishedName").Value
    > strFaxNumber = objRecordSet.Fields("facsimileTelephoneNumber").Value &
    > ""
    > Set objUser = GetObject("LDAP://" & strUserDN)
    > If (strFaxNumber <> "") Then
    > objUser.Put "facsimileTelephoneNumber", strFaxNumber
    > objUser.SetInfo
    > End If
    > objRecordset.MoveNext
    > Loop
    > =========
    > I concatenate the blank string, "", to the fax number in case the value is
    > Null, so the variable is a blank string in that case. I don't do this for
    > distinguishedName, as it is required (or should be).
    >
    > --
    > Richard Mueller
    > MVP Directory Services
    > Hilltop Lab - http://www.rlmueller.net
    > --
    >
    >


      My System SpecsSystem Spec

  5. #5


    Richard Mueller [MVP] Guest

    Re: Reading from Access to update Active Directory

    In the past when I want to remove an attribute value (make it not set) I
    select a special value to indicate this. For example, when I read values
    from a spreadsheet, I often use ".delete" to mean remove the value. I guess
    you could also use a blank string, "", but I have that mean do nothing
    (leave any existing value alone). For example, I use code similar to:
    ========
    Const ADS_PROPERTY_CLEAR = 1

    Do Until objRecordset.EOF
    strUserDN = objRecordSet.Fields("distinguishedName").Value
    strFaxNumber = objRecordSet.Fields("facsimileTelephoneNumber").Value &
    ""
    Set objUser = GetObject("LDAP://" & strUserDN)
    If (strFaxNumber = ".delete") Then
    objUser.PutEx ADS_PROPERTY_CLEAR, "facsimileTelephoneNumbver", 0
    objUser.SetInfo
    ElseIf (strFaxNumber <> "") Then
    objUser.Put "facsimileTelephoneNumber", strFaxNumber
    objUser.SetInfo
    End If
    objRecordset.MoveNext
    Loop

    --
    Richard Mueller
    MVP Directory Services
    Hilltop Lab - http://www.rlmueller.net
    --
    "Glenn" <nospam@xxxxxx> wrote in message
    news:%23YF84haGKHA.4168@xxxxxx

    > Incredible - I discovered the problem you mentioned last night (about
    > blanks), and found a work around (using a query to find records only with
    > fax numbers), but wanted to find a better solution for the problem long
    > term. I had figured it needed to be something similar to what you
    > suggested, but wasn't sure how to code that. I just came on with the
    > intent to ask the question, and you had already answered it. Thank you!
    > I haven't asked questions on here in a while, but when I have you've
    > always been extremely helpful.
    >
    > One more, hopefully quick question - what if I actually wanted to delete
    > the value of what was in the field? Is there something I could put in my
    > database that would accomplish this?
    >
    > Thanks.
    >
    >
    >
    > "Richard Mueller [MVP]" <rlmueller-nospam@xxxxxx> wrote in
    > message news:urDEylWGKHA.2832@xxxxxx

    >>
    >> "Glenn" <nospam@xxxxxx> wrote in message
    >> news:u56ngdVGKHA.3708@xxxxxx

    >>>I am trying to use the script below to read from an Access database and
    >>>to update Active Directory. When I run it I get an error that "The
    >>>server is not operational" Code 8007203A. It happens on the "Set obbUser
    >>>= Get Object _ line.
    >>>
    >>> If I change the line below that, to ("LDAP://...") (filling in the
    >>> "..." with the distinguished name of a given user, it works fine.
    >>>
    >>> Can someone please help me out with how to write the LDAP line?
    >>>
    >>> Thanks!
    >>>
    >>> ---script:
    >>>
    >>> Set objConnection = CreateObject("ADODB.Connection")
    >>> Set objRecordSet = CreateObject("ADODB.Recordset")
    >>>
    >>> Const adOpenStatic = 3
    >>> Const adLockOptimistic = 3
    >>>
    >>> objConnection.Open _
    >>> "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    >>> "Data Source = \\myserver\myshare\users.mdb"
    >>>
    >>> objRecordSet.Open "SELECT * FROM QRY_STAFF" , _
    >>> objConnection, adOpenStatic, adLockOptimistic
    >>>
    >>> objRecordSet.MoveFirst
    >>>
    >>> Do Until objRecordset.EOF
    >>>
    >>> Const ADS_PROPERTY_UPDATE = 2
    >>>
    >>> Set objUser = GetObject _
    >>> ("LDAP:// & objRecordSet(""distinguishedName"") & ")
    >>>
    >>> objUser.Put "facsimileTelephoneNumber", "" &
    >>> objRecordSet("facsimileTelephoneNumber") & ""
    >>>
    >>> objRecordset.MoveNext
    >>> Loop
    >>>
    >>> objRecordSet.Close
    >>> objConnection.Close
    >>
    >> I would try:
    >> =========
    >> Do Until objRecordset.EOF
    >> Set objUser = GetObject _
    >> ("LDAP://" & objRecordSet.Fields("distinguishedName").Value)
    >> objUser.Put "facsimileTelephoneNumber", _
    >> objRecordSet.Fields("facsimileTelephoneNumber").Value
    >> objUser.SetInfo
    >> objRecordset.MoveNext
    >> Loop
    >> =========
    >> An error will be raised if there is no value for
    >> facsimileTelephoneNumber, so you might want to test for that. For
    >> example:
    >> =======
    >> Do Until objRecordset.EOF
    >> strUserDN = objRecordSet.Fields("distinguishedName").Value
    >> strFaxNumber = objRecordSet.Fields("facsimileTelephoneNumber").Value &
    >> ""
    >> Set objUser = GetObject("LDAP://" & strUserDN)
    >> If (strFaxNumber <> "") Then
    >> objUser.Put "facsimileTelephoneNumber", strFaxNumber
    >> objUser.SetInfo
    >> End If
    >> objRecordset.MoveNext
    >> Loop
    >> =========
    >> I concatenate the blank string, "", to the fax number in case the value
    >> is Null, so the variable is a blank string in that case. I don't do this
    >> for distinguishedName, as it is required (or should be).
    >>
    >> --
    >> Richard Mueller
    >> MVP Directory Services
    >> Hilltop Lab - http://www.rlmueller.net
    >> --
    >>
    >>
    >


      My System SpecsSystem Spec


Reading from Access to update Active Directory
Similar Threads
Thread Forum
Access Active directory with sql server in powershell PowerShell
Active Directory: General Access denied VB Script
searching active directory while reading/writing to excel VB Script
Active Directory Vista networking & sharing
active directory PowerShell