Excel not writing to MySQL db

[Nimrod]

I'm able to download data from MySQL to excel but cant write data back to MySQL.... can anyone see what Im doing wrong ? Pleeeassee

Public Sub MySQL_DAO_AddRecs()
Dim ws As Workspace
Dim conn As Connection
'Dim queryDef As queryDef
Dim RS As Recordset
'Dim cl As rdoColumn




'connect to MySQL using MySQL ODBC 3.51 Driver
Set ws = DBEngine.CreateWorkspace("", "root", "root", dbUseODBC)


InfoStr = "ODBC;DSN=ResourceView;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=ResourceView;SERVER=localhost;UID=root;PASSWORD=;PORT=3306;OPTION=3;STMT=;"

' connect to Database
Set conn = ws.OpenConnection("ResourceView", dbDriverNoPrompt, False, InfoStr)

' connect to Table of database
Set RS = conn.OpenRecordset("Assignments", dbOpenDynamic)

' Move to the first row of the recordset.
RS.MoveFirst

With ThisWorkbook.Sheets("Data")
For Each c In .Columns("A:A").SpecialCells(xlCellTypeConstants, 2)
Col = Col + 1

RS.AddNew


RS!Tasks = Trim(.Cells(1, Col).Value)
RS!Project = Trim(.Cells(2, Col).Value)
' NOTE THIS IS THE DATE MANDITORY DATE FORMAT IN MYSQL
RS!StartDate = Format(Trim(.Cells(3, Col).Value), "yyyy-mm-dd") ' NOTE THIS IS THE DATE MANDITORY DATE FORMAT IN MYSQL
RS!DueDate = Format(Trim(.Cells(4, Col).Value), "yyyy-mm-dd")
RS!DropDeadDate = Format(Trim(.Cells(5, Col).Value), "yyyy-mm-dd")
RS!EstimateHrs = Trim(.Cells(6, Col).Value)
RS!Priority = Trim(.Cells(7, Col).Value)
RS!Static = Trim(.Cells(8, Col).Value)
RS!WkND = Trim(.Cells(9, Col).Value)
RS!PercentCompl = Trim(.Cells(10, Col).Value)
RS!ActualHrs = Trim(.Cells(11, Col).Value)
RS!Status = Trim(.Cells(12, Col).Value)
RS!Initiative = Trim(.Cells(13, Col).Value)
RS!Descript = Trim(.Cells(14, Col).Value)
RS!Depend = Trim(.Cells(15, Col).Value)
RS!FollowUp = Trim(.Cells(16, Col).Value)
RS!Details = Trim(.Cells(17, Col).Value)
RS!Risks = Trim(.Cells(18, Col).Value)
RS!CTA = Trim(.Cells(19, Col).Value)
RS!TaskOwner = Trim(.Cells(20, Col).Value)
RS!OOOVO = Trim(.Cells(21, Col).Value)




RS.Update

' Move to the next record.
RS.MoveNext

Next c
End With
' Close the Recordset object.
RS.Close






End Sub

 

[Nimrod]

Anyone out there using MySQL as the Backend and Excel as the User Interface ?

 

[guybrush]

I took a brief look at your code.

I have never interfaced with MySql, but have worked
a lot with DAO. On the surface, what you are doing seems
straight forward.

You may not need to use the
rs.movenext
command at the end of your loop.
(the rs.addnew at the top of the loop creates a new record).

What type of errors are you getting when
you run this code?

Also, I am curious, why are you interfacing MySql with Excel?