Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: phranque
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.
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.
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.
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.
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.