Access error -query is corrupt

[isladogs]

For info. New article from MS released today

https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec

Here's an example

Only a month to wait for the fix - 10 Dec 2019. Sigh!
But of course sooner for those running 365! 24 Nov.

Reminds me of the Access 2003 SP3 error and later hotfix

 

[theDBguy]

I actually saw this happen to someone's database yesterday. Thanks!

 

[isladogs]

Just done some further testing.
If done using code, the same sql fails with same error - 3340.
That's a bigger issue at least for me

The suggested workround is to create a query based on the table then run an update query based on that.
It also seems to work fine if you just join another table or query to the one you want to update or even just use a cartesian join with something unrelated.

The update that caused this cockup was released on 12 Nov to 'fix a security vulnerability'. This link describes the patch https://support.microsoft.com/en-gb/help/4484127/security-update-for-office-2010-november-12-2019.
As its unclear how serious a vulnerability was patched, it MAY be better to block the update if you don't have it. Or if you do, you can uninstall it! Its KB4484127 for A2010. I've just done so and the error has gone

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.

 

[pbaldy]

Geez, where were you when I needed you?

I got hit by 4 different users of 4 different apps first thing this morning with this error, then others all day. The good news was that indicated pretty early that it was a broken update, not a code problem. I've been fixing computers all day, either rolling back updates (at the time we weren't sure which update it was) or switching runtime versions. In retrospect switching versions shouldn't have solved the problem since it affects all versions, but something in the uninstall must have unwound that update. I was in too much of a hurry trying to get screaming users running to do diagnostics, and my search early this morning didn't turn up the MS article.

It's gotten quiet now, which is a good thing.

 

[shadow9449]

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.

I use Access 2003 (still) and a query in my application that updates a table has been working flawlessly on my computer (Win 7 and Win 10) but failing on the computers of 7 clients today who have been using the same application on Access 2010 runtime.

 

[pbaldy]

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.

As I understand it 2007 wasn't affected, some are switching to the 2007 runtime.

 

[Galaxiom]

Our admin had a look at the updates and said he couldn't see it affecting Runtime 2010 which most of our staff use.

 

[isladogs]

The example I made in post #1 was done in A2010 before I uninstalled the Office update

 

[smig]

How do I install this update ?

I see no Office updates, only as Office was installed today (No.15th) :banghead::banghead::banghead:

:Crying:

 

[isladogs]

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:

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

change it to:

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!

 

[isladogs]

Further update:
I have now successfully removed the flawed update from Office 365

My Office 365 version was 16.0.12307.20000 which included this Access bug

According to the MSDN forum, it should be possible to roll back to a previous Office 365 version by running this command from Start...Run:

"C:\Program Files\Common Files\microsoft shared\ClickToRun\OfficeC2RClient.exe" /update user updatetoversion=16.0.12130.20272

However when I tried this I repeatedly got an error 'Something went wrong' with error code 30029-27.

After further checks, I found my previous version was in fact 16.0.12231.20000. So I modified the above code accordingly:

"C:\Program Files\Common Files\microsoft shared\ClickToRun\OfficeC2RClient.exe" /update user updatetoversion=16.0.12231.20000

That worked! It took about 10 minutes to complete but the version number was successfully rolled back. Update queries now work again

IMPORTANT:
Remember to switch off automatic updates afterwards ... at least till the fix is released.

 

[pbaldy]

Another good link:

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

 

[theDBguy]

Another good link:

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

Hi Paul. That link seems to be broken. I took out the two stars at the end, and it worked.

 

[pbaldy]

Fixed it, thanks. Fat fingers I guess.

 

[smig]

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:

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

change it to:

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!

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)

:banghead::banghead::banghead:

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.

 

[isladogs]

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)

Are you running:
a) Office 2016 retail (MSI) - if so suggest you remove & then reinstall Office - do not tick the option to check for the latest files (updates) during installation
or
b) Office 365 subscription (C2R) - if so you need to use a different approach as described in post #11

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.

As the MS article makes clear, the issue only occurs for update queries (or SQL statements) performed on single tables with a WHERE clause. Whether you use the MS work-round or David Marten's alternative, there is no point modifying any other update query as they aren't affected by this bug.

 

[isladogs]

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

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.

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.

 

[smig]

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

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

Thanks Colin

As I only have only one Built update query I'll go with the Modify the Query's sql solution.
I think it's much better then adding too many queries that all they do is pointing to the NewTable alias name.

It's not too difficult to use Find and paste the (Select * From Begin and add the closing )

For me I can't even uninstall the faulty update, and I prefer going around by changing the code and upload a new version, and not trying to mess with the end users' systems.

Boycott MS :banghead:

 

[isladogs]

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?

 

[smig]

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?

I already did with the UPDATE ( Select * From solution
Took me about 10-15 min.

For me it's Office 2016 MSI installation. but I mostly don't want to go into the end users' systems