Autonumber ID gone out of sync (A2K SR1)

Topic

New Reply

I have a database which contains a table with an autonumber ID, set as indexed (no duplicates). After adding and deleting a few data, I found that the autonumber has gone out of sync. For example, my sample database has 1302 records, and the next new number should have an ID of 1302. But it didn’t. It started with a new record with ID 787, which was already used by another recordd! Of course it was an error and my attempted new record wasn’t accepted. Then I exited and started again. This time the new record had an ID 788, which was again used and again caused an error.

To overcome the problem, I copied the problem table to another name, delete the old one and rename the new one back to the old table name. Then the automatic ID works as expected. A new record will start with a number immediately after the last one. The table works fine after this (so far!).

My question is – how does the autonumber go out of sync and what causes it? Is there a way or ways the program can detect / prevent the autonumber from going out of sync?

Reply | Quote

Replies

Are you saying that there was already a record in the same table with that autonumber? If so, I would be extremely surprised. However, if you deleted records and then compacted the database, the autonumber will reset itself to start at the next highest number. In that case, Access is behaving exactly as designed and the problem is in the way you’re using it. You’ll have to explain further if that isn’t what you meant.

Reply | Quote

Yes, I have 1302 records in the table of my sample database. The table has autonumber ID. It is a split file, called by a main database file. I didn’t compact/repair the sample database often during testing. (But I did run Compact/Repair database after I discovered the error, but the error persisted even after compact/repair.) When I said the new record was 787 instead of 1302, I meant the record pointer (?) was reset to 787 instead of the expected 1302 for new record. As for whether all my MS Office components were SR1, I access Help->About Microsoft windows for all my Office components, they show version 9.0.4402 SR-1. This is the only clue that I have to indicate my Office is of SR-1 version.

Initially I never expected the error to occur as I suspected something wrong with my codes (which ran correctly before the error). With the error, I couldn’t go to a new record. After hours of modifying my codes without success, then it only dawned on me to check the table in the split database. That was the moment of truth.

Reply | Quote

Sorry, but more questions:
Are you the only one using the database, or are other users also using it, and if so are they using it at the same time?
If there are multiple users, do they all have A2K SR1?
Is the back-end database residing on your local hard drive, or is it on a server somewhere?
Have you had any hardware crashes on your PC, the network, or on the server?
Do you have memo fields in the table that has the bad autonumber?

If the answer to all of those is negative, then I would still suspect a code error or SQL error somewhere that is updating the primary key in some fashion. The other even more remote possibility is that somehow the hardware failed to correctly update the pointer – a memory error perhaps – but that’s stretching things quite a ways. We support numerous databases doing the same sort of thing you are with narry a problem of that type.

Reply | Quote

Yes, I am the only one using the database so far. It hasn’t seen the light outside my desktop yet. Both the front- and back-end of the database are in the local computer. Essentially I worked in a non-networked environment during the codes and interface writing stage.
The records with bad autonumber do have memo fields – but what is the relationship between the error and memo fields?

As far as I can recall, the desktop I am using suffers no crashes during the developmental stage of my database. Unlikely the record pointer wasn’t updated by the computer because of crashes.

As you suggested, I will go back to my codes again. My back-end database has a main table related to another two tables, with the autonumber as the primary connecting key. In the codes, the three tables are related using SQL. This could be the cause of error since you mentioned it.

I really hope the error is in my codes else my confidence in Access would be dented.

Reply | Quote

On the issue of memo fields, Jet records are much more prone to being corrupted if memo fields are used. I think it’s because memo fields can span multiple pages, and that’s where computer crashes can bite you – in addition, the memo field is stored in a different page from the rest of the record, with just a pointer to the correct page in the main record page. But if you haven’t had computer crashes or lock-ups where you had to do the three-finger salute or hit the reset button, then memo fields aren’t a likely cause of trouble.

I would look at any SQL statements you are using to do data updates and make certain that the autonumber field is not being updated. In general Access will block that sort of thing, but at the code level you may be able to fool the Jet engine.

Reply | Quote

Are you certain you are running SR1 on all of your systems? There was a reported problem of this kind in the original release of A2K that was fixed in SR1. The only other thing that seems likely is that your database is going corrupt. Are you running a split database? Is the database multi-user? Are you doing regular compact/repairs?

Reply | Quote