Autonumber strange behavior?

[john]

A record of a table with an autonumber field was accidentally deleted. From
the backup table I made a new table with only the deleted record. Then with
an add query I added that record to the original table. After that, when
adding a new record, the autonumber continued with the last ID value (from
the add query) + 1 there by overriding already existing records.

Is this WAD?

What should be best to do in such a situation?

Thanks,
john

[Allen Browne]

This is a known bug in JET.

See:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

The article describes the causes, and how to reset the Seed of the
AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john" <hg*@neckso.comwrote in message
news:31***************************@cache110.multik abel.net...

>A record of a table with an autonumber field was accidentally deleted. From
the backup table I made a new table with only the deleted record. Then with
an add query I added that record to the original table. After that, when
adding a new record, the autonumber continued with the last ID value (from
the add query) + 1 there by overriding already existing records.

Is this WAD?

What should be best to do in such a situation?

Thanks,
john

[Stuart McCall]

"john" <hg*@neckso.comwrote in message
news:31***************************@cache110.multik abel.net...

>A record of a table with an autonumber field was accidentally deleted. From
the backup table I made a new table with only the deleted record. Then with
an add query I added that record to the original table. After that, when
adding a new record, the autonumber continued with the last ID value (from
the add query) + 1 there by overriding already existing records.

Is this WAD?

What should be best to do in such a situation?

Thanks,
john

Don't include the autonumber field in the append query. That way Access will
assign new autonumbers to all incoming records.

[john]

Thanks.
john

"Allen Browne" <Al*********@SeeSig.Invalidschreef in bericht
news:47**********************@per-qv1-newsreader-01.iinet.net.au...

This is a known bug in JET.

See:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

The article describes the causes, and how to reset the Seed of the
AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john" <hg*@neckso.comwrote in message
news:31***************************@cache110.multik abel.net...

>>A record of a table with an autonumber field was accidentally deleted.
From the backup table I made a new table with only the deleted record.
Then with an add query I added that record to the original table. After
that, when adding a new record, the autonumber continued with the last ID
value (from the add query) + 1 there by overriding already existing
records.

Is this WAD?

What should be best to do in such a situation?

Thanks,
john

[john]

"Stuart McCall" <sm*****@myunrealbox.comschreef in bericht
news:fd*******************@news.demon.co.uk...

"john" <hg*@neckso.comwrote in message
news:31***************************@cache110.multik abel.net...

>>A record of a table with an autonumber field was accidentally deleted.
From the backup table I made a new table with only the deleted record.
Then with an add query I added that record to the original table. After
that, when adding a new record, the autonumber continued with the last ID
value (from the add query) + 1 there by overriding already existing
records.

Is this WAD?

What should be best to do in such a situation?

Thanks,
john

Don't include the autonumber field in the append query. That way Access
will assign new autonumbers to all incoming records.

Since the number of this deleted record was already 'given out' to third
parties as well I wanted it to be added with the original number.

john

[Stuart McCall]

"john" <hg*@neckso.comwrote in message
news:9f***************************@cache100.multik abel.net...

"Stuart McCall" <sm*****@myunrealbox.comschreef in bericht
news:fd*******************@news.demon.co.uk...

>"john" <hg*@neckso.comwrote in message
news:31***************************@cache110.multi kabel.net...

>>>A record of a table with an autonumber field was accidentally deleted.
From the backup table I made a new table with only the deleted record.
Then with an add query I added that record to the original table. After
that, when adding a new record, the autonumber continued with the last ID
value (from the add query) + 1 there by overriding already existing
records.

Is this WAD?

What should be best to do in such a situation?

Thanks,
john

Don't include the autonumber field in the append query. That way Access
will assign new autonumbers to all incoming records.

Since the number of this deleted record was already 'given out' to third
parties as well I wanted it to be added with the original number.

john

Ah. Misunderstood your question. Sorry. I see Allen's answered it (spot on
as usual!).