AutoNumber field changes

Question

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 1

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.

Answer 2

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.

Answer 3

You are adding a column as a PK without populating it. Leaving Null values.

I would suggest instead of using Alter Table, that you create a new table and append records to it.

Answer 4

The customer can and should specify what they want to SEE.

In general they are not qualified to dictate HOW they come to see it!

If they want to see a unique, sequential ID number, by all means give them one - there are scads of examples on the net for how to set one up. 

But if there's also an Access Autonumber "under the hood", providing the automated linking between tables, that should be no more of their concern than the alloy composition of the piston rings in their car!

Answer 5

I don't need it but the customer requests it.

They will have to do without it...

Thanks