Autonumber field

[Aurora]

I am using Access 2000

I created a Db with and ID Autonumber field. But the user
wanted to add in some old records, so I changed the
autonumber to a "number" field so they could in put their
own numbers. Now I want to put it back to an autonumber
field and append and format it to start the next entry
with a particular number, but it won't let me do this.
Does anyone have any ideas? I would appreciate you help
as soon as possible.

Aurora

 

[Cheryl Fischer]

Aurora,

Once a table has had any data added to it, you cannot change a Number field
to an AutoNumber. (You can change *from* an AutoNumber but you cannot go
back.)

There have been times when I have had to append old records or records from
another source to an existing database where there is currently an
AutoNumber field representing, for example, Order ID. When the clients
have wanted some reference to the old Order ID, I have given them a new
field just for that purpose but have indicated that the database will
generate the "true" ID.

Since changing a DataType in a table is a significant modification, I hope
you kept a backup of your data before making that change. If you did, I
would go to the backup, add the new field, and then append the old orders.
When you do this, the ID of the old order will be appended to the OldOrder
field and the database will generate the New ID value.

hth,

 

[SBD]

Another thing you need to realize here is that an
autonumber's ONLY purpose is to provide a unique ID to a
record. It should not be used for sequential numbered or
any type of situation where specific numbers are needed.
You should be using other fields for that.

 

[Tim Ferguson]

I created a Db with and ID Autonumber field. But the user
wanted to add in some old records, so I changed the
autonumber to a "number" field so they could in put their
own numbers. Now I want to put it back to an autonumber

As you have seen from the other replies, you can't go back. The easiest way
is to copy the table structure only to a new table, change the field back
to autonumber, and then use an append query to put all the old data into
it. Finally, you'll have to redo all the relationships, get rid of the old
table, and rename the new one back to what the old one was. Do remember to
take a lot of backups first!

For future reference, there are two points to add.

The first is to reinforce Scott's point, that if you need to have control
over an AN value, then you probably should not be using an autonumber.
There are lots of ways to generate numbers according to the user's rules
when you need to.

That having been said, there are times when you need to poke a record into
an AN sequence: for example, replacing an accidentally-deleted record
(although even then, prevention is better than cure..<g>). In such cases,
you can use an append query to insert the record like this:

INSERT INTO MyTable (MyANField, MyRequiredField)
VALUES (2045, "Eric")

or build it in the query grid, filling in any required fields as you go.
Since it should not be happening very often, the fact that it is a bit
roundabout is not a problem.

Hope that helps


Tim F