none
VBA DAO won't update one field in table RRS feed

  • Question

  • Hi,

    I am trying to update a table in an Access 2003 database from data held in an Excel 2010 spreadsheet.

    For some reason one field will not update. This seems to occur on each of the updates I am trying to do and on more than one table. I seem to always get the error on the field next to the fields in my WHERE .. AND criteria. (so in this case General).

    eg. Table: Category, Department, Supplier_Number, General, Marketing .......

     strSQL = "UPDATE " & FixedTable & _
                " SET General =" & Val(Range("FT_General").Value) & _
                ", Marketing =" & Val(Range("FT_Marketing").Value) & _
                ", Promotion =" & Val(Range("FT_Promotion").Value) & _
                ", EDI =" & Val(Range("FT_EDI").Value) & _
                ", Logistics =" & Val(Range("FT_Logistics").Value) & _
                ", ReturnsUllage =" & Val(Range("FT_ReturnsUllage").Value) & _
                ", Growth =" & Val(Range("FT_Growth").Value) & _
                ", Other1 =" & Val(Range("FT_Other1").Value) & _
                ", Other2 =" & Val(Range("FT_Other2").Value) & _
                ", Other3 =" & Val(Range("FT_Other3").Value) & _
                ", Other4 =" & Val(Range("FT_Other4").Value) & _
                " WHERE Category ='" & strCategory & "'" & _
                " AND Department ='" & strDepartment & "'" & " AND Supplier_Number ='" & strSupplierNumber & "';"         

     db.Execute (strSQL)

    If I remove the line:  General =" & Val(Range("FT_General").Value) & _General =" & Val(Range("FT_General").Value) & _     it works fine.

    The error I get is: Run-time error '3144' Syntax error in UPDATE statement.

    Can anyone help me?

    Wednesday, January 16, 2013 3:29 PM

Answers

  • What happens if you enclose the field name General in square brackets? I see now that it is a reserved name in SQL.

                " SET [General]=" & Val(Range("FT_General").Value) & _


    Regards, Hans Vogelaar

    • Marked as answer by Gazza101 Thursday, January 17, 2013 11:21 AM
    Thursday, January 17, 2013 11:18 AM

All replies

  • Try the following: between the lines strSQL = ... and db.Execute ..., insert

    Debug.Print strSQL

    This will output the SQL being executed to the Immediate window. When the error occurs, copy the SQL string from the Immediate window, and paste it into a reply here. That will give others a chance to inspect the "problem" SQL.


    Regards, Hans Vogelaar

    Wednesday, January 16, 2013 5:18 PM
  • Hi Hans

    Here's the output from the SQL.

    UPDATE tbl_FixedTerms_Group SET General =100, Marketing =200, Promotion =300, EDI =400, Logistics =500, ReturnsUllage =600, Growth =700, Other1 =800, Other2 =900, Other3 =1000, Other4 =1100 WHERE Category ='Electrical' AND Department ='Audio' AND Supplier_Number ='230489';

     

    Thursday, January 17, 2013 9:27 AM
  • Thanks. The syntax looks OK.

    Are you sure that all field names are spelled correctly?

    Is Supplier_Number a text field? The value 230489 is enclosed in single quotes; this is correct and necessary for a text field but will cause an error if Supplier_Number is a number field.


    Regards, Hans Vogelaar

    Thursday, January 17, 2013 10:14 AM
  • Hi

    Yes Supplier_Number is a text field as it sometimes contains letters. I have double checked field names.

    Thursday, January 17, 2013 10:38 AM
  • I have cut the SQL down to something simple now.

    This updates the Marketing field ok. 

    strSQL = "UPDATE " & FixedTable & _
                " SET Marketing=" & Val(Range("FT_Marketing").Value) & _
                " WHERE Category ='" & strCategory & "'" & _
                " AND Department ='" & strDepartment & "'" & " AND Supplier_Number ='" & strSupplierNumber & "';"

    This does NOT update General field. I get the error.

    strSQL = "UPDATE " & FixedTable & _
                " SET General=" & Val(Range("FT_General").Value) & _
                " WHERE Category ='" & strCategory & "'" & _
                " AND Department ='" & strDepartment & "'" & " AND Supplier_Number ='" & strSupplierNumber & "';"

    Both General and Marketing are of Data Type: Number and Field Size: Double

    Out of interest I just used and update query in Access to update the General field and it worked fine.

    • Edited by Gazza101 Thursday, January 17, 2013 11:06 AM
    Thursday, January 17, 2013 10:45 AM
  • What happens if you enclose the field name General in square brackets? I see now that it is a reserved name in SQL.

                " SET [General]=" & Val(Range("FT_General").Value) & _


    Regards, Hans Vogelaar

    • Marked as answer by Gazza101 Thursday, January 17, 2013 11:21 AM
    Thursday, January 17, 2013 11:18 AM
  • A colleague of mine just suggested the exact same thing, works perfect now.

    Thanks Hans. You're a legend.



    • Edited by Gazza101 Thursday, January 17, 2013 11:22 AM
    Thursday, January 17, 2013 11:20 AM