Need help with SQL query UPDATE from worksheet

[JumboCactuar]

Hi,
i used the following methods on this page to update an access table from excel (ADODB) but unsure the best or correct way to update an SQL table
http://www.datawright.com.au/excel_resources/excel_access_and_ado_part_3.htm

I have the following code:

Sub SQLquery1()


var1 = Sheets("Sheet2").Range("A1")


MYSQL = "UPDATE frghtdf_database.`TABLE 1` " _
            & "SET Country = 'FRANCE' " _
            & "WHERE myID=" & var1 & " and Country = 'UK';"
            
'MYSQL = "SELECT * FROM frghtdf_database.`TABLE 1`"


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DRIVER=MySQL ODBC 5.1 Driver;UID=frghtdf_admin;PWD=********;PORT=3306;DATABASE=frghtdf_database;SERVER=192.168.1.1" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = MYSQL
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

Now running this above, the UPDATE works but it throws an error
Run-time error '1004': Application-Defined or object-defined error

I know the UPDATE works as if i comment out the top query and run the second one, the data has changed correctly.
Im guessing it due to nothing selected and having destination A1 ?

So if possible i would like to be able to use my sql database with this code though unsure how to add my connection to this

Sub PopulateOneField()   Dim cnn As ADODB.Connection
   Dim MyConn
   Dim rst As ADODB.Recordset
   Dim i As Long, j As Long
   Dim Rw As Long
   Dim sSQL As String


   Sheets("New Field").Activate
   Rw = Range("A65536").End(xlUp).Row


   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB


   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With


   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   'Update one field in each record of the table. First record is in Row 2.
   For i = 2 To Rw
     sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & Cells(i, 1).Value
     rst.Open Source:=sSQL, _
              ActiveConnection:=cnn, _
              CursorType:=adOpenKeyset, _
              LockType:=adLockOptimistic
     rst(Cells(1, 3).Value) = Cells(i, 3).Value
     rst.Update
     rst.Close
   Next i


   ' Close the connection
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing


End Sub

any help appreciated

 

[syquyen1987]

Hi,
i used the following methods on this page to update an access table from excel (ADODB) but unsure the best or correct way to update an SQL table
Excel, Access and ADO: Part 3 -- DataWright Information Services

I have the following code:

Sub SQLquery1()


var1 = Sheets("Sheet2").Range("A1")


MYSQL = "UPDATE frghtdf_database.`TABLE 1` " _
            & "SET Country = 'FRANCE' " _
            & "WHERE myID=" & var1 & " and Country = 'UK';"
           
'MYSQL = "SELECT * FROM frghtdf_database.`TABLE 1`"


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DRIVER=MySQL ODBC 5.1 Driver;UID=frghtdf_admin;PWD=********;PORT=3306;DATABASE=frghtdf_database;SERVER=192.168.1.1" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = MYSQL
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
   
End Sub

Now running this above, the UPDATE works but it throws an error
Run-time error '1004': Application-Defined or object-defined error

I know the UPDATE works as if i comment out the top query and run the second one, the data has changed correctly.
Im guessing it due to nothing selected and having destination A1 ?

So if possible i would like to be able to use my sql database with this code though unsure how to add my connection to this

Sub PopulateOneField()   Dim cnn As ADODB.Connection
   Dim MyConn
   Dim rst As ADODB.Recordset
   Dim i As Long, j As Long
   Dim Rw As Long
   Dim sSQL As String


   Sheets("New Field").Activate
   Rw = Range("A65536").End(xlUp).Row


   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB


   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With


   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   'Update one field in each record of the table. First record is in Row 2.
   For i = 2 To Rw
     sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & Cells(i, 1).Value
     rst.Open Source:=sSQL, _
              ActiveConnection:=cnn, _
              CursorType:=adOpenKeyset, _
              LockType:=adLockOptimistic
     rst(Cells(1, 3).Value) = Cells(i, 3).Value
     rst.Update
     rst.Close
   Next i


   ' Close the connection
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing


End Sub

any help appreciated

I have code similar yours, but i dont know why the number changed into string. Ex DataEntry have number 1.01, when updated to database that 1.01 changed into '1.00999999999999999999. Could you please explain this. Thanks

 

[JumboCactuar]

I have code similar yours, but i dont know why the number changed into string. Ex DataEntry have number 1.01, when updated to database that 1.01 changed into '1.00999999999999999999. Could you please explain this. Thanks

I'm guessing that long number is the actual value in spreadsheet

If you need to update with just 2 decimals can use ROUND function

 

[syquyen1987]

I'm guessing that long number is the actual value in spreadsheet

If you need to update with just 2 decimals can use ROUND function

Below is images of problem and my code. Hope that you can help me the answer. Many thanks

And below is my code