Retrieve from Archive causes PK damage?

[wendyp]

I have a large and complicated system and am adding an archival system to keep the .mdb file small. I archive all the customers, their orders, invoices, products, transactions and service calls if they have been inactive for 2 years.

I have the archival system working just fine.

Now, one of these archived customers calls and needs a service call. I want to retrieve them from the archive and move them to the current customer table (along with all their associated records).

I wrote the code to do this and it also works fine.

BUT - it damages the PK index.

For example: my last customer PK id is: 12980, so the next time I add a record, I will get a PK of 12980.

If I do an INSERT to retrieve the customer record from the archive and it's PK is 800. The next customer I add to the table gets an ID of 801.

Very difficult to track down, because since the majority of those 800 records are archived, there are no key violations and new customers are being added with an id of 801, 802, 803, etc. until we run into an old customer who might have placed a recent order and so wasn't archived. Then there are key violations because it tries to add record 804 and it already exists.

The only way I can think to work around this is to add a new customer record with a record set (can't use INSERT because I can't get the last added id) and copy the old information over. Then change the customer ID in all related records as I put them back into the current system.

I really hate to do this because my relationship structure is:

tCustomer
tOrders
tInvoices
tOrderItems
tProd1
tProd2
tProd3
tProd4
tProd5
tTransactions
tServiceCalls

And tOrderItems also has a relationship with itself (in case a product is an add on to another product), and with tServiceCalls so that we can see what product the service call is for.

So you can see changing all the primary keys for all the related records is going to be a nightmare.

Anyone have any clever ideas for this situation?

Is there a way to turn off the primary key, manually check to make sure I'm not overwriting any records and then move the records back and then reset the PK and have it reindex? (of course, without have exclusive access to the database at the time (are you laughing now?))

/Wendy

 

[BNPMike]

It looks like you are using an Autonum field as a business key. I don't recommend this. It's good as an alias (to use a Codd term) but if you need to have numbers that follow a particular rule then you should control them yourself. Normally if you insert a record with its own autonum value, the values will start from the next number but only if that's above the number the table would have issued next if that rcord hadn't arrived. You seem to be experiencing the reverse. I'd try it on another system to check yours isn't corrupted. You should be alright bringing old stuff back in.

You have various options. One is to keep all your customers and just archive the detail records. That will maintain you key integrity.

Personally I think if you've got enough customers that you really need to archive data then you should be getting a real IT department to do a more enterprise solution.

I archive data in the way you seem to be doing but you can just as easily use views (ie queries) to exclude what you don't want to see. I doubt the Jet database needs you to archive records.