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
> Query Is Corrupt - Bug Fix Utility, Access 2010    
 
   
theDBguy
post Nov 22 2019, 03:48 PM
Post#1


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hello,

I originally posted the attached utility in the News Forum, but many users were having problems downloading it. I am hoping by posting it to a regular forum would fix it.

Again, this utility is my approach to temporarily fix the subject bug from installing the MS Security update that caused UPDATE queries to become corrupt.

Please let me know if you have any questions or recommendations.

Hope it helps...

Cheers!
Attached File(s)
Attached File  theDBguyQueryBugFixV1.5.zip ( 33.63K )Number of downloads: 27
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
payfast8898
post Nov 22 2019, 04:03 PM
Post#2



Posts: 366
Joined: 23-April 15
From: NY


Bug it works great thank you and GREAT work as usual
Thank you!

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
theDBguy
post Nov 22 2019, 04:07 PM
Post#3


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks! Glad to hear it worked for you. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
FrankRuperto
post Nov 22 2019, 05:00 PM
Post#4



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


So by your fix app prefixing "dbg_" to each tablename without modifying the update qrys, that solves the problem?
And when MS fixes the bug with a new patch, we run your fix app again to revert the dbg_tablenames back to their original names?
This post has been edited by FrankRuperto: Nov 22 2019, 05:03 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
 
theDBguy
post Nov 22 2019, 05:20 PM
Post#5


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hi Frank. Yes, that's it in a nutshell. Let's say you have a bunch of UPDATE queries to update Table1 using different values and criteria. For instance:

Query1
UPDATE Table1 SET Field1=Something WHERE ID=1

Query2
UPDATE Table1 SET Field2=SomethingElse WHERE ID=2

Query3
UPDATE Table1 SET Field2=SomeOtherThing WHERE ID=3

Now, according to MS, you can create a query like so:

Query4
SELECT * FROM Table1

Now, you'll have to update all your queries to use Query 4, like:

UPDATE Query4 SET FieldName=SomeValue WHERE SomeCriteria (You have to do this as many times over as you have UPDATE queries.)

So, if you have a lot of queries or VBA code referencing the table, then you'll have to update all of them to use the new query that selects all the fields in the original table.

In the approach I'm using, there's no need to update any queries or code (except for some special cases, but at least it would then just be a minimal, I hope, of a change by then).

I thought this approach would be more convenient because you can even send this utility to your users (I would probably send them a ACCDE version though) to run against their ACCDE copy of the FE on their computers, so you don't even have to re-deploy the updated FE. This utility can be used by anyone to update their own copy of the FE (it works with ACCDB, ACCDE, and ACCDR). And when they get the fix from Microsoft, they can use the same utility to go back to the way it was before the bug.

However, I am open to any suggestions or recommendations or discussions regarding its logic and design.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
FrankRuperto
post Nov 22 2019, 06:09 PM
Post#6



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


First all, I cant wrap my head around why a bulk update with no where clause doesn't break, but one with a where condition breaks. One would think MS would be more concerned about a mass update of all records. And why just update statements and not also delete?

Anyway, to the point... so your fix canvasses the TableDef's, QueryDef's and prepends "dbg_" to all the tablenames it finds?
Would a table alias name, example: "Table1 AS TableOne", or create a synonym also work?

I understand this no longer works: UPDATE Table1 SET Field1=Something WHERE ID=1
And MS said this will make it work again: UPDATE (SELECT * FROM Table1) SET Field1=Something WHERE ID=1
And your fix is doing this mod?: UPDATE dbg_Table1 SET ..
And voila, it works?...
I'm iconfused.gif please provide a sample update statement of what your app does, based on the above example.

Thanks, Frank

P.S. I am assuming that update statements that make use of subqueries in the where clause will also cause the qry corrupt err?
Example: UPDATE Table1 SET Field1=Something WHERE ID IN (SELECT Field2 FROM Table1);
This post has been edited by FrankRuperto: Nov 22 2019, 06:17 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
 
FrankRuperto
post Nov 22 2019, 08:26 PM
Post#7



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


Okay, now I get it what you are doing, you are creating the "SELECT * FROM tblname" query for each tablename and replacing the tblname with the qry.. my bad crazy.gif

CODE
db.CreateQueryDef strOldTableName, "SELECT * FROM [" & strNewTableName & "]"

--------------------
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
 
theDBguy
post Nov 22 2019, 09:27 PM
Post#8


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Don't you think that was simpler than updating all the queries and VBA while waiting for the fix from MS? I hope so...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
fizzy1
post Nov 22 2019, 10:03 PM
Post#9



Posts: 551
Joined: 26-May 11



That's pretty clever, and elegantly simple. Nicely done.

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

thanks,
fizzy1.
Go to the top of the page
 
theDBguy
post Nov 22 2019, 10:05 PM
Post#10


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hi fizzy. Thanks! hat_tip.gif

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
FrankRuperto
post Nov 22 2019, 10:56 PM
Post#11



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


Agreed dbGuy, very nice solution indeed. I visited the MS link you provided in your commented vba and MS now says the fix wont be available for the older 2010 and 2013 versions until estimated 11/29/19 and 12/10/19 respectively. So users with those versions could certainly benefit from your solution. The challenge is making them aware that your fix exists.

--------------------
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
 
theDBguy
post Nov 22 2019, 11:35 PM
Post#12


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hi Frank. Thanks. I'm trying to write a blog post right now. It might help a little bit in spreading the word. Cheers!

Edit: Here's the link to the blog post.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ngins
post Nov 23 2019, 02:16 PM
Post#13



Posts: 410
Joined: 18-August 05
From: DFW, TX, USA


People don't seem to be aware of this, but the fix for this bug was released several days ago!

  • 365 monthly channel: just go to File | Account and do Update Now.
  • 365 semi-annual channel: update will be available on Monday
  • Manual install users: download the update and install.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
theDBguy
post Nov 23 2019, 02:24 PM
Post#14


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hi Neil. Thanks for the update. According to MS website, right now, the fix is only available to O365, 2019, and 2016 users. For those using 2013 and 2010, we are still waiting on a fix.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ngins
post Nov 23 2019, 03:04 PM
Post#15



Posts: 410
Joined: 18-August 05
From: DFW, TX, USA


Oh, OK. I guess i didn't see that this post had A2010 as the version. My bad. Carry on. :-)

--------------------
Neil
Accessing since '96
Go to the top of the page
 
ngins
post Nov 23 2019, 03:06 PM
Post#16



Posts: 410
Joined: 18-August 05
From: DFW, TX, USA


P.S. Just realized that I said "Carry on" in my reply to you, and your signature has a quote from "Carry On Wayward Son." Funny! :-)

--------------------
Neil
Accessing since '96
Go to the top of the page
 
isladogs
post Nov 23 2019, 03:15 PM
Post#17


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


If you have Office 365 and previously removed the version with the bug, beware when you update.
I ran the update to install the fix. All fine.
The next day I found the bug was back so the 12 Nov update must have subsequently been reinstalled.
As a check I repeated the update. Again fixed. I ran the update again. Bug returned for a second time.
I've fixed it for a third time and switched off updates again!

FWIW I was an Office insider (but no longer) and have version 1912 build 12231.20000

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
theDBguy
post Nov 23 2019, 03:31 PM
Post#18


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (ngins)
Oh, OK. I guess i didn't see that this post had A2010 as the version. My bad. Carry on. :-)
The utility was actually created using 2016, and it will work with 2016 and 2019 systems, if one tries to use it. I selected 2010 in the version dropdown to make sure older version users will know they can use this.

QUOTE (ngins)
P.S. Just realized that I said "Carry on" in my reply to you, and your signature has a quote from "Carry On Wayward Son." Funny! :-)
Did you like that? I like it. smile.gif

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Nov 23 2019, 03:32 PM
Post#19


UA Moderator
Posts: 77,281
Joined: 19-June 07
From: SunnySandyEggo


Hi Colin. Thanks for the update.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
FrankRuperto
post Nov 23 2019, 05:49 PM
Post#20



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


@Colin,

The same thing happened to other O365 users so the bottom line seems to be that we cant rely on MS providing accurate information and users either have to disable auto updates or mod the frontend's, if possible.
MS stated the corrupt qry bug also ocurrs with FE's linked to SQL Server and I am wondering if it also applies to pass-through update qrys or with just Access SQL update qrys? I would think PT's are not affected.

@Leo (theDBGuy),

How is your Access fix program able to work on compiled accde FE's when the qry and tbl defs are not accessible?
This post has been edited by FrankRuperto: Nov 23 2019, 06:13 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
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    17th January 2020 - 11:40 AM