Error 3340 Work Around

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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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