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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   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,762
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,800
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 Editor
Posts: 10,213
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)

--------------------
Go to the top of the page
 
cheekybuddha
post Nov 13 2019, 02:12 PM
Post#4


UtterAccess VIP
Posts: 11,680
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: 175
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: 175
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: 252
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 Yesterday, 10:50 AM
Post#8


UtterAccess VIP
Posts: 1,800
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 Yesterday, 11:36 AM
Post#9



Posts: 175
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 Yesterday, 11:38 AM
Post#10



Posts: 175
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 Yesterday, 02:11 PM
Post#11



Posts: 252
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 Yesterday, 07:22 PM
Post#12


UtterAccess Editor
Posts: 10,213
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

--------------------
Go to the top of the page
 
isladogs
post Today, 08:21 AM
Post#13


UtterAccess VIP
Posts: 1,800
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 Today, 10:02 AM
Post#14


UtterAccess VIP
Posts: 1,800
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.

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



Posts: 543
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
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 04:11 PM