Autonumber problem...

[s0rtd]

Hey guys,

simply - i have a table with a few thousand records in it. the Autonumber field (primary key) is up to 2945, and is a long integer, incremented, and indexed with no duplicates.
so far adding new records to this table has not been a problem, however if i try to add a new record to this table now, for some strange reason it tries to use a value for the autonumber field which is already in use, (ie: it has started from 1 again) almost like it has forgotten about the previous 2xxx records.

not sure what is going on here, but seems very strange. I have tried running a compact and repair without any luck.

i have sort of fixed the problem, imported the table from a backup and added all the records since (keeping in mind the value of the autonumber field).

but, i would like to get to the bottom of this, for future purposes.

any ideas are much appreciated

cheers
Jurgen

 

[SQL_Hell]

Hi,

I can only suggest that you must of re-set the auto-number sequence, it is very unlikely that this would just happen by itself.

it would be difficult for anyone to explain exactly what went on, as they weren't with you at the time.

 

[socatoa]

socatoa

Just drop the autonumber column and recreate another autonumber column, It should automatically fill up to the max record.

 

[Eniac]

Hum...

I remember having such a problem wayyyyyy back.

Try this :

1. Delete any relationship that table (remember them for later....)
2. Go in design mode for the table and change your primary key as a long integer number type (anything than autonumber should do)
3. Save and exit. Click yes to all Access's questions about integrity and yada yada yada
4. Go back in design mode for that same table and re-set the field to Autonumber increment
5. Save and exit. and again, click yes on every question access will ask.
6. Re-do your relationships.

That should do it.

if that still doesn't, then you could consider what I just did, avoid autonumbers and take the matter into your own hands.

Its funny, I asked a question today in Modules & VBA about how to call a query from a module whose purpose was to replace the autonumber...)

 

[s0rtd]

cheers guys, i will definetely keep an eye on it.
one question because my db is split - will running compact and repair on the frontend fix the problem?

cheers
Jurgen