Migrating from MS Access to MySql

how big an effort is it?

[john5]

Hi everybody

I have a large, dynamic web site written with ASP, VBScript. As database system I use MS ACCESS intensively with a DB of about 70 tables. This month I had the weird OPEN CONNECTION error "provider error 80004005 unspecified error" and I guess MS Access has come to its limits. I want to switch to MySql as MS SQL Server is not an option for cost reasons. I want to switch fast and therefore keep the ASP/VBScript and the underlying IIS server.
Has anyone done it before with an ASP platform? For any input thank you in advance.

[txbakers]

It's painless! I just did the same thing, with about the same number of tables. I'll write with more details in a little while, but it's very easy to do.

[bateman_ap]

When I switched (to SQL server, not MySQL) all I ahd to do was run the Acess wizard that put the tables into my sql database and then just change the connection string in my asp page (which luckly was an include! forward thinking!)

Everything with the exception of one bit of code worked perfectly, and that was a 2 minute job to change (was something to do with working out if a field was a NULL)

No more than a days work if you ask me, and you can run both databases until you have checked your new code as well.

[txbakers]

I'm back. I didn't use the wizard, I generally never trust wizards.

My process was to create the tables in mySQL first, then add an extra field at the end of the list.

I exported the Access table into a Tab deimited text file, then imported the file into the mySQL table. The extra field was to allow for the CRLF chracter which showed up if I didn't use the extra field.

There are some SQLsyntax diffrences you need to watch for. mySQL doesn't support subselects or unions. This caused a little bit of grief at first until I figured out the roundabout syntax. Also, I just discovered another syntax difference with respect to aggregates. select sum(field) as betty worked fine, but this syntax didn't:

select sum(field1) as betty, sum(field2) as suzy, (betty-suzy) as differ.

Nor did this work:

select sum(field1) as betty, sum(field2) as suzy, (sum(field1) - sum(field2) ) as differ

So I need to solve that one in the next 10 minutes.

Good luck with your conversion. It took me about a day as well, and another few days to realize my mistakes. TinyInt does not mean 3 digits! it means 3 bytes. That cost me a whole nights sleep to repair that mess. I might have even lost a customer over that mistake.

[john5]

txbakers and bateman_ap - thanks a lot!

Sounds comparable to a conversion from MS Access to MS SQL Server. I had prepared this some time ago, but never went into production with it. I remember some differences like SELECT queries with dates. I heard a lot of good news about MySql, but getting assurance from someone who actually did it, gives me a pretty good feeling. I have decided to do it before I am getting more of these 80004005 connection errors with MS Access.

[txbakers]

My site was running with Access and it was horrible - 6 users could bring down the entire system, and I'd need to reboot.

I did forget to mention that dates were different as well. There is only one date format in mySQL - yyyy-mm-dd which makes the most sense. However, any input to the DB will have to be in the same format, and if you display a date on your web page you'll have to format it before displaying it.

A few hoops, but well worth it.

[john5]

I installed MySql today without any problems. I want to use a conversion program MS-Access to MySql. Just a reliable conversion of tables, nothing else, no reports, makros or whatever. The MS-Access database has about 70 tables with alltogether 10 megs. I found Access-to-MySql from Intelligent Converts for USD39 and it makes a good impression from the description. Can anybody recommend a product or tried this program? - thanks in advance

[jmccormac]

The MySQL site has some Access > MySQL converter scripts.

[mysql.com...]

I used some of them a few years ago on a project and they worked well.

Regards...jmcc

[dingman]

If sub-selects and foreign keys are important to you, you might take a look at PostgreSQL. It's the same price as MySQL, version 7 fixed the problem of being notably slow, and it's a much more feature-complete RDBMS that actually passes the ACID test. I don't know about ease of migration, but even if there are no migration scripts available it wouldn't be any harder than the approach txbakers suggests.

[john5]

jmcc and dingman - thank you. In the meantime I could convert my database from MS-Access to MySql. Went without problems. Some problems with "access denied" message due to my complete inexperience with MySQL. The first read tests of my web site with MySQL test-database showed heaps of page errors. Date queries in SQL statements use (as in MS-SQL) the apostrophes instead of the # sign - as expected. And MySql does not like the SELECT statements where I use an openstatic cursor. Still a long way to go for a large web site, I guess. But it looks promising so far.