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.