Since installing the windows update for Office 2010 resolving KB 4484127 I get an error while executing queries which contain a WHERE clause.
For example executing this query:
DoCmd.RunSQL "update users set uname= 'bob' where usercode=1"
Results in this error:
Error number = 3340 Query ' ' is corrupt
The update in question is currently still installed:
How can I successfully run my queries? Should I just uninstall this update?
This is a known bug caused by the Office updates released on November 12, 2019. The bug affects all versions of Access currently supported by Microsoft (from Access 2010 to 365).
This bug has been fixed.
Here is a minimal repro example:
Execute the following code in the VBA editor's Immediate Window:
CurrentDb.Execute "UPDATE Table1 SET myint = 1 WHERE myint = 1"
Expected result: The statement successfully finishes.
Actual result with one of the buggy updates installed: Run-time error 3340 occurs ("Query '' is corrupt").
Related links:
90150000-006E-0409-0000-0000000FF1CE ... that's -0409-, not -0407-. –
Utimer -006E-0409- as well. Both machines have Service Pack 1 for Microsoft Office 2013 (KB2850036) installed. –
Utimer {90140000-0011-0000-0000-0000000FF1CE} in the batch script. Note {9014... not {9114..} –
Osithe For my users, waiting nearly a month till December 10 for a fix release from Microsoft is not an option. Nor is uninstalling the offending Microsoft update across several government locked down workstations.
I need to apply a workaround, but am not exactly thrilled with what Microsoft suggested - creating and substituting a query for each table.
The solution is to replace the Table name with a simple (SELECT * FROM Table) query directly in the UPDATE command. This does not require creating and saving a ton of additional queries, tables, or functions.
EXAMPLE:
Before:
UPDATE Table1 SET Field1 = "x" WHERE (Field2=1);
After:
UPDATE (SELECT * FROM Table1) SET Field1 = "x" WHERE (Field2=1);
That should be much easier to implement across several databases and applications (and later rollback).
This is not a Windows update problem, but a problem that was introduced with the November Patch Tuesday Office release. A change to fix a security vulnerability causes some legitimate queries to be reported as corrupt. Because the change was a security fix, it impacts ALL builds of Office, including 2010, 2013, 2016, 2019, and O365.
The bug has been fixed in all channels, but the timing of delivery will depend on what channel you are on.
For 2010, 2013, and 2016 MSI, and 2019 Volume License builds, and the O365 Semi-annual channel, the fix will be in the December Patch Tuesday build, Dec 10. For O365, Monthly Channel, and Insiders, this will be fixed when the October fork is released, currently planned for Nov 24.
For the Semi-Annual channel, the bug was introduced in 11328.20468, which was released Nov 12, but doesn’t roll out to everyone all at once. If you can, you might want to hold off on updating until Dec 10.
The issue occurs for update queries against a single table with a criteria specified (so other types of queries shouldn’t be impacted, nor any query that updates all rows of a table, nor a query that updates the result set of another query). Given that, the simplest workaround in most cases is to change the update query to update another query that selects everything from the table, rather than updating the query directly.
I.e., if you have a query like:
UPDATE Table1 SET Table1.Field1 = "x" WHERE ([Table1].[Field2]=1);
Then, create a new query (Query1) defined as:
Select * from Table1;
and update your original query to:
UPDATE Query1 SET Query1.Field1 = "x" WHERE ([Query1].[Field2]=1);
Official page: Access error: "Query is corrupt"
To temporarily resolve this issue depends on the Access version in use:
Access 2010 Uninstall update KB4484127
Access 2013 Uninstall update KB4484119
Access 2016 Uninstall update KB4484113
Access 2019 IF REQUIRED (tbc). Downgrade from Version 1808 (Build 10352.20042) to Version 1808 (Build 10351.20054)
Office 365 ProPlus Downgrade from Version 1910 (Build 12130.20344) to a previous build, see https://support.microsoft.com/en-gb/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic
We and our clients have struggled with this the last two days and finally wrote a paper to discuss the issue in detail along with some solutions: http://fmsinc.com/MicrosoftAccess/Errors/query_is_corrupt/
It includes our findings that it impacts Access solutions when running update queries on local tables, linked Access tables, and even linked SQL Server tables.
It also impacts non-Microsoft Access solutions using the Access Database Engine (ACE) to connect to Access databases using ADO. That includes Visual Studio (WinForm) apps, VB6 apps, and even web sites that update Access databases on machines that never had Access or Office installed on them.
This crash can even impact Microsoft apps that use ACE such as PowerBI, Power Query, SSMA, etc. (not confirmed), and of course, other programs such as Excel, PowerPoint or Word using VBA to modify Access databases.
In addition to the obvious uninstallation of the offending Security Updates, we also include some options when it's not possible to uninstall due to permissions or distribution of Access applications to external customers whose PCs are beyond your control. That includes changing all the Update queries and distributing the Access applications using Access 2007 (retail or runtime) since that version isn't impacted by the security updates.
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.
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 using 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 queries 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.
(And of course, depending on your coding style, it is also possible to break things in your application. So after verifying that the fix generally works for you, it's never a bad idea to export all your objects as text and use some find replace magic to ensure that any occurrences of table names use will be run against the queries and not the tables.)
In my case, this fix works largely without any side effects, I just needed to manually rename USysRibbons_Table back to USysRibbons, as I hadn't marked it as a system table when I created it in the past.
TableDef.Attributesand copy that to my answer ;) and an undo function is a good idea (but old and new name should be stored in a table as depending on no tables with suffix before the rename). Some other parts are faulty (e.g. tables can end with the suffix or newname is allready in use or On Error Resume Nextwithout handling errors later). Do you know RubberduckVBA? This Addin can inspect your code and makes nice suggestions for improvemnt, besides all the other features. –
Inspire VBA-Script for MS-Workaround:
It is recommended to remove the buggy update, if possible (if not try my code), at least for the MSI Versions. See answer https://mcmap.net/q/239435/-getting-error-3340-query-39-39-is-corrupt-while-executing-queries-docmd-runsql .
For CTR(Click-To-Run) Versions, you have to remove all Office November-Updates, what may cause serious security issues (not sure if any critical fixes would be removed).
From @Eric's comments:
Table.Tablenameto bind forms, they get unbound as the former table-name is now a query-name!.OpenRecordSet(FormerTableNowAQuery, dbOpenTable) will fail ( as its a query now, not a table anymore)Caution! Just quick tested against Northwind.accdb on Office 2013 x86 CTR No Warranty!
Private Sub RenameTablesAndCreateQueryDefs()
With CurrentDb
Dim tdf As DAO.TableDef
For Each tdf In .TableDefs
Dim oldName As String
oldName = tdf.Name
If Not (tdf.Attributes And dbSystemObject) Then 'credit to @lauxjpn for better check for system-tables
Dim AllFields As String
AllFields = vbNullString
Dim fld As DAO.Field
For Each fld In tdf.Fields
AllFields = AllFields & "[" & fld.Name & "], "
Next fld
AllFields = Left(AllFields, Len(AllFields) - 2)
Dim newName As String
newName = oldName
On Error Resume Next
Do
Err.Clear
newName = newName & "_"
tdf.Name = newName
Loop While Err.Number = 3012
On Error GoTo 0
Dim qdf As DAO.QueryDef
Set qdf = .CreateQueryDef(oldName)
qdf.SQL = "SELECT " & AllFields & " FROM [" & newName & "]"
End If
Next
.TableDefs.Refresh
End With
End Sub
For testing:
Private Sub TestError()
With CurrentDb
.Execute "Update customers Set City = 'a' Where 1=1", dbFailOnError 'works
.Execute "Update customers_ Set City = 'b' Where 1=1", dbFailOnError 'fails
End With
End Sub
Inventory to reorder Subform for HometoInventorytable inHomeform, without problems. Even isn't it recomended to bind forms to queries instead of tables (isn't binding to table likeSelect * From table ?). –
Inspire Table.TableName notation. If you do SELECT * FROM TableName instead, you're of course fine. But if you use Table.TableName, your subform will become unbound if you rename the table. –
Orva TableDefs!MyTableName.OpenRecordset(dbOpenTable), though (support of index seeking), which I also tend to use and will also cause errors with your approach –
Orva For those looking to automate this process via PowerShell, here are a few links I found that may be helpful:
There is a PowerShell script available here https://www.arcath.net/2017/09/office-update-remover that searches the registry for a specific Office update (passed in as a kb number) and removes it using a call to msiexec.exe. This script parses out both GUIDs from the registry keys to build the command to remove the appropriate update.
One change that I would suggest would be using the /REBOOT=REALLYSUPPRESS as described in How to uninstall KB4011626 and other Office updates (Additional reference: https://learn.microsoft.com/en-us/windows/win32/msi/uninstalling-patches). The command line you are building looks like this:
msiexec /i {90160000-0011-0000-0000-0000000FF1CE} MSIPATCHREMOVE={9894BF35-19C1-4C89-A683-D40E94D08C77} /qn REBOOT=REALLYSUPPRESS
The command to run the script would look something like this:
OfficeUpdateRemover.ps1 -kb 4484127
The recommended approach here seems to be hiding the update. Obviously this can be done manually, but there are some PowerShell scripts that can help with automation. This link: https://www.maketecheasier.com/hide-updates-in-windows-10/ describes the process in detail, but I will summarize it here.
Use the following command to hide an update by KB number:
Hide-WUUpdate -KBArticleID KB4484127
Hopefully this will be a help to someone else out there.
I replaced the currentDb.Execute and Docmd.RunSQL with a helper function. That can pre-process and change the SQL Statement if any update statement contains only one table. I already have a dual(single row, single column) table so i went with a fakeTable option.
Note: This won't change your query objects. It will only help SQL executions via VBA. If you would like to change your query objects, use FnQueryReplaceSingleTableUpdateStatements and update your sql in each of your querydefs. Shouldn't be a problem either.
This is just a concept (If it's a single table update modify the sql before execution). Adapt it as per your needs. This method does not create replacement queries for each table (which may be the easiest way but has it's own drawbacks. i.e performance issues)
+Points:
You can continue to use this helper even after MS fixing the bug it won't change anything. In case, future brings another problem, you are ready to pre-process your SQL in one place. I didn't go for uninstalling updates method because that requires Admin access + gonna take too long to get everyone on the correct version + even if you uninstall, some end users's group policy installs the latest update again. You are back to the same problem.
If you have access to the source-code, use this method and you are 100% sure that no enduser is having the issue.
Public Function Execute(Query As String, Optional Options As Variant)
'Direct replacement for currentDb.Execute
If IsBlank(Query) Then Exit Function
'invalid db options remove
If Not IsMissing(Options) Then
If (Options = True) Then
'DoCmd RunSql query,True ' True should fail so transactions can be reverted
'We are only doing this so DoCmd.RunSQL query, true can be directly replaced by helper.Execute query, true.
Options = dbFailOnError
End If
End If
'Preprocessing the sql command to remove single table updates
Query = FnQueryReplaceSingleTableUpdateStatements(Query)
'Execute the command
If ((Not IsMissing(Options)) And (CLng(Options) > 0)) Then
currentDb.Execute Query, Options
Else
currentDb.Execute Query
End If
End Function
Public Function FnQueryReplaceSingleTableUpdateStatements(Query As String) As String
' ON November 2019 Microsoft released a buggy security update that affected single table updates.
'https://mcmap.net/q/239435/-getting-error-3340-query-39-39-is-corrupt-while-executing-queries-docmd-runsql
Dim singleTableUpdate As String
Dim tableName As String
Const updateWord As String = "update"
Const setWord As String = "set"
If IsBlank(Query) Then Exit Function
'Find the update statement between UPDATE ... SET
singleTableUpdate = FnQueryContainsSingleTableUpdate(Query)
'do we have any match? if any match found, that needs to be preprocessed
If Not (IsBlank(singleTableUpdate)) Then
'Remove UPDATe keyword
If (VBA.Left(singleTableUpdate, Len(updateWord)) = updateWord) Then
tableName = VBA.Right(singleTableUpdate, Len(singleTableUpdate) - Len(updateWord))
End If
'Remove SET keyword
If (VBA.Right(tableName, Len(setWord)) = setWord) Then
tableName = VBA.Left(tableName, Len(tableName) - Len(setWord))
End If
'Decide which method you want to go for. SingleRow table or Select?
'I'm going with a fake/dual table.
'If you are going with update (select * from T) as T, make sure table aliases are correctly assigned.
tableName = gDll.sFormat("UPDATE {0},{1} SET ", tableName, ModTableNames.FakeTableName)
'replace the query with the new statement
Query = vba.Replace(Query, singleTableUpdate, tableName, compare:=vbDatabaseCompare, Count:=1)
End If
FnQueryReplaceSingleTableUpdateStatements = Query
End Function
Public Function FnQueryContainsSingleTableUpdate(Query As String) As String
'Returns the update ... SET statment if it contains only one table.
FnQueryContainsSingleTableUpdate = ""
If IsBlank(Query) Then Exit Function
Dim pattern As String
Dim firstMatch As String
'Get the pattern from your settings repository or hardcode it.
pattern = "(update)+(\w|\s(?!join))*set"
FnQueryContainsSingleTableUpdate = FN_REGEX_GET_FIRST_MATCH(Query, pattern, isGlobal:=True, isMultiline:=True, doIgnoreCase:=True)
End Function
Public Function FN_REGEX_GET_FIRST_MATCH(iText As String, iPattern As String, Optional isGlobal As Boolean = True, Optional isMultiline As Boolean = True, Optional doIgnoreCase As Boolean = True) As String
'Returns first match or ""
If IsBlank(iText) Then Exit Function
If IsBlank(iPattern) Then Exit Function
Dim objRegex As Object
Dim allMatches As Variant
Dim I As Long
FN_REGEX_GET_FIRST_MATCH = ""
On Error GoTo FN_REGEX_GET_FIRST_MATCH_Error
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Multiline = isMultiline
.Global = isGlobal
.IgnoreCase = doIgnoreCase
.pattern = iPattern
If .test(iText) Then
Set allMatches = .Execute(iText)
If allMatches.Count > 0 Then
FN_REGEX_GET_FIRST_MATCH = allMatches.item(0)
End If
End If
End With
Set objRegex = Nothing
On Error GoTo 0
Exit Function
FN_REGEX_GET_FIRST_MATCH_Error:
FN_REGEX_GET_FIRST_MATCH = ""
End Function
Now just CTRL+F
Search and replace docmd.RunSQL with helper.Execute
Search and replace [currentdb|dbengine|or your dbobject].execute with helper.execute
have fun!
Ok I'll chime in here as well, because even though this bug has been fixed, that fix has yet to populate fully through various enterprises where the end users may not be able to update (like my employer...)
Here's my workaround for DoCmd.RunSQL "UPDATE users SET uname= 'bob' WHERE usercode=1". Just comment out the offending query and drop in the code below.
'DoCmd.RunSQL "UPDATE users SET uname= 'bob' WHERE usercode=1"
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("users")
rst.MoveLast
rst.MoveFirst
rst.FindFirst "[usercode] = 1" 'note: if field is text, use "[usercode] = '1'"
rst.Edit
rst![uname] = "bob"
rst.Update
rst.Close
Set rst = Nothing
I can't say it's pretty, but it gets the job done.
© 2022 - 2024 — McMap. All rights reserved.