Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Autonumber for too long (Access 2k2, VB6)

    I have an application where I am using the convenient Autonumber field in the back end database to index a record. I believe this field is of integer value. Does this mean that the application will blow up when it gets 32K (and change) records in it? If so, do others get around this by writing their own code for Autonumber using a long integer datatype?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    It's not an integer value, it's a long integer value.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Pat is correct - it wouldn't run out until you get to just over 2,000,000,000 and then it starts over at -2,000,000,000.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Pat/Wendall, thanks. I think room for 4M records will get me by for now.

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

    Re: Using Autonumber for too long (Access 2k2, VB6)

    That's 4 G (4 billion), not million (phew)!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    My God, I not real proficient counting zeros this early in the morning. I doubt that would hold true if we were talking dollars. 4 billion records, I think that will hold me for a while.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    To be more accurate it is - 2147483648 to + 2147483648.
    I imagine if you had that many records that might even blow the .mdb size <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Now, if you want to be really really really accurate, it's -2147483648 to +2147483647. For 2147483648 = 2^31; in binary representation this is a 1 followed by 31 zeros. But in 32 bit integers, the first bit is the sign bit, so from a 32-bit viewpoint, it is negative...

    You'd be in trouble long before you had 4 billion records, for tables, as well as the database as a whole, have a maximum size of 2 billion bytes.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Close but no cigar, the top order bit is the sign bit, therefore the number of bits for the number is 31 hence a number of 2^31 - 1 = 2,147,483,647.

    This then means the range is -2,147,483,647 to +2,147,483,647. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

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

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Now, Pat, I don't want to sound arrogant, but in this subject I know what I'm talking about.

    Try this little routine:

    Sub Test32Bit()
    Dim n As Long
    On Error GoTo HandleErr
    ' This is OK
    n = 2 ^ 31 - 1
    MsgBox n
    ' This is OK too
    n = -2 ^ 31
    MsgBox n
    ' This will cause an error message
    n = 2 ^ 31
    MsgBox n
    Exit Sub
    HandleErr:
    MsgBox Err.Description
    End Sub

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    ... You all have way to much spare time on your hands. .... and you know way too much. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

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

    Re: Using Autonumber for too long (Access 2k2, VB6)

    That's true - based on the fact that an autonumber takes 4 bytes, and ignoring the fact that tables and Access itself takes some overhead, you would run out of gas at about 500 million records. Of course if you deleted a million every so often you could eventually run out, but then there's the issue of how fast all those records could be added and deleted. And of course, it would make sense to have some other sort of data in the table beside the autonumber field, so . . . . this could go on forever!
    Wendell

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Arrogance has nothing to do with this, however, you are right.

    I'm a bit puzzled, I give an example in hex and decimal:
    eg.1. 8000 0000 = 2,147,483,648 = 2^31
    eg.2. 7FFF FFFF = 2,147,483,647 = 2^31 - 1
    eg.3. FFFF FFFF = -2,147,483,647 = -2^31 - 1

    This tells me that the top bit is not exclusively the sign bit, as example 1 shows, this would be -0 if the top bit were the sign bit. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I think Gary is right though.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Using Autonumber for too long (Access 2k2, VB6)

    Egads - I hadn't thought about 2's complement since a grad level EE logic class in 1968! I just let the PC do the hard stuff.
    Wendell

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using Autonumber for too long (Access 2k2, VB6)

    That's life, but school's not quite out.

    Hats off to you Hans <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> , I was <img src=/S/confused.gif border=0 alt=confused width=15 height=20> by the lack of understanding of the taking away the number from 2^32.

    Thanks for the lesson, it's amazing how much I have forgotten about this from school days.

    Pat <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Page 1 of 2 12 LastLast

Posting Permissions

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