Autonumber not working (A2K SR-1)

Topic

New Reply

I have a table with an an autonumber as the ID in a local database. Recently when I tried to enter new data by manually opening it, I discovered that the autonumber isn’t working as expected – instead of a new number after the existing highest value, the new record had an ID which was already given to an existing record, resulting in my new record not accepted by Access. I tried to solve the problem by using Access’s Compact and repair database but without success. After compacting and repairing, the new record that I tried to enter has a different autonumber ID but which was still used by another existing record.

Does it mean that my table is corrupted? How should I do so that the autonumber is working correctly again?

How can I detect such problem (collision of autonumber) using codes?

Reply | Quote

Replies

Almost certainly you have a corrupt table, though it’s surprising that the compact and repair didn’t fix it. There was a problem in the intitial release of 2000 that caused this kind of problem, but it was fixed in SR1, and we haven’t seen it for two or three years. In order to fix the problem, you will probably need to create a new table with empty records, and then run update queries to take the values from the existing table and populate the new table, and then delete those records where there isn’t a matching record in the old table. This assumes you were using an autoincrement and not random numbers. If they were random, the Jet engine would take care of duplicates and simply assign a new number when it hit an existing record match. Hope this makes sense and is helpful.

Reply | Quote

After visiting Preston’s helpful link, I guessed the table corruption might be due to the following cause:

One of the desktops that was used in entering the data has MS Jet version 4.00.2927.17 (SP3) and mine is 4.00.6218.0 (SP6). The same back-end database had been processed by these two different builds of MS Jet. These different versions of the Jet engine might have caused the corruption.

Following your advice, I created a new table and copied the data from the corrupted table to the new one. The new table plus the autonumber primary key field are working fine now. There is no more duplicates of the key autonumber field.

Thanks a lot, Wendell.

Reply | Quote

Take a look at : http://support.microsoft.com/default.aspx?…b;en-us;Q257408%5B/url%5D

Reply | Quote

I followed Preston’s pointer and visited the Microsoft’s web site and its related links. I became a little disenchanted with the whole thing:
(1) I need to know the OS version of the system that is going to use my MS Access’s program. Windows 95/98/Me/NT/2000/XP each has a different update for the same version number of the Jet engine. One that is working perfectly in Windows 98 may not work well in Windows Me, for instance, because the updated Jet engine is different.

(2) I need to ensure that the computer that is going to use my program have the same updated version of the Jet engine. To help update the targeted computer, I have to know its OS plus its updates, and the Jet engine plus its updates.

These two considerations are really a pain in the ass if I want to make certain what works fine in mine work fine in the other computers too, though they all have MS Access 2000.

Thanks Preston for giving me the valuable link.

Reply | Quote

The reason for the different versions is because Win2k and later use the Windows installer, while earlier versions of Windows didn’t.

Reply | Quote