Data migration from old tool to new tool

[SachAccess]

Hi
First, I do not know if it is called data migration. I might be using in-correct term, pardon my ignorance.
I will try to explain my scenario with the forum, that might be more useful to understand.

There are two MS Access DB files, we can refer these as ‘tool’ too (both has back-end file).
Both performs same set of actions (or at least supposed to).
One is existing tool, second is new tool, build on the base of old existing tool.
Old tool is in use on the production from couple of years.
New tool is build using dummy or very old data as reference.

Now we are trying to replace old tool and use new tool.
However, all the ‘till date’ data is with accessed by old tool with the original back-end.
At the moment I do not have original back-end file.

Number of tables and table structure is almost same in the UAT back-end file I have created and the original back-end file.
But there can be some difference in few of the tables, few additional fields might be present in tables from the UAT back-end file and vice versa.
Few additional tables might be present in the UAT back-end file which are not present in the old existing back-end file.

Main motive is, newly build tool should get linked to old existing back-end file and work smoothly.
How do I achieve this. I have never worked on this kind of task before.
What are the points that I should check, what precautions I should take. How do I approach this task.
How can I compare old tables with new tables.
What are the risks, challenges, check-points I should follow.
Can anyone please help me in this.

 

[Ranman256]

You can link in the old tables (via odbc ) and have them like: tCustomers_Old, etc
then you can run queries against both tCustomers & tCustomers_Old

 

[plog]

Number of tables and table structure is almost same in the UAT back-end file

That is equivalent to saying 'The new back end is different from the old back end'. To solve this, I would build an import process:

Take a new copy of the old back end and blank copy of the new back end and build a set of APPEND queries that move the data from the old back end to the new back end. Once everything is moved, test your reports and forms to make sure they have the correct data and still work properly. Once you have verified that--clear out the new back end tables and do it again--this time document the process and make it as easy and as fast as possible to do.

Once you have your import process down cold--you need to take your live database down--kick everyone out, tell them you need however long you need to move the data. The do the import once more but this time for real and put the new database into production.

 

[Pat Hartman]

As @plog suggested, it is important to build a procedure that you can run by pushing a button. The code will run a bunch of queries in the correct order to load the data into the new BE. Unless you are moving millions of records, I would make sure that all the relationships and indexes are in place. It slows down the load but it finds problems early rather than late.

Create the clean template by opening a new database and importing all the tables and relationships but no data. Then each time you try the migration, copy the template and use the copy rather than dirtying your only copy. This process will almost certainly take more than one try so make it as easy on yourself to rerun as possible. Remember when you are loading data that you have to mind the dependencies and load the data in an appropriate order.

 

[SachAccess]

You can link in the old tables (via odbc ) and have them like: tCustomers_Old, etc
then you can run queries against both tCustomers & tCustomers_Old

Thanks for the help @Ranman256 . I did not understand this. Could you please explain if you get time. Have a nice day ahead.

 

[SachAccess]

That is equivalent to saying 'The new back end is different from the old back end'. To solve this, I would build an import process:

Take a new copy of the old back end and blank copy of the new back end and build a set of APPEND queries that move the data from the old back end to the new back end. Once everything is moved, test your reports and forms to make sure they have the correct data and still work properly. Once you have verified that--clear out the new back end tables and do it again--this time document the process and make it as easy and as fast as possible to do.

Once you have your import process down cold--you need to take your live database down--kick everyone out, tell them you need however long you need to move the data. The do the import once more but this time for real and put the new database into production.

Hi @plog thanks a lot for the help. Have a nice day ahead.

 

[SachAccess]

As @plog suggested, it is important to build a procedure that you can run by pushing a button. The code will run a bunch of queries in the correct order to load the data into the new BE. Unless you are moving millions of records, I would make sure that all the relationships and indexes are in place. It slows down the load but it finds problems early rather than late.

Create the clean template by opening a new database and importing all the tables and relationships but no data. Then each time you try the migration, copy the template and use the copy rather than dirtying your only copy. This process will almost certainly take more than one try so make it as easy on yourself to rerun as possible. Remember when you are loading data that you have to mind the dependencies and load the data in an appropriate order.

Hi @Pat Hartman thanks for the detailed procedure. Am going through it. Have a nice day ahead.