Moving from MS Access to MySQL

[Gary Williams]

Hi All,

I have a database application written in asp that works with an ms access database.

I am moving the application to a server that supports asp but not access. It has mysql available.

I have used a converter programme to create a mysql dump file which I have checked I can use to create a new mysql copy of the original access database.

So far, so good.

I understand that changing the connection file from access to mysql working is not difficult but I cannot find a suitable tutorial to explain this or how/where/if I need to edit my existing asp files.

Any links/hints?

Regards

Gary

[raf]

Basically all you need is a MyODBC driver + changing your connectionstrings.

If you used Jet-SQL goodys like "select top etc", stored procedures, datashaping etc, then you'll need to rewrite it to MySQL.

I only got the MySQL 2.5 running (got errors on 3.51)
Here are some examples

'DNS-less connection --> MyODBC 2.5

Set lpSQL=Server.CreateObject("ADODB.Connection")
lpSQL.ConnectionString="Driver=MySQL;server=localhost;user id=your_mysqluser_name;password=your_mysql_password;database=your_db_name;"
lpSQL.Open

'DNS-less connection --> MyODBC 3.51
Set lpSQL=Server.CreateObject("ADODB.Connection")
strConnection = "Driver=MySQL ODBC 3.51;server=DUPLICITY;user id=WebAccess;password=;database=OLANA-MGR;"
lpSQL.ConnectionString=strConnection
lpSQL.Open


'With DNS --> MyODBC 3.51 en 2.5
dim adoConn
set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "DSN=mysql_dsn2"
if adoConn.errors.count = 0 then

[Gary Williams]

Evening Raf,

OK, I get that. It seems fairly straight forward. Does this mean that the rest of the asp application files that write to, and read from, the database should not need to be edited and will work as they are?

I've got the other test server set up with mysql now, so I'll give it a try.

Cheers

Gary

[raf]

Does this mean that the rest of the asp application files that write to, and read from, the database should not need to be edited and will work as they are?
Basically, yes.

Unless you used dome of the typical JetSQL goodies that are not supported in MySQL's sql version. But most standard SQL is supported by both. Only subquerys, a "select into", the "select TOP" and a few other things will need to be rewritten.

[raf]

gary,

can you mail me from another mailaccount because my mails don't seem to get to your current one.