Reset Autonumber

[Cillies]

Hello, I was wondering if anyone knew how to reset an auto number.

I.E.
in my database I have two tables with autonumbers as primary keys. So
I was wondering if anyone knew an easy way of resetting them, as I
have made a few errors throughout the database.

Kindest Regards

[fredg]

On 28 May 2004 08:50:06 -0700, Cillies wrote:

Hello, I was wondering if anyone knew how to reset an auto number.

I.E.
in my database I have two tables with autonumbers as primary keys. So
I was wondering if anyone knew an easy way of resetting them, as I
have made a few errors throughout the database.

Kindest Regards

You can reset the autonumber field to 0 by first deleting all the data
in the table. NOT the table itself, then compacting the database.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[devdex.com]

Hi thanks for the advice, but is there any way of doing it without
deleting all the data in the table.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Stewart Allen]

Create a new table with identical field structure. Create an Append query
using the old table as its record source and use the new table as the target
and match every field except the autonumber fields. Run the query and then
rename and/or delete the appropriate tables.

This will not work if there's a relationship from the Autonumber field
linked to a field in another table. Why do you really want to reset the
Autonumber back to zero if you want to keep the data?

Another way I've done it but not for any client of mine is to delete the
Autonumber field, save the table and then re-create the field again. If the
table has 500 records they will automatically have the numbers 1 to 500.

Before doing these operations, ***Back Up*** first.

Stewart
"devdex.com" <an*******@devdex.com> wrote in message
news:40*********************@news.frii.net...

Hi thanks for the advice, but is there any way of doing it without
deleting all the data in the table.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!