My AutoNumber is messed up!! Starting from 1 after splitting database

[Sierras]

I have a table with an Auto Number as the primary key.
All was fine but at some point, the autonumber started giving numbers
starting from 1 again and is trying to increment with an addition of
a record. . Now every time you try to create a record, I get the
conflict as my primary key can not have duplicates. I'm using a split
database with a FE and BE which has the problem. The original unsplit
database is working fine.

Any suggestions?

 

[Sierras]

Well it's late and instead of trying to figure out what went wrong, I
just took the easy way out and created a new database from scratch.
Then imported all the tables from the BE, renamed the new BE and then
re-linked the tables to the FE.

All is working now but I'd like to try and figure out what happened so
as to prevent it from happening again.

If anyone else ever had this happen, please let me know what you
found.

Thanks...

 

[Rick Brandt]

Well it's late and instead of trying to figure out what went wrong, I
just took the easy way out and created a new database from scratch.
Then imported all the tables from the BE, renamed the new BE and then
re-linked the tables to the FE.

All is working now but I'd like to try and figure out what happened so
as to prevent it from happening again.

If anyone else ever had this happen, please let me know what you
found.

It's a form of corruption, known to happen occassionally, and I believe
supposed to be fixed by one or more Jet service packs that were issued for
Jet 4.0.

 

[Guest]

Hi Rick,

Yes, this was a problem with Jet version 4.0.2927.4 (Service Pack 3) and
earlier:

http://support.microsoft.com/?id=291162

However, one can still have a duplicate autonumber problem even with SP-8 IF
the autonumber field was created with JET SP3 or earlier. Having JET SP-8
will not prevent the problem if the field was created on another PC that was
not patched properly. The solution is to delete the autonumber field and
create a new one in it's place, with SP-8 already installed.

Sierras:
If you need to replace an autonumber field in an existing table, where there
are likely gaps in the numbers due to previously deleted records, then create
a copy of the structure of the entire table, without data. Then run an
append query, using all of the fields from the table with the bad autonumber
field (including the autonumber field) serving as the source of records.
Finally, delete the source table and rename your new table with the same name
that the source table used to have. Re-establish relationships with
referential integrity between your new table and the other table(s).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________