Apologies if this has been covered before. I have had a quick search and couldn't find a thread with my exact problem.
The thing is I have a table with a primary key, as you should, but say I add 10 records which then have primary key values 1,2,3,..., 10 and then delete the records with keys 4,5,and 6, I am then left with a table with key values 1,2,3,7,8,9,10.
If I then do a compact and repair and start adding new records, via a form, via a query on the table. the first new record will get an ID value of 4 i.e. Access is reusing previously deleted ID values, then the next one 5, the next record, 6 and then when i try and add another record I get the RunTime error 3022 that I am trying to create a record with a duplicate Index blah blah blah as there is already a 7. Primary key is set to Long Integer, Increment, Indexed: (Yes) No Duplicates which is the standard by default.
Why is this happening ??? Why can't Access skip existing values and add new records at the next available number.
Or is there something in my query I need to change?
I am only developing the DB at the moment and am adding and deleting records to test my forms / queries / calcs. But worry if this will happen once the DB is rolled out.
So, to date it is only a accdb file as I am yet to split and save as accde.
Will this stop happening once the DB is split, accde'd ???
Thanks,
The thing is I have a table with a primary key, as you should, but say I add 10 records which then have primary key values 1,2,3,..., 10 and then delete the records with keys 4,5,and 6, I am then left with a table with key values 1,2,3,7,8,9,10.
If I then do a compact and repair and start adding new records, via a form, via a query on the table. the first new record will get an ID value of 4 i.e. Access is reusing previously deleted ID values, then the next one 5, the next record, 6 and then when i try and add another record I get the RunTime error 3022 that I am trying to create a record with a duplicate Index blah blah blah as there is already a 7. Primary key is set to Long Integer, Increment, Indexed: (Yes) No Duplicates which is the standard by default.
Why is this happening ??? Why can't Access skip existing values and add new records at the next available number.
Or is there something in my query I need to change?
I am only developing the DB at the moment and am adding and deleting records to test my forms / queries / calcs. But worry if this will happen once the DB is rolled out.
So, to date it is only a accdb file as I am yet to split and save as accde.
Will this stop happening once the DB is split, accde'd ???
Thanks,