Autonumber Sequence problem..

[djtech2k]

I have an Access db that is doing wierd things in a table. I have a primary key that is an autonumber. I have been inserting data via queries all day. Along the way, I had some statements that locked it up, so i had to kill access and strat over. When i just did my last query, i noticed that my primary key field jumped from like 700 to over 1 million and then later jumped to like 4 million. I NEVER committed any changes to the db when I was running the queries. The total amount of data looks correct, its just the increment is WAY off.

How can I re-sequence it? I have tried compact and repair, but it doesnt work. Any help appreciated!!

-DJ

 

[PHV]

An AutoNumber field should NEVER have any other meaning other than to be unique.

 

[RobSindle]

I agree with PHV, but I do use the autonumber as a means of only determining order of input, no real meaning. In which case, it doesn't matter that there are big gaps.
However, to answer to your question.
It looks as if you ran some insert queries that failed and were "rolled back." When this happens the autonumber fields get 'calculated' anyway and the starting autonumber continues to grow.
You used to be able to get back to the last number inserted (plus 1) by doing a compact, but that no longer happens.
You can always brute force it by creating an empty, identical table, and inserting all records (and all fields OTHER THAN THE Autonumber field) ordered by the autonumber.
That will resequence.
Also, I have code somewhere that will reset the autonumber to the last used number (plus 1), but that doesn't help with the gaps.
Good luck.
Rob

 

[djtech2k]

I know it is only required to be unique, but the numbers jumped by millions when they were only to increment by 1. There are only 1200 rows in the table. There is no reason the number should go from 702 to over 1 million, then to over 4 million. Can I re-sequence the numbers?

 

[RobSindle]

From my earlier:

You can always brute force it by creating an empty, identical table, and inserting all records (and all fields OTHER THAN THE Autonumber field) ordered by the autonumber.
That will resequence.
Rob

 

[djtech2k]

Thanks. Thats what I figured I would have to do. So, I did that. I did not want to have to re-establish all of my relationships, but I think I got them all. Breaking relationships in Access scares me. Wierd things sometimes happen after the fact.

Thanks,

DJ