Microsoft Access Query is Corrupt Error 3340

Provided by: Luke Chung, Molly Pell, and the FMS Development Team

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".

Query 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.

Programs Impacted

This problem impacts:

  • All Microsoft Access versions since 2010 including Access 2010, 2013, 2016 and 2019 and Office 365
  • 32 and 64-bit versions of Microsoft Access
  • Programs that use the Access Database Engine (ACE) and ADO to connect to Access databases (ACCDB and MDB) files and update them with queries. This could include:
    • Programs written in Visual Studio .NET or Visual Basic 6
    • Web solutions built on ASP.net, Classic ASP, etc.
    • VBA code running in other hosts such as Excel, PowerPoint, Word
    • Microsoft programs such as PowerBI, Power Query, and SQL Server Migration Assistant (SSMA) that use ACE to work with Access databases (we have not confirmed these programs are using Update queries that crash)

Types of Update Queries Affected

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:

  • Updates a single table (i.e. it updates a table, rather than the output of a Select query or join)
  • Specifies a WHERE clause (i.e. has entries in the Criteria row in the query designer)

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.

Security Updates for Microsoft Office

The issue was introduced on November 12, 2019 via the following patch updates for MSI builds:

  • Office 2010: Description of the security update for Office 2010 - November 12, 2019 (KB4484127)
  • Office 2013: Description of the security update for Office 2013 - November 12, 2019 (KB4484119)
  • Office 2016: Description of the security update for Office 2016 - November 12, 2019 (KB4484113)
  • Office 2016: Update for Office 2016 - November 12, 2019 (KB3085368)

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?

Current Microsoft Fixes

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.

  • Access 2010: KB4484193 - Build 14.0.7243.5000
  • Access 2013: KB4484186 - Build 15.0.5197.1000
  • Access 2016: KB4484180 - Build 16.0.4939.1000

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.

  • Access 2010 MSI, Access Database Engine 2010 Redistributable: Fixed Build 7241.5001 - November 27, 2019
    This update is only available for manual download and installation from the Microsoft Download Center.
    To manually download the update, visit November 27, 2019, update for Office 2010 (KB2986256).
    Organizations that want to distribute the update without requiring each user to install manually, visit Distribute product updates for Office 2010 for more information.
  • Access 2010 C2R: Fixed Build 7243.5000 - December 10, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
  • Access 2013 MSI: Fixed Build 5189.1002 - November 27, 2019
    This update is only available for manual download and installation from the Microsoft Download Center.
    The update can't be installed on Office Home and Student 2013 RT.
    To manually download the update, visit November 27, 2019, update for Office 2013 (KB2965317).
    Organizations that want to distribute the update without requiring each user to install manually, visit Distribute updates for Office 2013 products for more information.
  • Access 2013 C2R: Fixed Build 5197.1000 - December 10, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
  • Access 2016 MSI, Access Database Engine 2016 Redistributable: Fixed Build 4927.1002 - November 18, 2019
    This update is only available for manual download and installation from the Microsoft Download Center.
    To manually download the update, visit November 18, 2019, update for Office 2016 (KB4484198).
  • Access 2019 Volume License: Fixed Build 10353.20037 - December 10, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
  • Access O365 Monthly Channel/ Access 2016 C2R/Access 2019 (Version 1910): Fixed Build 12130.20390 - November 18, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
    For more information on the update, visit Version 1910: November 18.
  • Access for Office 365 (Microsoft Store Version) Fixed Build 12130.20390 - November 22, 2019
    Open Microsoft Store, Click on [...] in the upper right corner, Choose [Downloads and Updates]
  • Access for O365 Semi-Annual (Version 1808) Fixed Build 10730.20422 - November 22, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
    For more information on the update, visit Version 1808: November 22.
  • Access for O365 Semi-Annual (Version 1902) Fixed Build 11328.20480 - November 22, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].
    For more information on the update, visit Version 1902: November 22.
  • Access for O365 Semi-Annual (Version 1908) Fixed Build 11929.20494 - November 22, 2019
    Open an Office program, select [File], click [Account], click [Update Options] and select [Update Now].

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.


Perpetual License and Disk Installations of Office/Access, and ACE Distribution Installations

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:

  1. Press the Windows key and R to bring up the Run prompt.
  2. Type "appwiz.cpl" and press [OK] to launch the Control Panel's Program and Features program.
    Run command
  3. Click [Viewed installed updates].
    View installed updates
  4. In the Search bar, search for the following KB's.
    • Office 2010: KB4484127
    • Office 2013: KB4484119
    • Office 2016: KB4484113
  5. On this system, KB4484127 is present.
  6. Right-click the program, and select [Uninstall].
  7. Click [Yes] to start the uninstall process.
  8. The prompt will ask if you're ready to uninstall. Click the [Uninstall] button to proceed.
  9. Click [OK] when the uninstall of the update was successful.

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.


Microsoft Office 365Office 365 Subscription (Click to Run) Installations

For Office 365 Users, follow these steps:

  1. Use this link to look up the version of Office that you want to revert to: Update history for Office 365 ProPlus (listed by date)
    For instance, "16.0.12130.20272"
  2. Download the Office Deployment tool (Download).
    Follow the prompts to unzip the files to a folder that’s easily accessible.
  3. In that folder, use Notepad to create a new file named “config.xml” containing:
    <Configuration>
    <Updates Enabled="TRUE" TargetVersion="16.0.12130.20272" />
    </Configuration>			
    
    config.xml
  4. In an elevated command prompt, change directories to the folder where you unzipped the files. Then run the following command:
    setup.exe /configure config.xml
    
    command prompt
  5. Open an Office program, go to the Account page, click [Update Options] and select [Update Now]. This reverts back to update 16.0.12130.20272.
    Microsoft Office Account Update Now
  6. Open an Office program, go to the Account page, click [Update Options] and select [Disable Updates]. This ensures that the problematic security update is not installed again in the future.
    Microsoft Office Disable Updates

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:

  1. Modify all your queries so they don't trigger the problem.
  2. Rename tables to a new table name and create queries for them using the original table name.
  3. Use DAO/ADO recordsets to update records.
  4. Microsoft Access 2007Deploy your Access application with Access 2007 or earlier. You can use the free Access 2007 runtime to do so. More information on our Microsoft Access Runtime page which includes a free download of the runtime program. This assumes your Microsoft Access database runs in Access 2007 and doesn't include features introduced in Access 2010 or later. Visit our page on Microsoft Access Version Features to see the differences among versions.

Modify All Your Update Queries

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.

Not a Best Practice

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.

Total Access AnalyzerIf 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.


Replace Tables with Queries

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:

  • DoCmd.OpenTable
  • Macros that open a table
  • TableDef or TableDefs collection
  • OpenRecordset commands with explicit table options

Module CodeUsing Code and Recordsets to Update Data

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.

Blog for Access Query is CorruptFeedback

Want to talk about this? Visit our Blog to post your comments and questions: Microsoft Access Query is Corrupt (Error 3340)

 


Free Product Catalog from FMS