none
Issue with Execute method and dbFailOnError

    Question

  •  strSQL = "INSERT INTO " & tlkpName & " ( " & fldName & ") SELECT """ & strData & """ AS " & flName
    CurrentDB.Execute strSQL, dbFailOnError

    The table field in question is set to Field Size 50.  If strData is say 100 "a"s, the SQL will execute and will add and save what are presumably the first 50 "a"s to the table field without any error being returned to the calling procedure.

    This seems odd or is it to be expected?

    Thx


    Jon Lewis

    Wednesday, April 24, 2013 3:05 PM

Answers

  • Douglas, I tested this:

    Code to test:

    sql = "Create table truncatestring ( " & _
    "test varchar(5));"
    CurrentProject.Connection.Execute sql
    
    sql = "INSERT INTO truncatestring ( test ) VALUES (""DAO Works fine"");"
    CurrentDb.Execute sql, dbFailOnError
    
    sql = "INSERT INTO truncatestring ( test ) VALUES (""ADO Works fine"");"
    CurrentProject.Connection.Execute sql

    When DAO is used (currentdb) string was truncated, and in table is entry "DAO W".

    For ADO (currentproject) I get runtime error 2147217833 String or binary data would be trouncated.

    So, if you want warring use ADO instand of DAO:

    strSQL = "INSERT INTO " & tlkpName & " ( " & fldName & ") " & _
      "VALUES (""" & strData & "")"
    On Error Resume Next
    Currentproject.Connection.Execute strSQL
    If Abs(Err.Number) = 2147217833 Then
        MsgBox "String or Binary Data Would be Truncated"
    Else
        MsgBox Err.Description
    End If


    Michał


    • Edited by Dziubek Michał Wednesday, April 24, 2013 4:58 PM
    • Marked as answer by jon1854 Wednesday, April 24, 2013 5:02 PM
    Wednesday, April 24, 2013 4:49 PM

All replies

  • I'd expect a warning, not an error, so I'm not surprised dbFailOnError isn't triggered.

    BTW, you might try the following instead of what you've got:

    strSQL = "INSERT INTO " & tlkpName & " ( " & fldName & ") " & _
      "VALUES (""" & strData & "")"
    CurrentDB.Execute strSQL, dbFailOnError


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Wednesday, April 24, 2013 4:26 PM
  • Guys,

    I have been getting error, not warnings with  CurrentDB.Execute strSQL, dbFailOnError.  So i stoped using the option dbFailOnError.

    Wednesday, April 24, 2013 4:34 PM
  • Douglas, I tested this:

    Code to test:

    sql = "Create table truncatestring ( " & _
    "test varchar(5));"
    CurrentProject.Connection.Execute sql
    
    sql = "INSERT INTO truncatestring ( test ) VALUES (""DAO Works fine"");"
    CurrentDb.Execute sql, dbFailOnError
    
    sql = "INSERT INTO truncatestring ( test ) VALUES (""ADO Works fine"");"
    CurrentProject.Connection.Execute sql

    When DAO is used (currentdb) string was truncated, and in table is entry "DAO W".

    For ADO (currentproject) I get runtime error 2147217833 String or binary data would be trouncated.

    So, if you want warring use ADO instand of DAO:

    strSQL = "INSERT INTO " & tlkpName & " ( " & fldName & ") " & _
      "VALUES (""" & strData & "")"
    On Error Resume Next
    Currentproject.Connection.Execute strSQL
    If Abs(Err.Number) = 2147217833 Then
        MsgBox "String or Binary Data Would be Truncated"
    Else
        MsgBox Err.Description
    End If


    Michał


    • Edited by Dziubek Michał Wednesday, April 24, 2013 4:58 PM
    • Marked as answer by jon1854 Wednesday, April 24, 2013 5:02 PM
    Wednesday, April 24, 2013 4:49 PM
  • So that's the way it is then by the look of it.  How odd! Particularly when DAO will normally throw error 3163: Field too small...

    e.g.

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(tlkpName)
    With rs
    .AddNew
    .Fields(fldName) = strData
    .Update
    End With

    Thanks to all for answering


    Jon Lewis

    Wednesday, April 24, 2013 5:02 PM