Autonumber nightmare

[Mark Sreeves]

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

[Serge]

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 (http://www.icq.com/51055819)

[john_murphy]

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.......

[Mark Sreeves]

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

[Clunietp]

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

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.

[Mark Sreeves]

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