Database developer's challenge - how to combine data from several databases?

[sbasak]

Consider this problem.

I have two tables (say in an Access 2000 database) - Bank and Account

Bank has following fields
No (auto number)
BankName
User

Account has following fields
BankNo (foreign key Bank.No)
AccountNo
User

Now, say there are several databases (mdb files) for several users (database table structures are same but data are different).

At one point of time, I decide that I shall combine all users' records into single database (mdb) file.

Now my question is, how do I combine all the records?

Because, in first mdb file, there are banks no. with 1,2,3 etc. and accounts depending on them. In second mdb file also, there are banks with no. 1,2,3 etc. Mere copy pasting won't do, because relational integrity will be violated.

I illustrated this problem with just two tables. But in my actual problem, there are 10 tables with relations among all of them.

Could you please help?

Thanks a lot.

[Dinz]

If u want to combile all this tables into one, there is no other way u have to change the primary keys...................

[Muddy]

Why not a table in a new mdb with all possible fields. Then use ado to read existing mdb's and write into new one. you could just throw in some text to flag fields that dont exist for entries.

[sbasak]

I devised following crude way:

1. Open all mdb files
2. Change all main primary keys (eg. BankNo) so they become unique in combined mdb file
3. copy from source and paste to combined mdb file

The process will be quite tedious and inefficient.

Is the any other way out?

Well, if I assume that this condition may happen while I'm still at design phase, how can I design my database to handle this problem?