Resetting Autonumber after deleting records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

=Ray= - 21 Jun 2007 16:29 GMT

I have a table with an autonumber primary key. I have been using it for bogus
data while I've been working on the database design, but now I've deleted the
data, but I can't reset the autonumber so that it starts at 1 again.

Can someone tell me how to do this?
Thanks,
=Ray=

Reply to this Message

Al Campagna - 21 Jun 2007 17:01 GMT

Ray,
After you delete all the records in the table, do a Compact.
The next record you enter after that should start with 1.

Signature

hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

>I have a table with an autonumber primary key. I have been using it for
>bogus
[quoted text clipped - 5 lines]
> Thanks,
> =Ray=

Reply to this Message

Paul Shapiro - 21 Jun 2007 20:14 GMT

It depends on the Access version. In recent versions, compacting no longer
resets the autonumber to the next available number. From an earlier post by
Tony Toews:

Resetting AutoNumbers
http://www.allenbrowne.com/ser-40.html

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and
Repair a Database
http://support.microsoft.com/?kbid=257408

AutoNumber Field Duplicates Previous Values After You Compact and Repair a
Database
http://support.microsoft.com?kbid=291162

How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database
Engine
http://support.microsoft.com/?id=239114

You may receive an error message when you try to insert a new record in a
table that contains an Autonumber field in Access 2007 or in Access 2003
http://support.microsoft.com/?id=884185

> Ray,
> After you delete all the records in the table, do a Compact.
[quoted text clipped - 4 lines]
>> the
>> data, but I can't reset the autonumber so that it starts at 1 again.

Reply to this Message