Originally published November 15, 2019. Last updated December 10, 2019.
A set of Microsoft Office security updates released on November 12, 2019 causes Access databases to fail when it runs Update Queries to modify data. An error like this appears when the query is run:
From Microsoft Access:
Error 3340: "Query 'qryName' is corrupt".
From a web page trying to update an Access database:
Record Update Error: -2147467259 - Query '' is corrupt.
It doesn't matter if the query is against a table in the current database, a linked table, or a linked SQL Server table. If the Access database engine is processing the UPDATE query, the error occurs.
In this example, the query qryUpdateEmployee, is perfectly fine. It was working before and will work again if the security update is uninstalled.
This problem impacts:
When attempting to run an Update query, it may fail with the error: "Query 'query name' is corrupt".
This occurs for an UPDATE query that:
These queries can be saved Access query objects or in SQL strings executed in VBA code (or other languages that use ACE).
NOTE: Passthrough queries to SQL Server are not impacted by this because those queries are not processed by the Access database engine.
The issue was introduced on November 12, 2019 via the following patch updates for MSI builds:
We are in touch with the Microsoft Access development team and do not have a clear answer on what went wrong and how something so serious was released to the world.
We share your concern that security updates like this cause a bigger problem than the viruses or malware they are attempting to block.
Here is their web page describing the problem: Access error: "Query is corrupt"
Not sure what version of Office you're using? To find out, check out Microsoft's About Office: What version of Office am I using?
There is a December 10, 2019 security update for the MSI builds, that will be available via WSUS (Windows Server Update Services) and will be automatically applied that fixes the issue.
Note: If you try to apply the patch and you receive a message that says "No products affected by this package installed in the system", this means you have a click-to-run (C2R) installation of Office, rather than an MSI installation.
The best way to fix the problem is to uninstall the Security Update for Office which is the source of the problem. The solution is different based on whether your Access installation is from a traditional perpetual license, ACE, or Office 365 subscription.
If you installed Access/Office directly on your machine through a perpetual license, local setup program/disk, or distributed the Access Database Engine (ACE), the Security Updates were installed on the machine outside of the Office 365 subscription service. Follow the steps below to find and uninstall the updates:
In our experience, if this uninstalls all the offending security updates, the problem is resolved without a need to reboot.
We will have additional information for Office 365 installations shortly.
For Office 365 Users, follow these steps:
<Configuration> <Updates Enabled="TRUE" TargetVersion="16.0.12130.20272" /> </Configuration>
setup.exe /configure config.xml
Microsoft resource: How to revert to an earlier version of Office 2016 Click-to-Run
In some situations, you may not be able to uninstall the Security Updates. Maybe you or your users don't have permission to change that. Or you are distributing your solution to people whose machines you don't control. If you want to make sure your Access application works even if the security update is installed, you have a few options:
If you decide to modify your queries, you'll need to locate all the Update queries that trigger the error. These could be saved queries and SQL queries invoked in your code. Once you identify them, you'll need to modify them so they update the results of another query, rather than updating a table directly. These examples are provided by Microsoft in the referenced page above.
For example, if you have this query which gets flagged as corrupt:
UPDATE Table 1 SET Table1.Field1 = "x" WHERE ([Table1].[Field2]=1);
You can 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);
By replacing all references to Table1 with Query1 this will produce the same results and avoid the error.
Needless to say, this is a brutal exercise that would need to be performed for every one of your databases, and retested before you deploy it again. It's also not considered a "Best Practice", so the effort does not produce a long term benefit.
If you have our Total Access Analyzer program, it documents your database queries and identifies which ones are Update queries, so that can help. It also identifies unused queries, so you may be able to delete some without the need to modify them.
An alternative is to replace the affected tables with queries. For instance, you can rename "tblCustomer" to "tblCustomer_shadow", then create a query called "tblCustomer" that is query on the shadow table.
SELECT * FROM [tblCustomer_shadow]
With this, all the queries that reference tblCustomer, now reference the query. Since the Update query bug doesn't apply to queries that update queries, this eliminates the need to modify the individual queries.
However, there's a limitation. Any code that tries to open the table fails if the table is now a query. This would include comments like:
So far, we have not experienced problems using recordsets in VBA, VB6 or Visual Studio.NET code to update records (e.g. defining an ADO or DAO recordset using variable like rst, opening a record, and editing it using code like rst.Edit and rst.Update).
These direct record updates are not the same as issuing an Update query and are not impacted by the Security Update problems. So another option is to replace Update queries with recordset updates. This may make sense for some simple updates of a few records, or an application that just has a few such conflicts with this Security Update (maybe it's a single data entry form or web page). However, update queries remain best for updating many records in a table or across multiple tables, so it wouldn't be advisable to make this a permanent solution.
Want to talk about this? Visit our Blog to post your comments and questions: Microsoft Access Query is Corrupt (Error 3340)