modify the code that resets the autonumber field to the next number.
I have found that when I append records that may have been previously deleted or use record numbers that may have been skipped (for whatever the reason), Access resets the number to the next number after the imported record number (autonumber field). This creates a problem as there are records past this record number in the table. Would it be possible to always reset the autonumber field to the next highest value based on the highest key in the table after an append operation. To get around this, I append a dummy record with the highest key value to reset properly.
Pat Hartman commented
Is your Autonumber marked as the primary key? If it is not, that may be what is causing the problem.
Yes there is a couple of workarounds as described but taking databases offline whether Access, SQL server or anything else I would not consider "best practices". For those unfamiliar with this behaviour, it can be a bit of surprise.
Thanks for your comments.
Grover Park George commented
Appending "lost" records back into a table is not at all difficult, if you follow two rules.
To restore any missing AutoNumber, simply include that AutoNumber in an append query that also includes other fields needed to complete the record. In other words, if you know which AutoNumber value was deleted, you can easily put it back this way.
Then, to prevent Access from trying to append new records with previously issued Autonumbers, run a Compact & Repair on that database immediately after appending back the lost record(s).
Obviously, if you have a problem taking the database offline in an unscheduled situation, you'll need to do this during some previously established maintenance window, or in some sort of "emergency" procedure.
In any event, disrupting two decades of established behavior doesn't seem to rise to the level of "enhancement."
Hi Alphonse, Thanks but I tested in the backend db and it is a problem still in Access 2016 anyway. You can repair and compact but that is not a good work around when dealing with several users who are in the system.
Alphonse Giambrone commented
John, I've seen the issues you describe and it was supposedly fixed sever versions ago, although I've seen it come back. What I have found is that it only happens if you insert into a linked table. If you insert directly into the back end, there is no problem. Also, there is code around for resetting the autonumber without inserting an extra record. Just do a web search.
@ Dolly. There is no importance to the number. This issue I have is restoring records that have been deleted. When the records are appended back, the autonumber is put to a lower number than the highest existing record so when a user adds a new record, they can get errors because the new record number already exists. I can't take the backend database offline easily so I append another record with the highest record in the table to reset.
All I am saying it is not intuitive to have an autonumber value to be reset to a lower value than what is currently based on existing data.
I agree with Mark. Since PK have no meaning, the fact that they jump should have no importance.
Performing a C&R resets them to the greatest taken number, so you could always do that to get back into sequence.
You seem to have a specific scenario in which you are reliant on the values of autonumber which seems wrong. Why not create your own numeric field then you can populate it as you see fit with whatever sequence you wish.
Could you point me in the direction to where this behaviour is documented.
Mark Burns commented
Whiel having a finer means of controlling the next autonumber might be nice, what you are describing is long established and well documented behavior. IT SHOULD NOT BE CHANGED as doing so could break many existing applications which rely on the currently defined behavior.