Results 1 to 7 of 7

Thread: Autonumber nightmare

  1. #1

    Thread Starter
    Frenzied Member Mark Sreeves's Avatar
    Join Date
    Nov 1999
    Location
    UK
    Posts
    1,845

    Post

    I always thought that an Autonumber field in an access table always incremented and was not possible to reset it (not easily anyway).

    I expect you can guess what I'm going to say now...


    THE F****** AUTONUMBER KEEPS RESETTING ITSELF!

    Of course this only happens on the customer's PC and always works fine on our development PCs

    VB5 front end
    Access 97 database

    Any ideas why?


    ------------------
    Mark Sreeves
    Analyst Programmer

    Mark.Sreeves@Softlab.co.uk
    A BMW Group Company

  2. #2
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Post

    What do you mean by resetting itself? Is it doing something like this:
    After lets say it adds number 100, the next number it adds is less then 100? Or do you mean something else?

    ------------------

    Serge

    Software Developer
    Serge_Dymkov@vertexinc.com
    Access8484@aol.com
    ICQ#: 51055819


  3. #3
    Hyperactive Member
    Join Date
    Nov 1999
    Location
    Galway, Ireland
    Posts
    316

    Post

    Are you using SQL commands to update the table
    if so this can upset the autonumber!!!

    Make sure you do not specify the autonumber if you are.......

  4. #4

    Thread Starter
    Frenzied Member Mark Sreeves's Avatar
    Join Date
    Nov 1999
    Location
    UK
    Posts
    1,845

    Post

    Serge

    By resetting I mean going back to zero

    and

    john_murphy
    I was using SQL to clear all data with the autonumber field as the criteria.

    I clear the table as above and then, when I add an new record (using .addnew) the autonumber field may or may not be 1

    It was a very urgent problem and I've done a work around by setting the field to 'number' incremanting an external (ie. in the vb)counter.

    It would still be interesting to know why it happened though.

    Perhaps I should have set it as the primary key...



    ------------------
    Mark Sreeves
    Analyst Programmer

    Mark.Sreeves@Softlab.co.uk
    A BMW Group Company

  5. #5
    Guru Clunietp's Avatar
    Join Date
    Oct 1999
    Location
    USA
    Posts
    1,844

    Post

    I agree -- set your autonumber field as a primary key, also set your IDENTITY SEED = 1 and IDENTITY INCREMENT = 1, also check the IDENTITY column.

    This is all assuming you're running SQL server.....

    Tom

  6. #6
    Guest

    Post

    are you compacting the database?

    taken from access help files
    if so this may apply:

    If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

  7. #7

    Thread Starter
    Frenzied Member Mark Sreeves's Avatar
    Join Date
    Nov 1999
    Location
    UK
    Posts
    1,845

    Post

    Thanks you all for you suggestions!

    larryn's help file extract seems to explain the behavior because I do indeed compact the database with the table being empty!



    ------------------
    Mark Sreeves
    Analyst Programmer

    Mark.Sreeves@Softlab.co.uk
    A BMW Group Company

Posting Permissions

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



Click Here to Expand Forum to Full Width