Access error -query is corrupt

isladogs said:

The MS article makes no mention of updating A2007 or earlier. Is that because they aren't affected or just because they are past end of support period?
If the latter I may be forced to move away from A2010 in the near future as its approaching end of support also.

Click to expand...

isladogs said:

The MS article makes no mention of updating A2007 or earlier. Is that because they aren't affected or just because they are past end of support period?
If the latter I may be forced to move away from A2010 in the near future as its approaching end of support also.

Click to expand...

pbaldy said:

Another good link:

https://accessexperts.com/blog/2019/11/13/critical-office-update-breaks-access-query-is-corrupt/**

Click to expand...

isladogs said:

Code:

Assume you mean how do I uninstall this update?

It will have been installed when you installed Office if you ticked the option to check for updates

Go to Wndows Update ...View Update History then view the office updates it lists

The update to remove will depend on which version Of Office you are using.
I believe the ones you need to remove are
Office 2010 – KB4484127 (also Office 2007?)
Office 2013 – KB4484119
Office 2016 – KB4484113

If you are running Office 365, see this article

UPDATE
1. Also see this article by Luke Chung:
http://fmsinc.com/MicrosoftAccess/Errors/query_is_corrupt/index.htm

2. Another workround that David Marten suggested at UA
Instead of running something like:

Code:

UPDATE [B][COLOR="red"]Table1[/COLOR][/B] SET Table1.Grade = "X" WHERE (((Table1.ID)=3));

change it to:

Code:

UPDATE [B][COLOR="Red"](SELECT * FROM Table1)[/COLOR][/B] SET Table1.Grade = "X" WHERE (((Table1.ID)=3));

This creates a table alias '%$##@_Alias' and the query runs without error!

Click to expand...

smig said:

I'm using Win7, Office 2016

If I look at the System updates I see no updates since Sept. (Control Panel -> System and Security -> Windows Update -> Show Updates history)
If I look at App. updates I see no updates for Office. It only show me as Office was installed Yesterday, Nov.15th (Control Panel -> Application -> Show Installed Updates)

Click to expand...

smig said:

If I go with the second solution of Changing Update Table1 Set... to Update (Select * From Table1) Set...
Do I need to do it also for Joined queries? Will it work if I do?
I have 70+ queries to update, but I think it might be simpler then trying to resolve the "Update issue" on the End Users machines.

Click to expand...

Use the following module to automatically implement Microsofts suggested workaround (using a query instead of a table). As a precaution, backup your database first.

Use AddWorkaroundForCorruptedQueryIssue() to add the workaround and RemoveWorkaroundForCorruptedQueryIssue() to remove it at any time.

Click to expand...

Option Compare Database
Option Explicit

Private Const WorkaroundTableSuffix As String = "_Table"

Public Sub AddWorkaroundForCorruptedQueryIssue()
    On Error Resume Next
    
    With CurrentDb
        Dim tableDef As tableDef
        For Each tableDef In .tableDefs
            Dim isSystemTable As Boolean
            isSystemTable = tableDef.Attributes And dbSystemObject
            
            If Not EndsWith(tableDef.Name, WorkaroundTableSuffix) And Not isSystemTable Then
                Dim originalTableName As String
                originalTableName = tableDef.Name
                
                tableDef.Name = tableDef.Name & WorkaroundTableSuffix
                
                Call .CreateQueryDef(originalTableName, "select * from [" & tableDef.Name & "]")
                
                Debug.Print "OldTableName/NewQueryName" & vbTab & "[" & originalTableName & "]" & vbTab & _
                            "NewTableName" & vbTab & "[" & tableDef.Name & "]"
            End If
        Next
    End With
End Sub

Public Sub RemoveWorkaroundForCorruptedQueryIssue()
    On Error Resume Next
    
    With CurrentDb
        Dim tableDef As tableDef
        For Each tableDef In .tableDefs
            Dim isSystemTable As Boolean
            isSystemTable = tableDef.Attributes And dbSystemObject
            
            If EndsWith(tableDef.Name, WorkaroundTableSuffix) And Not isSystemTable Then
                Dim originalTableName As String
                originalTableName = Left(tableDef.Name, Len(tableDef.Name) - Len(WorkaroundTableSuffix))
                
                Dim workaroundTableName As String
                workaroundTableName = tableDef.Name
                
                Call .QueryDefs.Delete(originalTableName)
                tableDef.Name = originalTableName
                
                Debug.Print "OldTableName" & vbTab & "[" & workaroundTableName & "]" & vbTab & _
                            "NewTableName" & vbTab & "[" & tableDef.Name & "]" & vbTab & "(Query deleted)"
            End If
        Next
    End With
End Sub

'From https://excelrevisited.blogspot.com/2012/06/endswith.html
Private Function EndsWith(str As String, ending As String) As Boolean
     Dim endingLen As Integer
     endingLen = Len(ending)
     EndsWith = (Right(Trim(UCase(str)), endingLen) = UCase(ending))
End Function

You can find the latest code on my GitHub repository:

github.com/lauxjpn/CurruptQueryAccessWorkaround

AddWorkaroundForCorruptedQueryIssue() will add the suffix "_Table" to all non-system tables, e.g. the table "IceCreams" would be renamed to "IceCreams_Table".
It will also create a new query with the original table name, that will select all columns of the renamed table. In our example, the query would be named "IceCreams" and would execute the SQL "select * from [IceCreams_Table]".

RemoveWorkaroundForCorruptedQueryIssue() does the reverse actions.

I tested this with all kinds of tables, including external non-MDB tables (like SQL Server). But be aware, that using a query instead of a table can lead to non-optimized statements being executed against a backend database in specific cases, especially if your original queries that used the tables are either of poor quality or very complex.

In my case I needed to manually rename [USysRibbons_Table] back to [USysRibbons], as I hadn't marked it as as system table when I created it in the past.

Click to expand...

isladogs said:

Here'a another solution which I've copied in its entirety form an answer by LauXjpn at MSDN forums.
Its very clearly explained and I think it may save some of you a lot of time.

Hope that helps

Code:

Option Compare Database
Option Explicit

Private Const WorkaroundTableSuffix As String = "_Table"

Public Sub AddWorkaroundForCorruptedQueryIssue()
    On Error Resume Next
    
    With CurrentDb
        Dim tableDef As tableDef
        For Each tableDef In .tableDefs
            Dim isSystemTable As Boolean
            isSystemTable = tableDef.Attributes And dbSystemObject
            
            If Not EndsWith(tableDef.Name, WorkaroundTableSuffix) And Not isSystemTable Then
                Dim originalTableName As String
                originalTableName = tableDef.Name
                
                tableDef.Name = tableDef.Name & WorkaroundTableSuffix
                
                Call .CreateQueryDef(originalTableName, "select * from [" & tableDef.Name & "]")
                
                Debug.Print "OldTableName/NewQueryName" & vbTab & "[" & originalTableName & "]" & vbTab & _
                            "NewTableName" & vbTab & "[" & tableDef.Name & "]"
            End If
        Next
    End With
End Sub

Public Sub RemoveWorkaroundForCorruptedQueryIssue()
    On Error Resume Next
    
    With CurrentDb
        Dim tableDef As tableDef
        For Each tableDef In .tableDefs
            Dim isSystemTable As Boolean
            isSystemTable = tableDef.Attributes And dbSystemObject
            
            If EndsWith(tableDef.Name, WorkaroundTableSuffix) And Not isSystemTable Then
                Dim originalTableName As String
                originalTableName = Left(tableDef.Name, Len(tableDef.Name) - Len(WorkaroundTableSuffix))
                
                Dim workaroundTableName As String
                workaroundTableName = tableDef.Name
                
                Call .QueryDefs.Delete(originalTableName)
                tableDef.Name = originalTableName
                
                Debug.Print "OldTableName" & vbTab & "[" & workaroundTableName & "]" & vbTab & _
                            "NewTableName" & vbTab & "[" & tableDef.Name & "]" & vbTab & "(Query deleted)"
            End If
        Next
    End With
End Sub

'From https://excelrevisited.blogspot.com/2012/06/endswith.html
Private Function EndsWith(str As String, ending As String) As Boolean
     Dim endingLen As Integer
     endingLen = Len(ending)
     EndsWith = (Right(Trim(UCase(str)), endingLen) = UCase(ending))
End Function

Click to expand...

isladogs said:

The solution I copied from MSDN forums means that no queries need to be amended. Instead you are temporarily changing table names by adding a suffix. When MS release a fix, the process is reversed.
Its quick to do - much faster than modifying all queries manually.

The downside of that approach is that all forms / reports etc would be affected but using name autocorrect might solve that. As the author says, take a backup.

Anyway, I'm not proposing any one solution as the best. Just offering different ideas that I've picked up elsewhere.

@smig
You didn't answer my question about the Office version and install method - MSI or C2R?

Click to expand...