Re-set autonumber in a table (primary key)

[Biffo]

I have built a database and tested it thoroughly. I deleted the test records
but now my first record has an automunber or 15 and this is winding me up.
Can I re-set the autonumber back to 0?

 

[Bob Barnes]

Try a compact and repair on the DB (front- or back-end) having that table.

 

[Wayne-I-M]

Delete the field. Save the table. Insert a new autonumber field

 

[Krzysztof Pozorek [MVP]]

Delete the field. Save the table. Insert a new autonumber field

....or you can simply execute something like this:
DoCmd.RunSQL "alter table Table1 alter column ID counter(1,1)"

For example counter(3, 10) means:
3 - initial value of counter
10 - step value.

K.P. MVP, Poland
www.access.vis.pl

 

[Wayne-I-M]

Why not not just
Delete the field. Save the table. Insert a new autonumber field
Much better for someone who has no records in a table that has just been
created.

Mind you I never really bother what an autonumber field has in it. As long
as it's unique - thas all I'm bothered about.

You could also compact the DB

You could also ....etc etc

But still think it's better when 1st createing a table to ...
Delete the field. Save the table. Insert a new autonumber field






--
Wayne
Manchester, England.

 

[Krzysztof Pozorek [MVP]]

(...)

Why not not just
Delete the field. Save the table. Insert a new autonumber field
Much better for someone who has no records in a table that has just been
created.

Mind you I never really bother what an autonumber field has in it. As
long
as it's unique - thas all I'm bothered about.

You could also compact the DB

You could also ....etc etc

But still think it's better when 1st createing a table to ...
Delete the field. Save the table. Insert a new autonumber field

Your advice is good and I agree with this, what you wrote.
Although, what it is better, it depends on what You need at that moment.
The "alter table... " gives the easy possibility of steering the counter
with code. So it is less well-known than compact or recreate the autonumber
field. Therefore I mentioned about it.

Kris, Poland
www.access.vis.pl

 

[Steve Schapel]

Wayne,

I prefer the advice given by Bob, to simply Compact the database. Your
idea is more difficult, but still fine, but could get further
complicated if this table has Relationships defined based on this
Autonumber field, etc.

 

[Wayne-I-M]

Hi Steve

Yes you are right. I assumed it was a stand along table. - Is it too late
to make a new year's resolution - don't make assumptions.

 

[Wayne-I-M]

Hi Krzysztof

You are correct if there are relationships. You method would be better as
mine would cause problems. I assumed it was a new stand alone table.


--
Wayne
Manchester, England.

 

[Steve Schapel]

Hi Wayne,

On the other hand, I am only right if one makes the assumption ;-) that
they are using Access 2003 or earlier. It appears that compacting the
database does not re-set the Autonumber seed in Access 2007!

 

[Steve Schapel]

Oooopos!! Correction - sorry!! That is not correct. I was testing on a
remote server, which was somehow messing things up. When doing it
locally, it behaves as in earlier versions - I should have paused before
posting!