Easiest Way to Replace Data in a Table

[Guest]

I made of copy or our database so I could work on it. I've now completed my
work and would like to have this copy of the database become the live
version. The only problem is that the data in the tables are not up to date
because users were entering data in the live database while I was working on
the copy. The table names etc are still identical. What's the easiest way
to replace the data in the new database tables with the data in the old
database tables.

 

[Jeff Boyce]

If you are very confident you can update existing (old) records with your
new data (and only in that direction), try something like this:

* create a copy of the database you'll be updating (this is insurance)
* open the database
* use File | Get External Data | Link... to link to the tables you will
use to provide the updating info, in whatever database file that is.
* when you have links to all the (new) tables with the (new) info, start
creating queries...
* create a query that first shows the old table - old value and the new
table - new value (a SELECT query). Use any selection criteria necessary.
* when that SELECT query is working correctly, change it to an UPDATE
query -- use the new table/newvalue in the Update To "cell" for each value
to be updated.
* move on to the next pair of tables ...
* keep cycling until done...


Regards and Good Luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[j_beverly]

I didn't know it was such a complex process. I used a simpler way. If
there were no relatioships (except those defined only in queries,
forms and reports) I just deleted the old tables and imported copies
from the other database.
If I needed to preserve relationships, I deleted all records from the
old tables, linked to the tables in the live database and then ran
append queries to append all the records to the empty tables.

Did I miss something? Is there a reason to use the more complex
update queries described below?

Jeff Beverly

 

[Jeff Boyce]

Perhaps I misunderstood. I thought there were changes to both the old and
new tables, and the updates had to preserve the new info...

Glad you found a way!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[j_beverly]

....or it could have been my mis-assumption.

"tmc"'s original question stated "The table names etc are still
identical..." I assumend (maybe incorrectly) that meant there were NO
changes to the tables. If there WERE changes, then I understand the
need for your method. Thanks for the clarification.

Jeff Beverly