changing a number field to a autonumber

[yosteee]

Many years ago, when I first started in DB, teaching myself, I created a Database that has a number field as the primary key. This should have actually been an auto number field. However, this field started with a -149 value and is currently up to 1027, manually entering a sequential number for each new record. We started with a negative number because after starting using the DB, it was decided to go back three years worth of work and add them to the DB.
1. I would like this field to "automatically" increase by one for each new record. What is the best SAFE way to accomplish this?

[ranman256]

you cannot turn numbers into autonumbers.
you can save the existing numbers, create a new autonum field,
then backfill all related tables to the new autonum using update queries.

Convert to autonum:
rename ID (orig ID) to ID_old (in all tables)
create ID (as autonum) in master table
now join tables on ID_Old and fill in the ID on all tables.

[ArviLaanemets]

Make a copy of your DB!

In case you used relationships at table level and referential integrity, remove them.

Create a empty table with structure identical with table having numeric PK, but without PK defined. Add an autonumeric field, and define it as PK.

Run insert query to copy data from original table into lately created (autonumeric PK field is filled automatically, don't include it into query.

Run update queries for all tables having earlier numeric field as FK, replacing it witch according autonumeric field value from new table.

Rename/delete old table. Rename the new table with name the old table had.

In case you had table-level relationships/referential integrity, recreate them.

Check, is all OK.

[ssanfu]

I would like this field to "automatically" increase by one for each new record. What is the best SAFE way to accomplish this?

You might want to rethink switching to an autonumber if you need sequential numbers.
Is this a multi-user dB? If so, you need to take that into account.


AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key


Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers


An autonumber type field is not guaranteed to be sequential, gap less nor is it guaranteed to be positive. It is only guaranteed to be UNIQUE.
You need to create a "Custom Autonumber" routine. There are lot of those on the web.

You could check out:
Scott Gem's site https://scottgem.wordpress.com/2009/...ial-numbering/
Paul's site: http://baldyweb.com/CustomAutonumber.htm


Google "custom auto number in access" (no quotes)

[ArviLaanemets]

You need to create a "Custom Autonumber" routine. There are lot of those on the web.

It looks like OP has the custom routine or autonumeric PK until now too. Simply OP wants the numbering started from 0 instead of -149.

Btw, as addition to my previous post, before assigning new FK values to other tables, possible orphan records (there is no matching old PK value in table OP is changing for FK in another table) from those must be deleted - otherwise some of them may be attached to some record in main table, where they don't belong.

[ssanfu]

@Arvil,

<snip>However, this field started with a -149 value and is currently up to 1027, manually entering a sequential number for each new record.<snip>

In post #1, the OP indicates they manually enter the number. So if they need a sequential number (with no gaps) AND they have multiple users, an Autonumber type field in not a good choice.
If they have an autonumber type field for the PK field AND they keep their "number field", I support that.
They could still create a "Custom Autonumber" routine to enter a sequential number in their "number field".

My $0.02.......