Autonumber less than previously used value (XP SP2)

Topic

New Reply

I have a database that has started exhibiting abnormal behaviour with Access XP. Autonumber fields are returning values that have already been used, causing key violations for the user. The database is a FE BE with FE’s installed on XP Pro workstations. There are no more than 4 concurrent users of the system and the BE size is only 2.5Mb.

Obviously I have repaired the database and reset the Autonumber field by appending a new max value. The problem is that other tables has exhibited the same behaviour and there is no guarentee that the problem will not reoccur. I’m looking for reasons and a fix.

I’ll replace the autonumber field with a long and write a routine to handle the incrementing if I have to but I’d prefer to understand the root cause and correct it if I can.

I’ve searched for a solution or information on this behaviour but have had little luck.

Has anyone else seen this sort of thing happening and know of a solution or be able to point me in the direction of more information?

Thanks

Stewart

Reply | Quote

Replies

According to Microsoft, this was a problem with early versions of Jet 4.0. It should be corrected in later versions – see AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database. Unfortunately, these later versions have another side-effect: an AutoNumber field will not always be reset to start at the first available number after a compact and repair, but that is less problematic.

Reply | Quote

Thanks Hans as always you are quick to respond. I dont know how you manage to get any work done though.

I’ll read the article and ponder my option.

Thanks again.

Stewart

Reply | Quote

It is possible programatically to inadvertently add a duplicate number in an autonumber field through errant code and other similar situations. I would suggest you take a long hard look at that kind of possibility – we run many databases with autonumber fields and have ever only seen one situation – a know bug in early versions of 2000 – where we actually got duplicates. All the other cases we’ve seen were situations where the database had corrupted, or ones where an errant form or code was doing things it shouldn’t.

Reply | Quote

If I can scale down a sample db with form that errors intermitly, would anybody be interested in checking for misbehaving code?

John

Reply | Quote

Go ahead, you know the rules (zip file < 100 KB). If the problem is intermittent, we can't guarantee that we'll see it or find a solution.

Reply | Quote

Go ahead, you know the rules (zip file < 100 KB). If the problem is intermittent, we can't guarantee that we'll see it or find a solution.

Reply | Quote

If I can scale down a sample db with form that errors intermitly, would anybody be interested in checking for misbehaving code?

John

Reply | Quote

It is possible programatically to inadvertently add a duplicate number in an autonumber field through errant code and other similar situations. I would suggest you take a long hard look at that kind of possibility – we run many databases with autonumber fields and have ever only seen one situation – a know bug in early versions of 2000 – where we actually got duplicates. All the other cases we’ve seen were situations where the database had corrupted, or ones where an errant form or code was doing things it shouldn’t.

Reply | Quote

I have to agree with Wendell. I encountered a problem in Access 2000 with “duplicate” autonumbers popping up after records were appended to a table and the autonumbers were also appended. This was NOT fixed by compact and repair, which did not reset the seed. Office 2k SR-1 and Jet SP4 addressed those issues. Later Jet service packs reintroduced the problem of the seed not being reset by compact, but this has apparently been addressed in SP8. I have never seen a duplicate come up otherwise except when it was introduced by either the program or the way the application was distributed to various machines.

Reply | Quote

If I understand you correctly there are two issues here:

1. Errant code as suggested by Wendell could cause my duplicate autonumber proplem

2. If I do get the autonumber problem Detect & Repair & SP8 will reset the autonumber seed to highest, which would in effect allow my operator tp proceed appending records as described in Post 319900

Thanks, John

Reply | Quote

I don’t think it is errant code or the Compact/Repair. I think the problem is with append queries (we hit it while doing append queries to import data), and unfortunately the Compact/Repair won’t fix it. If you want to follow the link below to my website, I’ve got a couple of solutions (DAO and ADO) to help repair the problem in a table once you’ve determined you have one.

Reply | Quote

Hi Mark

Thanks for the code.

Does this mean when my operator gets “Duplicate Values In The Index Message” she can exit the system. run your db and code to reset the autonumber?

Does your code return any feedback that there was a problem with the autonumber and the autonumber was reset to the highest value?

John

Reply | Quote

I don’t think it returns any values, but you can add it yourself. The ADO solution requires that the target database (or at least the target table) not have anyone else working in it at the same time. The DAO solution does not have that same restriction, although it probably is a good idea not to have anyone else in it at the same time.

Reply | Quote

Thanks Mark

In Charlottes post 321689 above does Jet SP8 Detect and Repair also sovle this problem?

John

(Edited by HansV to make post reference clickable)

Reply | Quote

I’m not sure what you’re asking. Detect and Repair is an item on the Help menu of MS apps. Compact and Repair is a Jet engine process. Detect and repair is for fixing an installation. Compact and repair is for compacting a database.

Reply | Quote

Sorry

Should have read:

In Charlottes post 321689 above does Jet SP8 Compact and Repair database also sovle this autonumber seed problem?

John

Reply | Quote

Have I missed something here? Both Charlotte and Mark indicated that the reset of the seed so that it duplicated an existing record was fixed in SP4 of Jet. But in doing that they introduced the problem of a Compact and Repair not always setting the seed to the next higher number, and I can’t find any information that says SP8 fixed that problem, though Charlotte hinted that it might be. So what is the question?

Reply | Quote

As far as I know, Jet SP 4 fixed the seed resetting on compact. The SPs *after* SP4 broke it again. I do not think it has been fixed in SP8, although I haven’t tested it, since the environment where I have applied it doesn’t use autonumbers.

Reply | Quote

As far as I know, Jet SP 4 fixed the seed resetting on compact. The SPs *after* SP4 broke it again. I do not think it has been fixed in SP8, although I haven’t tested it, since the environment where I have applied it doesn’t use autonumbers.

Reply | Quote

Have I missed something here? Both Charlotte and Mark indicated that the reset of the seed so that it duplicated an existing record was fixed in SP4 of Jet. But in doing that they introduced the problem of a Compact and Repair not always setting the seed to the next higher number, and I can’t find any information that says SP8 fixed that problem, though Charlotte hinted that it might be. So what is the question?

Reply | Quote

Sorry

Should have read:

In Charlottes post 321689 above does Jet SP8 Compact and Repair database also sovle this autonumber seed problem?

John

Reply | Quote

I’m not sure what you’re asking. Detect and Repair is an item on the Help menu of MS apps. Compact and Repair is a Jet engine process. Detect and repair is for fixing an installation. Compact and repair is for compacting a database.

Reply | Quote

I don’t think Microsoft has solved the problem yet. But I could be wrong. I’d have to see what latest patches are.

Reply | Quote

I don’t think Microsoft has solved the problem yet. But I could be wrong. I’d have to see what latest patches are.

Reply | Quote

Thanks Mark

In Charlottes post 321689 above does Jet SP8 Detect and Repair also sovle this problem?

John

(Edited by HansV to make post reference clickable)

Reply | Quote

I don’t think it returns any values, but you can add it yourself. The ADO solution requires that the target database (or at least the target table) not have anyone else working in it at the same time. The DAO solution does not have that same restriction, although it probably is a good idea not to have anyone else in it at the same time.

Reply | Quote

Hi Mark

Thanks for the code.

Does this mean when my operator gets “Duplicate Values In The Index Message” she can exit the system. run your db and code to reset the autonumber?

Does your code return any feedback that there was a problem with the autonumber and the autonumber was reset to the highest value?

John

Reply | Quote

If I understand you correctly there are two issues here:

1. Errant code as suggested by Wendell could cause my duplicate autonumber proplem

2. If I do get the autonumber problem Detect & Repair & SP8 will reset the autonumber seed to highest, which would in effect allow my operator tp proceed appending records as described in Post 319900

Thanks, John

Reply | Quote

I have to agree with Wendell. I encountered a problem in Access 2000 with “duplicate” autonumbers popping up after records were appended to a table and the autonumbers were also appended. This was NOT fixed by compact and repair, which did not reset the seed. Office 2k SR-1 and Jet SP4 addressed those issues. Later Jet service packs reintroduced the problem of the seed not being reset by compact, but this has apparently been addressed in SP8. I have never seen a duplicate come up otherwise except when it was introduced by either the program or the way the application was distributed to various machines.

Reply | Quote

Thanks Hans as always you are quick to respond. I dont know how you manage to get any work done though.

I’ll read the article and ponder my option.

Thanks again.

Stewart

Reply | Quote

Hi Stewart

I have been chasing this same problem for 12 months now.

Customer was using A2k (9.0.4402) SR-1 Jet 4.0 SP-4 when problem started, custonmer is now using a2k (9.0.6926) SP-3 Jet 4.0 SP-7 with sam problem

Using FE and BE DB

I tried decpmpile and importing all objectes into a new DB with same problem

It happens intermitly and not necessarily after a Compact and Repair

This problem happens on customers computer with same table and same form only. It

Reply | Quote

Hi John,

I’m glad I’m not alone. Did you see the reply from Hans?

You said How do you do this?

Simply by using an append query to add the new value to the autonumber field, sequential numbers then start from the new seed.

Thanks for taking the time to reply.

Stewart

Reply | Quote

Hi John,

I’m glad I’m not alone. Did you see the reply from Hans?

You said How do you do this?

Simply by using an append query to add the new value to the autonumber field, sequential numbers then start from the new seed.

Thanks for taking the time to reply.

Stewart

Reply | Quote

Hi Stewart

I have been chasing this same problem for 12 months now.

Customer was using A2k (9.0.4402) SR-1 Jet 4.0 SP-4 when problem started, custonmer is now using a2k (9.0.6926) SP-3 Jet 4.0 SP-7 with sam problem

Using FE and BE DB

I tried decpmpile and importing all objectes into a new DB with same problem

It happens intermitly and not necessarily after a Compact and Repair

This problem happens on customers computer with same table and same form only. It

Reply | Quote