[Ahmedn1]

When I delete record in a db the ID(autonumber) doesn't change automatically

How can I do that

My delete code is

rs.Open "select * from Emp where EID=" & x & "", db, adOpenDynamic, adLockOptimistic

rs.Delete
rs.Update

rs.Close

As example
if I have IDs : (1,2,3,4,5,6)
when I delete record whose ID = 2
it should look like (1,2,3,4,5)
but what happens is (1,3,4,5,6)

This post has been edited by Ahmedn1: 08 March 2010 - 10:58 AM

[raziel_]

the ID will never change it is ID witch mean that it will always continue from the last one even if you delete it. that is why the ID stand for. it is unique. this rly is a Data base question post it in this forum for more DB questions:
Databases

good luck

This post has been edited by NoBrain: 08 March 2010 - 12:54 PM

[vb5prgrmr]

If it is the last record deleted, you can use a compact/repair on it and access will reset the autonumber so that when you add a new recored there is not a gap in the numbers. Otherwise it is like NoBrain said... However! If you delete a middle record (as in your example with ids from 1 to 6 and you delete #3) you can get around this and reset the autonumber so that your records have a continous id (1 to 5) if you so wish. BUT! If this ID is used in a primary key foreign key relationship, then you will break all referential integrity rules and ruin you database.

So how do you do this you ask? Well you create a temp table with the same structure and select into that table with the information from the other table less the ID field. Then you can do one of two things. Delete/Drop the old table and then do an update table and change the table name to the name of the old table, or delete the contents of the table, do a compact/repair, move the records back, and finally delete the temp table...



Good Luck

[Ahmedn1]

good point NoBrain
I didn't think of that
Yes it is logically not useful

and thanks for help vb5