Importing data from Access to MySQL

Hello experts!
At work we have been using an MS Access database to track employee hours. This database was build in Access 95 in 1996. I have been tasked with creating a new system that isn't inherently internal (meaning our other offices would not need to remote in to use it). I've decided, based on my strengths, to use PHP / MySQL to do this.

Now the first problem is trying to copy all the data from Access to MySQL so we don't have to continue to maintain the old system (if we can avoid it). I'm unsure the best way to do this as I'm not very versed in Access and the queries running it aren't following general SQL standards. To make matters worse, the Access database wasn't even developed to follow 1NF so I can't in good conscience use the same schema as Access. Even worse there are about 70 tables in the Access db, however some of them are empty, and there are series of TableName1 TableName2 etc so I'm not even sure which ones are being used! Information is duplicated in so many places I can't follow the logic of the schema (if there is any)

My question to you guys is this: What is the best way to copy this data into the new system? How would you go about analyzing an older DB like this to make sure I'm copying over the correct current data?
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You should be able to export the access to a .csv file.  That is importable by my sql.  Use phpmyadmin to experiment with the option settings and experiment.  

Jeffrey CoachmanMIS LiasonCommented:
Well if the data is not normalized, then you can import it, but you still have to "Fix" it.

There is no set way to "fix" un-normalized data.
Fixing un-normalized data will require *You* to understand what this data was trying to represent.

For example, a separate custom system will be required to get the DB from "0NF" to 1NF.
Then another to go from 1NF to 2NF, then another for 2NF to 3NF...etc
Not counting all the exceptions and possible inconsistencies in the data.

In other words, with 70 tables, there are no easy answers here.

Let's see what other Experts may contribute.
DerokorianAuthor Commented:
My apologies for not returning sooner, I got absolutely swamped with work today.

@COBOLdinosaur The problem isn't the simple export, and the import, but rather the step in between where I have to alter the data from 0NF to 3NF

I know its going to require figuring out the data, I'm just not sure how to do that. I can't seem to figure out what tables are actually being used. For example there are 7 different tables that hold the similar employee information, with as few as 16 rows to as many 78. The queries are just mind boggling puzzling.

Another question is should I just pretend Access doesn't exist until after I get the new application working and then try to figure out how to the Access data fit into the new system or should I keep the Access data in mind in developing the new system?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Jeffrey CoachmanMIS LiasonCommented:
Did you read my post above...
This will not be easy.

Also not that we don't have any access to your data so this makes it all the more difficult.

How much do you know about Normalization...?
Considering the amount of duplicate data, I would develop the new application and the DB design to support it.  Treat the access tables a a data store.  Testing the new application an db will required a small sample of the data and in developing that test data from the data store you will have an understanding of what kind of conversions will be necessary, and with the testing you will be less likely to make a mistake for the major conversion.

You may find that in some cases you bring the raw data into temporary mysql tables and then use mysql facilities to do the conversion into the live database.  As already said this is not going to be an easy conversion.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DerokorianAuthor Commented:
@boag I know quite a bit about normalization I like to think. Its kind of a pet peeve of mine. In fact my DBA friend tells me all my projects have followed 3NF completely. And since I design my DBs before I start trying to work with any data, I'm just having a hard time following the so called "logic" of this older database. Was hoping there was kind of a general solution that would need modification to fit my needs (like analyze each table, and  fix columns with multiple values into their own tables, or whatever)

@COBOL So basically I shouldn't build a freestanding system and try to make the old data fit, but rather somewhere in between? I didn't think about using temporary tables in between. That will make it much easier.

Last question: I thought about duplicating the Access DB to test on, such that I remove tables with similar names and test if it still works or not. Does this sound like an ok approach to at least try to limit the amount of redundant data I work with? I worry I may not test every aspect and may lose real data.
Yes it makes some sense to run a test series on the old datasets, and see if you can remove some of the redundancy.  Anything that reduces the number of tables is worth trying.  The thing is you want to run a test series first to give you a baseline of results.  Columns that looks redundant might not have all the same same values; that is an inherent risk with duplicate data in any system.  Sometimes the same column in two different tables are out of synch.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.