<%
' MS Access MDB 2 mySQL Database Converter v1.2
' ------------------------------------------------------------------------------------------------
' You don't need to change anything in this script, as everything is done with a simple form when
' you load this page into your browser.
'
' This script will convert an Access database to a mySQL database using either an .SQL text file
' that can be imported using for example phpMyAdmin on an online mySQL server or executing the SQL
' statements directly to the mySQL server (highly recommended!)
'
' The fields on the form are:
'
' Access Database The access database to convert
' Map relative database path Check to map a relative database path usign Server.MapPath()
' Prefix mySQL tables with If you want to prefix the new tables, write the prefix here
' Convert table structure only Check to only convert the table structure
' Convert to DROP TABLE 's only Check to only convert to DROP TABLE statements
'
' Convert directly to mySQL server Check to convert database directly to a mySQL server
' mySQL server mySQL server address
' mySQL database mySQL database name to convert to
' DB userID mySQL database user id
' DB password mySQL database password
Function PadD(Number)
PadD = String(2 - Len(Number), "0") & Number
End Function
Const adSchemaColumns = 4
Tbl_Prefix = LCase(Request("tbl_prefix"))
MapDBPath = Request("mappath")
ConvertTblStructOnly = Request("tbl_struct_only")
ConvertDirectly = Request("directly_to_mysql")
ConvertToDropTables = Request("only_drop_tables")
If ConvertToDropTables > "" Then ConvertTblStructOnly = "on"
mySQL_Server = Request("mysql_server")
mySQL_DB = Request("mysql_db")
mySQL_Uid = Request("mysql_uid")
mySQL_Pwd = Request("mysql_pwd")
%>
<html>
<head>
<title>MDB 2 mySQL</title>
<style type="text/css">
body { font-family: tahoma; font-size: 10pt; color: #000000; }
td { font-family: tahoma; font-size: 9pt; color: #000000; }
input { font-family: tahoma; font-size: 8pt; color: #008000; }
</style>
</head>
<body>
<form action="mysql2access.asp" method="get">
<font size="4"><b>MDB 2 mySQL</b></font><br>
<hr>
<table border="0" cellpadding="0" cellspacing="0">
<tr><td width="210"><b>Access Database:</b> </td><td><input type="file" name="db" size="70"></td></tr>
<tr><td width="210"><b>Map relative database path:</b> </td><td><input type="checkbox" name="mappath" onclick="this.blur();"<% If MapDBPath > "" Then Response.write " checked"%>></td></tr>
<tr><td width="210"><b>Prefix mySQL tables with:</b> </td><td><input type="text" name="tbl_prefix" size="20" value="<%=Tbl_Prefix%>"></td></tr>
<tr><td width="210"><b>Convert table structure only:</b> </td><td><input type="checkbox" name="tbl_struct_only" onclick="this.blur();"<% If ConvertTblStructOnly > "" Then Response.write " checked"%>></td></tr>
<tr><td width="210"><b>Convert to DROP TABLE's only:</b> </td><td><input type="checkbox" name="only_drop_tables" onclick="this.blur();"<% If ConvertToDropTables > "" Then Response.write " checked"%>></td></tr>
<tr><td width="210"><b> </b> </td><td> </td></tr>
<tr><td width="210"><b>Convert directly to mySQL server:</b></td><td valign="top"><input type="checkbox" name="directly_to_mysql" onclick="this.blur();"<% If ConvertDirectly > "" Then Response.write " checked"%>></td></tr>
<tr><td width="210"><b>mySQL server:</b> </td><td><input type="text" name="mysql_server" value="<%=mySQL_Server%>"></td></tr>
<tr><td width="210"><b>mySQL database:</b> </td><td><input type="text" name="mysql_db" value="<%=mySQL_DB%>"></td></tr>
<tr><td width="210"><b>DB userID:</b> </td><td><input type="text" name="mysql_uid" value="<%=mySQL_Uid%>"></td></tr>
<tr><td width="210"><b>DB password:</b> </td><td><input type="text" name="mysql_pwd" value="<%=mySQL_Pwd%>"></td></tr>
<tr><td width="210"><b> </b> </td><td><input type="submit" style="font-size: 7pt; font-weight: bold" value="- CONVERT -"></td></tr>
</table>
<%
strDB = Request("db")
If MapDBPath > "" Then strDB = Server.MapPath(strDB)
If strDB > "" And InStr(strDB, ".") > 0 Then
strDBName = Right(strDB, Len(strDB) - InStrRev(strDB, "\"))
strDBName = Left(strDBName, InStr(strDBName, ".") - 1)
strFile = Left(strDB, InStrRev(strDB, "\")) & strDBName & ".sql"
Set fso = Server.CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strDB) Then
If ConvertTblStructOnly = "" Then strData = "& data "
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & strDB
If ConvertDirectly > "" Then
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.Open "DRIVER={MySQL};SERVER=" & mySQL_Server & ";DATABASE=" & mySQL_DB & ";UID=" & mySQL_Uid & ";PWD=" & mySQL_Pwd & ";"
ReDim mySQL_Sql(0)
Select Case (ConvertToDropTables = "")
Case True: Response.write "<pre><hr>Converting table structure " & strData & "from <b>" & LCase(strDBName) & ".mdb</b> to mySQL <b>" & mySQL_DB & "</b> @ <b>" & mySQL_Server & "</b>..." & vbCrLf & vbCrLf
Case False: Response.write "<pre><hr>Converting to DROP TABLE statements from <b>" & LCase(strDBName) & ".mdb</b> to mySQL <b>" & mySQL_DB & "</b> @ <b>" & mySQL_Server & "</b>..." & vbCrLf & vbCrLf
End Select
Else
Select Case (ConvertToDropTables = "")
Case True
Response.write "<pre><hr>Converting table structure " & strData & "from <b>" & LCase(strDBName) & ".mdb</b> to <b>" & LCase(strDBName) & ".sql</b>..." & vbCrLf & vbCrLf
Set file = fso.OpenTextFile(strFile, 2, True)
Case False
Response.write "<pre><hr>Converting to DROP TABLE statements from <b>" & LCase(strDBName) & ".mdb</b> to <b>" & LCase(strDBName) & ".sql</b>..." & vbCrLf & vbCrLf
End Select
End If
Set Rs = Conn.OpenSchema(adSchemaColumns)
ReDim Tables(1, 0)
If Not (Rs.BOF Or Rs.EOF) Then
Response.Write "Converting tables"
Do
strTable = Tbl_Prefix & Rs("TABLE_NAME")
If LCase(strTable) = Tbl_Prefix & "msysaccessobjects" Then
Do
strTable = Tbl_Prefix & Rs("TABLE_NAME")
If LCase(strTable) <> Tbl_Prefix & "msysaccessobjects" Then Exit Do
Rs.MoveNext
Loop Until Rs.EOF Or Rs.BOF
End If
If Rs.EOF Or Rs.BOF Then Exit Do
Tables(0, UBound(Tables, 2)) = LCase(strTable)
strSQL = "CREATE TABLE " & LCase(strTable) & " ("
Do
ColumnName = LCase(Rs("COLUMN_NAME"))
ColumnHasDefault = CInt(Rs("COLUMN_HASDEFAULT"))
ColumnFlags = CInt(Rs("COLUMN_FLAGS"))
ColumnDefault = Rs("COLUMN_DEFAULT")
IsNullable = CInt(Rs("IS_NULLABLE"))
DataType = CInt(Rs("DATA_TYPE"))
FieldLength = CLng("0" & Rs("CHARACTER_MAXIMUM_LENGTH"))
FieldPrecision = CLng("0" & Rs("NUMERIC_PRECISION"))
Select Case DataType
Case 2: DataType = "SMALLINT(5)"
Case 3
DataType = "INT(6)"
If ColumnFlags = 24 Then
Extras = " auto_increment"
Tables(1, UBound(Tables, 2)) = ColumnName
End If
Case 4: DataType = "REAL"
Case 5: DataType = "DOUBLE"
Case 11, 16: DataType = "TINYINT(1)"
Case 128: DataType = "LONGBLOB"
Case 130
Select Case (FieldLength < 256)
Case True: DataType = "VARCHAR(" & FieldLength & ")"
Case False: DataType = "LONGTEXT"
End Select
Case 131
DataType = "DECIMAL(8," & FieldPrecision & ")"
Case 135: DataType = "DATETIME"
End Select
If IsNullable = False Then IsNullable = " NOT NULL" Else IsNullable = ""
If ColumnHasDefault = True Then ColumnHasDefault = " DEFAULT '" & ColumnDefault & "'" Else ColumnHasDefault = ""
strSQL = strSQL & ColumnName & " " & DataType & ColumnHasDefault & IsNullable & Extras & ", "
Rs.MoveNext
If Rs.EOF Or Rs.BOF Then Exit Do
If Tbl_Prefix & Rs("TABLE_NAME") <> strTable Then Exit Do
Extras = ""
Loop Until Rs.EOF Or Rs.BOF
Select Case (Tables(1, UBound(Tables, 2)) > "")
Case True: strSQL = strSQL & "PRIMARY KEY(" & Tables(1, UBound(Tables, 2)) & "));"
Case False: strSQL = Left(strSQL, Len(strSQL) - 2) & ");"
End Select
ReDim Preserve Tables(1, UBound(Tables, 2) + 1)
If ConvertToDropTables = "" Then
Select Case (ConvertDirectly > "")
Case True: mySQL_Sql(UBound(mySQL_Sql)) = strSQL: ReDim Preserve mySQL_Sql(UBound(mySQL_Sql) + 1)
Case False: file.writeline strSQL
End Select
End If
Response.Write "."
Loop Until (Rs.EOF Or Rs.BOF)
ReDim Preserve Tables(1, UBound(Tables, 2) - 1)
Response.Write " done!" & vbCrLf
If ConvertTblStructOnly = "" Then
For Index = 0 To UBound(Tables, 2)
strTable = Tables(0, Index)
strPrimaryKeyFld = Tables(1, Index)
strTbl = Right(strTable, Len(strTable) - Len(Tbl_Prefix))
Set Rs = Conn.Execute("SELECT * FROM " & strTbl & ";")
Response.Write "Converting data for table <b>" & strTable & "</b>... "
If Not Rs.BOF Then
strInitSQL = "INSERT INTO " & strTable & " ("
For Each Field In Rs.Fields
strField = LCase(Field.Name)
If Not strField = strPrimaryKeyFld Then strInitSQL = strInitSQL & LCase(Field.Name) & ","
Next
strInitSQL = Left(strInitSQL, Len(strInitSQL) - 1) & ") VALUES ("
Do
strSQL = strInitSQL
For Each Field In Rs.Fields
DataType = CInt(Field.Type)
If Not LCase(Field.Name) = strPrimaryKeyFld Then
FieldValue = Field.Value
If DataType = 135 Then
If InStr(FieldValue, " ") > 0 Then
FieldValue = Right(Year(FieldValue), 2) & "-" & PadD(Month(FieldValue)) & "-" & PadD(Day(FieldValue)) & " " & PadD(Hour(FieldValue)) & ":" & PadD(Minute(FieldValue)) & ":" & PadD(Second(FieldValue))
Else
Select Case (InStr(FieldValue, ":") > 0)
Case True: FieldValue = Hour(FieldValue) & ":" & Minute(FieldValue) & ":" & Second(FieldValue)
Case True: FieldValue = Right(Year(FieldValue), 2) & "-" & PadD(Month(FieldValue)) & "-" & PadD(Day(FieldValue))
End Select
End If
End If
If (DataType > 201 And DataType < 205) Or DataType = 135 Then DataType = "'" Else DataType = ""
If FieldValue > "" And DataType = "'" Then
FieldValue = Replace(FieldValue, "'", "''")
ElseIf FieldValue = "" And DataType = "" Then
FieldValue = "0"
End If
strSQL = strSQL & DataType & FieldValue & DataType & ","
End If
Next
strSQL = Left(strSQL, Len(strSQL) - 1) & ");"
Select Case (ConvertDirectly > "")
Case True: mySQL_Sql(UBound(mySQL_Sql)) = strSQL: ReDim Preserve mySQL_Sql(UBound(mySQL_Sql) + 1)
Case False: file.writeline strSQL
End Select
Rs.MoveNext
Loop Until Rs.EOF Or Rs.BOF
End If
Response.Write "done!" & vbCrLf
Next
End If
If ConvertDirectly = "" Then
If ConvertToDropTables = "" Then
file.close: Set file = fso.OpenTextFile(strFile, 1)
strContent = file.readall
file.close
End If
Set file = fso.OpenTextFile(strFile, 2, True)
Response.Write "Creating DROP TABLE statements"
Else
ReDim Preserve mySQL_Sql(UBound(mySQL_Sql) - 1)
Response.Write "Executing DROP TABLE statements"
End If
For Index = 0 To UBound(Tables, 2)
strTable = Tables(0, Index)
strSQL = "DROP TABLE IF EXISTS " & strTable & ";"
Select Case (ConvertDirectly > "")
Case True: myConn.Execute strSQL
Case False: file.writeline strSQL
End Select
Response.Write "."
Next
Response.Write "done!" & vbCrLf
If ConvertDirectly > "" Then
Response.Write "Executing SQL statements on mySQL server, please wait"
intUb = UBound(mySQL_Sql)
For Index = 0 To intUb
myConn.Execute(mySQL_Sql(Index))
If (Index Mod Int(intUb / 10)) = 0 Then Response.Write "."
Next
Response.Write "done!" & vbCrLf
Else
file.write strContent
file.close: Set file = Nothing
End If
Response.Write vbCrLf & "Finished converting database!" & vbCrLf
If ConvertDirectly = "" Then
Response.Write "Open <b>mysql.exe</b> and type the following to complete the convertion:" & vbCrLf & vbCrLf
Response.Write "CREATE DATABASE " & strDBName & "; <font size='1'><i>(choose another name if you want)</i></font>" & vbCrLf
Response.Write "USE " & strDBName & vbCrLf
Response.Write "\. " & strFile & vbCrLf & vbCrLf
End If
Response.Write "Remember to setup a DSN name for your new database!" & vbCrLf
Else
Response.write "<hr>Nothing to convert!"
End If
Set fso = Nothing
Set Rs = Nothing
Conn.Close: Set Conn = Nothing
If ConvertDirectly > "" Then myConn.Close: Set myConn = Nothing
Else
Response.write "<hr>The database file <b>" & strDB & "</b> was not found!"
End If
Else
Response.write "<hr>No database selected or invalid database filename!"
End If
%>
Ny bruger
Nybegynder
Opret
Preview
