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.

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<

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.<

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.
<

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..<

ps. MODS - sticky this topic..

---

uhmm, it already is.<

why cant u just use mysql front and import from an ODBC file ?? a lot easier<

Not everyone has remote access to their database maccad.<

Originally posted by Gremlin
Not everyone has remote access to their database maccad.

---

my bad...sorry<

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.<

@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.<

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:

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!<

You probably should have created a new topic for this mongooseKY, what version forum are you using ?<

Thanks Gremlin, I guess I should have done that to begin with. I've started a new topic with some additional info as well at http://forum.snitz.com/forum/topic.asp?TOPIC_ID=47438<

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?<

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
<

Originally posted by achurd
Another solution is to use AccessDUMP instead of Access-to-MySQL.

The only problem I have with this is
1. I don't have Access and can't afford it and
2. It says it AccessDUMP) can't do it if the db is password protected-that bytes even if I have the password :( I discussed this a little with Rui-guess I need more options.
Does anyone here know any other way? Or did I miss something with AccessDUMP.
UPDATE: Once I made the db not read only and archive it worked fine with AccessDUMP- -just thought I should rectify what I posted :D<

I have just converted my access2000 db using mysqlfront v2.5. http://www.mysqlfront.de. It took about 5 seconds and works flawlessly with my snitz forum! not a single error either with the db or with the snitz forum code. woohoo !<

Originally posted by chumbawumba
I have just converted my access2000 db using mysqlfront v2.5. http://www.mysqlfront.de. It took about 5 seconds and works flawlessly with my snitz forum! not a single error either with the db or with the snitz forum code. woohoo !

chumbawumba
I have remote access to my server and wanted to know if you could describe the steps in using mysqlfront? I have no experience with mysql and it took me about a day of reading just to get it up. I created a database and that is about all that I have done. I installed mysqlfront. I just want to convert my current forum from Access 2000 to mysql.<

robbear7

I was trying to follow your steps but when I get to step 3 I use phpmyadmin and select my empty database called forum. I follow the steps and nothing is put into the database. It says No SQL query!
When you say we need an empty database and we have to have our our config.asp modified correctly, do you mean that I should go a head and intall Snitz into an empty database? I have remote access to my server and I am still running my current access data base Snitz forum. I copied the database and I am working from the desktop. I want to test is all before I go live. If I need to I can install another forum to another location if need be.
Also my Access database is about 12MB. After I do Step 2 the sql file I create is about 12KB. Is this correct.
Thanks for any help yo can provide.
Step 3 - Head on over to Phpmyadmin. Select your empty database. Click the tab SQL. 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 Or Location of the text-file . 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.
<

Originally posted by wannab
chumbawumba
I have remote access to my server and wanted to know if you could describe the steps in using mysqlfront? I have no experience with mysql and it took me about a day of reading just to get it up. I created a database and that is about all that I have done. I installed mysqlfront. I just want to convert my current forum from Access 2000 to mysql.

from memory, here are the steps i took.
1)change config.asp to point to your mysql database. A sample connection string is already included in the config.asp
2)run setup.asp, snitz will correctly find that there are no tables / data in the database. Click create the tables.
Thats it. unless you have existing data in your Access database, in which case do the following
3) using mysqlfront, click Im-/export | ODBC import | browse to your access file
4) click ok, and all your data will be imported. some errors will appear such as
"Error: 1050 - Table 'forum_totals' already exists" you can safely ignore this as you already made them in step 1.
5) run setup.asp again

incase you are wondering why not import the entire access database instead of doing steps 1 & 2, it is because there will be some problems with default values in certain fields.

Thats all i did as far as i can remember. Have a play around with it. You have got your Access file as a backup anyway, so your data is safe. good luck <

chumbawumba what mysqlfront are you using? It seems there are more than one. I downloaded a couple and either one of them have the Im-/export | ODBC import | that you mentioned.
I have this one http://www.mysqlfront.de/
and this one http://www.dwam.net/mysql/win32_clients.asp

Maybe I need to register it?
Thanks for any help you can provide.<

Oh great...I found out why...I have the latest version which doesn't have it. Sigh! I went here looking for the old http://mysqlfront.sstienemann.de/ and it says

"This version has all the features you know from the versions before. Additional there are a lots of new features"

Then I find this http://www.mysqlfront.de/modules.php?name=Forums&file=viewtopic&t=686

Man what a whip!
All the links to the older version are either broken or point to the new. Does anyone have the 2.5 version they can email or something?
Please help <

wannab, yes you need to use v2.5 . you can get it here

http://www.sstienemann.de/mysqlfront/MySQL-Front_2.5_Setup.exe
http://mysqlfront.phpsoft.it/MySQL-Front_2.5_Setup.exe<

Thanks chumba and all who help us newbies out. I am up and running even though I had to use the 2.5 ODBC driver, couldn't get the 3 to work. But hey I don't care about that...I am now running on MySQL. I never thought it possible!
Thanks EVERYONE!!!<

I'm getting errors!
I decided to use the MySQL Front method... when it got down to the 'FORUM_REPLY' table i get the error 'Data provier or other service returned an E_FAIL status'. So i just tried to skip that table to see if it was just that one which was playing up, but it also did it with the 3 tables after that (FORUM_SUBSCRIPTIONS, FORUM_TOPICS, FORUM_TOTALS). I thought this may have been a space issue with our server, so i deleted the tables i'd just uploaded and tried to just upload the 'FORUM_REPLY' table and got the same error.
When this failed i tried to resort to one of the other methods.
AccessDUMP fails dumping when it gets to the FORUM_REPLY table - the progress br gets 9/10 of the way through that table and i get the error 'Run-time error 3197: The Microsoft Jet database engine sopped the process because you and another user are attempting to change the same data at the same time'. So i tried making a copy of the database, so i could be sure nothing else was editing it, but i got the same error.
This leaves me with the Phpmyadmin solution - but due the fact that i don't have a php server handy, and im in quite the hurry to get my forum back online, it's not really an option unless i can get some php webspace quickly! (ps. my previous experiences with apache have left me anti-apache - so i only use it on my linux partition, which i cant access at this point)

So if anyone has a solution, or at least any ideas please let me know!
Also (if it's of any use) - my access database is currently 23.8mb

-Tom<

no idea what's going on. have you tried a repair and compact on the said .mdb ?<

Oh and also - when using MySQL Front, if i try and import the tables after i get the E_FAIL error i get 'ADOQuery1: Cannot perform this operation on an open dataset'<

I just tried that and it seems to be working! taking ages to send tho.. and im getting a "column doesnt match value at row 1" error
oh well we'll see what happens!<

okay that error must have messed things up a bit because none of the posts, topics or replies got copied across<

Okay this is how i managed to fix it - i just deleted the tables that the setup.asp created.. i dont know if thats a bad thing (?), but it seems to have done the trick and my forum is working fine! thanks for the help people :)<

okay i officially hate mysql.
while i was testing my new mysql forum i put it in a test folder, when it was working properly i changed it back to the other folder. this is when things started going wrong. the main page loaded fine, with all the forums and stuff.. but i couldnt get in to the forums.. it was still trying to use the access database? so i changed the folders back again and they worked fine again.. then i delete the "forums" folder on the root of my server.. but i could still access it?!
so i deleted everything. then i reuploaded snitz, resent all the data from the access database to the mysql database.
when i loaded it up everything looked fine.. except none of the settings were carried across.. colour schemes.. images.. etc.. it just looked like a default snitz forum with my original data on it.. then i made a post, which worked fine.. then i went back to the forum - where it says how many times the thread has been read it was blank? and every previous post on the forum said "Edited by - on" at the bottom

ive gone back to access. any help would be greatly appreciated because we are running out of space on our server and access takes up far too much space!<

Originally posted by FourARSEDSheep ive gone back to access. any help would be greatly appreciated because we are running out of space on our server and access takes up far too much space!

So does an access db require more space than a mySQL db, for the same number of posts / members?<

Got the following error having followed the steps at the start of this topic:

Error

SQL-query :

LOAD DATA LOCAL INFILE 'C:\\WINDOWS\\TEMP\\php465.tmp' INTO TABLE `forum_badwords` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

MySQL said:


The used command is not allowed with this MySQL version

I am using phpMyAdmin 2.5.0 and mySQL 4.0.16-nt
<

Is it at all possible to use the converted database with other forums? Ie. Invision or phpBB? Or does this require another method? Thanks<

Originally posted by Lazlow
Is it at all possible to use the converted database with other forums? Ie. Invision or phpBB? Or does this require another method? Thanks

Yes, you will not be able to use Invision or phpBB with the Snitz table structure Snitz uses. You will have to ask/search at their site if the provide a converter which organizes the Snitz data for use with other forums.<

Chumawumba, Wannab, et..
I've gone to all the links you've posted for myfrontsql 2.5 and none is working. Could you post a link or email me a copy?

I appreciate your help. Jaber

<

You can download MySQL-Front 2.5 at the following places:
http://mysqlfront.phpsoft.it/
http://www.bumpersoft.com/Programming/Databases_and_Networks/Review_982_index.htm<

Okay, I just found one.

Always good to have a few on hand just in case.. :)

http://support.webhostautomation.com/?_a=knowledgebase&_j=questiondetails&_i=133<

Davio,

I just downloaded MySQL front but my hosting company says all SQL database management must be done through their web-based PHPmyadmin interface.

Mysql front seems like a faster easier way to convert can I still do this without using my providers default interface?
Thanks



<

Hello!! I converted my ACCESS to MySql:
Access= 13Mb
MySql= 13.9 Mb
is it possible? Thanks
<

Is there anyone that can help me?!?!? thanks<

can an existing invision forum version2, sql be used with latest snitz forum pl. I desperately need to convert pl.<

While it's fresh in my mind thought I'd post this little gotcha on conversions to mySQL.
I did a conversion earlier this week and there were complaints that the badword filter wasn't working correctly afterwards. Turns out they had several filters loaded with a space after the text so that words like assume didn't get filtered. This in itself wasn't an issue. However the field for the badwords in the database is type VARCHAR and theres an interesting "feature" of mySQL that trims all trailing spaces from data in VARCHAR fields, so consequently these filters were no longer working correctly.
Fairly simple fix was to delete that field and redefine it as type text which retains the trailing space (I tried CHAR but for some reason mySQL kept chaing it to VARCHAR even though I had explictly said CHAR in my CREATE statement.)<

I've converted my access database to mysql, and I'm having a problem with the connect string. I'm hoping someone can point me in the right direction. Currently I'm getting the following error message:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Click the Refresh button, or try again later.
Open the www.miltonhog.ca home page, and then look for links to the information you want.
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Provider (0x8000FFFF)
Catastrophic failure
/forum_new/inc_header.asp, line 114


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)

Page:
GET /forum_new/Default.asp

Time:
Monday, January 17, 2005, 3:03:48 PM


More information:
Microsoft Support



This is my connect string:

strConnString = "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=root;pwd=**********;database=snitz"

Can anyone point me in the right direction?
Thanks,

Shaun<

Either of the two provided in the config.asp should work

<

I would have thought so too, but unfortunately neither is working for me. For server_IP, should I be using something like localhost, or the server's actual IP address?
Shaun

Originally posted by Gremlin
Either of the two provided in the config.asp should work

Code:

'strConnString = "driver=MySQL;server=SERVER_IP;uid=UID;pwd=PWD;database=DB_NAME" '## MySQL w/ MyODBC v2.50
'strConnString = "driver={MySQL ODBC 3.51 Driver};option=4;server=SERVER_IP;user=UID;password=PWD;DATABASE=DB_NAME;" '##MySQL w/ MyODBC v3.51

<

I had the same problem using MyODBC 3.51.10. I solved this problem downgrading myODBC to the version 3.51.09

Excuse me for my bad English, but I'm italian.<

That worked like a charm. I downgraded to MyODBC 2.5 and all is well.
Thanks,

Shaun

Originally posted by scuderip
I had the same problem using MyODBC 3.51.10. I solved this problem downgrading myODBC to the version 3.51.09

Excuse me for my bad English, but I'm italian.

<

Originally posted by shaungallant
That worked like a charm. I downgraded to MyODBC 2.5 and all is well.

Maybe I spoke too soon. Now I'm getting this error:

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/forum_new/topic.asp, line 352


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)

Page:
GET /forum_new/topic.asp

Time:
Friday, January 21, 2005, 7:12:03 AM


More information:
Microsoft Support

I've converted the database using the method described in this thread, as well as mysqlfront doing an odbc table import, but nothing seems to work.
Does anyone have any ideas?
Shaun<

Maybe I spoke too soon. Now I'm getting this error:

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

Never mind. I was able to get this working.
Shaun<

I've just done the migration today... and all works fine! Thanks<

stefen, did you do exactly what is written in the first post ?
I haven´t got it to work yet - main because of:

Step 4 - Open your Access DB in Microsoft Access. In the objects column, make sure you are viewing Tables. You should see each Table listed. Right click on the first table and select Export. You need to select the Save as Type as Text Files (*.txt;*.csv;*tab;*asc). Now in File Name manually add .csv on the end. Click export.
On the next screen make sure Delimited is selected. Click next.
Select Comma as our field delimiter. Click Next.
Select the file destination and click finish. Repeat step 4 for each table.
My Access won´t let me export in that format.<

Yes, but have not use access to export data, i've use dbtools. You can download Freeware version here:
http://www.dbtools.com.br/<

Link is dead ?!<

After spending the last 3 days trying many different options such as SQLyog, MYSQL-Front, phpmyadmin,and DB manager, I can now say I'm getting very close to importing my data from my old access db/forum
- http://www.fuerteventura-forum.com

However, there are 4 csv (now txt files) that I can't seem to transfer as they are too big for (or seem to 'time out') whenever I use phpmyadmin. I have also tried using the import data from an ODBC data source feature in SQLyog which seems to work fine for the smaller files but not for these larger ones -

FORUM_A_REPLY.TXT - 5mb
FORUM_REPLY.TXT - 8mb
FORUM_A_TOPICS.TXT - 1mb
FORUM_TOPICS.txt - 1.5mb
Can anyone help me out with any suggestions? Is it possible to split a csv or txt file so I can send it via phpmyadmin? Thanks<

Has anyone tried FreeMascon to convert Access to MySql?
http://www.scibit.com/products/mascon/index.htm<

Originally posted by scuderip


..., but I'm italian.

Nobody is perfect...

No, I'm joking.
Just wanted to say that the robbear7's post works well. for all those who have probs with the csv files because access says commas won't work, convert with semicolon and then open the .txt-files and change ; to ,

cu,
pascal<

I am using access dump and MySql front. This should work but I also have a few mods to switch over. I would think I could manually edit the tables for the mods but this is what I have so far.
A new blank MySql Database with the snitz table structure installed. No data has been instered yet.
I have no idea where to safely go from here. Has anyone used this combination of software to do this?<

I used the exact method as stipulated in the first post - it worked a treat.

The only thing I had to do different was delete the first row and column from the imported data.
Also - you have to watch date values as when you convert to CSV all the date values end up as 2E+13 and not the actual number they should be.

Many Thanks<

It appears that AccessDUMP is no longer available. I would like to try this program. Anyone have a copy?
Thanks<

Hi All,

My 20Mb Access 2000 .mdb is crashing my hosts IIS (memory leaks?) about 3 times a month now, or so they tel me..., and they are not happy. They are not happy? jees, my users are screaming for blood!!
So, time for me to have a go. I've got MySQL running nicely on my XP PC and am using HeidiSQL to build the database back up from scratch - http://www.heidisql.com/

I've used the http://www.convert-in.com/acc2sql.htm free demo download to recreate the whole db structure inc the first five lines and it dropped it straight onto the MySQL server on XP
Having some issues thought with Heidi not importing all rows e.g. forum_members table has 612 rows, Heidi only imported 61 (10%)
will keep all posted and I'll be back for help! Jonathan<

Hmmm 20M not exactly huge, and as for Memory Leak - I'm not aware of any leaks in Jet 4 (Access 2k is a desktop application that uses Jet as the DBEngine)

Possibly your db is growing/has messy pages and a compact/repair will bring it back in line.
But, enjoy your migration anyway! Let us know how you get along<

Originally posted by pdrg
Possibly your db is growing/has messy pages and a compact/repair will bring it back in line.

Tried that, no joy it is nearly four years old though
Cheers for the wishes, I'm editing my above post rather than keep adding new ones. <

XR4i - that's just confusing! Post to a new topic or append this one to keep a dialogue going! Reason why you only hit 10% I guess is a script timeout on the server, by the way<

Oops, sorry - don't want to cause confusion
Seing as this thread is 2 plus years old, I thought i'd try and bring it up to date....
I now have my Windows XP Pro 1.8MHz 256Mb PC running the following:
Apache 2.2.0
PHP 5.2.0
MySQL 5.0.27-community-nt

I'm too tired now to actualy do any database conversion work...I was going to do all this on Linux but it's just too **** complicated for a Windows chap like me <

No worries, stay in the MS camp, life's easier sometimes not having to recompile your own operating system when it updates ;-)<

Hi

The simplest option is to use MySql Migration toolkit . It gives a nice wizard interface for the migration. U can download from http://dev.mysql.com/downloads/gui-tools/5.0.html
Note: i had the mysql migration toolkit running on the server in which i ran mysql server. This might not be a necessary requirement. but i have not checked

Steps
1) On welcome page click next, on next page choose "direct migration" might be selected by defaut. Click next

2) and select source database as "MS Access". In connection parameters browse to the acess db. keep username and password as blank (my access db was not password protected. Have not checked iwth password protected DB. but u can always remove the password protection and migrate). click next

3) choose target database as mysql. Give the hostname of ur mysql database and its username and password (i gave the root username and password) that has permissins to create tables add data etc. Click next

4) In next page the connection to server must execute successfully. click next

5) select the source schema. ie name of ur DB in access. the db will be displayed as an icon . click on the db icon to select and click next

6) click next in all folowing pages and u will have the db migrated successfully.

it works like a breeze

regards
sibi<

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.<

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.<

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)<

I can do it, email me if you're interested.<

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.<

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?
<

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.<

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.<

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.<

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.<

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.<

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.<

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.<

THANK U SOOOOOOOOOOOOOOOOOOOOOO MUCH

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...