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
> Update Query - Query Is Corrupt, Access 2016    
 
   
Brepea
post Jan 17 2020, 10:35 AM
Post#1



Posts: 619
Joined: 11-January 09
From: UK


Hi All

I have just come across an issue with an application I've created (Access FE & SharePoint Lists as BE)...whenever an UPDATE query runs (where the query is referencing a table directly) - it gives an error message "Query 'queryName' is corrupt"...

I did some searching online and came across this article: external webpage

I did what that article suggested - I created another query to get the data output which needed to be updated - then in the update query i referenced that new query instead of the table directly - it worked...

My issue: I have loads of update queries in my application- a simple example of one is:
CODE
        
strSQL = "UPDATE t_Staff SET t_Staff.TeamID = " & VSid & ", t_Staff.SubTeamID = " & FuncID & ", t_Staff.TeamThreeID = " & Team3id & vbCrLf & _
                                "WHERE (((t_Staff.StaffID)=" & StaffNum & "));"
                            
                            DoCmd.SetWarnings False
                                DoCmd.RunSQL strSQL
                            DoCmd.SetWarnings True


Even the above update query (execute via VBA) still fails with the corrupt message.

Is there an easy way to convert the above to work in VBA? I mean - can i put that "strSQL" output into a temp query; then reference that temp query to execute the update on? - something like:
CODE
'Create a new query
Dim tmpStrSQL as String
tmpStrSQL = "SELECT * FROM t_Staff WHERE t_Staff.StaffID=" & StaffNum & ";"                          ***How do I write this output in a way i can reference it below?? ***

strSQL = "UPDATE tmpStrSQL SET tmpStrSQL.TeamID = " & VSid & ", tmpStrSQL.SubTeamID = " & FuncID & ", tmpStrSQL.TeamThreeID = " & Team3id & ";"                  

                            DoCmd.SetWarnings False
                                DoCmd.RunSQL strSQL
                            DoCmd.SetWarnings True

This post has been edited by Brepea: Jan 17 2020, 10:37 AM
Go to the top of the page
 
cheekybuddha
post Jan 17 2020, 10:48 AM
Post#2


UtterAccess Moderator
Posts: 12,262
Joined: 6-December 03
From: Telegraph Hill


There should be a fix out already to sort this bug, but in the meanwhile check out theDBguy's temporary fix.

Better to investigate installing the update first IMHO.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
DanielPineault
post Jan 17 2020, 11:12 AM
Post#3


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



This has been fully resolved. Try updating your installation.

https://www.devhut.net/2019/11/14/access-bu...ery-is-corrupt/

--------------------
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
 
isladogs
post Jan 17 2020, 11:42 AM
Post#4


UtterAccess VIP
Posts: 2,181
Joined: 4-June 18
From: Somerset, UK


Further to Daniel's comment, if you have Access 365 and use linked SQL tables there is a brand new serious bug which MS acknowledged only yesterday.
See https://support.office.com/en-us/article/ac...f1-7e043812d60d

So one problem solved and another differently bad problem almost immediately.
Just when you thought it was safe to go back in the water...

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 12:21 PM
Post#5



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


.. and that has created a catch_22 situation because Microsoft has identified a severe cryptographic security flaw in Windows10 (1911) and is urging everyone to update to (1912). Me and my users are more concerned about Microsoft causing something to break every time they release an update rather than security vulnerabilities. This is the main reason we decided to stay on Windows 7, Office 2010 msi and no updates turned on. Its stable and everything always works!
This post has been edited by FrankRuperto: Jan 17 2020, 12:25 PM

--------------------
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 Jan 17 2020, 01:17 PM
Post#6


UtterAccess VIP
Posts: 2,181
Joined: 4-June 18
From: Somerset, UK


Frank
I believe you are confusing Windows updates with Office 365 updates
The cryptographic flaw identified by the NSA is in Windows 10 and the patch is nothing to do with version 1912 update for Office 365.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 02:20 PM
Post#7



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Hi Colin,

Apologies, Microsoft posted the following about the Access/SQL_Server identity bug:
QUOTE
Version 1911 of Office 365 does not have this issue, so you may choose to use that build until the issue is fixed. The Semi-Annual Channel build (version 1908) also does not have this issue.


Nevertheless, it has become a mind_boggling experience for many Windows10/O365 users to maintain stability when Microsoft is pushing Windows10 and O365 updates that have been consistently breaking things that have worked for years. Cases of the Access corrupt query bug continue to be reported to current date despite Microsoft releasing a fix almost one month ago. Will tranquility ever be achieved?
This post has been edited by FrankRuperto: Jan 17 2020, 02:23 PM

--------------------
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 Jan 17 2020, 02:49 PM
Post#8


UtterAccess VIP
Posts: 2,181
Joined: 4-June 18
From: Somerset, UK


I agree. The updates are now too often a source of concern or even dread....
I remember losing two long standing clients because they thought I released too many updates.
At least with mine, the updates were thoroughly tested and clients did not have to install them.

I had to fix the corrupt query bug 3 times on my main PC.
That may have been a side effect of having both Office 2010 and 365 installed

Anyway, FWIW, I've found a work-round for the new Access /SQL bug without uninstalling Office 1912. See the other thread if interested.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Brepea
post Jan 17 2020, 04:00 PM
Post#9



Posts: 619
Joined: 11-January 09
From: UK


QUOTE
This has been fully resolved. Try updating your installation.


I don't have the option to update my Office package - see image of my options? Should my version of Office be bug-free - that's what' i'm understanding from the various responses...
Attached File  OfficeAccount.png ( 36.23K )Number of downloads: 1
Go to the top of the page
 
isladogs
post Jan 17 2020, 04:08 PM
Post#10


UtterAccess VIP
Posts: 2,181
Joined: 4-June 18
From: Somerset, UK


You could check whether your SysAdmin has applied the patch.
But the easiest method of checking is to see where you get the error message applying an update query with conditions as specified in the MS article

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 04:25 PM
Post#11



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Brepea,

The image you posted says the installed version is 1902 Semi-annual Channel, so I dont understand why you are experiencing the corrupt query bug.

--------------------
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
 
Brepea
post Jan 17 2020, 04:32 PM
Post#12



Posts: 619
Joined: 11-January 09
From: UK


My version is "1902" - so yes as per Dan's blog post should be sorted. Perhaps the company i work for have their own special Office updates>?

Presumably an 'admin' can login to my pc and try perform the "update" from Access...?

I was just about to edit all my queries (i only have like 10 update queries (which reference the tables directly; but then remembered that there are a huge amount of VBA update statements - and no way i can possibly rewrite those all)).
This post has been edited by Brepea: Jan 17 2020, 04:38 PM
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 04:59 PM
Post#13



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


That 1908 version I mentioned was in reference to a different bug with Access/SQL_Server.

However, you are correct, your current version is 1902, build 11328.20468 and according to the attached image, your IT administrator needs to install build 11328.20480 to fix the problem. Its just too many varieties of Access, Office and Windows to keep track of pullhair.gif

See Microsoft's official response about the corrupt query bug: https://support.office.com/en-us/article/ac...83-f21636caedec
This post has been edited by FrankRuperto: Jan 17 2020, 05:03 PM
Attached File(s)
Attached File  CorruptQryFixSchedule.PNG ( 41K )Number of downloads: 6
 

--------------------
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 Jan 17 2020, 05:03 PM
Post#14


UtterAccess VIP
Posts: 2,181
Joined: 4-June 18
From: Somerset, UK


It only takes a few seconds to test - much quicker than asking the question in several posts!
Create an update query on a table with a where filter. If it runs without error, your app is OK.
If the corrupt query error message appears, you still have the bug.

See https://support.office.com/en-us/article/ac...zMEXRb8_TeROPew

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Brepea
post Jan 18 2020, 06:41 AM
Post#15



Posts: 619
Joined: 11-January 09
From: UK


Isladogs...I've obviously tested it already (which is why I've made a post here)? --》 so not sure what value your comment adds.. iconfused.gif
Go to the top of the page
 
Brepea
post Jan 18 2020, 06:53 AM
Post#16



Posts: 619
Joined: 11-January 09
From: UK


Thanks Frank, that's most useful. The annoyance is the company IT department may take convincing to change things... just because i say so. They have their own bespoke updates applied... some 550 users involved.
Go to the top of the page
 
FrankRuperto
post Jan 18 2020, 07:18 AM
Post#17



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Maybe IT can just update, or rollback, the Office version on your box to avoid the query bug, unless there are other users using Access that are also experiencing the query bug?

Another alternative is to run theDBGuy's Access app that temporarily renames the tables which fixes the problem, and then you run his app again to undo the renaming once your IT folks update everyone's Office versions.

Best Wishes thumbup.gif

--------------------
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 Jan 18 2020, 07:31 AM
Post#18


UtterAccess VIP
Posts: 2,181
Joined: 4-June 18
From: Somerset, UK


@brepea
Sorry. I have been answering a similar thread at another forum and forgot what you had written in post #1.
Good luck

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Jan 18 2020, 08:37 AM
Post#19


UtterAccess Moderator
Posts: 12,262
Joined: 6-December 03
From: Telegraph Hill


Absolutely do NOT try changing all your queries individually if you continue to experience this problem - use DBG's utility which I linked to in Post#2

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


Regards,

David Marten
Go to the top of the page
 
Brepea
post Jan 21 2020, 03:21 AM
Post#20



Posts: 619
Joined: 11-January 09
From: UK


Thanks everyone - really appreciate the help. I have raised a ticket with our IT department and will let you know how that turns out...
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 05:15 AM