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

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    Author
    Topic
    #361796

    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

    Viewing 1 reply thread
    Author
    Replies
    • #547993

      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!

      • #548004

        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.

        • #548038

          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.

    • #548039

      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.

    Viewing 1 reply thread
    Reply To: Acc97: Running out of AutoNumbers! (Acc97 SR-2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: