dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] How Reliable is Autonumber In Access?

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2006
    Posts
    39

    Resolved [RESOLVED] How Reliable is Autonumber In Access?

    In Ms Access, can I rely on using an Autonumber field to generate unique record-ids? I will be dealing with a record size of 500-5k. I ask coz there're few people who prefer to programatically generate the record-ids rather than rely on an Autonumber field. Somehow they're suspicious of the long-term viability of an Autonumber field. I do not know why! Any observations on this issue?

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: How Reliable is Autonumber In Access?

    Autonumber is reliable with respect to unique IDs...they will not be duplicated.

    There is something to keep in mind however.

    If you have 10 records numbered
    1 2 3 4 5 6 7 8 9 10

    and you delete record 3 and record 8 your next unique number will be 11. It will not reclaim the deleted numbers.

    So, now you would have records numbered like this:
    1 2 4 5 6 7 9 10

    If that isn't a problem, then OK

    I've always gotten around this by only allowing "soft" deletes...in other words, I set a flag in a field which my code interupts as a delete so that record never, ever shows up anywhere, but it maintains my consequtive record numbering (which auditors like) and it allows me the option of "undeleting" a record.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2006
    Posts
    39

    Re: How Reliable is Autonumber In Access?

    hack, I understood you. You're right! So the final judgement is I can go ahead with Autonumber 4 any no. of records. Right?

  4. #4
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: [RESOLVED] How Reliable is Autonumber In Access?

    The final decision is in your hands, not mine.

    If it makes sense to you, then go for it. If it doesn't look like it will work for you, then explore alternatives.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  5. #5

    Thread Starter
    Member
    Join Date
    Jul 2006
    Posts
    39

    Re: [RESOLVED] How Reliable is Autonumber In Access?

    hack my dear, it certainly does make sense to me. I was just concerned about the long-term reliability of using Autonumber. Since you've loads of experience I trust you.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,090

    Re: [RESOLVED] How Reliable is Autonumber In Access?

    I know this is marked as resolved, but I thought I'd chime in.

    In terms of reliability (and your time to get it working), AutoNumber is the better option.

    I've seen several people make attempts at creating their own version, and in each case there has always been at least one situation where it failed (or would have given enough time), such as multiple new records being created 'simultaneously' by different users.

    There are a surprising number of situations that need to be thought of carefully before creating something like that, and it has already been done properly (and tested by millions of people) in AutoNumber.


    As Hack posted, the downside to AutoNumber is the lack of consecutive numbering... but that is actually a good thing in terms of the database (as it means any 'orphan' data in other tables wont get linked to a new record). If you need consecutive numbering, I would recommend thinking of using another field for that purpose.

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width