Web Development

Ask something
Discussion Locked

Access XP Autonumber

0 helpful
6 replies

by a former member

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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)

6 replies

by Former member

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.

by khall3

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

by Former member

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.

by Former member

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.

by Former member

This question was closed by the author

This discussion is closed to new replies.