Home Ask Login Register

Developers Planet

Your answer is one click away!

rosa February 2016

Can Autoincrement field ever use the same value twice?

I have a table1 with an id field, type AutoIncrement. I need to copy the entire record from table1 into table2 if there is no record with the same id in table2. Then I delete the record from table1.

I need to know that if table1 gets new records, the id field will never be a number that was ever used before. Does this happen automatically, or do I need to do something to ensure this?

I tried deleting some records and adding new ones, and it really didn't use the same id, but I'm not sure that this is what always happens.


Sixthsense February 2016

Auto increment never uses the same # even though it's deleted from the table.

It requires complete reset so that it will start from the base and create new #.

Cisco February 2016

it is really so, Auto-increment fields in MS Access are always incremental, even if records are deleted, database compacted, etc. The proposed number can be reset deleting the auto-increment field, perform the copy of the table and then adding the auto-increment field again.

Sergey S. February 2016

It is possible to duplicate numbers in autoincremet field quite easy, but normally applications don't work this way.

Access remembers last inserted value in autoincrement field and uses it for calculating next value. You cannot insert particular value into autoincrement field using table designer or recordset in VBA, but it's possible if you use INSERT SQL statement. So, if autoincrement field has no unique index, you can insert any value. Also if you insert value less than maximum existing number, Access will generate duplicates automatically.

So I would not recommend rely on unique autoincrement numbers without unique index.

INSERT SQL can be used for resetting numeration without dropping field/table, just run query like this in query builder or using VBA:

INSERT INTO Table1 ( id ) SELECT 1;

This is table with autoincrement field ID I just created:

enter image description here

Post Status

Asked in February 2016
Viewed 3,054 times
Voted 7
Answered 3 times


Leave an answer

Quote of the day: live life