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