DELETE table and reset AutoNumber

[McKirahan]

I'm using MS-Access and was wondering if there's a way to DELETE a table and
reset the primary key which is an AutoNumber field.

If I do "DELETE FROM table_name" then all the rows are removed but the
AutoNumber isn't initialized; thus, the next the AutoNumber field after the
next INSERT is not 1.

Thanks in advance.

[Bob Barrows [MVP]]

McKirahan wrote:

> I'm using MS-Access and was wondering if there's a way to DELETE a
> table and reset the primary key which is an AutoNumber field.
>
> If I do "DELETE FROM table_name" then all the rows are removed but the
> AutoNumber isn't initialized; thus, the next the AutoNumber field
> after the next INSERT is not 1.
>
> Thanks in advance.

This is a frequently-asked question in the Access newsgroups (where it
should have been asked <grin>)

The easiest way is to compact the database after the deletion..

Another way is to change the field to Number and back to AutoNumber.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[McKirahan]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:#b7GbZMeEHA.1000@TK2MSFTNGP12.phx.gbl...

> McKirahan wrote:

> > I'm using MS-Access and was wondering if there's a way to DELETE a
> > table and reset the primary key which is an AutoNumber field.
> >
> > If I do "DELETE FROM table_name" then all the rows are removed but the
> > AutoNumber isn't initialized; thus, the next the AutoNumber field
> > after the next INSERT is not 1.
> >
> > Thanks in advance.

>
> This is a frequently-asked question in the Access newsgroups (where it
> should have been asked <grin>)
>
> The easiest way is to compact the database after the deletion..
>
> Another way is to change the field to Number and back to AutoNumber.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>

You're a god!

Compacting after deleting and before inserting works great.

Sorry for posting in the wrong ng; it's just that it was a "db" question and
I monitor this ng.