Replace a database with a new one while keeping the relations of the old one and its properties

Question

Hello, as mentioned in the title I have encountered a problem with an access file, following a duplicate which we realized too late, I find myself with two access files with two tables linked together that differ only on the end (about 500 different lines). First of all I would like to know if it is possible to merge these databases without duplicating them while keeping the relationships and properties? I tried to export the concerned tables in excel, I succeeded in obtaining databases in which there are all the values and no duplicates, but when I import my new database after having deleted the other one, there is no more relation and I don't have any more the properties which were present (example one of my column was in AutoNumber). I don't know if it's very clear, hoping that someone has a solution.

Answer 1

You should be able to create a link in one of the files to the duplicate table in the other.  Then create an 'append' query which inserts rows from the linked table into the local table.  If the primary keys values for the rows which are duplicated in each are the same in each table you can join them on the keys.  When you execute the 'append' query any rows with duplicate key values will be rejected, and only those with different key values appended.  If the tables in each file both have autonumber primary keys, however, the value will be arbitrary, and there will ne mismatches when the 'append' query is executed.  You will then have to try and identify a candidate key on which the rows will match.  This is likely to comprise multiple columns, possibly all columns bar the autonumber key.  The candidate key should then be included in a single unique index in the local table.  This will cause any duplicates from the linked table to be rejected when the 'append' query is executed.  Exclude the autonumber key from the 'append' query.  The new rows inserted will then be given new autonumber values.