Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting Error 3340 Query ' ' is corrupt while executing queries DoCmd.RunSQL

Tags:

vba

ms-access

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:

Screenshot showing Microsoft Office 2010 Service Pack 2 update 448127

How can I successfully run my queries? Should I just uninstall this update?

like image 805
Zvi Redler Avatar asked Nov 13 '19 07:11

Zvi Redler


3 Answers

Summary

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.

  • If you use a C2R (Click-to-Run) version of Office, use "Update now":
    • Access 2010 C2R: Fixed in Build 7243.5000
    • Access 2013 C2R: Fixed in Build 5197.1000
    • Access 2016 C2R: Fixed in Build 12130.20390
    • Access 2019 (v1910): Fixed in Build 12130.20390
    • Access 2019 (Volume License): Fixed in Build 10353.20037
    • Office 365 Monthly Channel: Fixed in Build 12130.20390
    • Office 365 Semi-Annual: Fixed in Build 11328.20480
    • Office 365 Semi-Annual Extended: Fixed in Build 10730.20422
    • Office 365 Semi-Annual Targeted: Fixed in Build 11929.20494
  • If you use an MSI version of Office, install the update matching your Office version. All of these patches have been released on Microsoft Update, so installing all pending Windows Updates should suffice:
    • Access 2010 MSI: Fixed in KB4484193
    • Access 2013 MSI: Fixed in KB4484186
    • Access 2016 MSI: Fixed in KB4484180

Example

Here is a minimal repro example:

  1. Create a new Access database.
  2. Create a new, empty table "Table1" with the default ID field and a Long Integer field "myint".
  3. 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:

  • MSDN forum thread
  • Official Microsoft page for this bug
like image 131
Heinzi Avatar answered Nov 19 '22 18:11

Heinzi


Simplest Solution

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

like image 44
Joe Marinucci Avatar answered Nov 19 '22 17:11

Joe Marinucci


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"

like image 20
Gustav Avatar answered Nov 19 '22 16:11

Gustav