Update MySQL database using Excel VBA

[zovche]

Hi, I have easy one here, but can't get it done, I want to update MySQL DB via Visual basic based on cell value.
Table in MySQL has column ID and column Value, I would like to update that value based on A1 cell value in Sheet1. So far I have this code, but I receive an error message. Can you guide me where I did wrong or how to update it in other way.

Thank you.

Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset




strCon = "Driver={MySQL ODBC 5.1 Driver};SERVER=doks;DATABASE=teke_brojeva;UID=***;PWD=***"


Set cn = CreateObject("ADODB.Connection")


cn.Open strCon
Set rs = CreateObject("adodb.recordset")


rs.Open "UPDATE ugovori_dt SET br_ugovora='" & (Cells(1, 1).Values) & "'", cn


rs.Close


End Sub

 

[zovche]

What happens if you remove the 2nd rs.Close?

Solution from ScottR works fine and resolved this issue. Thank you for help.

By the way, the code as posted is only going to put the value from F12 on 'Books' into the field br_ugovora ,it's not going to take the value from that cell, add 10 to it and then update with that.

If you wanted to do that then this,

 updSql = "UPDATE ugovori_dt SET br_ugovora='" & ThisWorkbook.Sheets("Books").Range("F12") & "'"[/code
would need to be move below this.
[code]
    Sheets("Books").Range("F12") = rs.Fields("br_ugovora") + 10

Oh, I guess I'm just too tired and missed this one. I am executing updSql below but I named that variable before that command so no wonder it worked messed up :D Have to admit it was funny to see how it worked every second time Now it works perfectly.

PS Is the SELECT query only ever returning one record?

Yes, there will be only one record, last saved/used number.

 

[zovche]

Just overall biiig thank to everyone who participated and helped me out. Just learning new things and this should be great for my next needs of use of this. Hope it will help to others in my situation as well. Stay awesome everyone Case Closed

Final code:

Sub connect()
    Dim strSql As String
    Dim updSql As String
    
Application.ScreenUpdating = False
    
    Set rs = CreateObject("ADODB.Recordset")
    
    strSql = "Select br_ugovora FROM ugovori_dt"
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=***;DATABASE=****;UID=***;PWD=***"


    rs.Open strSql, cn, adOpenDynamic
    
    Sheets("Books").Range("F12") = rs.Fields("br_ugovora") + 10
    
    rs.Close
    Set rs = Nothing
    
    updSql = "UPDATE ugovori_dt SET br_ugovora='" & Sheets("Books").Range("F12") & "'"
    cn.Execute updSql
    
    cn.Close
    Set cn = Nothing


Application.ScreenUpdating = True


End Sub