Strange 'Stale Data' issue in Access 97 app

[Antilles]

We have a weird issue in a legacy Access 97 database. A previously working piece of functionality has suddenly broke. We have 2 databases which share a common ID number, an identical routine in each database connects to a shared database, retrieves the next ID number in the sequence, then increments by 1 and saves it back to the shared database. Similar to an identify field but shared across 2 systems.

We have a user who keeps getting stale data (i.e. IDs that have already been used). For example in normal operation, Users 1 and 2 may be getting IDs as follows:

User 1 - ID 1. Add 1 and update to 2
User 1 - ID 2. Add 1 and update to 3
User 1 - ID 3. Add 1 and update to 4
User 2 - ID 4. Add 1 and update to 5
User 1 - ID 5. Add 1 and update to 6
User 1 - ID 6. Add 1 and update to 7
User 2 - ID 7. Add 1 and update to 8

Then User 3 comes along and will get ID 4 (i.e. an out of date record), instead of ID 8. It only seems to happen on 1 PC. The ID generation routine uses pessimistic locking so that it not the issue.

It's like the affected PC is stuck in a timewarp! Has anyone come across a similar issue?

 

[gemma-the-husky]

at what stage(s) do you read/write the next number?

 

[Antilles]

OK, we solved it. Turned out we had a corrupted table in the shared database which seemed to be causing some strange locking whereby users would get copies of old records in other tables being read in that database.

Recreating the corrupted table and repairing the database fixed the issue. Very strange!