How To Reset Autonumber to "1" 1

[mpsDA]

How do you reset the Autonumber field so it starts from number 1 again. Also just when you delete a record it tends ignore the record number completely and jump to the next number rather than using the number just deleted. Is there a way around this?

 

[THWatson]

mpsDA
Here's a link from the Microsoft Office site, that shows how to reset the autonumber in Access

http://support.microsoft.com/kb/812718/en-us

As for re-using an autonumber, after deleting a record, you can't. That's the purpose of an autonumber - it is used once. However, it's not a problem to have missing autonumbers, as the autonumber is merely an identifier.

A way around it is to create your own numbering system, and not use the autonumber.

Tom

 

[Lilliabeth]

If you care what value is in an autonumber field, you aren't using it as intended.

 

[mpsDA]

The reason i wish to rest the the auto number is because It was in the middle of development and is now ready to go live for users to use. Hense the reason why i wish for it begin from number 1 now.

 

[THWatson]

mpsDA
The way, then, is to follow the approach from the Microsoft link. I have done that in the past, and it works.

Tom

 

[Lilliabeth]

If the table has 0 records, won't compacting reset the autonumber?

 

[mpsDA]

What Microsoft Link?

And no by deleting the records will not reset it back to 1

 

[Lilliabeth]

I didn't say deleting records would reset autonumber. I know it will not.

Have you compacted the db?

 

[THWatson]

I gave you the link in my first reply...

http://support.microsoft.com/kb/812718/en-us

That's from the Microsoft Knowlege Base.

Tom

 

[Lilliabeth]

I just tested it. Compacting a database reset the autonumber for a table in which all the records had been deleted.

 

[PRPhx]

Deleteing the column with the autonumber and then adding it in again seems to reset the auto numberx back to the proper sequence, starting with the number 1.

But as mentioned, why worry about the value. It's meaningless and your users should never see it anyways. IF you really need to use this value for something, it's better to come up with your own numbering method.

 

[Lilliabeth]

I must be missing something. If so, point it out.

The db should be compacted before putting into production anyway, so where's the issue?

 

[Remou]

I must agree with Lilliabeth, what I would usually do is clear down all tables that should be blank and then compact before putting into production, which, as Lilliabeth says, resets all autonumbers. I would never delete a key field as it would mess up the relationships. There are other ways of resetting autonumers, SQL for example, but I go with Lilliabeth all the way on this one, for what its worth.

 

[PHV]

Another way (SQL code]:
ALTER TABLE yourTable ALTER COLUMN yourID COUNTER(1,1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Lilliabeth]

Thank you Remou. I was starting to think I had slipped into the Twilight Zone where I could speak but no one could hear me.

 

[mpsDA]

Well done mate Compacting works. Took 8 relies to get this sorted Well Done Lilliabeth for your help!