Reset the automatic counter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to reset the value of the automatic counter type field in an Access 97 database or do you have to recreate the whole table?
 
Reset to what ? 1?

If you want to start at 1, empty the table, compact the database, and the
first record should start with 1 as the autonumber value.
--
Ken Snell
<MS ACCESS MVP>

Joachim said:
Is there a way to reset the value of the automatic counter type field in
an Access 97 database or do you have to recreate the whole table?
 
Is there a way to reset the value of the automatic counter type field in an Access 97 database or do you have to recreate the whole table?

In A97, you can delete all the records in the table, and Compact the
database to reset the autonumber to 1. This reset won't work in later
versions, though.

Generally it's best to keep Autonumbers "under the hood" - they'll
always have gaps, can become random, and generally aren't suitable for
human interaction. If you want a sequential number that starts at 1
and doesn't have gaps, use a Long Integer and write VBA code to
increment it.
 
an Access 97 database or do you have to recreate the whole table?
In A97, you can delete all the records in the table, and Compact the
database to reset the autonumber to 1. This reset won't work in later
versions, though.

Actually, John, I use this technique in A2K2 successfully all the time!
 
In A97, you can delete all the records in the table, and Compact the
database to reset the autonumber to 1. This reset won't work in later
versions, though.

John, the autonumber is set back to 1 for me in all Access versions.

- Steve Schapel, Microsoft Access MVP
 
Actually, John, I use this technique in A2K2 successfully all the time!

Ok, maybe I was dreaming about seeing that change while reading
newsgroups... not too surprising considering how much time I spend
here! Does Compacting also bring the next autonumber to one more than
the highest existing value? (you can see how often I look at
autonumber values)
 
Yes.

--
Ken Snell
<MS ACCESS MVP>

John Vinson said:
Ok, maybe I was dreaming about seeing that change while reading
newsgroups... not too surprising considering how much time I spend
here! Does Compacting also bring the next autonumber to one more than
the highest existing value? (you can see how often I look at
autonumber values)
 
Not for me, it doesn't, neither in Access 2000 nor Access 2002.

- Steve Schapel, Microsoft Access MVP
 
OK -

who uninstalled this feature from my A2K2?? I coulda sworn that it had
worked for me in the past....but it ain't now....

Compacting after deleting some records does not restore autonumber to start
with the next available number.

Sorry for the wrong info, John!
 
Compacting after deleting some records does not restore autonumber to start
with the next available number.

I *vaguely* remember someone posting here that it depends on which
service packs you've installed.
 
Back
Top