Results 1 to 12 of 12
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Determine if a field is anAutonumber/Identity (2000+)

    I want to be able to determine if a field in a table is an Identity/Autonumber field

    I think this is possible by using the Attributes Property of the field

    I cannot find any actual properties or data types in the vba environment that give this info

    IF this is possible does anyone know what the argument value or intrinsic variable name is for this

    I believe it might be possible using a bit of logic like this

    If fld.attributes And intAutoNumber = intAutoNumber then ....

    where intAutonumber is the value that the attributes assign to identifying an Autonumber field

    On the other hand I could be barking up the wrong tree.

    But any info useful at this stage

    Thanks in advance
    Andrew

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if a field is anAutonumber/Identity

    OOPS Sorry

    I just had a look through the object browser and found it.
    I should have looked there first.

    However. it may help others.

    Solution is dbAutoIncrField

    e.g. To Determine if a field is an autonumber use the logic below

    If fld.Attributes And dbAutoIncrField = dbAutoIncrField Then MsgBox "This field is an Autonumber", vbExclamation (Or whatever else you need to do).
    Andrew

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine if a field is anAutonumber/Identity (2000+)

    Are you using DAO or ADO?

    Added: OK, you already found it.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if a field is anAutonumber/Identity

    My solution uses DAO, and a tabledef and field object

    However it also works using ADODB and a field from a Recordset

    Probably also possible by looking at the table and field objects from the ADOX library rather than ADODB
    but I didn't try that one.
    Andrew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine if a field is anAutonumber/Identity

    But dbAutoIncrField is a DAO constant only - it is not known or valid in ADODB.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Determine if a field is anAutonumber/Identity (2000+)

    Using DAO, if the .Type property of a field = 4, and if the .Attributes property = 17, then it is an autonumber. Offhand, I don't remember want the constants are (if there are any).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if a field is anAutonumber/Identity

    It mat well be a DAO constant but it does also work in ADO if applied to a recordset field object
    At lease it appears to work in my tests.
    Andrew

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine if a field is anAutonumber/Identity

    The DAO field atrribute dbAutoIncrField has value 16. The ADODB field attribute with the same value is adFldFixed; this indicates whether the field contents have a fixed length (this is true for number fields and false for text fields), so I don't think you should use dbAutoIncrField in an ADODB context - it means something different.

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if a field is anAutonumber/Identity

    I very good point, and one I had not spotted so thanks.
    Doesn't appear to be an attribute for ado that specifies this option.
    Not a Type property..
    Maybe someone knows what it is or how to do it in ado.
    It must be possible.
    Andrew

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine if a field is anAutonumber/Identity

    It must be possible in ADO or ADOX, but why bother? DAO is much more convenient for this kind of thing.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine if a field is anAutonumber/Identity

    And I've also heard that DAO has been given a new lease on life in Access 2007 while ADO is being phased out.

  12. #12
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine if a field is anAutonumber/Identity

    I know, it's purely academic.
    It's sometimes just nice to know stuff for the sake of it.
    Andrew

Posting Permissions

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