AutoNumber edit

[AlkieDropout]

I'm wondering if you can help me.

I'm trying to write a new interface to an old system written in VB so I'm starting again in C++.
I have however hit a snag while i'm hooking into the old, poorly designed database.

Table A - "Repairs"
Primary Key = ID -> AutoNumber
.....

Table B - "Completed Repairs"
Primary Key = ID -> AutoNumber
.....

Once a Repair is complete it is deleted and moved into Complated Repairs. The problem is both tables have used AutoNumber is the ID instead of a Numeric on the completed table.

I cannot redesign the tables so I need to find a way I can use the ID supplied by Repairs and not the ID given by Completed Repairs.

I don't know how this was done in the old VB system, every time I try and manipulate "ID" in the complete repairs table I just get errors saying it's not allowed.

i.e.
Repairs
11 - "McDonalds"
12 - "Burger King"
14 - "Pizza Hut"

Completed Repairs
10 - "Pizza Express"
13 - "Dominos"

I should be able to delete "12 - Burger King" from Repairs and add it into Completed Repairs

Repairs
11 - "McDonalds"
14 - "Pizza Hut"

Completed Repairs
10 - "Pizza Express"
12 - "Burger King"
13 - "Dominos"

but what I get is

Completed Repairs
10 - "Pizza Express"
13 - "Dominos"
14 - "Burger King"

That 14 should be 12 but I cannot change it.

[Thomas1965]

Could you maybe create and add a new table?

[AlkieDropout]

I can't do anything to the existing tables as this would have a knock-on effect on a different system which uses the same data tables.

I also can't add new tables for the same reason.

[Thomas1965]

What do you actually need the old id for?
Would it be an option to store the old and new id in a separate file?

[AlkieDropout]

The problem is the old programmer uses the ID as the RMA number so it needs to be preserved. The big issue is other applications need the data in the old format and any new data has to exist in the old format unless i rewrite everybodies systems.

I just cannot see how the old programmer preserved the IDs in both tables when they are both AutoNumber fields. Maybe some VB black art.