Same access database, one has more data

[cfry315]

I was working with an Access database. While I was on vacation the
person entering data changed part of the database (can't figure out
what). I have the old copy still saved on my computer. What I was
wondering is if there was a way to take the data she entered and add it
into the old file. The tables and everything are identical except for
the added entries she did while I was away. Also some fields we have
entered from the beginning, so those weren't changed, just the fields
we are entering into as surveys come in (ex: id, survey #, address all
entered at the beginning, answers to q11, q6b, etc are entered as
surveys come in). I don't know if this makes sense at all but
basically all I want is to be able to use my old database file, but add
in the new stuff without entering them all one by one by hand again.
Any suggestions would be greatly appreciated. Thanks!

 

[John Nurick]

Should be possible. Try this with COPIES of the new and old versions so
there's no risk of doing permanent damage:

1) Open the OLD database (the pre-vacation version).

2) Make sure that every table you'll be working on has a primary key
that will ensure that duplicate records cannot be imported. If
necessary, create primary keys.

3) Use File|Get External Data|Link to create new linked tables to each
of the tables in the NEW database (the one the other person worked on).
Give each of these linked tables a consistent name, e.g. if the original
table (in each database) is tblAnswers, call the linked table
linkAnswers.

4) Create an Append query that gets its data from one of the linked
tables and appends it to the corresponding old table. Run this query to
append the records; you'll probably get an error message about key
violations, which of course refers to the records in the NEW database
that are already in the OLD database from before your vacation.

5) Repeat for all the tables. If you have set up relational integrity
between the various tables you may need to run the queries in a
particular order (e.g. with a 1:M relationship you'll need to do the "1"
table before the "M" table).

6) Then check that everything has come across. E.g. make sure that both
tables in each pair have the same number of records *and* use the Find
Unmatched query wizard to create queries that will find discrepancies.

7) Finally delete the linked tables and the queries you created.