Microsoft Access Reusing Record ID's

0
votes

I have a (Microsoft access office 365 database). I have a number of records in a table in the database. Lets say I have 5 records numbered 1 to 5 respectfully. now when I delete record number 3. Record number 3 is now gone. The problem I am having is when I create a new record I see that record number 3 is being used again as a record number despite the fact that record number 3 has been deleted. This to me should not be happening. I recently upgraded the Microsoft Access Database Engine redistributable 2016 on our server from 2010. Thinking this might resolve the problem. The data type and primary key for the archiveID field is AutoNumber.

Please advise.

1
ms-access
Of course that shouldn't happen! Any data-macros? – ComputerVersteher
The data type and primary key for the archiveID field is AutoNumber. It it's really autonumber, that should not happen in any case, as far as I know. Are you sure it is Autonumber? Also, as @ComputerVersteher said, is there any macro or VBA code or something that might be interfering with that? – Foxfire And Burns And Burns
@FoxfireAndBurnsAndBurns assuming the OP operates on table level no macro or vba is executed. Only a database trigger (data-macro) can interfer there. – ComputerVersteher

1 Answers

0
votes

Some times AutoNumber can be damaged, for example by manual assignment. Try to repair it:

INSERT INTO [TableName]([archiveID]) SELECT MAX([archiveID]) FROM [TableName];

And check if problem repeats.