Application Integration

Expand all | Collapse all

OLEDB Query '' is Corrupt

Jump to Best Answer
  • 1.  OLEDB Query '' is Corrupt

    Posted 11-19-2019 14:33
    Hello all,

    I had been using OLEDB to change three fields within an excel sheet based on the value in the field. It had been working up until a couple days ago. Now I am receiving an error on the execute Query stage. The error I am receiving is : Internal : Could not execute code stage because exception thrown by code stage: Query '' is corrupt. The file name is dynamic and so I have a multi-calc setting the values and storing them in a data item. The information i used can be seen below.

    Database String: [File Folder Path] &"\"& [New Workbook Name] & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

    Provider String: "Microsoft.ACE.OLEDB.12.0"

    Connection String: "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="& [File Folder Path] &"\"& [New Workbook Name] &";Extended Properties=""Excel 12.0 Xml;HDR=YES;""" & ";"

    Query: "UPDATE ["&[Worksheet Name]&"$A2:DO] SET [Field 1]= 'Value 1', [Field 2]= 'Value 1', [Field 3]= 'Value 1' WHERE [Field 1] = 'Value 2';"

    I have tried doing a get as collection stage instead and I am able to get the fields I am trying to change as a collection but would still get that error when trying to update the workbook. I'm inclined to think it doesn't have to do with the connection since I am able to do other queries and get data as a collection. I double checked field names against what I have written and everything matches up. I had been using that exact query string and it worked for a while with no issue. Any help would be appreciated.

    ------------------------------
    Bryant Backus
    ------------------------------


  • 2.  RE: OLEDB Query '' is Corrupt

    Posted 11-20-2019 02:46
    That error is new to me, although I had no luck updating fields with a WHERE clause - something about using HDR=YES,  IMEX=1, ReadOnly=true for reads, but that it wasn't great with HDR=YES for updates. So kudos for getting this to work in the first place. You know your query looks good, so it itself does not look problematic. Out of curiosity, are all the cells set to Text or General or ?

    ------------------------------
    Brian Clayton
    Lead Developer - C#/Blue Prism RPA
    The Auto Club Group
    America/Detroit
    ------------------------------



  • 3.  RE: OLEDB Query '' is Corrupt

    Posted 11-20-2019 03:25
    Edited by Bryant Backus 11-20-2019 03:54
    Thank you. Currently it seems like the cells are all set to General. Just to see what would happen, I changed cell format to text for all values and tried to run the query again. I ended up getting the same error.

    ------------------------------
    Bryant Backus
    ------------------------------



  • 4.  RE: OLEDB Query '' is Corrupt
    Best Answer

    Posted 11-20-2019 15:40
    Edited by Sachin J 11-20-2019 16:50
    Same issue we are facing while executing update queries on the Access Database.

    https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec
    http://fmsinc.com/MicrosoftAccess/Errors/query_is_corrupt/



    ------------------------------
    Sachin J
    RPA Modeler
    BNY Mellon Technology
    Asia/Kolkata
    ------------------------------



  • 5.  RE: OLEDB Query '' is Corrupt

    Posted 11-20-2019 16:52
    Good to know that it was a microsoft issue and not a mistake I couldn't find. I ended up uninstalling access database engine and reinstalling. I'm guessing they applied a fix while I was doing that because it worked again after the install. Thank you Sachin!

    ------------------------------
    Bryant Backus
    ------------------------------