none
SQL Update not working... Help! RRS feed

  • Question

  • Hello,

    I have an Excel spreadsheet connected to an Access database, in which I make updates to the db via SQL statements. My code has several INSERT and UPDATE statements that work fine, but one of them for some reason doesn't work... No error msgs, it simply doesn't update the table as it's supposed to. I even created the exact same query inside Access and it works, but it doesn't work via code:

    Dim SQL_Text As String

    SQL_Text = "UPDATE change_details SET change_details.cad_change_status = 'CM Review' WHERE (((change_details.cad_change_status) Is Null) AND ((change_details.change_number) Like 'cr*') AND ((change_details.pdm_change_status)='Active') AND ((change_details.change_phase)='0'));"
    Execute_SQL (SQL_Text)

    My Execute_SQL function is:

    Sub Execute_SQL(SQL_Text As String)

    Dim ADO_Connection As ADODB.Connection
    Set ADO_Connection = New ADODB.Connection
    Connection_String = Get_Connection_String()
    ADO_Connection.Open (Connection_String)
    'On Error Resume Next
        ADO_Connection.Execute SQL_Text
    'On Error GoTo 0ADO_Connection.Close
    Set ADO_Connection = Nothing

    End Sub

    Which I know it works fine, the problem I have is with this one UPDATE statement... Any ideas?

    Friday, October 21, 2011 5:49 PM

Answers

  • I am not sure, but you may test it with an "%" charachter in the Like Operator, to see if that makes any differences. I have read some threads, which mentioned that in ADO you have to use the "%" instead of the "*" character. See if that helps:

    SQL_Text = "UPDATE change_details SET change_details.cad_change_status = 'CM Review' WHERE " & _
    "change_details.cad_change_status Is Null AND change_details.change_number Like 'cr%' AND " & _
    "change_details.pdm_change_status='Active' AND change_details.change_phase='0';"
    
    

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishaniModerator Monday, October 24, 2011 4:19 PM
    • Marked as answer by avardaneg Monday, October 24, 2011 4:35 PM
    Saturday, October 22, 2011 5:46 PM
    Moderator

All replies

  • Try this:

    SQL_Text = "UPDATE change_details SET change_details.cad_change_status = 'CM Review' WHERE " & _
    "change_details.cad_change_status Is Null AND change_details.change_number Like 'cr*' AND " & _
    "change_details.pdm_change_status='Active' AND change_details.change_phase=0;"
    

    NOTE: The last change_details.change_phase Field is problably a Numeric datatype, and therefore removed the quotes.

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, October 21, 2011 6:51 PM
    Moderator
  • danishani,

    Thanks for the suggestion but all of these fields are text type... It didn't solve my problem.

    Friday, October 21, 2011 7:09 PM
  • I am not sure, but you may test it with an "%" charachter in the Like Operator, to see if that makes any differences. I have read some threads, which mentioned that in ADO you have to use the "%" instead of the "*" character. See if that helps:

    SQL_Text = "UPDATE change_details SET change_details.cad_change_status = 'CM Review' WHERE " & _
    "change_details.cad_change_status Is Null AND change_details.change_number Like 'cr%' AND " & _
    "change_details.pdm_change_status='Active' AND change_details.change_phase='0';"
    
    

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishaniModerator Monday, October 24, 2011 4:19 PM
    • Marked as answer by avardaneg Monday, October 24, 2011 4:35 PM
    Saturday, October 22, 2011 5:46 PM
    Moderator
  • The "%" did the trick... Thanks a lot for the tip!
    Monday, October 24, 2011 12:59 PM
  • Glad to know that did the trick! :)
    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, October 24, 2011 4:20 PM
    Moderator