UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Office Update Can Cause Issues With Access Queries    
 
   
DougY
post Nov 13 2019, 12:50 PM
Post#1


Utterly Abby-Normal
Posts: 15,773
Joined: 30-June 04
From: Seattle, WA [USA]


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: Access error: "Query is corrupt"

--------------------
- Doug

When I said the program was fool proof, I hadn’t realized there were so many fools.
Go to the top of the page
 
isladogs
post Nov 13 2019, 01:42 PM
Post#2


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


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/44...ovember-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.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
jleach
post Nov 13 2019, 02:08 PM
Post#3


UtterAccess Administrator
Posts: 10,278
Joined: 7-December 09
From: St. Augustine, FL


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)

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 02:12 PM
Post#4


UtterAccess Moderator
Posts: 11,903
Joined: 6-December 03
From: Telegraph Hill


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

?

--------------------


Regards,

David Marten
Go to the top of the page
 
Wedge
post Nov 13 2019, 04:10 PM
Post#5



Posts: 177
Joined: 29-March 05
From: Yorkshire Dales, England


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-...ess-error-3340/

--------------------
Sam
Go to the top of the page
 
Wedge
post Nov 13 2019, 04:14 PM
Post#6



Posts: 177
Joined: 29-March 05
From: Yorkshire Dales, England


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

--------------------
Sam
Go to the top of the page
 
FrankRuperto
post Nov 13 2019, 10:15 PM
Post#7



Posts: 346
Joined: 21-September 14
From: Tampa Bay, Florida, USA


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

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
isladogs
post Nov 14 2019, 10:50 AM
Post#8


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Wedge
post Nov 14 2019, 11:36 AM
Post#9



Posts: 177
Joined: 29-March 05
From: Yorkshire Dales, England


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.

--------------------
Sam
Go to the top of the page
 
Wedge
post Nov 14 2019, 11:38 AM
Post#10



Posts: 177
Joined: 29-March 05
From: Yorkshire Dales, England


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

--------------------
Sam
Go to the top of the page
 
FrankRuperto
post Nov 14 2019, 02:11 PM
Post#11



Posts: 346
Joined: 21-September 14
From: Tampa Bay, Florida, USA


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

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
jleach
post Nov 14 2019, 07:22 PM
Post#12


UtterAccess Administrator
Posts: 10,278
Joined: 7-December 09
From: St. Augustine, FL


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

http://fmsinc.com/MicrosoftAccess/Errors/q...rrupt/index.htm

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
isladogs
post Nov 15 2019, 08:21 AM
Post#13


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


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.



--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
isladogs
post Nov 15 2019, 10:02 AM
Post#14


UtterAccess VIP
Posts: 1,891
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
fizzy1
post Nov 15 2019, 01:43 PM
Post#15



Posts: 546
Joined: 26-May 11



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.

--------------------

thanks,
fizzy1.
Go to the top of the page
 
Wedge
post Nov 15 2019, 03:39 PM
Post#16



Posts: 177
Joined: 29-March 05
From: Yorkshire Dales, England


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.

--------------------
Sam
Go to the top of the page
 
DaveH
post Nov 16 2019, 10:16 AM
Post#17



Posts: 26
Joined: 1-May 18



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.
Go to the top of the page
 
DanielPineault
post Nov 18 2019, 02:31 PM
Post#18


UtterAccess VIP
Posts: 7,006
Joined: 30-June 11



Microsoft has just released the first patch for Access 2016 MSI

You can download the patch from https://support.microsoft.com/en-us/help/44...-2016-kb4484198

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

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
FrankRuperto
post Nov 18 2019, 06:38 PM
Post#19



Posts: 346
Joined: 21-September 14
From: Tampa Bay, Florida, USA


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.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
tina t
post Nov 18 2019, 09:06 PM
Post#20



Posts: 6,182
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 02:20 PM