AutoNumber duplicate error

[JTRockville]

I'm having a very weird problem with many of my tables, which have an AutoNumber field. When I add a record to the table, it generates an value for the AutoNumber field that already exists. I never really paid much attention to this field, until I started to get a message saying I couldn't add a record because it would create a duplicate record. Has anyone else ever encountered this?

 

[Lightning]

I've seen this with a user-defined ID field, but not with an Autonumber field.&nbsp;&nbsp;I found that compacting the database resolved the problem.&nbsp;&nbsp;Maybe this will help with yours as well.<br><br>Lightning

 

[JTRockville]

Thanks Lightning. Compacting used to work, but now the only way around it is to try to append a record with the highest autonumber.<br><br>INSERT INTO MyTable ( MyAutoNumberId )<br>SELECT TOP 1 [MyTable].[MyAutoNumberId] AS ID<br>FROM MyTable<br>ORDER BY [MyTable].[MyAutoNumberId] DESC;<br><br>Of course, no record is appended, because a record already exists. But for awhile after, the autonumber functions properly. The problem is, I have about 20 users and 300 tables, so it's starting to become a full time job just to keep these functioning properly.<br>

 

[elizabeth]

This is just a shot in the dark, but I wonder if it could have anything to do with concurrent users and record locking.

 

[JTRockville]

Thanks elizabeth. What you said sparked something in the back of my mind. I decided to review their procedures. It turns out the windows &quot;copy&quot; function was used to back up the database. Since the database is now over a quarter gig in size, it takes several minutes to copy it, during which time some weird things happen. I have a table with an ID (autonumber) and a NAME (text) field. When you open the table, the records are sorted ascending by name. When you try to add a record WHILE the .mdb is being copied, the autonumber increments to 1+ the last record displayed, rather than 1+ the highest ID! Ouch.<br><br>Now that the cause has been determined, we've come up with a different backup procedure, and things are smooth.<br>

 

[MichaelRed]

JT,<br><br>You may have solved the ID numering for the moment.&nbsp;&nbsp;In a multiuser environment, the recommended way to keep from having this problem is to create a seperate table to make your own ID value.&nbsp;&nbsp;This table needs to be locked for read/write by other users while being accessed to get the next number.&nbsp;&nbsp;Of course, the routine which gets the number needs to very short.&nbsp;&nbsp;Get the current value, increments it by one, edit the field, update the table, EXIT, Release the reference to the table.&nbsp;&nbsp;I use a SEPERATE MDB (database) for this, as it more clearly isolates the ID function.<br><br>An Alternative to this is to use a GUID instead of the &quot;Autonumber&quot;, however this also requires a datastame in theerecord to retrieve the records in any semblance ot the &quot;order of entry&quot;.&nbsp;&nbsp;The, having a date time stamp in a recor is often useful all by itself.<br><br>MichaelRed<br>There is never time to do it rigfht butr there is always time to do it over

 

[elizabeth]

MichaelRed, can you give us a link to an example of this recommended method? Thx

 

[MichaelRed]

Jt, Elizabeth, et al,<br><br>I don't have a reference, as I learned it the HARD WAY, I could e-mail iterested indididuals some of the code, but you would would need to look at my implementation and adpt it to your own use.&nbsp;&nbsp;I have (almost) always used this in a situation where only a single table needed the AutoNumber, and then used this table!AutoNumber as the Key for other tables.&nbsp;&nbsp;If you have multiple tables which need autonumbers, you will need to make the adaptations.<br><br>If You want to persue this, send me an e-mail @ <A HREF="mailto:mred@duvallgroup.com">mred@duvallgroup.com</A>, and I will extract as much of hte relevsnt code as I can into a single (Zip) package for you.<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over.

 

[elizabeth]

MichaelRed, I think you could say this falls into the category of a FAQ. If you have the time to enter this on the forum's FAQ page (see tab at top of page), it would be helpful to a number of people in the future.

 

[MichaelRed]

Jt, Elizabeth, et al<br><br>I did what Elizabeth suggested.<br><br>It is &quot;<u>Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)</u>&quot; In Ms Access Tables and Relationships<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over

 

[elizabeth]

Thank you. I took a qick look at it, but I don't understand why I have used AutoNumberIDs for many production databases with many users, on a server, and have never, ever run into this problem. Is there some more specific record-adding volume or other criteria that makes this a problem? I thought Access did put a lock on a table when the first user wrote to it...???

 

[MichaelRed]

Elizabeth,

This is beyond what I have actually proven, but my hazy understanding is that MS Access does lock the file, however it releases the lock as soon as the value has been passed back to the Users system, and it does not 'reserve' the value in any way. On some systems, the system doesn't actually post the new record with the autonumber value before the next user also attempts to add a new record, gets their autonumber (the same one!) and then posts the record. Depending on how the system works, the second record to arrive for posting will get the duplicate autonumber. My first experience with this was on a WAN, with some systems VERY distant from the server and others in immediate proximity. Later, on a LAN system, I had a similar problem, and implemented the (posted) soloution, which fixed the thing. It was while I was implementing the LAN system soloution that the Owners of that system requested the addition of the date info in the autonumber, and agreed to the &quot;limit&quot; of 10000 records per month (actually it is 99,999 records). Their app was a &quot;call center&quot; for installation technicians, which averaged approx 100 calls per day, but had peak rates of about 80 calls per hour (usually Monday AM).

 

[LucyB]

I had this problem too (in a database where the front end refered to tables in 2 back-end databases), but while just ONE user was using the database. Compacting the back-end databases seems to have solved the problem, at least for the mean time, so thanks for that suggestion

 

[slim22]

I have a problem where the autonumber jumped from about 20,000 to 39,000. The table is tblTrans. It is the transaction file and shows dues paid (MEMB), conference registration fees (CONF), contributions (CONTRI) and sales of books and other items (SALE). I did a query where the transaction ID (TRAN-ID) was less than 10,000, performed a make table, then added a new tran. The autonumber increased by 1 from 9999 to 10,000. I then went to 15,000 and autonumber incremented by 1 from 14999 to 15000. Same thing at 19,000. At 19,924 the problem recurred. I added a transaction and the autonumber jumped to 39,686. I then reentered about 80 records manully. Now, I believe the autonumber may have jumped again. I can live with the gap in transaction numbers but I would like to know what is happening. Slim22.