resetting autonumbering to 1

[VTM3]

I have created a new table and I noticed that the autonumbering started at 2
instead of 1. I deleted the record for 2 and tried to enter data in the next
field over, and it labeled the first record 3. I deleted that record and
started over, but then it labeled the first record 4. So, how do I get it to
start over a 1 for the autonumbering primary key column?

Thanks!

 

[Maurice]

Drop the autonumberfield from the table. Close and save the table and
recreate the autonumber field again. This should set it to start at 1 again.

But while we're at the subject, why would you want to do that. In this
situation you have only one record. What would you do when you have related
tabels with data. At that point this trick doesn't work anymore. So to keep
it clear, use an autonumer what it is supposed to be used for. Create
uniqueness in a record no matter what number is invthere. You should do
anything else than besides accepting it as a unique number

hth

 

[Tom van Stiphout]

On Fri, 4 Jul 2008 10:45:03 -0700, VTM3

To reset the autonumber you have to delete all the data and compact
the database.
But there is no need: the Autonumber value should NOT have any
meaning, other than a unique number. You will get in trouble down the
road if you try to keep the autonumber sequential.
If you must have a sequential number, you can use a long integer field
and the "DMax + 1" technique. Since this is a FAQ you should have no
problem finding articles about this when searching at
groups.google.com

-Tom.

 

[Tom Wickerath]

An autonumber should be considered a *meaningless* number. Don't try to
assign any meaning, including a count of records. That said, in order to have
an autonumber start over at 1, you need to do two things:

1.) Delete all records from the table (as you have done)
2.) Compact the database

To compact the database in Access 2003 or earlier, click on:
Tools | Database Utilities... | Compact and repair database

To compact the database in Access 2007, click on the button thingy in the
upper left corner, then select Manage, and then Compact and repair database.
It's always a good idea to make a back-up copy of your database prior to
compacting, especially if you are using Access 2007.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[a a r o n _ k e m p f]

in SQL Server, you can run TRUNCATE TABLE tblName and it will do this
(just for one table like you want).
I dont' think that this is practical or possible with JET.

But then again-- JET is obsolete, so just move to SQL Server and you
can _DO_ the things you want!

-Aaron