Results 1 to 13 of 13

Access error: "Query is corrupt" (It's on an Update Query)

  1. #1
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    130

    Access error: "Query is corrupt" (It's on an Update Query)

    I'm getting the above message on a update query that has been working fine for years. It's a very simply query. I looks at a single field in an table and if the value is greater than zero and another field is not equal null, it changes the value greater than zero to -1.



    I saw this posted on the internet and it says problem started on 11/12/19 and the fix was implemented on 12/10/2019, but I'm still having the issue. I used this query successfully last week (after 11/12) and it worked fine.

    I believe I have Access 2013 unless it has been updated without my knowledge. If the fix hasn't been implemented on my computer how can I get it?

    https://support.office.com/en-us/art...3-f21636caedec









  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,464
    AFAIK, the fix date depends on which Office suite you have - installed via msi or 365. In at least one of those cases, the fix date is December.
    You must have seen the workaround solution then? To update an updatable select query instead of directly on the table? There have been other suggestions; some of which I think are too complicated for what should be a temporary issue.
    EDIT - found the link I had in mind
    https://www.access-programmers.co.uk...d.php?t=307918
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    130
    Quote Originally Posted by Micron View Post
    AFAIK, the fix date depends on which Office suite you have - installed via msi or 365. In at least one of those cases, the fix date is December.
    You must have seen the workaround solution then? To update an updatable select query instead of directly on the table? There have been other suggestions; some of which I think are too complicated for what should be a temporary issue.
    EDIT - found the link I had in mind
    https://www.access-programmers.co.uk...d.php?t=307918
    I hate to seem so ignorant, but I don't know which Office Suite I have. I'm just pretty sure I have (or at least had) Access 2013 I bought myself and installed on my personal computer myself. I still have the box that Access came in when I bought it.

    Fortunately, I ran a query today to see how many records needed to be updated and it was only 9. So I did it manually. In most cases it should be in the 10-15 range this time of year, but could get much larger next spring/summer. I can handle it manually once a week if I have to for now. I'd really prefer not writing a query that creates a new updated output table to replace the old one. It's a monster table and I just can't afford to accidentally screw up.

    Should I assume a fix will be coming and it will update my computer automatically or will I have to be proactive to get a fix?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,464
    First, you don't do anything to the table. Basically, you create a select query that selects the records you need and then update that query instead of the table.
    sorry I can't help more than provide a link to what seems like it might be your problem. Then again, given that you have Access 2013 this may not apply to you. If you research, or if someone posts here who knows better, then you can determine which versions are affected. If you create that query and try to run an update query against it (thereby updating your table indirectly) and it fixes the problem, then the issue applies to you. If not, you have an unrelated problem. Best not to just assume that what I provided as information automatically applies to you. I suggest you try the work around to at least see what happens. That will tell you whether or not the issue applies in your situation.

  5. #5
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    130
    Quote Originally Posted by Micron View Post
    First, you don't do anything to the table. Basically, you create a select query that selects the records you need and then update that query instead of the table.
    I don't fully understand.

    I have a table with 100+k records. There is a process in place that adds a few hundred records every week from an external source. Among those, some will have to be updated after they are added. I've been running that job as a weekly update job for years until this error came up.

    I could write a query to find those records that need further updating (I already have). That's how I knew which ones to update manually for this week.

    Are you saying that if I use that select query output and then run an update query it will automatically update the original table also?

    That's the result I need.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,464
    Are you saying that if I use that select query output and then run an update query it will automatically update the original table also?
    First, the long answer to provide some context:
    A select query that is based on one table can be identical to that table in terms of whether or not you can modify its records. Both the table and a select query that is based on it are what you call 'domains'. For the most part, as long as a select query does not contain calculated fields it is updatable. If you want to see this in action, either open or create a select query based on one table. When in datasheet view of that query, try to modify a value in a field - even if it's just to add a period at the end of a text value. Close the query and open the table and find the record that you edited. The table will contain the modified value for the field in the record that you edited because the query and table are directly connected. For all intents and purposes, the query is the table.

    The short answer: yes

  7. #7
    yyeeyy is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    1
    Thanks mr. Micron, that is what I did and it's run fine.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,464
    Sounds good. If your issue is fixed, please mark your thread as solved.

  9. #9
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    43
    MS has released a hot fix for this, but apparently I couldn't apply it to my installation (Office365), so I had to change the update query on my form. There is an original "solution" posted by MS here.

    https://support.office.com/en-us/art...3-f21636caedec

    In my case the exact change went from this;
    Code:
    CurrentDb.Execute "UPDATE Logger SET [Logout] = Now() Where ([UserName] = '" & CurUserName & "' AND [ComputerName] = '" & CurComputerName & "' AND IsNull(Logout) );"
    to this
    Code:
    CurrentDb.Execute "UPDATE (SELECT * FROM Logger) SET [Logout] = Now() WHERE ([UserName] = '" & CurUserName & "' AND [ComputerName] = '" & CurComputerName & "' AND IsNull(Logout) );"
    I'm lucky in that I only have one or two such queries in my DB, so the task wasn't that difficult.

    Does anyone know where I can send the bill to MS for wasting my time

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,464
    I couldn't apply it to my installation (Office365)
    I just did (365 annual). At least, it reported that the update completed. But you're showing W8 and Access 2003?

  11. #11
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    130
    Quote Originally Posted by Micron View Post
    First, the long answer to provide some context:
    A select query that is based on one table can be identical to that table in terms of whether or not you can modify its records. Both the table and a select query that is based on it are what you call 'domains'. For the most part, as long as a select query does not contain calculated fields it is updatable. If you want to see this in action, either open or create a select query based on one table. When in datasheet view of that query, try to modify a value in a field - even if it's just to add a period at the end of a text value. Close the query and open the table and find the record that you edited. The table will contain the modified value for the field in the record that you edited because the query and table are directly connected. For all intents and purposes, the query is the table.

    The short answer: yes
    The workaround solved the problem.

    I'm not sure I fully understand it though because I have another Update Query that does a similar thing and that one worked fine without having to implement the work around. But at least I have a solution. Thank you for your help.
    Last edited by wcrimi; 11-20-2019 at 04:01 PM.

  12. #12
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    130
    Quote Originally Posted by Micron View Post
    I just did (365 annual). At least, it reported that the update completed. But you're showing W8 and Access 2003?
    How did you apply the Hot Fix?

    I don't see a link to a fix.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,464
    The instructions are in the link you provided. Something about File>Account. I'm too lazy to write them out when you can just go back there.
    P.S. I like the idea in post 9
    Last edited by Micron; 11-20-2019 at 11:55 AM. Reason: added comment

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-08-2019, 07:50 PM
  2. Replies: 3
    Last Post: 06-06-2018, 08:26 PM
  3. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  4. Replies: 5
    Last Post: 06-26-2013, 02:29 PM
  5. Replies: 0
    Last Post: 03-04-2010, 06:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums