468,554 Members | 1,966 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,554 developers. It's quick & easy.

Autonumber strange behavior?

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
Oct 2 '07 #1
5 1589
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
Oct 2 '07 #2
"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.

Oct 2 '07 #3
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

Oct 2 '07 #4
"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
Oct 2 '07 #5
"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!).
Oct 2 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Vladislav | last post: by
1 post views Thread by Alexander Inochkin | last post: by
reply views Thread by ivb | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.