AutoNumber field changes

I have a table Store_Request with an AutoNumber field named rec_id

After making some changes to the table, I want to reset the starting rec_id to 1

I am using the following command lines:

        CurrentDb.Execute "ALTER TABLE Store_Request ALTER COLUMN [rec_id] Number;"
        CurrentDb.Execute "ALTER TABLE Store_Request DROP COLUMN [rec_id];"
        CurrentDb.Execute "ALTER TABLE Store_Request ADD COLUMN [rec_id] Int Primary Key;"

The last one gives an error "Index or Primary Key cannot contain a null value"

What is wrong with the statement??

Thanks in advance.

Answer
Answer
You can reset an autonumber column by compacting and repairing the database.  However, an autonumber is designed only to guarantee distinct values, not sequential values.  If the value in each row is semantically significant, which it rarely is with a surrogate key, then an autonumber should not be used; the value should be computed when a row is inserted into the table.

You'll find an example in CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the option for 'Sequential Numbering' is developed from Roger Carlson's published method, and caters for possible conflicts in a multi-user environment.  You'll note that this has been extended to allow for seeding of the next number to be used, and for the increment to be set to a value other than 1.
_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Is the table now empty? If it has data in it, then the [rec_id] field must have a unique Long Integer value for every record; it would appear that you're trying to create a new (hence necessarily empty) field!

If your only purpose is to reset the starting point of the Autonumber in an existing table, well... don't; an Autonumber is NOT intended to be a count, or a gapless, meaningful, human readable identifier. It's intended to be a meaningless unique ID and NOTHING ELSE. In practice autonumbers will always have gaps, and can even become random (if you Replicate the database for instance).

If the table is in fact empty and you want to start the autonumber over at 1, simply Compact and Repair the database. This will reset (all) the Autonumbers to either start at 1 or at one more than the largest value.

John W. Vinson/MVP

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated September 4, 2018 Views 109 Applies to: