Strange AutoNumber Key Behavior 2007-2010

[Khalid_Afridi]

Dear All,

We recently upgrade all the computers in our office from 32bit to 64bit configuration.

From - To
Windows XP - Windows 7
Office 2007 - Office 2010

Our back-end database is in 2007 format, while front end we upgraded to 2010 format in order to make it compatible with 64bit configuration. (some of API calls were giving Error Msgs with 64 bit.)

Now a strange behavior occurred, All the AutoNumbers IDs of our tables differs while creating new record i.e. its generate the old ids which were previously issued in 2007 database format:

For Example:
Already Existence IDs with 2007 format
5001
5002
5003
=======

Newly record IDs with 2010 format
4001
4002
4003
It gives duplicate error because these Autonumbers already issued.

A big trouble now.... we have to create new dummy Ids untill we reach 5003 ID.

Any Idea?
Thanks

 

[madEG]

Hmmm... if I understand you correctly, after moving the front end apps to Off2010 the autonumber seeding is off? It seems you may be able to fix it a few ways:

http://support.microsoft.com/kb/812718

http://allenbrowne.com/ser-40.html

I haven't run into this before, but that is what I came up with...

Good luck!

 

[Khalid_Afridi]

Thanks madEG!

but its not the matter of how to reset the AutoNumber Key as your links says, the problem is i don't want to change or reset any ID as they are used as a reference in other tables as a foreign keys. If i change or reset it that would be a big mess.

This problem is happened in another tables also their New IDs is not incremented with the next number, it just generates some previously created IDs and gives duplicate Errors.

Its really really strange.... the first time I face this problem using Ms-Access 2010, and this problem just started after I compact and repair the back end database.
The Index key got corrupted I believe.
Did anybody face this problem???

 

[Khalid_Afridi]

Hmmm... if I understand you correctly, after moving the front end apps to Off2010 the autonumber seeding is off? It seems you may be able to fix it a few ways:

http://support.microsoft.com/kb/812718

http://allenbrowne.com/ser-40.html

I haven't run into this before, but that is what I came up with...

Good luck!

This link http://allenbrowne.com/ser-40.html is really entrusting and the function which I read is near to solve the problem. I didn't try it yet.
Thanks!

 

[Khalid_Afridi]

I have an AutoNumber field in some table which is the primary key and refers to others table as foreign keys.

I compact and repair 2007 with 2010 format and I got this problems with all the tables.
the column's Seed property is below the maximum and it produce the previously issued Autonumbers while creating new record and gives Duplicate Errors.

 

[gemma-the-husky]

the general fix is to manually insert a record, to force the autonumber sequence to recommence from an acceptable number

isn't that what madeg suggested in his links?

 

[Khalid_Afridi]

the general fix is to manually insert a record, to force the autonumber sequence to recommence from an acceptable number

isn't that what madeg suggested in his links?

Yes Dave!

I did the manual practice to insert a record until I reached to the acceptable number with one of my table, but this is a long process to go for every table to fix it as some table are really big and we have lot of tables in our database.

I will try the madeg provided solution in the office on the effected database, it seems to be okay as I tried it on my laptop in home but I didn't find any problem with my database as all the AutoNmbers sequence are okay in my database in home.

Thanks to madEG and you

 

[Khalid_Afridi]

Hmmm... if I understand you correctly, after moving the front end apps to Off2010 the autonumber seeding is off? It seems you may be able to fix it a few ways:

http://support.microsoft.com/kb/812718

http://allenbrowne.com/ser-40.html

I haven't run into this before, but that is what I came up with...

Good luck!

Thanks madEG!

The function worked exactly what I was expecting. Its really great. It fixed the column's Seed's property to the current maximum number and a new record can be added now.

One problem is still there, if you re-compact and repaired the database with the Ms access-2010 format, its again disorders the autonumber seed to the lowest number.

I dont know why this happens. Is there any seeding option in Ms-Access 2010 database? I tried to sort the effected tables autonumber columns in ascending order, I run the function and then re-compact the database so it dose not change the seeding orders to the lowest number.

Thank you once again all my friendz, specially madEG, gemma-the-husky and boblarson.

 

[wiklendt]

have you tried looking for hot-fixes or searching the MS site?

 

[gemma-the-husky]

Yes Dave!

I did the manual practice to insert a record until I reached to the acceptable number with one of my table, but this is a long process to go for every table to fix it as some table are really big and we have lot of tables in our database.

I will try the madeg provided solution in the office on the effected database, it seems to be okay as I tried it on my laptop in home but I didn't find any problem with my database as all the AutoNmbers sequence are okay in my database in home.

Thanks to madEG and you

no - you dont have to keep adding records - you just do it once

you can do an insert query, and actually specify the value of the autonumber field - which (should) reseeds the autonumber for future insertions.

 

[wiklendt]

no - you dont have to keep adding records - you just do it once

you can do an insert query, and actually specify the value of the autonumber field - which (should) reseeds the autonumber for future insertions.

Khalid said in his next post he actually tried that as instructed, but after compact-and-repair, Access 2010 still re-set the ID numbering...

..weird.

 

[Khalid_Afridi]

Khalid said in his next post he actually tried that as instructed, but after compact-and-repair, Access 2010 still re-set the ID numbering...

..weird.

Yes Agnieszka!

I noticed something special while compacting the database with 2010 format.
The tables which were not sorted in ascending order by its autonumbers key in the datasheet, drops its seeding number to the last record autonumber in the datasheet.

i.e
autonumbers (not sorted order)
1001
1002
1005
1006
1004
1003

here we have 1003 is the last record which is not the actual last record. The actual last record is 1006, so when you compact and repair Ms-Access 2010 consider that 1003 is the last seed in this example.
Its re-seed it 1004 as the next seed number instead of 1007. Logically it should give 1007 as the new seed.

I don't know why this happens? I think I am missing something in Access 2010, its really weird. Now I am afraid to recompact and repair it again, coz I have around 200 users they are using the same database everyday.

This bug is really a weird one

Dave! (gemma-the-husky)
If you run the insert query, i will fail because of duplicate autonumber. OR may be Execute query could be run to reach the next real seed, but the function provided fixed the seeding problem, the real problem is that it re-order seeding after compact and repair.

I think there is something which i am missing. Should I re-structure all the tables using Access 2010? (a brand new database in Access 2010) that's a big work

 

[ChrisLayfield]

I was just creating a table with the default ID with autonumber in place and as I would tab across the table to add data to the first record, the autonumber would increase with each tab. I tried to delete the record and add a new record and it took the next number. Then I deleted the table and tried to create it again and the problem repeated everytime...anyone heard of this?

 

[ChrisLayfield]

After having the previously discussed issue numerous times. I created the table structure and without adding data, saves the table adn that seems to have solved the issue. I have no idea the cause of the autonumber wierdness, but at least I found a work around

 

[Khalid_Afridi]

After having the previously discussed issue numerous times. I created the table structure and without adding data, saves the table adn that seems to have solved the issue. I have no idea the cause of the autonumber wierdness, but at least I found a work around

Are you using Access 2010?
If you delete the record with autonumber column, every version of Access is giving the next Autonumber id to its new record.

i.e
current AutoNumber
1001
you add new record
1002
you delete 1002
....
you add new record
1003
now your Autonumber sequence
1001
1003
......
......

 

[ChrisLayfield]

I am using Access 2007.

The issue I was having was on table creation. I also see the issue of deleting a record and Access assigning the next number. I assumed that was because the record was was already assigned the unique ID and Access would not reassign. I have not tried to delete a record save, then readd. That may force the record to be recognized as being deleted and free the previously used number again.

 

[wiklendt]

I am using Access 2007.

The issue I was having was on table creation. I also see the issue of deleting a record and Access assigning the next number. I assumed that was because the record was was already assigned the unique ID and Access would not reassign. I have not tried to delete a record save, then readd. That may force the record to be recognized as being deleted and free the previously used number again.

access does not, and should ever not, on principle of referential integrity, reassign previously used numbers.

this is why Khalid was concerned in his first post, because HIS access WAS re-assigning previously used numbers.

imagine this scenario - you have an employee database with employeeIDs 1-8. then, after 5 or so years of gathering data on these employees (e.g., timesheets, leave, jobs, corresponace, what-have-you), you delete an employee who has left for better pastures (not recommended either, you should actually have a 'status' field, which you can set to "inactive" or "ex-employee" or something).

anyway, for the sake of this argument, let's say you delete employee (and therefore his ID) of ID 4.

now you have a brand new employee - she will be assigned a new number - do you want her number to reuse ID 4, and then have the headache of all of the previous employee's records automatically latch on to hers? or do you want hers to have a brand new, never before used, autonumber for her ID that will never confuse her details with a previous employee's?

i know which i'd prefer.

 

[Khalid_Afridi]

access does not, and should ever not, on principle of referential integrity, reassign previously used numbers.

this is why Khalid was concerned in his first post, because HIS access WAS re-assigning previously used numbers.

Yes Agnieszka!
see this

In the following tblInvoice, InvoiceID is the Autonumber field (not in sorted order in this view) the last number is showing in this view is 4932 while there are 5757 records in this table.
so the new number should be 5758.

when you compact and repair the database it re-set the new seed to 4933 for this table but 4933 is already used previously for other record. (no new record can not be added to the table due to duplicate error)

Now I am afraid to compact and repair my database
imagine the BIG funny thing ... this Access 2010 with 64 bit configuration windows-7 is really a MONKEY play.

 

[wiklendt]

Khalid, this might be a silly question (because you said this all worked in a prevous version of access, if i recall correctly) but i just want to make sure that your InvoiceID is the ONLY field in that table with Autonumber data type...

 

[Khalid_Afridi]

Agnieszka! this problem is coming just when they installed Access-2010 on the new computer with 64-bit configuration and window-7 operating system.

I am using Access since 1998, and I did not find any problem with Autonumber key re-seeding behavior. I used all verions of Access.

In this table InvoiceID is the only Autonumber Field which get currupted while compacting and repairing the Database, not only the invoice table but all the other tables where there is an Autonumber field.

I try to Sort the tables with the autonumber field in accessending orders to make sure that the latest Autonumber should be the last number in the table, but when I compact and repaired the database its re-change the sequence to the previous orders and it seed it from the priouse last numbers.

Mr. madEG provided this link to re-set the seeding to its lates seed.

http://allenbrowne.com/ser-40.html

which solves the problem, but when you re-compact and repair. its give the same problem.
Strange.....................