Team Access:
By now we know that November's patch for Office created issues for Access 2010, 2013 and 2016 when a direct update to a table with a Where clause in the SQL string was executed.
Many posts have been submitted with answers.
Here is a simple VBA code example on the "before" and "after" if you have time to do the workaround before December 10th official fix.
Option Compare Database
Option Explicit
' **************************************************************
' Here'a a simple demo table used to test this bug
' Table Name: tblTestError3340
' Field 1: Id - Autonumber
' Field 2: TestCount - Long Integer
' Field 3: OrderType - String (Only one record has "TEST"
' **************************************************************
Public Sub TestError3340()
Dim strSQL As String
' **************************************************************
' This creates the "Corrupt Query" Error
' **************************************************************
Dim db As DAO.Database
Set db = CurrentDb()
strSQL = "Update tblTestError3340 Set TestCount = TestCount + 1 Where OrderType = 'TEST';"
db.Execute strSQL
End Sub
Public Sub TestError3340WorkAround()
Dim strSQL As String
' **************************************************************
' Workaround 1 - No Error
' This uses qrytblTestError3340
' which selects * from tblTestError3340
' **************************************************************
Dim db As DAO.Database
Set db = CurrentDb()
strSQL = "Update qrytblTestError3340 Set TestCount = TestCount + 1 Where OrderType = 'TEST';"
db.Execute strSQL
End Sub
Public Sub TestError3340WorkAround2()
Dim strSQL As String
' **************************************************************
' Workaround 2 - No Error
' This uses a subquery in the Update statement
' **************************************************************
Dim db As DAO.Database
Set db = CurrentDb()
strSQL = "Update (Select * from tblTestError3340) Set TestCount = TestCount + 1 Where OrderType = 'TEST';"
db.Execute strSQL
End Sub
Hope this clarifies the workaround.
Regards,
Rich Locus, Logicwurks, LLC
http://www.logicwurks.com
