Why are autonumber keys assigning large negatives 2007 Access?

[suvnick]

I recently upgraded my access 2002 database to run on a new machine with
office 2007. Now access 2007 is assigning AutoNumber keys as huge negatives
where as prior to this in 2002 they were going up +1.
Now starting at -257995 and going more positive with each new record.

I would love to change this so it goes back to doing what it did before.

Cheers

Nick

 

[Allen Browne]

You might be able to reset the autonumbers with a compact/repair:
Office Button | Manage

Alternatively, this code will reset it for you in all affected tables:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

 

[suvnick]

Hi Allen

I have been down this path without success.

The article does state it does not work with attached tables. I take it this
means linked tables. I have also tried doing a compact.

Surely there must be some other way to reset the values so they start off
from where the previous version left off.
The autonumbers are "keys" in this database.

Regards

Nick Suvalko

 

[Allen Browne]

Are your tables linked from another Access database?

If so, you need to compact and/or run the code in the back end database,
i.e. the database where the actual data resides. It won't work if you just
operate on the links in the front end.

 

[suvnick]

Hi Allen

Sorry it has taken me some time to get back to this.
OK I have tried running your AutoFixNum() function in the back end database
and although there are definitely negative autonums it returns a 0 (zero) as
though it did not find any tables.

I tried entering a number of new table entries on one table just to force
new negative records just prior to running this function.

Any other suggestions?

Regards

Nick Suvalko

 

[Allen Browne]

Okay, so now you are operating on the database where the tables actually
reside (typically called the back end.)

The code does not alter existing records with negative numbers. What it does
is look at the current *seed* of the autonumber - the value that determines
what number will be assigned next time you add a record - and changes that.
If the Seed has been fixed (even though some negatives exits), the code
won't report a problem.

For code to show you what the seed of a table is, see:
http://allenbrowne.com/func-ADOX.html#GetSeedADOX

You can then determine whether it needs resetting, and if so, this will
reset just one table:
http://allenbrowne.com/func-ADOX.html#SetSeed

 

[suvnick]

Hi Allen

I'm sorry as I probably did not expain the last email correctly.
You code worked perfectly to reset the seed for the tables affected, but
what I would like to know is there anything that can deal with the negative
autonums left in the tables from the previous autonums. Bear in mind that
they have propagated as foreign keys in associated tables as well. There are
not many but I would love to somehow clear these up.

Regards

Nick

 

[Allen Browne]

One approach would be to append a new record to the main table, with the
same values in other fields as the problem record. Then assign this new
autonumber value as the foreign key value for the related records, and
delete the original problem record.

It is really worth the effort? The autonum is a unique value, so it will
work as is.

 

[suvnick]

I guess you are right. I will just leave these as is and from now on it will
assign upwards correctly.
Thank you for your advice and help!

Kindest regards

Nick