Transfer Data From Old Database to New Database

[billgyrotech1]

Hello,



With great help from everyone here I have a newly created database. My old database has some different field names but is essentially the same information that I would like the data to transfer to the new database. Both are split and we are using Access 2013.

I would like advice please on the best way to accomplish this. I suppose I can export to Excel and change the field names so they match but I was hoping to accomplish this within Access.

Thank you for any help or advice,
Bill

[orange]

Bill,

You're working with a development environment database right? Or at least a copy of an operational database?
You are not repeatedly adjusting production?

Some ideas:
You can link to the tables in the old data base and create queries in the new database to append/update data in existing tables in the new database.
You could export the data from the old database to external files with the names you need, then import that data to your new database tables as required. You can adjust the names as needed in the query(s).

There may be other options. The bottom line is to create a test set up and try an approach and see if it works as you expected. In these types of scenarios --inserting/modifying data with older data from different sources - ALWAYS design a mock up/test situation and try it. BEWARE of typos, names/spelling, data types, NULL/empty fields, field order....etc.

Good luck.

[billgyrotech1]

Thank I will try to be careful and opt for using Excel as a transition then. This way I can change the column headers to match the new database.

Cheers!

[Uncle Gizmo]

You should heed the warning about not doing anything to your production database.

I would also add that one trap you can fall into is to move the data and everything looks fine, but then you will notice that one record that is supposed to be associated with another record is incorrect.

It is very important to have some sort of system for checking this, I generally make some dummy records which actually state something like this record number links to that record number in a different table.

The particular situation I'm referring to is if you have a main form with a subform, the main form table associates the records in the subform with a reference to a particular field which carries over the record number from the main form.

However when you copy records from one database to another database then you can have a situation where the ID assigned is different from the original.

The solution is to make sure you copy over the ID as well, and then run a query to change any records that refer to that ID.

The basic problem is you cannot change an auto number value, and the solution is to change any record that had the old autonumber value assigned to it to the new autonumber assigned to it..

Sent from my Pixel 3a using Tapatalk

[billgyrotech1]

I created an Excel workbook that has the correct IDs for all the tables now. What would be the next step to get the data into the new database?