locked
Jet SQL snafu RRS feed

  • Question

  • I am baffled by an SQL statement I wrote in a VBA module. When I try to run it, Access throws the following complaint: Operation must use an updateable query.

    Here is the SQL statement:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner] AS C
    INNER JOIN (SELECT [First Name], [Last Name], [Address], [City], Count(*) AS [Cnt] FROM [COUNTY OH - ARCHIVE 30 DAY]
    GROUP BY [First Name], [Last Name], [Address], [City]) AS A
    ON A.[First Name] = C.[First Name] And A.[Last Name] = C.[Last Name] And A.[Address] = C.[Address] And A.[City] = C.[City]
    SET C.[CaseId] = A.[Cnt] + 1
    WHERE C.[CaseId] = 1

    Can anybody tell why this error and how to fix it?

    Thanks


    50% of programming is coding. The other 90% is debugging
    Monday, November 28, 2011 2:04 PM

Answers

  • It was a little baffling though figuring out just how to include the quotes but I eventually fugured it out. Since the DCount function call was an argument to a query, the only way I know to word it is by using single quotes except for the quotes surrounding the RunSQL method. I ended up with something like

    Docmd.RunSQL "UPDATE [TT] SET [FF] = 1+DCount('*', '[T1]',  '[F1]='''   &   [F1]   &   '''    And [F2]='''  & [F2]   &   '''') WHERE blah"

    I am very pleased with the finished work. Thanks again

    You're welcome.  The single-quotes (') inside the double-quotes (") will work, so long as none of the field values you're concatenating between the single-quotes contains the single-quote character.  For example, the name O'Hara would cause problems, since you'd end up with a criterion of

         [Last Name]='O'Hara'

    and the query engine wouldn't know how to parse that.

    Since the double-quote character is much more rare in names and addresses, you can usually avoid problems by using the double-quote character inside the quoted string literal, *but you have to double it up*.  If you want to put a double-quote inside a double-quoted string literal, you have to put 2 double-quotes in a row to represent just one double-quote.  That's what I did in the original SQL statement I suggested (though I'll grant you I may have miscounted quotes somewhere), but now I see the extra wrinkle that the entire SQL statement is going to be specified as a quoted literal.

    Your RunSQL statement as posted above could be expressed using double-quotes instead of single-quotes, like this:

        DoCmd.RunSQL "UPDATE [TT] SET [FF] = 1+DCount(""*"", ""[T1]"", ""[F1]="""""" & [F1] & """""" And [F2]="""""" & [F2] & """""""") WHERE blah"

    Ugly, isn't it?  But it works -- in principle, anyway -- and protects agains the possibility of [F1] or [F2] containing a single-quote.  If these fields might contain either single- or double-quotes, or both, then things get complicated, but there's still a way to do it.

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Vanderghast Monday, December 5, 2011 6:29 PM
    Tuesday, November 29, 2011 5:40 AM
  • I am baffled by an SQL statement I wrote in a VBA module. When I try to run it, Access throws the following complaint: Operation must use an updateable query.

    Here is the SQL statement:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner] AS C
    INNER JOIN (SELECT [First Name], [Last Name], [Address], [City], Count(*) AS [Cnt] FROM [COUNTY OH - ARCHIVE 30 DAY]
    GROUP BY [First Name], [Last Name], [Address], [City]) AS A
    ON A.[First Name] = C.[First Name] And A.[Last Name] = C.[Last Name] And A.[Address] = C.[Address] And A.[City] = C.[City]
    SET C.[CaseId] = A.[Cnt] + 1
    WHERE C.[CaseId] = 1

    Can anybody tell why this error and how to fix it?

    It's because of the GROUP BY clause; Access sees that and concludes that the query is not updatable, because in general totals queries are not updatable.  Usually the simplest solution in these cases is to use a domain aggregate function such as DCount to get the count you're looking for.  For example, though I may not get this right on the fly:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner]
    SET C.[CaseId] = 1 + DCount("*", "[COUNTY OH - ARCHIVE 30 DAY]", "[First Name]=""" & [First Name] & """ And [Last Name]=""" & [Last Name] & """ And [Address]=""" & [Address] & """ And [City]=""" & [City] & """")
    WHERE C.[CaseId] = 1


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Monday, November 28, 2011 2:29 PM

All replies

  • If you would create a query in SQL view with this SQL statement, it would fail too. A totals query (i.e. with a GROUP BY clause) is never updateable. Try this version:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner] AS C
    SET C.[CaseId] = (SELECT Count(*) FROM [COUNTY OH - ARCHIVE 30 DAY] AS T WHERE T.[First Name] = C.[First Name] AND T.[Last Name] = C.[Last Name] AND T.[Address] = C.[Address] AND T.[City] = C.[City]) + 1
    WHERE C.[CaseId] = 1

    Added later:

    WARNING: The above doesn't work - see Dirk Goldgar's reply lower down!


    Regards, Hans Vogelaar
    Monday, November 28, 2011 2:17 PM
  • I am baffled by an SQL statement I wrote in a VBA module. When I try to run it, Access throws the following complaint: Operation must use an updateable query.

    Here is the SQL statement:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner] AS C
    INNER JOIN (SELECT [First Name], [Last Name], [Address], [City], Count(*) AS [Cnt] FROM [COUNTY OH - ARCHIVE 30 DAY]
    GROUP BY [First Name], [Last Name], [Address], [City]) AS A
    ON A.[First Name] = C.[First Name] And A.[Last Name] = C.[Last Name] And A.[Address] = C.[Address] And A.[City] = C.[City]
    SET C.[CaseId] = A.[Cnt] + 1
    WHERE C.[CaseId] = 1

    Can anybody tell why this error and how to fix it?

    It's because of the GROUP BY clause; Access sees that and concludes that the query is not updatable, because in general totals queries are not updatable.  Usually the simplest solution in these cases is to use a domain aggregate function such as DCount to get the count you're looking for.  For example, though I may not get this right on the fly:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner]
    SET C.[CaseId] = 1 + DCount("*", "[COUNTY OH - ARCHIVE 30 DAY]", "[First Name]=""" & [First Name] & """ And [Last Name]=""" & [Last Name] & """ And [Address]=""" & [Address] & """ And [City]=""" & [City] & """")
    WHERE C.[CaseId] = 1


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Monday, November 28, 2011 2:29 PM
  • Ok, I see. I usually elect to use joins in my action queries as opposed to the D functions only because when I was learning SQL (not that I am not still learning), I was cautioned that D functions can be significantly slower, and the table that the count info is coming from has quite a number of records in it. What if I were to create an intermediary table and use an append query to get the count data in it along with the join fields, would that be better from a performance stand point?

    Thanks for you response


    50% of programming is coding. The other 90% is debugging
    Monday, November 28, 2011 5:31 PM
  • If you would create a query in SQL view with this SQL statement, it would fail too. A totals query (i.e. with a GROUP BY clause) is never updateable. Try this version:

    UPDATE [SERVER OH - Archive 30 Day - CaseId Assigner] AS C
    SET C.[CaseId] = (SELECT Count(*) FROM [COUNTY OH - ARCHIVE 30 DAY] AS T WHERE T.[First Name] = C.[First Name] AND T.[Last Name] = C.[Last Name] AND T.[Address] = C.[Address] AND T.[City] = C.[City]) + 1
    WHERE C.[CaseId] = 1

    Hans, I don't think that will work, either, though I agree that it ought to.  I could be wrong, but I think the SQL aggregate function Count() is enough to make Access think it's not updatable.  I tried it a little while ago (in Access 2003) and it wouldn't work, but it's possible I made a mistake somewhere.  Does the above work for you?

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Monday, November 28, 2011 5:38 PM
  • Ok, I see. I usually elect to use joins in my action queries as opposed to the D functions only because when I was learning SQL (not that I am not still learning), I was cautioned that D functions can be significantly slower, and the table that the count info is coming from has quite a number of records in it. What if I were to create an intermediary table and use an append query to get the count data in it along with the join fields, would that be better from a performance stand point?

    Thanks for you response


    You're quite right that using domain aggregate functions in your queries will slow things down a lot, and they are to be avoided where possible.  Inserting the count data into a work table first and then joining the work table in your query will probably be quicker, but then you will have increased the tendency of your database to bloat (unless you use a table in a separate work database).  So it's up to you what you decide is the best trade-off in your particular circumstances.

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Monday, November 28, 2011 5:42 PM
  • Thanks for your reply. Most of my tables are linked. This work table will only have a few fields and will be used in a loop for a few iterations of the original statement. Therefore, I am not worried about the little bloat that it will create. To me, a table is just data and the performance gain far out-weigh whatever downsides there are to bloating my database by 1 small table. Yes, the extra table is inelegant but whats of most importance to me is getting the job done in the most efficient and expedient manner.
    50% of programming is coding. The other 90% is debugging
    Monday, November 28, 2011 7:10 PM
  • Dirk, you are correct. It was air code, and it doesn't work. <blush>

    So DCount should be the way to go here.

    Thanks for checking!


    Regards, Hans Vogelaar
    Monday, November 28, 2011 8:36 PM
  • I gotta hand it to you Dirk. I eventually had to go with the DCount method. It wasnt as bad performancewise as I had thought. When I really sat and thought about how to implement the work table method, it made me wanna barf. Because a calculated field (Cnt) would be appended to a table field, it meant that I would have to list every field in the query statement; once in the append-to clause, and again in the select clause, substituting the agregate field for the calculated value. This is being applied to a handful of tables consisting of different field names except for a few. So in each iteration of the loop I would have to call a function that returns all the fields of the current table except the field thats taking the calculated data. Long story short, that just seemed like more of a nightmare than to try your method, which turns out to run tolerably efficient and took a lot less time to code than what I originally wanted to do. It was a little baffling though figuring out just how to include the quotes but I eventually fugured it out. Since the DCount function call was an argument to a query, the only way I know to word it is by using single quotes except for the quotes surrounding the RunSQL method. I ended up with something like

    Docmd.RunSQL "UPDATE [TT] SET [FF] = 1+DCount('*', '[T1]',  '[F1]='''   &   [F1]   &   '''    And [F2]='''  & [F2]   &   '''') WHERE blah"

    I am very pleased with the finished work. Thanks again

     


    50% of programming is coding. The other 90% is debugging
    Tuesday, November 29, 2011 5:06 AM
  • It was a little baffling though figuring out just how to include the quotes but I eventually fugured it out. Since the DCount function call was an argument to a query, the only way I know to word it is by using single quotes except for the quotes surrounding the RunSQL method. I ended up with something like

    Docmd.RunSQL "UPDATE [TT] SET [FF] = 1+DCount('*', '[T1]',  '[F1]='''   &   [F1]   &   '''    And [F2]='''  & [F2]   &   '''') WHERE blah"

    I am very pleased with the finished work. Thanks again

    You're welcome.  The single-quotes (') inside the double-quotes (") will work, so long as none of the field values you're concatenating between the single-quotes contains the single-quote character.  For example, the name O'Hara would cause problems, since you'd end up with a criterion of

         [Last Name]='O'Hara'

    and the query engine wouldn't know how to parse that.

    Since the double-quote character is much more rare in names and addresses, you can usually avoid problems by using the double-quote character inside the quoted string literal, *but you have to double it up*.  If you want to put a double-quote inside a double-quoted string literal, you have to put 2 double-quotes in a row to represent just one double-quote.  That's what I did in the original SQL statement I suggested (though I'll grant you I may have miscounted quotes somewhere), but now I see the extra wrinkle that the entire SQL statement is going to be specified as a quoted literal.

    Your RunSQL statement as posted above could be expressed using double-quotes instead of single-quotes, like this:

        DoCmd.RunSQL "UPDATE [TT] SET [FF] = 1+DCount(""*"", ""[T1]"", ""[F1]="""""" & [F1] & """""" And [F2]="""""" & [F2] & """""""") WHERE blah"

    Ugly, isn't it?  But it works -- in principle, anyway -- and protects agains the possibility of [F1] or [F2] containing a single-quote.  If these fields might contain either single- or double-quotes, or both, then things get complicated, but there's still a way to do it.

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Vanderghast Monday, December 5, 2011 6:29 PM
    Tuesday, November 29, 2011 5:40 AM
  • Yeah, I understand the concept. I had run into the issue long ago of a name or address containing an apostrophe, and a query statement that had been working flawlessly all along just suddenly became a plaintif. So I had gone thru and replaced all apostrophes with double apos. My boss at the time was sort of micro-managing, and even though the change didnt have any noticeable effect to his eyes, he didnt understand the concept and wanted me to replace the apos with empty strings instead (in other words, delete them). So, now I dont have to worry about those being in the data. Once in a great while one may creep into another unexpected field. I havent seen this happen in a long time but I think I just deal with it whenever it happens
    50% of programming is coding. The other 90% is debugging
    Saturday, December 3, 2011 5:17 AM