Someone that was doing data entry, in a form, deleted a record from the table. Now the table has a gap between numbers, it goes from record 18 to 20 and skips 19. Will compacting the database fix things? If not then how can I get the numbers to NOT skip any records. By showing number 19 will it shift the data stored under number 20 to record 19?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Autonumber problem - resetting after a record deletion
- Thread starter RWB42
- Start date
As far as I am aware, there is nothing at all you can do about this. The autonumber data type is there to assist in maintaining a unique index. It can't keep a reference of deleted numbers just in case you want to use them again. The only time a compact and repair works is if you have been using test data, then delete it and compact and repair the database. This will reset your autonumbers to 1.
Just out of curiosity though, why on earth does it matter if 19 is missing?
I'm not quite sure what you mean about it skipping records? My only advice would be, if you don't want to upset your sequential numbering, lock your application down so that users can't delete data.
Just out of curiosity though, why on earth does it matter if 19 is missing?
I'm not quite sure what you mean about it skipping records? My only advice would be, if you don't want to upset your sequential numbering, lock your application down so that users can't delete data.
- Thread starter
- #3
To MissTipps,
It is necessary to keep track of how many records exist. I did set up different security levels but I was informed that some changes needed to be made to old records. I had no idea that someone would delete a record nor did I realize that it was impossible to reorder records. I see that compacting works if the LAST record is deleted but records that are located anywhere but in the last position cannot be renumbered. This is a problem for me and I imagine that it is a problem for others as well. Thanks for responding.
It is necessary to keep track of how many records exist. I did set up different security levels but I was informed that some changes needed to be made to old records. I had no idea that someone would delete a record nor did I realize that it was impossible to reorder records. I see that compacting works if the LAST record is deleted but records that are located anywhere but in the last position cannot be renumbered. This is a problem for me and I imagine that it is a problem for others as well. Thanks for responding.
RWB42,
Autonumber is designed to identify records, not for any other purpose. To do what you want cannot be done without compromising your app's data integrity. Recordcounts can be done using DCount, Recordset.RecordCounts, Count in SQL, etc.....
Craig
Autonumber is designed to identify records, not for any other purpose. To do what you want cannot be done without compromising your app's data integrity. Recordcounts can be done using DCount, Recordset.RecordCounts, Count in SQL, etc.....
Craig
- Status
Similar threads
- Locked
- Question
- Locked
- Question