Split database changes

[hiwelcome]

I have a split database where I've distributed the front end out to users to enter data and append records to the back end. I need to add a couple of fields to some tables which will be part of the composite key. My question is, instead of having all users append their current records and having some downtime in which I make the changes to the tables, queries, etc. and redistribute a new front end, is it feasible to make a copy of the back end, make all of the changes, and build a query in the new back end to transfer all of the data over? The thought is that it would involve much less downtime for the users.

A couple of us feel that it's safer to just have everyone append, then make the changes and redistribute, but this other option would be more convenient if it is possible and doesn't cause problems down the line.

 

[Ranman256]

you said add 'fields'. You cant really copy it over. You need to alter the prime table.
It only takes seconds. They cant be down that long?

 

[CJ_London]

depends how long it takes to implement the changes but your suggestion will work. Users will still need to be off the system from the time you start copying data across until the new copy has replaced the old one

 

[hiwelcome]

Ranman, that's the first thing I need to do. But I'd also have to edit the front end tables (they enter data into the front end tables, then the append query runs front to back), and also edit queries that will be directly affected by the changes. Plus I'd like some time to test everything to make sure it's all working.

CJ, I made copies just to see what happens. The append won't let me copy the original back end to the copied back end table, I assume since the structure is different (2 new fields). So I'm guessing I'd have to edit the back end to match, which I can't do while in use. I guess there's no way to avoid the downtime?

 

[The_Doc_Man]

The solution for your test case and test back-end is to just re-link the test version to the altered back-end versions using Linked Table Manager. The new fields will become available in the table definitions on the FE and you could then adjust any relevant queries.

However, you have a larger issue. If you are truly adding fields to the tables and they will become part/all of a new composite key, you must create and populate the fields as "ordinary" fields BEFORE the keys are declared (because otherwise you'll get a Key Violation since all the keys are equal - to null). But if your new fields contain duplicated values, you'll STILL get a Key Violation and won't be able to assert the new fields as your compound PK until you resolve the duplicates.

 

[hiwelcome]

Doc,

Thanks for the heads up. To your second point, the key as it stands now is two fields. We need to add two fields to the key. Example:

Currently:
001/Apple
001/Banana
002/Banana

Well it turns out that we can have two instances of 001/Apple, so fields are being added to the key to differentiate further:

001/Apple/100/X
001/Apple/100/Y
001/Apple/101/X

So the two new keys will be one number (100, 101 in the above example) and one text (X/Y). Are you saying I'll get a key violation if I add the two new fields to the key if there is more than one "100" or "X" in the new fields? Because there certainly will be. Or if each set of four is unique, am I ok?