Using VBA to combined tables, eliminate duplicates and establish relationship

[Jharrin]

So I know enough about Access and VBA just to be dangerous, any help on this would be greatly appreciated.

I am trying to combine data from 8 of the same database applications that reside on different machines. There are two tables I need data from in each of the databases which have a relationship established with three keys linking them togeather. I need to extract the data from those tables and merge all togeather into a single seperate database and maintain or restore the relationships in the tables with automation using VBA or some other method.

What I have done so far may be a little rough but has gotten me to the point where I have the table data combined into the seperate database using a series of UNION querys. The thought is to have all of the database files from each machine placed into a desiginated folder on a seperate computer that feeds a sharepoint system. When you run the database which has links established to each of the database tables in the folder, the tables merge the data without duplication.



The problem I face is that there needs to be a relationship between the final merged tables. The UNION Querys that I have built delete the tables from the previous merge to create the new ones which destroy the relationship I need between them.

The merged tables have 2900 records in one and 33000 records in the other. They have three one to many relationship established. I looked into the SQL APPEND but would then the need to evaluate for duplication when the merge occurs everytime.

Thx,
John

[Homegrownandy]

Are all machines on the same network? they should be using the same back end and same dataset where possible to avoid these situations.

However, Appending and validating duplicates is the right way to combine these tables.

[Jharrin]

No the applications are installed on laptops and the individuals are all telework. They were origionally designed to be standalone systems. Creating a split database in this scenario is not an option for these applications.

[June7]

Think I had a similar situations.

In one a standalone db distributed to project remote site. Records entered, file returned, records imported to master db. Duplication not an issue as each project was independent from others. I designed this db and decided not to use autonumber PK.

In another I was provided files from a contractor and required to merge. These used autonumber. Importing to maintain the relationships was a challenge. However, again each file was independent and duplication not an issue.

If your users are editing copies of same data, this is compounding the challenge.