Import table data from original database to a slightly updated and modifed version

[Modify_inc]

I’ve been making small changes here and there to my database in Access 2013. I have left the original database in place and it has acquired many new records from the last month. I’m ready to start using the newly updated design version of the database, but I do not want to have to type all those new records into the new database to get it up-to-date. There has to be a way to import the data from the original database to the newly updated one. Most, If not all of the fields have the same names, but some of the fields have changed, like to Combo boxes. I removed two or three unneeded fields, and added two or three. I understand the new fields will need to have data enter to them, but the remaining would be redundant and inefficient if I have to reenter all the recent records again.

How do I import the data from the original two tables to the new updated database? I only have two tables and they use a One-to-One relationship in both databases. The table’s names are different, but as I mentioned earlier, the fields are mostly the same. Do I need to import into Excel, and then modify the data slightly, and then import into my new database?



What is the best way to accomplish this?

Thanks
Mike

[June7]

How many fields and how many records?

Can copy/paste.

Can set up links to the old tables and use INSERT SELECT sql action.

INSERT INTO tablename(field1, field2, field3) SELECT field1, field2, field3 FROM oldtablename WHERE some criteria here;

[John_G]

Hi -

The best way is probably to link to the tables in the old database (no need to import them), and then create APPEND queries to transfer the data from old to new. The APPEND query design allows you to specify which fields are copied to which, and the names do not have to be the same. The number of fields in the old and new tables does not matter either - you only append the fields you need.

I don't know how - or even if - transferring to multi-valued fields will work. Many experts recommend they not be used at all, because they can lead to all sorts of problems later on, and they violate the principles of proper relational database design.

If you have defined relationships between your new tables, it might be easier to delete them, import your data, and then re-create them. Be sure to back up you new database before doing any of this!!

HTH

John