Primary Key Autonumber

[John_G]

Hi June7 (et al)

I just tried that (Access 2010) , and encountered what I would call a nasty bug.

I created a little 2-field test table, with autonumber a key, and added a few records (7)

Then I did this in the immediate window:



currentdb.Execute "Delete from table1 where key = 2"

No problem.

Then : currentdb.Execute "Insert into table1 values (2,""again"")" to put the record back.

No apparent problem - the table looks OK.

Then : currentdb.Execute "Insert into table1 (field1) values (""new record"")", dbfailonerror

Should give new a new record with the next autonumber, right?
But I get error 3022 - duplicate value in index or primary key.

The re-insert of the record with key=2 reset the autonumber counter, so the next insert failed! (It actually tries to use key=3)
This behaviour can be seen too if I try to add a new record directly in table view - the error is obvious

Not good - I would call it a bug.

So, I would say you cannot specify an autonumber on insert, or you may encounter serious problems later.

John

[John_G]

Data -

If you are talking about using the random number feature to generate the key, I don't think you could trust that, either.

There is always the chance that after you deleted a record, the random number used as its key would come up again, and be used for another (new) record. An attempt to restore the first one would fail.

I think you will have to keep track of the key yourself, but it's not difficult. Each time you add a new record, make the key the current maximum plus one: New_Key = dmax("key", "yourtablename") + 1

That way deletions and re-insertions will cause no difficulties.
HTH

John

[June7]

Interesting test, JohnG. Does appear to be bug because when doing manual data entry to the table after delete and reinsert by VBA, the next ID is 8. Then after that record is committed, the code will pick up with 9.

[data808]

I found that the PK will start from the last number that was added to the table. So whatever PK was last entered will be what sequence it will resume. This goes for appending a record through queries as well. So as long as I append the record with the last PK to the table it will pick up from that number. I just wish there was an easier way I could control what PK I wanted to start with.

[John_G]

Remember how ans autonumber type is designed to work - the key part is auto. Let MS Access assign the number. If you explicity assign it yourself, it does not give an error, but if you ever have to delete and then re-insert (with VBA) a record from the middle of the sequence, chances are you will get an error at some point.

If as you said you want to control the PK values yourself, then don't use autonumber. Use Integer or Long, set the field to required, and put a unique index on it. You can use the DMax funtion to determine the current maximum value:

dMax("PK","Tablename")

John