?
Solved

Importing data from Access to MySQL

Posted on 2012-03-29
7
Medium Priority
?
318 Views
Last Modified: 2012-03-30
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?
0
Comment
Question by:Derokorian
  • 3
  • 2
  • 2
7 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 37782683
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.  


Cd&
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37783586
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.
0
 
LVL 10

Author Comment

by:Derokorian
ID: 37784622
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?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787532
Did you read my post above...
http://www.experts-exchange.com/Database/MySQL/Q_27654283.html#a37783586
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...?
0
 
LVL 53

Accepted Solution

by:
COBOLdinosaur earned 2000 total points
ID: 37788178
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.

Cd&
0
 
LVL 10

Author Comment

by:Derokorian
ID: 37788245
@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.
0
 
LVL 53

Assisted Solution

by:COBOLdinosaur
COBOLdinosaur earned 2000 total points
ID: 37788482
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.

Cd&
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question