Multi Table Append

[Fizzio]

I have 2 identical databases on 2 sites storing patient information but now they are networked, I can now access a shared BE on the network. How can I append all the data from one BE to the shared BE when I have linked tables eg tblPatientInfo linked to tblReferralInfo via PatientID, tblReferralInfo linked to tblContactInfo via ReferralID. Referential integrity is enforced with cascade update enabled but I'm not sure where to start to ensure that the links remain valid.

 

[llkhoutx]

I think that you're going to have a problem with indicies. Each of your separate databases will have many of the same indicies.

Your going to have to resolve that problem first, by rebuilding indicies for one of the databases. Yeah, that's a mighty task, especially if you use a lots of indexes in multiple tables.

You have my sympathy.

Once you resolve the indexing problems, merely append from the end of each referential link, that is, most remote table first.

I don't think that you have a potentially very difficult task.

 

[Fizzio]

Cheers llkhoutx

I suspected there was no easy route but I do not fancy inputting all the info again. What is the best way of changing the autonumber ID in the first table and so on so that I can do a simple append to all the tables.
I am aware that I will have to do this for all tables that have a primary key and so will have to perform a renumbering process on all the tables.
Any help / advice is greatly appreciated.

 

[SeatrendSam]

What is the best way of changing the autonumber ID in the first table and so on so that I can do a simple append to all the tables.
IMHO - I have had to do this a couple of times. Here is what I did. The table you wish TO APPEND: table b.
The table to be APPENDED TO: table a.
Get the highest index number from a (lets say 1000).
Go to table b and sort a-->z on the index column.
Insert 1000 BLANK rows in the begining of the table.
Delete the index column.
Add a NEW index column with auto numbering on.
Delete the 1000 blank columns. Your new index count for the first colum should be 1001. Now you are ready to append it to table a.

Hope this helps,
Good luck.

If you find a more efficient way, please let me know. would you?

 

[Fizzio]

Cheers SeatrendSam.
Never thought about doing it that way. I'll give it a go.

 

[SeatrendSam]

You are most welcome.
I hope it helps.

 

[llkhoutx]

Sorry folks, I've been locked out.

SeatRendSam's solution is great.

However, be sure that you address the keys being destroyed that are in other tables. They also have to be modified. That's the monumental problem.

I have stopped using autonumber fields for reasons similar to this. I have a function which builds sequential numbers for indicies for the table name argument. This method makes development eassier too.

If anyone is interested in myu referenced function, let me know and I'll post it.