Convert Access to MySQL

Hi,

Just wondering if anyone knows of either a easy way to convert a access DB (and data held within) to a MySQL DB or a 'free' program that will do this for me?

Cheers

JP

 

I don't know how feasible this is for you; but you could just export to .CSV's and then import into MySQL. (You'll just have to recreate the structure)

 

Well, I know nothing about MySQL, just the DB I'm using is starting to flag as it's getting a bit of a pounding lately so need to get it transfered over to MySQL.

I'm using a web interface, myphpadmin which does not look like I can actually import anything.. so I'm thinking it don't matter now

 

A while ago I found MySql Administrator (downloadable for desktop). If your host allows direct connect, you can import the data into the MySql Administrator and upload to the host. I've done work with mine transferring from Sql Server and XML to MySql.

I believe you can check it out at http://dev.mysql.com/downloads/administrator/1.0.html

 

Working on past experiences, and a failing memory: I received a db I purchased, and i belive it was in access.

I looked at it's design, somehting not very normalized but workable and created tables in MySQL. I used those new tables and column names and tried to match them up to the data, I was ignoring the Access column names since what I did complete voided what they were. I created an SQL insert statement for the new MySQL tables based on the Access data.

I then cleaned up and deleted the Access columns I didn't want, and then did a comma delimited export, and had included empty information too. That turned the data into a text file, with each record into a row, with data fields seperated by commas.

Now with the SQL statements and the comma delimited informaiton I had to meld them into full insert statements.

I created a simple javascript based html page (yes I know in hind site I should have done this with php instead, but I wanted quick and minimal testing), a large textarea a the top and a large text area a the bottom.

The top text area had the data pasted into it. Say a row looked like: "00030, Santa Cruz, California, Pasific". I was owrking on a zip code database with all of us zip codes, it was big. With javascript initiated by a button, I grabbed each row, did a split and concatinated each array element into a insert SQL command.

Example:
row = split("read_in_row");
command = "INSERT INTO table(zip_code, city, state, time_zone) VALUES (\"" + row[0]+ "\",\"" + row[1] + "\",\"" + row[2]+ "\",\"" +row[3] +"\");";

then appended command to the output textarea's value, and kept looping till the input textarea was fully processed. With the output I just ran it as SQL in my MySQL CC and done.

Now it was a one time deal, I had no intenetions of running this over and over, so the sloppiness was ok, as long as it didn't require much of my time, and I was happy to toss the code when done. Also, I swear it was so easy the web page took like a couple minutes to build and test. Plus I took the output and tossed it into a file for later use on more than one project.

Does this confusing mumbo-jumbo help? Things I learned, although Javascript on an html page worked, it was slow as heck, so I started the process and walked away. if you want more zippy, I would use php and let a dedicated server do the plugging and chugging. Funny how after doing it, I had flash backs of COBOL in college and processing massive amounts of data in a flash.


hth,

tom

 

Check out http://www.softguru.net/

This is what you're looking for, but it's not free. I have a registered copy and it works great. I can't tell you how much work it's saved me.

Good Luck

 

Cheers for that, I have a funny feeling they wont but worth a shot

 

So whats the e-mail address I send my DB to then?

 

haha

Seriously, tho, I'd be happy to convert it for you. Let me know.

 

thanks for the offer.

I've downloaded the trial version to see how it handles, I've just got in touch with my host as they do stop external connection but they will allow me access for 1 week from my IP address.

JP

 

This isn't exactly what you are asking for....

http://sqlfairy.sourceforge.net/

This converts just the data structures of the old database to the new one. but moving the data over should be the easiest part.

 

Thanks, moving the data over may be the easiest part if you know SQL, my knowledge is limited and I don't know.

The MydbConverter does everything as I've tried with the demo version and it worked very well. The only down side is it's $42 and I will only use this once (I think).

Thanks for the suggestions everyone.