Option Explicit

Function Convert()

  'On Error Resume Next

  Dim strMDB
  Dim strMySQLServer, strMySQLDatabase, strMySQLUser, strMySQLPassword
  Dim strSQL, strConn
  Dim objConnMDB, objRsMDB
  Dim objConnMySQL, objRsMySQL
  
  '--Custom parameters-------------------------------------------------
  strMDB = "d:\ygroups.mdb"
  strMySQLServer = "localhost"
  strMySQLDatabase = "name-database"
  strMySQLUser = "root"
  strMySQLPassword = ""
  '--------------------------------------------------------------------

  'Open the MDB connection
  Set objConnMDB = WScript.CreateObject("ADODB.Connection")
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=_MDB_;Persist Security Info=False"
  strConn = Replace(strConn, "_MDB_", strMDB)
  objConnMDB.ConnectionString = strConn
  objConnMDB.Open
  Set objRsMDB = WScript.CreateObject("ADODB.Recordset")
  'strSQL = "select top 100 * from tblYgr_name-database order by YahooMessageID"
  strSQL = "select * from tblYgr_name-database order by YahooMessageID"
  objRsMDB.Open strSQL, objConnMDB, 3, 3  

  'Open the MySQL connection
  Set objConnMySQL = WScript.CreateObject("ADODB.Connection")
  strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=_MySQLServer_;PORT=3306;DATABASE=_MySQLDatabase_;USER=_MySQLUser_;PASSWORD=_MySQLPassword_;OPTION=3;"
  strConn = Replace(strConn, "_MySQLServer_", strMySQLServer)
  strConn = Replace(strConn, "_MySQLDatabase_", strMySQLDatabase)
  strConn = Replace(strConn, "_MySQLUser_", strMySQLUser)
  strConn = Replace(strConn, "_MySQLPassword_", strMySQLPassword)
  objConnMySQL.ConnectionString = strConn
  objConnMySQL.Open
  Set objRsMySQL = WScript.CreateObject("ADODB.Recordset")

  'Start transaction on MySQL database
  objConnMySQL.BeginTrans
  
  'Delete all rows from Archive table 
  strSQL = "delete from archive"
  objConnMySQL.Execute strSQL

  'Fetch all rows from MDB messages table
  strSQL = "select * from archive"
  objRsMySQL.Open strSQL, objConnMySQL, 3, 3  

  If Not objRsMDB.EOF Then
	objRsMDB.MoveFirst
    While Not objRsMDB.EOF
		'Copy fields from MDB table to MySQL table
		objRsMySQL.AddNew
		objRsMySQL("YahooMessageID") = objRsMDB("YahooMessageID")
		objRsMySQL("FromUser") = objRsMDB("From")
		objRsMySQL("FromEmail") = objRsMDB("FromEmail")
		objRsMySQL("Subject") = objRsMDB("Subject")
		objRsMySQL("SubjectSrt") = objRsMDB("SubjectSrt")
		objRsMySQL("RecDate") = objRsMDB("RecDate")
		objRsMySQL("Message") = objRsMDB("Message")
		objRsMySQL("AttCount") = objRsMDB("AttCount")
		objRsMySQL("NewMsgFlag") = objRsMDB("NewMsgFlag")
		objRsMySQL("DelMsgFlag") = objRsMDB("DelMsgFlag")
		objRsMySQL("FavMsgFlag") = objRsMDB("FavMsgFlag")
		objRsMySQL.Update    
		objRsMDB.MoveNext
    Wend
  End If
  
  'Commit/rollback transaction
  If Err.Number <> 0 Then
	If objConn.State = 1 Then 
	  objConnMySQL.RollbackTrans
	End If
  Else
	objConnMySQL.CommitTrans
  End If
  
  'Close MySQL connection
  If Not objRsMySQL Is Nothing Then
    objRsMySQL.Close
    Set objRsMySQL = Nothing
  End If
  If objConnMySQL.State = 1 Then
    objConnMySQL.Close
  End If
  Set objConnMySQL = Nothing

  'Close MDB connection
  If Not objRsMDB Is Nothing Then
    objRsMDB.Close
    Set objRsMDB = Nothing
  End If
  If objConnMDB.State = 1 Then
    objConnMDB.Close
  End If
  Set objConnMDB = Nothing
  
End Function

'--------------------------------------------------------------------
'Main application call
'--------------------------------------------------------------------
'On Error Resume Next
Convert
If Err.Number = 0 Then
  MsgBox "Conversion ready!"
Else
  MsgBox "Conversion failed: " & Err.Description
End If