Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ UtterAccess.com News _ Office Update Can Cause Issues With Access Queries

Posted by: DougY Nov 13 2019, 12:50 PM

The November Patch Tuesday Office release introduce an issue with Access queries. A change to fix a security vulnerability causes some legitimate queries to be reported as corrupt. The update is 1328.20468; it was released Nov 12, but hasn’t rolled out to everyone all at once.

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.

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

Information and workaround can be found here: https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec

Posted by: isladogs Nov 13 2019, 01:42 PM

I'm one of the 'lucky ones' affected by this update.

I've 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 as Doug suggests.
Or if you do, you can uninstall it! Its KB448127 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.

Posted by: jleach Nov 13 2019, 02:08 PM

Colin - my guess that there's no mention of '07 because it's past end of life (the article states that this effects all supported versions, and '07 doesn't get security patches anymore)

Posted by: cheekybuddha Nov 13 2019, 02:12 PM

Colin,

Do you still get the error if you change your code to:

CODE
  strSQL = "UPDATE (SELECT * FROM YourTable) SET Fld1 = 1 WHERE Field1 IS NULL;"

?

Posted by: Wedge Nov 13 2019, 04:10 PM

You have to remove the KBs for each version of Access as follows:

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)

MS has fixed it but won't release the patch until NEXT month. This is terrible quality control and customer support. MS - make this patch available now! You are poisoning Access installs world-wide - this is deadly serious!!

Great resource here that saved my life today: https://borncity.com/win/2019/11/13/office-november-2019-updates-are-causing-access-error-3340/

Posted by: Wedge Nov 13 2019, 04:14 PM

Further update. Even though you can remove the KB Windows will keep trying to put it back on so beware.

Posted by: FrankRuperto Nov 13 2019, 10:15 PM

Disable the Windows Update service in Computer Management > Services and applications.

Posted by: isladogs Nov 14 2019, 10:50 AM

Hi David
Sorry. I've uninstalled the Office update so unable to test your suggestion

Posted by: Wedge Nov 14 2019, 11:36 AM

Hi Frank. Thanks for the info but not really an option with lots of different customers across several sites. I am sure clients don't want me stopping them getting valid security updates. Hoping that MS pull the broken KB - I don't really understand why they keep pushing it out.

Posted by: Wedge Nov 14 2019, 11:38 AM

I am not sure if this KB is even really broken as such. It looks like someone has decided that update queries that directly effect tables are 'insecure' so has simply turned them off. I wouldn't mind but if MS are going to do this could you please give us a head-up so we can change our code? Wasted many hours yesterday morning with this one. As you can probably guess - I am not happy. mad.gif

Posted by: FrankRuperto Nov 14 2019, 02:11 PM

@Wedge

Agreed, disabling win update service has its downside, plus not all users can disable it because their boxes are managed by IT dept's via group policy settings.
MS is progressively getting worse at publishing updates without thoroughly testing them to make sure it doesn't impact users.

Posted by: jleach Nov 14 2019, 07:22 PM

Luke Chung of FMS has a comprehensive writeup on this here, including step by step fixes w/ visuals:

http://fmsinc.com/MicrosoftAccess/Errors/query_is_corrupt/index.htm

Posted by: isladogs Nov 15 2019, 08:21 AM

Just realised that I still have the issue on Access 365 so I tested cheeky buddha's suggestion

QUOTE
Do you still get the error if you change your code to:
CODE
strSQL = "UPDATE (SELECT * FROM YourTable) SET Fld1 = 1 WHERE Field1 IS NULL;"
?


I used
CODE
UPDATE (SELECT * FROM Table1a) SET Table1a.Surname = "XXX"
WHERE (((Table1a.[Pupil ID])="11816"));


The result was unexpected!
Doing this updates the table alias to '%$##@_Alias' and the query does indeed run without error.



Posted by: isladogs Nov 15 2019, 10:02 AM

Further Update:
I have now also 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:

CODE
"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:

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

EDIT:
It would be good if FMS also added info on fixing 365 to their web page. I wanted to provide feedback to the FMS article but there appears to be no means of doing so

Posted by: fizzy1 Nov 15 2019, 01:43 PM

I have a bunch of customers using one app. I have no control over how those users interact with my app, be it the runtime or full Access, and what version (2010, 2013, 2016 are all in use), or bitness (this part kills me). Some of them need the runtime but had MSO vs CTR conflicts so we had to install an "off year" runtime from the rest of their Office installation. So it's not feasible for me to get customers to mess with their Windows updates. Consequently I need to employ the workaround. So this latest debacle is just another straw on this camel's back. Probably the final one.

Anyway, my app has 66 SQL UPDATE lines that don't involve a join, and no stored action queries. I have lots of stuff like this:

CODE
    strSQL = "UPDATE tblItems SET ItemCost = " & CCur(Cost) & " WHERE ItemID = " & ItemID
    CurrentDb.Execute strSQL , dbFailOnError Or dbSeeChanges
    CurrentDb.Execute Replace(strSQL , "tblItems", "tblItemsCache"), dbFailOnError

I chose to make queries that mimic the table name with an additional "Xreplace" appended to the end, so the query for "tblItems" becomes "tblItemsXreplace". It could be anything but it needs to be consistent because when all this nonsense is over I'm just going to search for "Xreplace" and replace it with "".

This app has server tables and a local cache, so I ended up with things like this:

Tables:
tblItems
tblItemsCache

Queries:
tblItemsXreplace
tblItemsCacheXreplace

and code

CODE
    strSQL = "UPDATE tblItemsXreplace SET ItemCost = " & CCur(Cost) & " WHERE ItemID = " & ItemID
    CurrentDb.Execute strSQL , dbFailOnError Or dbSeeChanges
    CurrentDb.Execute Replace(strSQL , "tblItems", "tblItemsCache"), dbFailOnError

I don't need to mess with the third line above as the Replace function takes care of the rename for the second CurrentDb.Execute.

So far it seems to be going pretty well. Took me about 45 minutes to go through the app and make the necessary code changes, and to create the 13 different queries.

Fingers crossed this takes care of it!

[censored] glad I only have this one app...

Toby.

Posted by: Wedge Nov 15 2019, 03:39 PM

Luke is a hero. thanks.gif

I love FMS thumbup.gif

I have read the utterly amazingly woeful response on the FMS page from MS. In the name of sanity MS - even if you:

1). released an horrific security 'patch' that kills update queries
2). without testing
3). and will take a month (!) to fix it

will you...

*PLEASE STOP PUSHING IT OUT ON WINDOWS UPDATE*

I am hoping you at least have the ability to speak with someone who runs that part of the business and can maybe take it down? Or is that just too complicated?

This is comically bad MS.

Posted by: DaveH Nov 16 2019, 10:16 AM

Hardly a monthly update from Microsoft for Win 10/7 has passed without incident this year, surely some of the millions of Office users must have tested these updates before they were released but apparently not - else the fault would have been discovered.

KB4484127, KB4484119, and KB4484113 along with KB890830 (malicious removal tool) can’t possibly have been tested properly or at all.

Then to add insult to injury they won’t fix it until Decembers updates and even worse they have not pulled it.

I can only suggest that all the “script kiddies” fresh from University start their careers at MS implementing Windows updates and they’ve laid off all the old hands to try and save money, this is the result.

Posted by: DanielPineault Nov 18 2019, 02:31 PM

Microsoft has just released the first patch for Access 2016 MSI

You can download the patch from https://support.microsoft.com/en-us/help/4484198/november-18-2019-update-for-office-2016-kb4484198

As for the other versions/editions … patches should be coming soon.

Posted by: FrankRuperto Nov 18 2019, 06:38 PM

Lets hope MS thoroughly tested the patch, and not fix one thing that breaks other(s). These repeated blunders are putting a big dent in MS' credibility.

Posted by: tina t Nov 18 2019, 09:06 PM

QUOTE
These repeated blunders are putting a big dent in MS' credibility.

they have credibility left to dent? i hadn't noticed.

the (SELECT * FROM MyTable) workaround does work, at least so far, for me. since i am still working on my IT dept's conversion-from-A97-to-A2016-64bit project, i'm only going to fix the queries that err as i'm continuing to test my converted dbs. hopefully the 11/24 fix will take care of the issue without making a bigger mess.

:(
tina

Posted by: alancossey Nov 19 2019, 04:26 AM

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

It says that the update for "Access for Office 365/Access 2016 C2R/Access 2019 (Version 1911)" will be available around 22nd November. On the Monthly update channel we are currently version 1910 so I am hoping it means that we will get that corrected version of 1911 (12228.20152) that day. The page is not totally clear to me.

Posted by: mike60smart Nov 19 2019, 04:22 PM

Hi David

That worked for me

Many thanks


Posted by: FrankRuperto Nov 20 2019, 01:16 PM

Uninstalling the security update may temporarily fix the problem, but I get the feeling MS wants us to mod all the update statements since "An information disclosure vulnerability exists which fails to properly handle objects in memory"

According to https://portal.msrc.microsoft.com/en-US/security-guidance/advisory/CVE-2019-1402, MS' exploitability assessment states that the vulnerability is not publicly disclosed and no exploits have ocurred, but I feel the cat's out of the bag and hackers could exploit it now that they know everyone is rolling back the update.

Posted by: isladogs Nov 20 2019, 02:10 PM

True but do we know whether the fix includes the security part of the original update?

Posted by: DanielPineault Nov 20 2019, 04:28 PM

QUOTE
It says that the update for "Access for Office 365/Access 2016 C2R/Access 2019 (Version 1911)" will be available around 22nd November. On the Monthly update channel we are currently version 1910 so I am hoping it means that we will get that corrected version of 1911 (12228.20152) that day. The page is not totally clear to me.

MS has pushed out a fix for O365 C2R Monthly Channel in build 12130.20390 which is still 1910 according to https://docs.microsoft.com/en-us/officeupdates/update-history-office365-proplus-by-date?redirectSourcePath=%252fen-us%252farticle%252fVersion-and-build-numbers-of-update-channel-releases-ae942449-1fca-4484-898b-a933ea23def7. So anyone running O365 C2R Monthly Channel need only perform a manual update (File -> Account -> Update Options -> Update Now) and the problem should go away.

Posted by: FrankRuperto Nov 20 2019, 05:43 PM

At this point in time we don't know what MS plans on including in the fix, but my common sense tells me that they will try to resolve the handling of objects in memory so that the affected update statements wont need to be modified. However, in the https://www.catalog.update.microsoft.com/ScopedViewInline.aspx?updateid=894292fa-4136-46ba-85a1-548dcbd40d95 it says "A security vulnerability exists in Microsoft Office 2013 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability.", which sounds to me like MS' default generic description they use most of the times so I who knows what the real problem is. One things for sure, I bet many Access apps will remain broken if MS decides to continue enforcing the new security rule because either the users only have an accde and the developers are no longer available, or it will take forever to mod update statements in apps with tons of objects and code lines, and let alone, the tons of frontends that require the mod. In the meantime, those who rolled back the update in order to run their apps without having to mod the code are vulnerable. Those lucky enough to not have to mod the code in too many places will continue to run their apps whether the update is installed or not, unless MS' fix breaks something else.

Posted by: Wedge Nov 21 2019, 07:04 AM

That's a start but the others (especially the 365 one) is needed urgently. Why on earth MS cannot stop a rogue patch like this is anyone's guess - this is terrible MS. You are still pushing it out!

Posted by: youngb Nov 21 2019, 09:19 AM

Hi Guys,

I just learned the hard way yesterday about this issue and done a fix by installing a runtime version of Access07 to fix the problem, a pragmatic approach, they were a business that needed to get up and running fairly quickly and it worked despite been Windows 10, now have to say in general I didn't get caught because some my clients are on 2007 runtime versions, (a few are still on 2003 and they were fine) , initially I though was only full version but looks like it is also with Runtime, it was only a few who had unintentionally got updated with Office 365 with Windows 10 update from Windows 7 but I have put all these on 2007 runtime to get it working.
I have to agree that this 'Update' would not help your confidence in Access Applications.

Regards
Briah


Posted by: Wedge Nov 21 2019, 03:48 PM

QUOTE
MS has pushed out a fix for O365 C2R Monthly Channel in build 12130.20390 which is still 1910 according to Update history for Office 365 ProPlus (listed by date). So anyone running O365 C2R Monthly Channel need only perform a manual update (File -> Account -> Update Options -> Update Now) and the problem should go away.


I can confirm that this does indeed work. thumbup.gif

Posted by: theDBguy Nov 22 2019, 11:50 AM

Hi All,

For what it's worth, I created the attached small utility as my way of temporarily handling this issue. It's still preliminary, but I didn't want to wait too long for a permanent fix from MS before deciding to share it, so here it goes. I know it doesn't handle all potential scenarios like a database with a Make-Table query, but I'll try to add that functionality later.

Hope it helps someone. Please let me know if you find any problems using it.

Thank you!


 theDBguyQueryBugFixV1.4.zip ( 29.88K ): 2
 

Posted by: theDBguy Nov 22 2019, 02:35 PM

I had some time to add the functionality I mentioned earlier. Please see attached updated version... Thanks!



 theDBguyQueryBugFixV1.5.zip ( 33.63K ): 6
 

Posted by: jleach Nov 22 2019, 03:48 PM

All: I've edited the permissions on the news board to allow members to download from it. Some quirk in the backend specific to this board. Please try again and post back if there's continued issues.

Thanks

Posted by: theDBguy Nov 22 2019, 03:50 PM

Oh, thanks, Jack. I just created https://www.UtterAccess.com/forum/index.php?showtopic=2055885 to get around the download problem reported by some users. Oh well...

Posted by: gemmathehusky Nov 25 2019, 08:00 AM

Personally, I think the cure suggested by MS is as bad as the problem.

I think as well as the query issue, it also affects recordset operations (maybe depending on the query), so opening a table, and the rs.addnew etc also failed, I believe.
Changing an testing everything in complex databases is hardly feasible.

Anyway, I have advised clients to unwind the update as the simplest fix

With regard to colin/isladogs issue about the version to revert to, I suspect the version you needed depended on the update stream you were using.

https://docs.microsoft.com/en-us/officeupdates/update-history-office365-proplus-by-date









Posted by: theDBguy Nov 25 2019, 12:10 PM

Hi Dave. Using the fix I suggested/recommend, I tested, last night, opening a recordset was not a problem. Now, I just tested adding a new record to the recordset is also not a problem. No errors.

Posted by: JeffK Dec 3 2019, 09:13 AM

ADODB recordset Update calls are breaking for me as well, with the same “Query ‘’ is corrupt” message. Access 2019, only on PCs with the security update applied. However, it’s only in cases where CursorLocation is set to adUseClient. Remove that solves the problem. With the client-side cursor setting, no changes to the source fix the problem ( such as selectig from a query or subquery).

Posted by: theDBguy Dec 3 2019, 07:44 PM

Hi. Do you know if you have a volume licensed version of 2019?

Posted by: JeffK Dec 4 2019, 10:40 AM

Yes, my Access 2019 is the perpetual license, not O365.

Posted by: theDBguy Dec 4 2019, 11:26 AM

Just curious because VL 2019 doesn't have a fix available yet.

Posted by: rsindle Dec 4 2019, 02:58 PM

This is a slightly different spin on this problem.
I have Office Pro 2010 and 2013 installed on my dev laptop.
2010 did NOT exhibit this problem. 2013 did.
Weird thing is if I go to "Installed Updates" all of my 2010 Security updates etc show up.
NONE for 2013.
My Access 2013 is (according to Microsoft) the latest version (15.0.5189.1000) 32-bit.
I went ahead and ran the 2010 patch (ace2010-kb2986256-fullfile-x86-glb.exe) and that ran.
The 2013 patch (ace2013-kb2965317-fullfile-x86-glb.exe) gave me the message:
"There are no products affected by this package installed on this system"
So I am dead-in-the-water on 2013, but 2010 runs fine.
****** UPDATE ON THIS ******
Apparently I DO have C2R for my 2013 (MSI for my 2010) as this link says:
https://blogs.technet.microsoft.com/office_integration__sharepoint/2016/06/23/determining-your-office-version-msi-vs-c2r/

So will be waiting impatiently, for the 12/10/2019 fix for MY system.
UGH!
pullhair.gif
Rob


Posted by: fizzy1 Dec 12 2019, 03:45 PM

So, December 10 was 2 days ago. Is all of this mess behind us now?

Posted by: theDBguy Dec 12 2019, 09:04 PM

For the most part, yes, but not entirely. It's possible subsequent updates could override the fix update and bring back the problem. If so, you would have to reinstall the fix.

Posted by: FrankRuperto Dec 12 2019, 09:13 PM

QUOTE
It's possible subsequent updates could override the fix update and bring back the problem.


If that happens we would all be back to square one so I am hoping MS has enough sense to prevent that from happening.

Posted by: fizzy1 Jan 16 2020, 03:31 PM

So is this all behind us, and can I revert the cob-job fix I had to put in place?

Posted by: theDBguy Jan 16 2020, 03:50 PM

Hi. Yes, you "should be" safe now. Just double check first that you have applied the patch before going back. Also, be aware it's possible for the bug to come back and therefore have to apply the patch again. At least, that's what happened before to some users. Maybe you'll be luckier than them. Cheers!