none
Non-updateable query

    Question

  • Simple, simple query declared as non-updateable. Gotta be a bug.

    UPDATE to an Access table using a JOIN to a query whose data source is Excel.
    At first I thought it was because I had a LEFT JOIN; However, when changed to an INNER JOIN,
    I still received the dreaded: "Operation must use an updateable query".

    It's almost as if Access is making the wrong assumption that I am attempting to update the Excel workbook instead of the Access table. Clearly, the SQL says UPDATE Clients.

    Bug ?

    Wednesday, April 18, 2012 12:15 PM

Answers

All replies

  • Hi Syswizard,

    I don't think it's a bug.

    Usually what you describe happens when you run an update query between two tables without setting the PK or the FK in one of the two tables.

    HTH Paolo

    Wednesday, April 18, 2012 12:24 PM
  • I wouldn't say "bug" so much as "lazy programming" (on Microsoft's part!)

    Linked Excel tables are not updatable. Linking to any non-updatable table or query results in a non-updatable query.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by Syswizard Wednesday, April 18, 2012 3:09 PM
    Wednesday, April 18, 2012 12:27 PM
  • Thanks Doug, and once again, this is huge, load of BS.

    Since Excel is not being updated, what's the problem ?
    Microsoft did not think through all of the rules very well here.

    It's historically taken the low risk approach of too-easily marking queries as non-updateable....when in fact, that is a powerful feature of Access IMHO.

    Wednesday, April 18, 2012 3:11 PM
  • What's the actual SQL that's failing?

    There may well be an alternative way of writing it that will be updatable.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Wednesday, April 18, 2012 3:34 PM
  • What's the actual SQL that's failing?

    There may well be an alternative way of writing it that will be updatable.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)


    Ya lets see the SQL, AND also check the query properties by right clicking in query design maybe you have it as locked or disabled.
    Wednesday, April 18, 2012 4:17 PM
  • Nope, nope, nope...you're all wrong. The query is SO BASIC. The called query is simply a select from the Excel workbook that is linked. The UPDATE query simply joins the query to an Access Clients table that has its value set from a column in the Excel workbook.

    In fact, this isn't really an "UPDATEABLE query" at all. This is a true UPDATE query. So the error message was actually wrong....and that's always a bad sign (can you say "bug" ?).

    BIG DIFFERENCE: UPDATEABLE Query vs SQL UPDATE query.

    Thursday, April 19, 2012 12:09 AM
  • No need to be rude about it.

    While you may feel it's not an updatable query, obviously the Access developers don't agree with your analysis.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Thursday, April 19, 2012 12:16 AM
  • No need to be rude about it.

    While you may feel it's not an updatable query, obviously the Access developers don't agree with your analysis.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Or, more likely, they are completely wrong.
    Thursday, April 19, 2012 2:03 AM
  • Douglas J Steele [MVP] wrote:

    I wouldn't say "bug" so much as "lazy programming" (on Microsoft's part!)

    Linked Excel tables are not updatable. Linking to any non-updatable table
    or query results in a non-updatable query.

    Instead of linking the Excel Sheet as Table one can link it in a query that looks like this one:

    SELECT [Sheet1$A1:C13].*
    FROM [excel 8.0;HDR=NO;imex=0;DATABASE=C:\Temp\ExcelDB.xls].[Sheet1$A1:C99];

    of if you have a named range in the sheet that you want to use:
    SELECT [NamedRange].*
    FROM [excel 8.0;HDR=NO;imex=0;DATABASE=C:\Temp\ExcelDB.xls].NamedRange;
     This query now contains the Excel Sheet in an updateable version, data are written back to the excel sheet.

    The correct connect string for your query def can be read in the connect property of the linked Excel sheet this way (in the Immediate Window of VBA):
    ? currentdb.TableDefs("YourLinkedExcelSheetTableName").Connect
    Excel 8.0;HDR=NO;IMEX=2;DATABASE=C:\Temp\ExcelDB.xls

    Maybe using such a QueryDef instead of the TableDef linked to the Excel Sheet is the solution.

    HTH
    Henry

    Thursday, April 19, 2012 5:00 AM
  • Henry Habermacher wrote:

    Douglas J Steele [MVP] wrote:

    I wouldn't say "bug" so much as "lazy programming" (on Microsoft's part!)

    Linked Excel tables are not updatable. Linking to any non-updatable table
    or query results in a non-updatable query.

    BTW: Interesting fact about non-updateable Excel sheets.

    Earlier versions of Jet allowed to link Excel sheets in read/write mode. But then was a patent war between Amato and Microsoft and Amato won. As a result: MS had to pay and disabled the write mode probably to not have to pay license fees for all future versions of Jet.

    Fortunatly they let the backdoor with the updateable Queries linking to Excel sheets open ;-)

    http://www.morelaw.com/verdicts/case.asp?n=8:03-cv-00242-DOC-AN&s=CA&d=30129

    Henry

    Thursday, April 19, 2012 5:13 AM
  • Of course, just more of the same: lawyers putting the tech community into a state of misery.

    Ho, hum....nothing new. They've been doing this for decades.

    Thursday, April 19, 2012 1:16 PM
  • Did you deliberately choose to ignore the fact that Henry gave you a likely solution to your problem?


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Thursday, April 19, 2012 1:22 PM
  • Instead of linking the Excel Sheet as Table one can link it in a query that looks like this one:

    SELECT [Sheet1$A1:C13].*
    FROM [excel 8.0;HDR=NO;imex=0;DATABASE=C:\Temp\ExcelDB.xls].[Sheet1$A1:C99];

    of if you have a named range in the sheet that you want to use:
    SELECT [NamedRange].*
    FROM [excel 8.0;HDR=NO;imex=0;DATABASE=C:\Temp\ExcelDB.xls].NamedRange;
     This query now contains the Excel Sheet in an updateable version, data are written back to the excel sheet.

    Henry, this is a gem! I had no idea you could get an updatable query that way.  Thank you very much!

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Thursday, April 19, 2012 2:01 PM
  • Hi Dirk

    Dirk Goldgar [MVP] wrote:

    of if you have a named range in the sheet that you want to use:
    SELECT [NamedRange].*
    FROM [excel 8.0;HDR=NO;imex=0;DATABASE=C:\Temp\ExcelDB.xls].NamedRange;
    This query now contains the Excel Sheet in an updateable version, data
    are written back to the excel sheet.

    Henry, this is a gem! I had no idea you could get an updatable query that
    way. Thank you very much!

    If you start to use it you will even like it more as Jet will automatically extend your range for 1 row as soon as you add a new record. Automagically ;-)

    But don't shout out it to loud else they may have to block this workaround, too...

    Henry

    Friday, April 20, 2012 3:21 AM