Howto - Access to MySQL Conversion via ASP

I recently switched to a GoDaddy hosted account and they don't give you
direct access to MySQL. I wrote a little routine that copy's data from an
Access database into a MySQL Server database and wanted to share it with the
public. I kept the format similar to what I already have working with
Access, so when I switch to MySQL server I don't have to do anything other
than modify the database connection info. I also wrote a similar routine to
populate a Microsoft SQL Server database.

I had two tables, one called orders and another called itemsOrdered. I
start off by clearing the contents of both tables out of MySQL. Next I read
the contents of the orders table out of the Access database and write it to
MySQL. I do the same with the itemsOrdered table. Finally, I display the
data for the orders table and the itemsOrdered table by reading it out of
MySQL.

<%@ Language=VBScript %>


<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={MySQL ODBC 3.51
Driver};SERVER=GoDaddy_MySQL_ServerName;PORT=3306;DATABASE=D B_NAME;USER=USERNAME;PASSWORD='PASSWORD';OPTION=0;"

MySQL = "DELETE FROM orders"
Set MyRS = MyConn.Execute(MySQL)
Response.Write "MySQL Table orders Deleted
"
MySQL = "DELETE FROM itemsOrdered"
Set MyRS = MyConn.Execute(MySQL)
Response.Write "MySQL Table itemsOrdered Deleted
"
Set MyRS = nothing
Set MyConn = nothing
Response.write
"
------------------------------------------------------------ -------------------------------
"

Set MyRS = Server.CreateObject("ADODB.Recordset")
MyConn = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=GoDaddy_MySQL_ServerName;PORT=3306;DATABASE=D B_NAME;USER=USERNAME;PASSWORD='PASSWORD';OPTION=3;"

set MyConn2 = Server.CreateObject("ADODB.Connection")
MyConn2.ConnectionTimeout = 15
MyConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="&Server.MapPath("access_db/ara.mdb")&""

MySQL = "Select * FROM orders"
MySQL2 = "SELECT * FROM orders"

MyRS.Open MySQL, MyConn, adOpenDynamic, adLockOptimistic
Set MyRS2 = MyConn2.Execute(MySQL2)

Do until MyRS2.EOF
Response.Write "Copying Access to MySQL: " & MyRS2("BillFirstName") & " "
& MyRS2("BillLastName") & "
"
MyRS.AddNew
MyRS("orderID") = MyRS2("orderID")
MyRS("orderDate") = MyRS2("orderDate")

MyRS("BillFirstName") = MyRS2("BillFirstName")
MyRS("BillLastName") = MyRS2("BillLastName")
MyRS("BillAddress") = MyRS2("BillAddress")
MyRS("BillCity") = MyRS2("BillCity")
MyRS("BillState") = MyRS2("BillState")
MyRS("BillPostalCode") = MyRS2("BillPostalCode")
MyRS("BillCountry") = MyRS2("BillCountry")
MyRS("BillPhone") = MyRS2("BillPhone")
MyRS("Email") = MyRS2("Email")

MyRS("ccType") = MyRS2("ccType")
MyRS("ccNumber") = MyRS2("ccNumber")
MyRS("ccExpire") = MyRS2("ccExpire")
MyRS("CVVNumber") = MyRS2("CVVNumber")
MyRS("status") = MyRS2("status")
MyRS("authorization") = MyRS2("authorization")
MyRS("CVVNumber") = MyRS2("CVVNumber")

MyRS("subTotal") = MyRS2("subTotal")
MyRS("shipping") = MyRS2("shipping")
MyRS("service") = MyRS2("service")
MyRS("tax") = MyRS2("tax")
MyRS("total") = MyRS2("total")
MyRS.Update
MyRS2.MoveNext
Loop
MyRS2.Close
MyRS.Close


Set MyRS = nothing
Set MyConn = nothing
Set MyRS2 = nothing
Set MyConn2 = nothing

Response.write
"
------------------------------------------------------------ -------------------------------
"

Set MyRS = Server.CreateObject("ADODB.Recordset")
MyConn = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=GoDaddy_MySQL_ServerName;PORT=3306;DATABASE=D B_NAME;USER=USERNAME;PASSWORD='PASSWORD';OPTION=3;"

set MyConn2 = Server.CreateObject("ADODB.Connection")
MyConn2.ConnectionTimeout = 15
MyConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="&Server.MapPath("access_db/ara.mdb")&""

MySQL = "SELECT * FROM itemsOrdered"
MySQL2 = "SELECT * FROM itemsOrdered"

MyRS.Open MySQL, MyConn, adOpenDynamic, adLockOptimistic
Set MyRS2 = MyConn2.Execute(MySQL2)

Do until MyRS2.EOF
Response.Write "Copying Access to MySQL: " & MyRS2("orderID") & " " &
MyRS2("productID") & "
"
MyRS.AddNew
MyRS("orderID") = MyRS2("orderID")
MyRS("productID") = MyRS2("productID")
MyRS("description") = MyRS2("description")
MyRS("quantity") = MyRS2("quantity")
MyRS("price") = MyRS2("price")
MyRS("type") = MyRS2("type")
MyRS("date") = MyRS2("date")
MyRS.Update
MyRS2.MoveNext
Loop
MyRS2.Close
MyRS.Close

Set MyRS = nothing
Set MyConn = nothing
Set MyRS2 = nothing
Set MyConn2 = nothing

Response.write
"
------------------------------------------------------------ -------------------------------
"

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={MySQL ODBC 3.51
Driver};SERVER=GoDaddy_MySQL_ServerName;PORT=3306;DATABASE=D B_NAME;USER=USERNAME;PASSWORD='PASSWORD';OPTION=3;"

MySQL = "SELECT * FROM orders"

Set MyRS = MyConn.Execute(MySQL)

Do until MyRS.EOF
Response.Write "MySQL Data: " & MyRS("BillFirstName") & " " &
MyRS("BillLastName") & "
"
MyRS.MoveNext
Loop
MyRS.Close


Set MyRS = nothing
Set MyConn = nothing

Response.write
"
------------------------------------------------------------ -------------------------------
"

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DRIVER={MySQL ODBC 3.51
Driver};SERVER=GoDaddy_MySQL_ServerName;PORT=3306;DATABASE=D B_NAME;USER=USERNAME;PASSWORD='PASSWORD';OPTION=3;"

MySQL = "SELECT * FROM itemsOrdered"

Set MyRS = MyConn.Execute(MySQL)

Do until MyRS.EOF
Response.Write "MySQL Data: " & MyRS("orderID") & " " & MyRS("productID")
& " " & MyRS("description") & "
"
MyRS.MoveNext
Loop
MyRS.Close


Set MyRS = nothing
Set MyConn = nothing


%>