| Author |
 Topic  |
|
robbear7
Junior Member
106 Posts |
 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. |
http://www.r7designer.com
Free Dreamweaver Templates and loads of help for CSS, XHTML and Web Standards. |
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 10 July 2003 : 12:03:39
|
With all of the Access to MySQL questions, maybe someone should make this topic sticky.
By the way, I found this topic extremely helpful. Thanks for making the effort to share this with other members, Robbie< |
Ken
===============
Worldwide Partner Group
Microsoft |
Edited by - seahorse on 10 July 2003 12:16:22 |
 |
|
|
achurd
Starting Member
United Kingdom
1 Posts |
Posted - 14 July 2003 : 08:06:55
|
Another solution is to use AccessDUMP instead of Access-to-MySQL.
With the trial of Access-to-MySQL you can only really dump the structure of the database (well, actually 5 records for each table according to the developer's web site) whereas AccessDUMP can include all data plus its apparently free from the below URL.
http://www.intranet2internet.com/public/default.asp?PAGE=software&DETAILS=DUMP
Once the export is done, run the generated SQL in phpMyAdmin or SQLYog or whatever. Very quick and simple alternative to exporting Access tables to .csv files.
Another alternative would be to use the import tools in SQLYog or mySQL-Front, but I encountered problems with topics and replies being corrupted using this method, so did not investigate further.< |
Amos
amos@zeropanic.com |
|
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 15 July 2003 : 21:52:42
|
That was good advice. robbear's table create suggestion worked like a charm. However, I had some problems getting the data insert part of the robbear7's suggestion to work with my host, but the AccessDUMP generated SQL worked fine.
Which is even more reason to make this topic sticky. Great suggestions, guys.
< |
Ken
===============
Worldwide Partner Group
Microsoft |
|
|
|
forahobby
Starting Member
Australia
14 Posts |
Posted - 09 August 2003 : 10:01:28
|
Great advice i thought.
Im just about to convert my satellite forum from access to sql.. It is getting quite large and think its time to start using mySQL. Your advice sounds spot on guys and will get back to you when i have run through it..
cheers again,
ps. MODS - sticky this topic..< |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 09 August 2003 : 10:29:50
|
quote:
ps. MODS - sticky this topic..
uhmm, it already is.< |
Support Snitz Forums
|
|
|
|
maccad
Starting Member
7 Posts |
Posted - 26 August 2003 : 10:34:33
|
| why cant u just use mysql front and import from an ODBC file ?? a lot easier< |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 26 August 2003 : 10:35:57
|
| Not everyone has remote access to their database maccad.< |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
maccad
Starting Member
7 Posts |
Posted - 26 August 2003 : 10:38:28
|
quote:
Originally posted by Gremlin
Not everyone has remote access to their database maccad.
my bad...sorry< |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 26 August 2003 : 11:13:51
|
Nothing to be sorry for, was a valid question.
If you've got time to do a little FAQ on importing using MySQL-Front and ODBC I'm sure there's some people that would really appreciate it.< |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 26 August 2003 : 11:14:55
|
| @robbear7 - a little off topic, but I can see you now use Invision Board, did you make the transfer from Snitz? if yes, I would like to know how, I have tried without succes.< |
|
|
|
MongooseKY
Starting Member
9 Posts |
Posted - 04 September 2003 : 19:40:31
|
I have converted my forum from Access to MySQL successfully with the exception of some error in pop_profile.asp. It appears to have some sort of issue with the fields that are of type "text". If I comment out fields such as M_BIO and M_QUOTE in pop_profile.asp or add some text to the fields in the FORUM_MEMBERS table then the error goes away and I can once again view profiles. The error I get is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/forum/pop_profile.asp, line 231
Any ideas? I could just edit the members table for all members and make sure there is something in those fields, but it seems like there should be a better solution.
Thanks in advance!< |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 September 2003 : 05:35:15
|
| You probably should have created a new topic for this mongooseKY, what version forum are you using ?< |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
MongooseKY
Starting Member
9 Posts |
|
|
rebeatty
Starting Member
32 Posts |
Posted - 11 September 2003 : 00:54:18
|
I would benefit from ODBC advice on importing.
I am at the point where I have a MySQL-Front connection to my sql server.
I have an msAccess Snitz database
I have created a database on my sql server.
How the heck do I move my access - convert and make it sql on the server.
I have tried converting the databse to sql using AccesDUMP worked great! BUT, now I am scratching my head as to the next step.
I have also tried logging onto the MySQL-Front and do the imp/exp the gui says import msacces, cool that sounds eassy, but then an ODBC screen comes up asking for a name and password, well what does that have to do with my personal file?
Any help?< |
|
|
|
Vera
Starting Member
4 Posts |
Posted - 16 November 2003 : 12:13:50
|
Hi
I can´t download the converter upp here.
Is there any ather place a can fin it ?
Or anyone here that can help ?
Vera
< |
|
|
|
Topic |
|
How I converted my Access DB to mySQL - A guide
