Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2015
    Posts
    14

    Unanswered: Active Directory/MS Access Conversion Issue

    Here is the following code i have and the yellow is the conversion error. I have tried numerous ideas but cannot get it to work, any help is appreciated!

    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.Properties("Chase referrals") = ADS_CHASE_REFERRALS_EXTERNAL
    objCommand.CommandText = "SELECT givenName, sn, name, department, title, sAMAccountName, accountExpires, userAccountControl FROM '" & strOU1 & "' WHERE objectCategory='user' ORDER BY sAMAccountName"
    Set objRecordset = objCommand.Execute
    While Not objRecordset.EOF
    Set strName = objRecordset.Fields("Name")
    strName = Replace(strName, "'", "''")
    Set strDept = objRecordset.Fields("department")
    Set stradname = objRecordset.Fields("sAMAccountName")
    Set strExp = objRecordset.Fields("accountExpires").Value

    'Set strExp = objRecordset.Fields("accountExpires") 'I have tried this as well in place of the above

    CurrentDb.Execute "Insert Into Test (Tname,adname,dept,exp) Values ( '" & strName & "', '" & stradname & "', '" & strDept & "', '" & strExp & "')", dbFailOnError

    objRecordset.MoveNext
    Wend

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?
    Have a nice day!

  3. #3
    Join Date
    Apr 2015
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?
    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem. Thanks again.

  4. #4
    Join Date
    Apr 2015
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?
    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem. Thanks again.

  5. #5
    Join Date
    Apr 2015
    Posts
    14
    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem. Thanks again.

  6. #6
    Join Date
    Apr 2015
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?

    I get an error still on varExp = objRecordset.Fields("accountExpires").Value
    Run-Time error '438
    Object doesn't support this property or method.

    This goes back to its a conversion issue of some sort.

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem

  7. #7
    Join Date
    Apr 2015
    Posts
    14
    I get an error still on varExp = objRecordset.Fields("accountExpires").Value
    Run-Time error '438
    Object doesn't support this property or method.

    This goes back to its a conversion issue of some sort.

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem

  8. #8
    Join Date
    Apr 2015
    Posts
    14
    I get an error still on varExp = objRecordset.Fields("accountExpires").Value
    Run-Time error '438
    Object doesn't support this property or method.

    This goes back to its a conversion issue of some sort.

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem

  9. #9
    Join Date
    Apr 2015
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?


    I get an error still on varExp = objRecordset.Fields("accountExpires").Value
    Run-Time error '438
    Object doesn't support this property or method.

    This goes back to its a conversion issue of some sort.

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem

  10. #10
    Join Date
    Apr 2015
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?


    I get an error still on varExp = objRecordset.Fields("accountExpires").Value
    Run-Time error '438
    Object doesn't support this property or method.

    This goes back to its a conversion issue of some sort.

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem

  11. #11
    Join Date
    Apr 2015
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?


    I get an error still on varExp = objRecordset.Fields("accountExpires").Value
    Run-Time error '438
    Object doesn't support this property or method.

    This goes back to its a conversion issue of some sort.

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem

  12. #12
    Join Date
    Apr 2015
    Posts
    14

    reply

    Quote Originally Posted by Sinndho View Post
    1. As you don't know the data type of the values retrieved from the ADO.Fields objects, you should explicitly declare the variables as Variant, but in such a case, the prefix should be var, not str, so they can receive a Null value (if any):
    Code:
        Dim varName As Variant
        Dim varDept As Variant
        Dim varadname As Variant
        Dim varExp As Variant
    2. Since you want to retrieve the Value of AOD.Fields objects, the correct assignment operator in use should be (without the Set):
    Code:
    Variable = objRecordset.Fields("Name").Value
    or:
    Code:
    Variable = objRecordset.Fields("Name")
    (You can omit the .Value part because its the default property of an ADO.Fields object).
    You dont receive any compilation error because all the variables are implicitly declared (no Option Explicit instruction in the module). This causes all variables not explicitly typed to be Variants:
    Code:
        While Not objRecordset.EOF
            varName = Replace(strName, "'", "''")
            varDept = objRecordset.Fields("department")
            varadname = objRecordset.Fields("sAMAccountName")
            varExp = objRecordset.Fields("accountExpires").Value
    3. What does the string variable strOU1 contains?

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    Has anyone else seen how to convert the active directory field accountexpires? If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem. Thanks again.

  13. #13
    Join Date
    Apr 2015
    Posts
    14

    Response

    The stOU1 contains the LDAP of the OU and DC within active directory that I am getting the information from, i.e. "LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST"

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date that is what I am trying to do. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem. Thanks again.

  14. #14
    Join Date
    Apr 2015
    Posts
    14

    Access-active directory issue

    For some reason when I hit the reply button to my original thread it goes to a blank page and does not insert it so I am having to start this new thread, sorry for duplication. My question is how do I fix the following issue:

    All of the fields i have no issue with getting the information other than the "ACCOUNTEXPIRES" field. It is because it does not convert correctly or i have a bad setting. The following URL is something i tried, even modifiying with my limited ability but could not get it to work. http://www.rlmueller.net/Integer8Attributes.htm

    If there is a way to take what is in that link to convert that field to the correct date (ACCOUNTEXPIRES) that is what I am trying to do. The other fields work as intended, please do not let the other code throw you it all works. I hope that better helps to what i am doing. Like i said the other fields work with no issues, it is the one field that is causing me this problem. Thanks again.

    strOU1=LDAP://OU=TEST,DC=Test,DC=TEST,DC=TEST,DC=TEST

    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.Properties("Chase referrals") = ADS_CHASE_REFERRALS_EXTERNAL
    objCommand.CommandText = "SELECT givenName, sn, name, department, title, sAMAccountName, accountExpires, userAccountControl FROM '" & strOU1 & "' WHERE objectCategory='user' ORDER BY sAMAccountName"
    Set objRecordset = objCommand.Execute
    While Not objRecordset.EOF
    Set strName = objRecordset.Fields("Name")
    strName = Replace(strName, "'", "''")
    Set strDept = objRecordset.Fields("department")
    Set stradname = objRecordset.Fields("sAMAccountName")
    Set strExp = objRecordset.Fields("accountExpires").Value

    'Set strExp = objRecordset.Fields("accountExpires") 'I have tried this as well in place of the above


    CurrentDb.Execute "Insert Into Test (Tname,adname,dept,exp) Values ( '" & strName & "', '" & stradname & "', '" & strDept & "', '" & strExp & "')", dbFailOnError

    objRecordset.MoveNext
    Wend

Posting Permissions

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