Full rebuild; migrating old tables and lots of other fun stuff. General pointers requested. (1 Viewer)

[Martin_M]

Hi everyone,

Over the past few years I've been maintaining an Access DB that has been used since around 1996. It's was originally designed for a very specfic task of recording measurements but has had quite a bit of feature creep over the years before it became my responsibility.

It generally works in Access 2016 but it has a lot of non- (un?) normalised data, lots of unnecessary redundancy and quite a few weird bugs that I've been unable to resolve.

I've done a full audit of all of the table fields to identify what datatypes they're supposed to contain, built a test db in Access2016 with the expected functionality and am in the process of testing migrating data from the original DB to the test DB.

However, I'm not sure what is the optimal way to perform the data migration. The old tables have field names with spaces, reserved words (Date) and special characters so I've created alternatives in the tables of the test DB.
So, I'm at the point where I'd greatly appreciate advice on migrating data. My plan is to:
1. Export the table to the test DB.
2. Copy the data from the old table so that the contents of the, for example, "Date" field ends up in the "Measurement_date" field in the new table.
3. Create validation rules.
4. Fix any data that does not pass validation.

Is this the optimal approach? If so, is there best practice for step 2? I've seen VBA and SQL solutions but on testing I've not had much success.

Many thanks for taking the time to read this.

 

[tvanstiphout]

I typically create a new VBA module with procedures that perform the conversion.
There is a Main procedure that calls the others in sequence. It is designed to be able to run multiple times as you perfect the conversion more and more.
It starts with "Clear out most new tables".
Some are just one-liners, to run an Append or Update query.
There is code to check the number of records appended. Is it what we expected?
Sometimes it's just easier to use a recordset and VBA code. It all depends on the data, and your skills.

 

[isladogs]

For steps 1 & 2, I would create a new blank database and link to the existing tables but not import them
Make a local copy of each table. Table structure only without copying the data.
Now modify each table as necessary fixing field names, adding validation rules etc.

Then create append queries to transfer the data to each of the new tables.
Deal with any validation issues preventing specific records being transferred.

When all is done, delete the linked tables.
Keep the original database as a backup in case of problems.

 

[MajP]

I would recommend that you have a field in every new Table called "OldPK". Then you can save what the old Primary key was. So when you create a parent table you can then update the child table foreign keys to the new primary key based on the oldPK. If you do not do this it will be extremely hard to link the child tables.
Keep these fields for a while so if you ever have to reconstruct/analyze something you can. May even want an OldFK field in the child tables too.

I would also create a data map table to use in code. But this is also helpful just as a reference to keep things straight. Only need to log fields that are new or different.

Old Table name
New Table Name
Old Field Name
New Field Name

If I had those two things, A field holding the old keys and a data map I could do what tom says and write pretty short code to do all of the updating.
Also I hope when you say

Copy the data from the old table so that the contents of the

you are not doing any physical copying but insert queries.

 

[plog]

Yes, sort of. Broad strokes--I don't do this once-- I build a process to do this and make that process airtight. Once the process is built it, I run it one final time and have full confidence the data will migrate succesfully because I have worked out the kinks and fixed all the data.

That means I make a new directory and copy in the OLD system and the NEW system, so that I am not working on live data nor the only version of the NEW file. Then I make a third database MOVER. I link all tables from OLD and NEW in it. Then I build all the APPEND queries and VBA I need to move the data from OLD tables to NEW tables in MOVER.

I move all the data using those queries to NEW and test that data. I build queries to give me counts, to make sure fields aren't NULL, numbers are numbers, dates are dates, etc. Everyway I can think the data might fail when I move it, I make a test query for it. These test might mean bad data is in OLD. You should fix it in the actual live version of OLD if possible.

Once all the data moves correctly in MOVER. I suggest you repeat the process--new copies of OLD and NEW, and use MOVER again a few times to practice and refine moving the data. When you are confident everything will work, then you can set up a time to do it for real.

When you do it for real, you let everyone know OLD is done and NEW will be up in however long it will take. That way no new data gets added to OLD in the mean time. Then you repeat your move process one last time--copies of OLD and NEW, run MOVER then use the NEW.

 

[CJ_London]

What I have done in the past when migrating data from one system to another is to create a migration document. This lists all the tables and fields in the new system together with all datatypes and constraints. Sometimes with performance criteria as well.

The old system tables and fields are then mapped to the new system. It often isn’t as simple as renaming since the old system may not be normalised so data in one table may be updated to two or more tables in the new. Or perhaps there is a fudge in the old system which is properly addressed in the new. Or the new system has a constraint of 50 characters whilst the old system had no such constraint.

You may also find there are required fields in the new system which have no matching equivalent in the old - so perhaps need to be populated with a holding value

The document can then be used as a map to determine best order of migration and any sub processes that may be required, either using sql or vba functions

Ok, it takes a bit of time to create and review but better to measure twice and cut once.

Over the years I have been involved with a number of migrations, not just from access to access but also from/to corporate systems such as CRM, HR, accounting, stock management to name a few. It is worth spending time at this planning stage, it will save time later

 

[RonPaii]

I would do the following.

• Start with a new accdb BE database
  • Configure it
• Build new tables fixing the normalization problems.
• Add a Function to import the old data into the new tables.
• Add a Function to empty the new tables to allow for another import during testing.
• Start with a new accdb FE database linked to the new BE.
• Test new FE repeat the emptying and importing of the new BE with production data until release of the new FE.