Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    I have desinged a database for a client that become so popular that they are pulling data into it from 2 other databases at remote sites so as to keep all the latest and greatest info in this 'Flagship' database. Because of the huge diferences between the three schemas, I wrote a bunch of queries that pulls data from the outside sources, and completely writes over the local version, ensuring that if any field had changed in any record, it would be properly updated in our local file.

    Now the customer has begun updating every hour! That translates to approx 160k records per day being updated! Not so bad until you consider that each one is taking up a new AutoNumber in the local tables. So for the first time, I am wondering how to quickly prevent them from hitting the 2.1 billion number limit.

    If my math is correct, 2.1 billion divided by 160 thousand gives me approx 35.96 years to address this. But I am afraid to wait...(Think Y2K)

    Got any ideas?

    Thanks,

    Rich

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    It's actually 4.3 billion. I just checked - autonumbers wrap around to negative numbers (use an append query to start an autonumber off at 2147483647 and watch what you get for the next one).

    You're really concerned about what will happen 72 years from now (or even 36 years)?! You're either going to have to keep your computer (or a compatible one) around for a long time or the database will need to be updated when it is no longer compatible with current computers.

    Keep in mind that, unlike the problem with Y2K dates, your problem vanishes when 64 bit integers (and therefore, 64 bit autonumbers) become commonplace. 2^64 is a BIG number!

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    If you are REALLY concerned about using up all the autonumbers and they start adding records every minute or every second, you could use GUIDs. They come as a part of replication automatically, but you can make an autonumber field a GUID. They use 16 bytes of data to almost certainly ensure uniqueness. On the other hand they do impose some performance penalties if response times are an issue.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    Not to mention that they're a royal PIA to handle in code, since they're a hybrid sort of animal, sort of a string and something like an array.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    I think you're wasting your time worrying about it. If you really needed that many autonumbers, you would also need a full-fledged database server like SQL Server or Oracle.
    Charlotte

Posting Permissions

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