Primary Key autonumber problem?

[im99_chs via AccessMonster.com]

Hello to everyone!

I have a little problem and I would like your help.
I have a table with 2 fields (ID, salary_sum).
The ID is primary key and autonumber. It looks like
ID salary_sum
1 23
2 111
3 34
4 56.

When I am deleting the last record 4 56 the new record appeared
with
ID salary_sum
5 65
and no 4 65.

How can i fix this problem??

Thank you in advance.
Christos

 

[Allen Browne]

Christos, this is not a problem. The autonumber is a unique number that
never changes.

If it did change and you deleted #2, would you expect 3 to become 2 and 4 to
become 3 and so on? If it did that and you had a related table, all the
related records would now be related to the wrong number. Clearly, the
autonumber must stay consistent, and cannot change like that. Deleted
autonumbers are not reused, and even aborted entries (where you started
entering a new record and cancelled it) are not re-used.

If it is the last number that has been deleted, you can adjust that by
compacting the database:
Tools | Database Utilities | Compact.
If that does not work for you, you need to download the latest service pack
for JET 4 from:
http://support.microsoft.com/kb/239114

But the essential concept is to think of the autonumber as a unique
identifier that never changes rather than a sequence of numbers which it
never is for very long.

 

[Ken Sheridan]

Christos:

As Allen has said an autonumber column is intended to guarantee uniqueness
not sequence. If the sequence is important its better to generate your own
numbers. You'll find a demo of mine for doing this, with a means of
resetting the number at which the sequence will start when the next row is
inserted into the table at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps

Ken Sheridan
Stafford, England