UPDATE Query from Excel to (My)SQL Database

Recently I found out about a problem going between versions of MySQL last week and I though you might enjoy the solution. You see FLOAT()'s functionality changed whereby FLOAT(4,2) once meant 0000.00 to now meaning 00.00. The problem with this is that database imported with a value of 2341.33 becomes 99.99! This could be very bad. Fortunately you can export from the old DB via Query Browser to Excel (XML) and employ VBA and ODBC to get things right, fast.

Notably this does not update all values, only values where there is data, if you were to use this routine to post to a database repeatedly I would suggest adjusting the 'If ActiveCell.Value <> "" Then' functionality to set Null values as needed.

Sub UpdateDB()
'
' 09/10/2006 by Cameron Stevens
'

Set myconn = CreateObject("adodb.connection")
Connection = "Driver={MySQL ODBC 3.51 Driver}; Server=MyServer; ~SPLIT LINE~
          uid=webuser; pwd=b33rg00gl3s; database=brewery; option=3; port=3306;"
myconn.Open (Connection)
Set result = CreateObject("adodb.recordset")
Sql = "USE brewery;"
Set result = myconn.Execute(Sql)

Range("A1").Select
Selection.Offset(1, 0).Select
strUpdate = "UPDATE tblBottlePrices SET "
While ActiveCell.Value <> ""
strWHERE = " WHERE order_id='" & ActiveCell.Value & "' "
Selection.Offset(0, 4).Select
strSET = " "
If ActiveCell.Value <> "" Then strSET = strSET & "retail_price='" & ActiveCell.Value & "' "
Selection.Offset(0, 1).Select
If ActiveCell.Value <> "" Then strSET = strSET & ", discount_price='" & ActiveCell.Value & "' "
'Selection.Offset(0, 1).Select

strUQuery = strUpdate & strSET & strWHERE

Set result = myconn.Execute(strUQuery)

'MsgBox strUQuery
Selection.End(xlToLeft).Select
Selection.Offset(1, 0).Select
Wend
myconn.Close
Set result = Nothing
Set myconn = Nothing

End Sub