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

 

[Norie]

What's the error message?

PS Cells(1,1).Values should just be Cells(1,1).Value.

 

[zovche]

It says "Operation is now allowed when the object is closed", referencing on pre-last row "rs.Close".

Thnx for Value correction

 

[Norie]

Is the table being updated?

 

[zovche]

Is the table being updated?

Oh, actually you're right, table is updated as I wanted. I thought because of the error that nothing happened. Ok, so now I should just find a way to close everything properly so I don't get that error. I'm guessing just deleting that part wouldn't be smartest idea .

 

[Norie]

Try closing the connection then setting it and the recordset to Nothing.

 

[zovche]

Try closing the connection then setting it and the recordset to Nothing.

Hi,thnx for suggestion, I'm almost done but still I receive same error. In addition, I have code working (except for that error) but it works every second time. It suppose to pull the number in MySql table, adds 10 to it, returns value in excel and replaces old number in MySql with that new number, like a counter with addition of 10. But first time I run code it does that job, second time I run nothing happened, then third time I run and does it again as wanted. and so on. So every second time it works Can someone please check on my code and help me out why is that. Thank you so much.

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"
    updSql = "UPDATE ugovori_dt SET br_ugovora='" & ThisWorkbook.Sheets("Books").Range("F12") & "'"
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=***;DATABASE=***;UID=***;PWD=***"

    rs.Open strSql, cn, adOpenStatic
    
    Sheets("Books").Range("F12") = rs.Fields("br_ugovora") + 10
    
    rs.Close

    rs.Open updSql, cn, adOpenStatic
    
    rs.Close 'where error shows
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
Application.ScreenUpdating = True
End Sub

 

[ScottR]

Why are you using a recordset to fire a SQL update command? Not able to confirm with testing but this

cn.execute updSql

replaces this

rs.Open updSql, cn, adOpenStatic
    
    rs.Close 'where error shows
    Set rs = Nothing

 

[Norie]

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

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

PS Is the SELECT query only ever returning one record?

 

[zovche]

Why are you using a recordset to fire a SQL update command? Not able to confirm with testing but this

cn.execute updSql

replaces this

rs.Open updSql, cn, adOpenStatic
    
    rs.Close 'where error shows
    Set rs = Nothing

You're right, I'm new with MySql and VBA and I remember I read about this so no need for recordset to execute SQL update command, but I copied from code above and left it that way. Now it works just fine that part.