| T O P I C R E V I E W |
| robbear7 |
Posted - 10 March 2003 : 09:24:02
Hello everyone. I recently had to tackle the issue of converting my Access DB to mySQL. The forum I converted also contained a few mods, making the task that much more difficult. After a good deal of investigating, here is how I eventually tackled this process.
This guide assumes you have access to Microsoft Access and Phpmyadmin as well as already having an empty database created in mySQL and your config.asp file correctly modified. The process worked for me, but I cannot personally guarantee it will work for everyone, simply because it is not tried and tested except by myself. Perform this at your own risk. It is very important you start with a clean empty DB.
Step 1 - Visit http://www.convert-in.com/acc2sql.htm and download the <b>trial</b> version of <b>Access-to-MySQL</b>. The trial version is all we need.
Step 2 - Once you have downloaded Access-to-MySQL, run the program. The first thing you will see is the Database Conversion Wizard. Select <b>Store Into Dump File</b> and click next.
The next screen do not enter any information, simply click next.
Select the Access DB to convert in the next screen and the <b>Destination Dump File Path</b>. <b>Important!</b> make sure you select Convert <b>Table Definitions Only</b> before you continue. Click next.
It will create the sql file for you. That's it for this part.
Step 3 - Head on over to Phpmyadmin. Select your empty database. Click the tab <b>SQL</b>. We are going to run a query to create the tables in our database. Down below the query text box, you'll see the option <b>Or Location of the text-file </b>. Browse the the .sql file we created earlier from Access-to-mySQL. Click go. If all has gone correctly, we should have created the Tables and fields replicating our Access DB.
Step 4 - Open your Access DB in <b>Microsoft Access</b>. In the objects column, make sure you are viewing <b>Tables</b>. You should see each Table listed. Right click on the first table and select <b>Export</b>. You need to select the <b>Save as Type</b> as <b>Text Files (*.txt;*.csv;*tab;*asc)</b>. Now in <b>File Name</b> manually add .csv on the end. Click export.
On the next screen make sure <b>Delimited</b> is selected. Click next.
Select <b>Comma</b> as our field delimiter. Click Next.
Select the file destination and click finish.
Repeat step 4 for each table.
Step 5 - Now we need to browse to where we have all of our .csv files. Rename all of these files with the extension .txt, Windows may give you a warning about this, just ignore it. Once all of the files that used to be <b>.csv</b>, but are now <b>.txt</b> files, you can move on to step 6.
Basically, what we have done at this point is prepped our mySQL DB with the correct table structures and created some data files to import the data into our tables.
Step 6 - Open Phpmyadmin and select your DB. Select the first table in your list for that DB. Click the <b>SQL</b> tab.
Toward the bottom, we'll see a link that says <b>Insert data from a text-file into table</b>. Yup, you know what to do.. click it.
Once you have arrived at the new screen, you will have the option to browse to your datafile. Browse to the corresponding .txt file you exported from Access for that table. <b>Important!</b> In the <b>Fields terminated by</b> option specify a comma, not the semi-colan that is used by default. Click submit.
Finally, a table with the correct structure and definitions, and now we have data in in.
Repeat step 6 for the rest of the tables.
Once you have completed step 6 for all tables in phpmyadmin, and verify your Snitz forum is set up correctly for the mySQL connection, you should have a working Snitz forum powered by mySQL. Congratulations!
<b>Possible Issues and resolutions:</b>
If you are receiving errors while trying to import the .txt data-files in phpmyadmin, it may be because of some security settings with mySQL and/or phpmyadmin. Unfortunately I do not have root access to my phpmyadmin, so I do not know what settings in phpmyadmin would need to be modified to work around this error. The workaround? Whoever has root access to mysql/phpmyadmin should be-able to upload the data-files for you through Phpmyadmin.
If the data is importing, but is not importing correctly, you may have to check the <b>Optionally</b> box in the <b>Fields enclosed by</b> section before you submit your data file.
Well that is all, I really hope this helps out somebody in their Access to mySQL conversion process. If you have any questions on the above process, I'll be more then happy to try to answer them. |
| 15 L A T E S T R E P L I E S (Newest First) |
| mkervin |
Posted - 30 March 2011 : 13:24:02
Question regarding the connection string for MySQL do I use the one supplied in my config file:
strConnString = "driver={MySQL ODBC 3.51 Driver};option=16387;server=SERVER_IP;user=UID;password=PWD;DATABASE=DB_NAME;" '## MySQL w/ MyODBC v3.51
or is their a newer one to work with the latest version of ODBC 5.1 driver, which I am running on my testing machine? Thank you... |
| roa360 |
Posted - 10 April 2009 : 12:42:28
THANK U SOOOOOOOOOOOOOOOOOOOOOO MUCH |
| Target_Locked |
Posted - 03 December 2007 : 23:34:42
quote:
Originally posted by ruirib
SQLYog is the best tool I know to use with MySQL. If you really need the conversion, you should buy the Enterprise version, the trial won't import data, as far as I can remember.
Finally I could convert from Access to MySQL (I can see all messages in PHPMyadmin), but I got another problem. My Snitz forum can not display converted meggages (UTF-8 chracters), that is my Vietnamese language. When making new post I can type in my language and see it, but after posting the forum displays it incorrectly.
And even when I install new Snitz with MySQL, I could not make it to display Vietnamse correctly.
With Access DB, I just add 2 lines to each ASP files as follows then I can use Vietnamse UTF-8.
____________________________
<% Session.Codepage=65001 %>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
----------------------------
But this method does not work with MySQL.
Any one experienced this, pls give me an advice.
Thanks.< |
| ruirib |
Posted - 28 November 2007 : 06:12:37
SQLYog is the best tool I know to use with MySQL. If you really need the conversion, you should buy the Enterprise version, the trial won't import data, as far as I can remember.< |
| Target_Locked |
Posted - 28 November 2007 : 04:10:16
quote:
Originally posted by ruirib
The method is clearly stated here:
1. Create a blank DB and use setup.asp and then any dbs files to create the proper table structure.
2. Import the data from the Access DB, using one of several available methods. One simple option is to use the MySQL migration wizard to export all the data to another MySQL DB (let's call it the 2nd database). You can then use SQLYog export to another database option to export just the data from this 2nd DB (which does not have the proper database structure) to the database created by setup.asp.
3. The process may fail, specially in the use of the SQL migration toolkit to create a DB from the Access DB. If that happens, several options are available, but the easiest is simply to buy SQLYog's Enterprise version and use the import feature, which usually works ok.
4. As I wrote before, this many times is a trial and error approach.
ruirib,
Could you explain more in step 1 ?
I skip step 1 and go to step 2:
First I use "SQLYog's Enterprise" 30 day trial, go to its Migration tool kit but it can not connect to MDB file. "Error during initialization of provider"
Then I change to MySQL Migration Toolkit to convert MDB directly to MySQL, evething is smooth, but I have problem with Unicode charset; although I choose multi language UTF-8 option, so my forum can not display Vietnamese characters correctly.
For example:
(Sorry, I tried to give an example but this Snitz forum can not display Unicode characters either)
To use Unicode in my forum, I have to modify each ASP file by adding these lines at top of ASP file
---------------
<%@CODEPAGE=65001%>
<% Session.Codepage=65001 %>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
---------------
Any one, pls help to overcome this.
(I am also trying to get other tool for the conversion.)
Thanks.< |
| ruirib |
Posted - 27 November 2007 : 04:50:33
The method is clearly stated here:
1. Create a blank DB and use setup.asp and then any dbs files to create the proper table structure.
2. Import the data from the Access DB, using one of several available methods. One simple option is to use the MySQL migration wizard to export all the data to another MySQL DB (let's call it the 2nd database). You can then use SQLYog export to another database option to export just the data from this 2nd DB (which does not have the proper database structure) to the database created by setup.asp.
3. The process may fail, specially in the use of the SQL migration toolkit to create a DB from the Access DB. If that happens, several options are available, but the easiest is simply to buy SQLYog's Enterprise version and use the import feature, which usually works ok.
4. As I wrote before, this many times is a trial and error approach.< |
| Target_Locked |
Posted - 27 November 2007 : 03:31:22
quote:
Originally posted by ruirib
I am sorry to inform you, but that is not a valid migration strategy....
I'm a bit tired of everyone second guessing the advice given here about how to migrate a Snitz Db to MySQL. I would guess that I'm one of people here who has done more migrations. I can't advise a single method to do it, since the available tools fail one time or another. Anyway, the first step is always to create a proper Snitz MySQL table structure....
Hi ruirib,
I saw that many people displaying their methods and tools to do the migration, but how the result? some successful for only first 5 records in table, some tools are demo only, some have broken links !!!
I now need a final/best solution to migrate my Access DB to MYSQL for Snitz 3.4.06. If you have done some successful migrations, pls show me your instruction, just one method is enough.(pls note that my database use Unicode character UTF-8)
Thanks.< |
| ruirib |
Posted - 25 October 2007 : 20:15:25
Well if you do have the dbs files, then transferring the data is just a matter of creating a blank DB, seting it up using setup.asp and installing all the dbs files. Once you do that, you will need to transfer the data from the SQL Server DB. There are several ways to do this, depending on the tools you have. An easy path would be to create an ODBC data source for the SQL server DB and use it to import all the data to an Access Db. You could then use the MySQL migration tool to export the data to another Db (not the one created with setup.asp), because the migration tool will create the table structures too (though incorrectly). Anyway, it will transfer the data and then you can use a tool such as SQLYog to move the data to the destination DB from the DB created by the MySQL migration tool.
It's not direct, but should work.
Buying the pro version of SQLYog would allow you to import the data from the SQL Server Db to a MySQL Db (not the final one, again)... and then use SQLYog to copy just the data from that DB to the one created by setup.asp.
Sounds a bit complex? Yeah, I guess. One of the reasons for my preference for SQL Server.< |
| thelodger |
Posted - 25 October 2007 : 18:10:46
Yeah I have quite a few mods installed, all from snitzbits more installed in the new forum (mysql)than the old one (ms sql) as it was easier to do from a new build and without the forum being used so all was set up before I opened the new (mysql) forum, so yes I have all the dbs files and all the backup files for each mod before the next one was done all saved in the right folders on my PC.< |
| ruirib |
Posted - 25 October 2007 : 16:45:57
It's always possible, it's just a matter of the amount of work required. Is this a modded forum? If so, how many mods? And if you have mods, do you have the dbs files for them?
< |
| thelodger |
Posted - 25 October 2007 : 16:29:03
Ruirib is it possible to convert ms sql to mysql? I have had a forum running on a site that I am leaving and want to move the forum to a site I am keeping, the site I am keeping is mysql and the one I am leaving ms sql, I have created the new forum and its in use, the old forum is just sat waiting to have the posts moved over.< |
| ruirib |
Posted - 30 June 2007 : 09:31:28
I can do it, email me if you're interested.< |
| markuser |
Posted - 30 June 2007 : 08:54:30
Hello!
I have to convert my database from Access to MySQL. I have read all this text above, and I must say, I think I will not manage it  . I never had to do with MySQL before. So, is there anyone, who could do this for me? Of course against payment. The Access-Database has a size of 80 MB. The forum is a V.3.4.06 (just upgraded from 3.3.05).
Thank you very much!
Markus (from Austria)< |
| modifichicci |
Posted - 14 February 2007 : 15:05:56
quote:
The choice of methods varies and sometimes the only solution is to have two DBs, a good one created in the way I just desccribed, and another one created through an Access export, or the migration wizard. This second one has a bad structure and good data (usually, at least), but you can just get the data and export it to the one that has the good data...
I have done several migration, of base snitz and modded snitz and that is the best way. Forum HAS TO CREATE TABLES AND INDEXES or mysql doesn't work properly. You can see it in search function that stop with out of time or in profile showing that is slowing.< |
| ruirib |
Posted - 14 February 2007 : 07:43:47
I am sorry to inform you, but that is not a valid migration strategy. The migration wizard fails to create the proper table structure, which is nothing to be surprised about, since the Access table structure has no info on indexes, except for the obvious primary key ones. As such, the indexing info, essencial for the proper performance of a MySQL DB, will not be created.
We keep on saying how the migration needs to be done. If you choose to do it differently, do it, but please don't come here stating that a procedure that apparently, but apparently only works, is the solution to all migration problems.
If there was a simple migration procedure, don't you think we would have posted about it?
Also, the migration wizard fails miserably quite often, not importing all the records it should, and failing to properly migrate unicode characters. Will you be here to support people, when they use your migration strategy and it fails?
I'm a bit tired of everyone second guessing the advice given here about how to migrate a Snitz Db to MySQL. I would guess that I'm one of people here who has done more migrations. I can't advise a single method to do it, since the available tools fail one time or another. Anyway, the first step is always to create a proper Snitz MySQL table structure, using setup.asp, or recreating the table structure from a good, existing MySQL Db, with a tool such as SQLYog (or a phpmyadmin dump). Once that is done, you can use every method that moves just the data into the DB. The choice of methods varies and sometimes the only solution is to have two DBs, a good one created in the way I just desccribed, and another one created through an Access export, or the migration wizard. This second one has a bad structure and good data (usually, at least), but you can just get the data and export it to the one that has the good data...
Giving ample reason to my dislike of MySQL, I have not found a single strategy that works all the time. The same thing cannot be said, for example, for SQL Server moves, where EM or Management Studio have never failed me.< |
|
|
