none
Error 3340 Work Around RRS feed

  • Question

  • UPDATE queries crash either as

    Set q = db.QueryDefs(strQuery) ' a stored query
        q.Execute dbFailOnError

    Set q = db.CreateQueryDef("", strQuerySQL) 'embedded SQL
        q.Execute dbFailOnError

    In app with few cases, my work around is to replace use of UPDATE query/SQL with DAO Recordset loop

    ' 26-Sep-2019 Plug zero if field is null
    ''' FIX Nov 14 2019   x = ExecuteTheQuery("qryAllocations-SumHours3-FIX")
    ' UPDATE tmpAllocationsHeaderHours SET tmpAllocationsHeaderHours.LeaveHours = 0
    ' WHERE (((tmpAllocationsHeaderHours.LeaveHours) Is Null));
    '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    mSQL = "SELECT tmpAllocationsHeaderHours.LeaveHours"
    mSQL = mSQL & " FROM tmpAllocationsHeaderHours"
    mSQL = mSQL & " WHERE (((tmpAllocationsHeaderHours.LeaveHours) Is Null));"

    Set db = CurrentDb
    Set r = db.OpenRecordset(mSQL)

    If Not r.EOF Then
        With r
            .MoveFirst
            Do
                .Edit
                    !LeaveHours = ZERO
                .Update
                .MoveNext
            Loop While Not .EOF
        End With
    End If
    r.Close
    Set r = Nothing
    Set db = Nothing


    Dave Thompson

    Friday, November 15, 2019 4:29 PM

Answers

All replies

  • You can also uninstall a recent Office Update - see https://borncity.com/win/2019/11/13/office-november-2019-updates-are-causing-access-error-3340/

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by dave2001 Saturday, November 16, 2019 2:57 PM
    Friday, November 15, 2019 7:21 PM
  • Hans,

    Thanks and agreed, a more durable solution until (very soon one hopes) MS does update fix.

    Unfortunately many clients would have to convince their IT staff to do the KB uninstalls.

    My solution isn't particularly easy, or even recommended for most, but I have a client needing to run some reports and this got them going quickly (a small app anyway).

    As usual, one hates to modify existing code since we run the risk of introducing errors.

    The idea for the post is just to point those interested where to look for code that breaks on the shore of 3340.


    Dave Thompson

    Friday, November 15, 2019 7:44 PM
  • As noted in at least one other forum, failure occurs with query having UPDATE with a WHERE clause.

    I have UPDATE queries without a WHERE clause that do not throw the 3340 Error.

    Uncharted territory, but that's what I'm seeing ...


    Dave Thompson

    Monday, November 18, 2019 5:17 PM
  • Just an FYI, Microsoft has just released the first patch for Access 2016 MSI

    You can download the patch from https://support.microsoft.com/en-us/help/44...-2016-kb4484198

    *** This is a manual patch, so it will not automatically be installed by Windows Update and you must download and install it yourself! ***

    As for the other versions/editions … patches should be coming soon.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Monday, November 18, 2019 8:12 PM