Complex(?) Copy Operation - Best Practice?

[Chris]

I am writing a function to copy records from one table to another, and
would like any suggestions on the best methods I can use. I would
normally use recordsets, but if there are any SQL solutions I would
like to hear them. Here is the problem:

I have four tables, T1, T2, T3, T4. T1 and T3 have identical
structures, as to T2 and T4.

A user will enter records into T1. Sometimes, the record will be
linked to one other record in T1 (there will only ever be singles or
pairs). If two records are linked, an entry is made in T2, storing the
IDs of the two linked records in T1.

Once the user has entered some information and is happy it is copied
from T1 to T3. This means the IDs are almost certain to change. What
is the best way to copy or create the appropriate link records in T4?

Usually, I would use recordsets to step through the records and update
the information as I go. Is there any other way to do this? Also,
would there be a better design to use? It would be difficult to
implement such a change, but I am willing to hear suggestions.

Thanks in advance!

Chris.

 

[TC]

I am writing a function to copy records from one table to another, and
would like any suggestions on the best methods I can use.

Probably not what you want to hear, but: you should generally NOT copy
the fields from one table into another one. This generally means that
the tables are designed incorrectly.

If you'd like us to comment on your table structure, I suggest that you
list the main fields in each of your tables. (We do not need their
types & lengths.) Also tell us the primary key field(s) of each table.

Note:

(1) *No SQL*

(2) *No example data* (unless it's very short & simple, not more than
a few rows & columns.)

(3) The primary keys are critical. If you don't know what a primary
key is, you need to stop right now, & do some research

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Chris]

Probably not what you want to hear, but: you should generally NOT copy
the fields from one table into another one. This generally means that
the tables are designed incorrectly.

If you'd like us to comment on your table structure, I suggest that you
list the main fields in each of your tables. (We do not need their
types & lengths.) Also tell us the primary key field(s) of each table.

Note:

(1) *No SQL*

(2) *No example data* (unless it's very short & simple, not more than
a few rows & columns.)

(3) The primary keys are critical. If you don't know what a primary
key is, you need to stop right now, & do some research

Hi TC,

I'm ok on the primay keys and so on thanks.

I would normally agree that this isn't the ideal structure, but I think
it is sensible here and a first glance doesn't show me a better way to
do it. Basically, the records are entered into T1 because they may be
repeatedly "posted" to T3, and a copy of what the user entered is
required for traceability. I realise I didn't put that much
information on the whole structure, but I was interested in what sprang
to anyone's mind, without me influencing them.

Back to the problem, I have done the copying using four recordsets, one
per table. A psuedo-code level breakdown is:
For each record in T1
Copy to T3
If there is a link record in T2
Check if a new link in T4 has been started
If not, Create one side of the link (in T4) with the new T3 record
ID
Copy the other side from the original link in T2
If it has, replace the side of the link that is pointing to T1 with
the new T3 ID
Move to next T1 record

To put it another way, the record is copied, then if there is a link
associated with that record it is copied, and the side with a new T3
record is updated. This means the new T3 record is logically linked to
a T1 record temporarily. This method is used as it allows us to make
half a link (which we must do - we do not know the second half the
link's new ID yet). When the T1 side is copied, the second side is
then updated, leaving us with a copy of the records and a new link
between them with the correct IDs in place.

All this is wrapped in a transaction to make sure any errors will not
cause problems.

As you can see, this isn't a straightforward task. I have worked more
with recordsets in the past, but I'm keen to flex my SQL, hence the
question. I hope the above helps someone who comes across a similar
problem.

Chris.

 

[TC]

Sorry, I probably can't help with that. Hopefully someone else will
jump in & make some suggestions.

Cheers,
TC (MVP Access)
htp://tc2.atspace.com
(off tomorrow)