How can I Manually Change Access Primary Key ID Fields ?

[darthskeeter]

I accidently deleted about 60 records from a database. I was able to
recover the records from an archived copy of the database but there is
an issue with the primary key values now.

The primary key (autonumber) field is used to generate a Spare Part #
field in the records. The items I've generated are already in use. So
when I copied the data from the archived database into the current
table, it just incremented the primary key autonumber field to a new
number.

Example:

Before I deleted records:
ID (Primary Key) Inventory Key
220 D-220
221 D-221
222 D-222

After I deleted records and copied back in from archive:
ID(Primary Key) Inventory Key
1337 D-220
1338 D-221
1339 D-222

If I change the primary key from Autonumber to Number, I can manually
change the "ID" entries back to what they used to be, but then I can't
change the field type back to "Autonumber".

I'm not an access whiz, so I was willing to manually change each ID
entry, but if there's some fancy SQL or other way to do it maybe I can
muddle through that... any ideas ?

Any help would be greatly appreciated.

 

[Ken Snell \(MVP\)]

You can use an append query to assign specific values to the autonumber
field by mapping the field from the archived table to the autonumber field
in that append query.

 

[Jeff Boyce]

I believe I understand that you want to change the values, but I don't
understand why.

An Access Autonumber ID is designed to be a unique row identifier.

It appears you have both a unique row identifier (your [ID] field) and an
[InventoryKey] field (e.g., "D-220").

Why does the value of the [ID] field matter?

(yes, I see that originally the InventoryKey/SparePart# matched the [ID]
field, in part, but why is that important ... that is, what business need
will be met by doing this?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/