none
Example Workaround for Access "Corrupt Query" Bug in November Patch RRS feed

  • General discussion

  • 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

    Sunday, November 17, 2019 1:36 AM