How to return record counter to the zero position

[Lilian]

Hi,
I will to restart the record counter in my db. I deleted all records from
the db. I tryied to make compact an repair it didn't help. I also triyed in
the tables designvy to erase primarykey and write it again it didn't help.
Do someone knows how to return record counter to the begining so that new
record will start from 1.

Best regards,
Lilian

 

[Douglas J. Steele]

Why bother? AutoNumber fields exist for one purpose only: to provide a
(practically guaranteed) unique value that can be used as a primary key.
235, 237, 238 fills that need as well as 1, 3, 4 does.

Meaning should never be assigned to the value produced by an AutoNumber
field. In fact, it's common to hide the field, so that the user doesn't even
see it.

If you're determined, though, see what Allen Browne has at
http://www.allenbrowne.com/ser-26.html

 

[Tom Wickerath]

Hi Lilian,

You should not consider an autonumber field as a valid record counter; it
should only be considered as a *meaningless* value. If a person starts to add
a record, but presses the Escape button to undo their edits, the autonumber
will not reuse the value. Likewise, if a record is deleted, the autonumber
does not automatically reset for all remaining records. Thus, it should not
be considered a reliable indicator of record count.

Normally, deleting all records from a table and then performing a Compact
and repair will reset the autonumber field to 1 (as long as randomize is not
set as a property). Do you have the latest service pack installed for the JET
database engine? Here are some links that I recommend that you follow, to
ensure that your system is properly updated:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

In particular, follow the three links:

Verify that the latest operating system service pack is installed
Verify that the latest Microsoft Jet service pack is installed and
Verify that the latest service pack for your version of Office is
installed

Also recommended: Install the latest version of MDAC (Microsoft Data Access
Components)
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

That said, if the autonumber still refuses to reset to 1, I would try Access
MVP Allen Browne's code. Go to this article:
http://allenbrowne.com/ser-40.html and scroll to the very bottom. Follow the
two links:

For code to examine a table and find out what the Seed of the AutoNumber is,
see GetSeedAdox().

For code to reset the Seed for one table only, see: ResetSeed().



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Daryl S]

A quick way to do this is to copy the empty table and paste it (structure
only - no data). Then you can delete the old table and rename the new one.

 

[Larry Linson]

Assuming you have a related table with foreign keys, had you thought what
you might want to do about those?

As several have told you... there are some ways to do what you want, but no
good reason to do so -- only good reason I can think of is that you have
developed an application, are preparing to distribute it (and the data
tables) to users who will be filling with their own data, and you'd like to
have it _appear_ pristine and new to anyone who "looks under the covers".
That won't make it any better, but it might be a motivation to consider what
you are asking about.

Just as a matter of interest... only in the very earliest versions of
Microsoft Access were what are now called Autonumber fields called "Counter"
fields, similar to the term you use. Is it possible that you are working on
a DB that has been around since Access 1.0 or 1.1 days?

Larry Linson
Microsoft Office Access MVP