Primary Key Reset

[EYEMDABEST]

I have created a Database and Form for inputing to the database. I have entered information to test the rules and formats and now would like to start using the form but the primary key starts numbering where i left off even if I delete the rows of bogus information. How can I reset the primary key and have it start over at 1?

I removed the primary key, saved and readded the key but no luck.

Thanks for your help.

 

[Derek Brown]

'Compact and Repair' at earlier versions of Access would usually solve the problem. I am not sure when it changed (2003 or 2007) but for the later versions there is a possiblity of being able to re-seed the index using a Function.
What version of Access are you using?
Have a look at:
http://support.microsoft.com/kb/812718

 

[Joe4]

I am assuming you are talking about using an AutoNumber field.

After you delete all the records, if you compact and repair the database, it should reset it. Otherwise, you would need to add a new field and delete the old one.

However, many people will tell you that you should not use an AutoNumber field for anything meaningful, it is really just designed to ensure a unique identifier.

How exactly are you trying to use it?
There may be better ways.

 

[EYEMDABEST]

Using 2007. Where would I compact and repair?

 

[Derek Brown]

Here is a link to the function:
http://allenbrowne.com/ser-40.html

 

[Joe4]

Using 2007. Where would I compact and repair?

Click the Office button in the upper left-hand corner and select "Manage". It is one of the option under there. Make sure you close out of all objects first.

 

[EYEMDABEST]

Compact and repair worked. Thanks for all of your help guys.

 

[Derek Brown]

Pleased to hear that all is now OK.
However, a word of warning if you expect to do this again ....
Make sure that you take a backup copy of your database first (this is also mentioned at the beginning of the Microsoft article - the link in my first reply).
It is possible that Compact and Repair could lose your database in the process - it happened to me earlier this year but I had a backup copy!

 

[boblarson]

Compact and repair worked. Thanks for all of your help guys.

Just a word of warning about autonumbers - They should ONLY really be used behind the scenes as a surrogate key. Neither you, nor your users, should care what it is and probably shouldn't even see them. Just because you have it set to increment, doesn't mean that it will always do so. It can change, sometimes on its own, to random which can then generate large numbers out of order and/or negative numbers.

So, while set to be indexed, non duplicating, it should give you a UNIQUE number and that is the only thing you really should expect from it.