Move Data From Old Table Structure to New Table Structure

[crhodus]

I have made changes to a table for a client. I've had to add additional fields, modify properties of certain field (such as Allow Zero Length), add input mask for phone numbers, changed the length of field size ( Increased some and decreased others), etc....

I now have my new table structure. What would be the quickest/easiest way to move their current data from the "old" table structure to the newly modified table structure?

Thanks,
crhodus

 

[The_Doc_Man]

If the tables are in the same MDB file, I might suggest Append queries. It would also be possible to bind to an external table. There is a way to define a table link to an external file, and that file could be another MDB if it needed to be.

As an alternative for when the tables are not in the same MDB file, you might export the tables to .CSV format and re-import them to the other .MDB file using the import wizard. That would let you choose the names of the fields to which you bound each column.

 

[DCrake]

Simple Software Solutions

Have you removed any fields from the new table? If so, you will need to perform what is called Gap Analysis. ie what is in the new table that is not in the old. What is in the old table that is not in the new.

Will the changes in the formatting of specified fields have any effect on the existing data. You said you reduced the field size in some instances, will this mean that existing data be truncated, lost?

If you changed the data types of certain fields this may affect and referential integrity in force or any existing relationships.

Have you set/cleared any validation routines on fields?

Run the migration of a temporary mdb to analyse the effects before performing the migration on live data. Get the owner of the data to sign off the changes prior to migration. This way if data is lost you back is covered.

Better to safe than sorry.

CodeMaster: