General discussion

Locked

Access XP Autonumber

By jon.russell1 ·
I have a table in an Access XP database. The table contains an Autonumber field and a text field, and has 5 records, the autonumber field counts from 1 to 5 as expected, however when adding a new record, the autonumber field does not return 6 as expected, but a higher number every time. I have tried repairing/compacting the database but it does not seem to have any effect. Has anybody had this issue or any idea how to resolve it? I have installed the Office XP service packs (1 and 2)

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access XP Autonumber

by DKlippert In reply to Access XP Autonumber

Have you tried deleting the AutoNumber field and saving and then adding it back in?

Collapse -

Access XP Autonumber

by jon.russell1 In reply to Access XP Autonumber

Unfortunately, there are other tables related to this one, if I delete the autonumber field, I'll lose the relationships. The structure of the entire database is rather complex. The behavior of the autonumber field seems to be related to some of the other problems I've been having with this project. I am suspicious that I have a corruption problem, I know the repair function with Access is pretty cursory and it is not returning any errors.

Collapse -

Access XP Autonumber

by khall3 In reply to Access XP Autonumber

Ok the first thing you will need to do is to look at your table in design view. Obviously you only have two field the Autonumber field and the text field. In the Autonumber field I am assuming you have the data type set as "AutoNumber" and the text field set as text. In the field properties you need to make sure your field size is set to "Integer" or Long Integer". Also the New Values property must be set to "Increment" not "Random"

Also you might want to open you table and view the fiverecords you have. Then choose format, unhide columns. You might have accidently hide some columns.

Hope this helps

Collapse -

Access XP Autonumber

by jon.russell1 In reply to Access XP Autonumber

Sorry, that's not it either. After some more checking, I've discovered that this is happening in several tables in this database (only one database out of several shows has this problem, I am not able to replicate the problem in any other database). The only way I am able to get the autonumber counter to work correctly is to delete the relationships (enforced) then delete all data in the table, then compact. It seems as if Access is not reseting the autonumber counter during the compact operation unless the table has no data.


I've been working with Access since version 2.0 and have never seen an autonumber behave this way. This database is secured with user level security, but I am logged in with administrative rights when I compact.

Collapse -

Access XP Autonumber

by jon.russell1 In reply to Access XP Autonumber

I've found the solution, for anybody interested, see MS KB article Q287756. I used the code sample to successfully reset my autonumber counters. The article fails to mention that in addition to the reference to "Microsoft ActiveX Data Objects 2.x Library" you also need reference the "Microsoft ADO Ext. 2.x for DDL and Security" library for the code to compile. The question will be closed with no points awarded. Thanks for your attention.

Collapse -

Access XP Autonumber

by jon.russell1 In reply to Access XP Autonumber

This question was closed by the author

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums