Howto - Access to SQL Conversion via ASP

I recently switched to a GoDaddy hosted account and they don't give you
access to SQL Server via Enterprise Explorer or the ability to create an
ODBC connection to it. I wrote a little routine that copy's data from an
Access database into a Microsoft SQL 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 SQL server I don't have to do anything
other than modify the database connection info. I also wrote a similar
routine to populate a MySQL database.

I had two tables, one called orders and another called itemsOrdered. I
start off by clearing the contents of both tables out of SQL. Next I read
the contents of the orders table out of the Access database and write it to
SQL. 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
SQL.

<%@ Language=VBScript %>


<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open = "Driver={SQL
Server};SERVER=GoDaddy_ServerName;DATABASE=DB_NAME;UID=USERN AME;PWD=PASSWORD"

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={SQL
Server};SERVER=GoDaddy_ServerName;DATABASE=DB_NAME;UID=USERN AME;PWD=PASSWORD"

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 MS-SQL: " & 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={SQL
Server};SERVER=GoDaddy_ServerName;DATABASE=DB_NAME;UID=USERN AME;PWD=PASSWORD"

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 MS-SQL: " & 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={SQL
Server};SERVER=GoDaddy_ServerName;DATABASE=DB_NAME;UID=USERN AME;PWD=PASSWORD"

MySQL = "SELECT * FROM orders"

Set MyRS = MyConn.Execute(MySQL)

Do until MyRS.EOF
Response.Write "MS-SQL 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={SQL
Server};SERVER=GoDaddy_ServerName;DATABASE=DB_NAME;UID=USERN AME;PWD=PASSWORD"

MySQL = "SELECT * FROM itemsOrdered"

Set MyRS = MyConn.Execute(MySQL)

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


Set MyRS = nothing
Set MyConn = nothing


%>