Access 2010 - problem with Autonumber fields

[tbclbd]

We have an Access database that was developed in Acc2003. It is broken up into the front-end and back-end. We want to upgrade to Acc2010, but found that in several tables the autonumber fields became corrupt(?). I then rebuilt the table structures in Acc-2010 and then ran a query to append all the fields, except the autonumber field, to the new table. When I added a record directly into the new table, all was ok, yet, when I opened the front-end and tried to add a new record I got a message "Addition of record with cause duplicate keys." After that if I tried to manually add the record in the back-end, then the new autonumber field had a value somewhere in the middle of the existing records. Once this happened, had to start all over again - rebuilding the tables. Has this happened to anyone else, if so, did you get it resolved?

 

[randy700]

What do you mean when you say the autonumber fields became corrupt?


Randy

 

[tbclbd]

I don't know if corrupt is the correct word. But, here's what is happening. There are records in the table, and the autonumber field values are from 1 to 48875. When I manually add another record to to table, the value of the autonumber is 48876, but if I go into the program and try to enter a new record, the value of the autonumber field is 19102. I had the same issue in almost every table in the database.

 

[randy700]

It sounds like Access thinks you have a large number of records that have been deleted. In an autonumber field, numbers are never repeated, even if the record has been deleted. Can you post the code you use to enter the new record?


Randy

 

[judgeh59]

I have had a problem with autonumbers and this helped. It's a bit cumbersome, but it worked. Check out this link.

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

hope this helps

Ernest

Be Alert, America needs more lerts

 

[Hap007]

Couple of questions for you.

1) Did you upgrade the FrontEnd and BackEnd to Access 2010?
2) After the upgrade, did you refresh the table links?
3) Using the frontend, Can you just open one of the tables that is giving you trouble and try adding a record to the table. What exactly is the value then of the AutoNumber field?

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Hap007]

FYI:

You may want to try this:

Resetting the Seed
You may be able to solve the problem with a compact/repair:

•In Access 2010: Compact and Repair Database on the Database Tools ribbon.
•In Access 2007: Office Button | Manage.
•In earlier versions: Tools | Database Utilities.
Alternatively, the code below checks all tables in your database, and offers to fix any where the Seed of the AutoNumber is less than zero or below the existing values. The function returns the number of tables that were changed - zero if none had the problem. It does not alter the values of any duplicate values in the column.

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

The code does not work on attached tables: if your database is split, run it in the back end data file.



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121