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

[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.

 

[Martin_M]

Many thanks to you all for taking the time to reply. I'm seeing a few general trends that make logical sense.

I've been doing my best to document everything so I think I'm in a good position (theoretically) to complete the data migration. I'm also in the fortunate position where there are semi-regular periods where the DB is not written to and I have comprehensive backups of the existing database so I have room to test before the new DB goes live.

Also I hope when you say you are not doing any physical copying but insert queries.

Haha, good catch! I'm somewhat of a neophyte so I'm still (hopefully infrequently) using incorrect terminology. Yes, I'll be using queries.

Thanks again, everyone.

 

[DickyP]

My only experience of this as a large change with lots redundant feature/data etc was that we stopped an drew a functional diagram of the existing system showing each screen from the main menu onwards and the passage/calls etc. Each of the square boxes that represented screen was annotated with a business and functional description. This identified most of the redundancies and unused aspects and let us define what we wanted to do. I might add that this was on paper long before anything was created as code/objects etc.

And yes, it was long and boring but saved a lot of time an anguish in the end.

 

[RonPaii]

The nice thing about having a running production application warts and all is you have a good staring point for a system requirements document. It also provides the ability to compare functionality with your new application during testing. Even better if you can encode that functionality in unit tests.

 

[CJ_London]

about 20 years ago I was involved in migrating an access system for calculation of sales commission for around 600 sales people. It used to take about 1 1/2 hours to create 600 reports and email them to each person with copies to managers. The new boy on the block decided that access was not a suitable application for this purpose (primarily potential lack of support) and to make his mark, got a budget of £1m plus around £70k/month for licensing to migrate to a 'corporate' system. The only real benefit was that sales people would be able to log on to the system to see their current commission status (something they used to do by emailing the commissions team). On first launch, the new system took nearly 2 days to generate 600 reports - during which time nobody could do anything. They eventually got it down to around 8 hours. The real irony, salespeople continued to email the commissions team for updates - and about 3 years later the new system supplier went bust. The new boy in the meantime, moved on and up.