Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2021
    Location
    Michigan
    Posts
    1

    Problem with UPDATE query with SQL Server backend

    Note: Also posted in comp.databases.ms-access

    Greetings everyone!

    When I set up SQL Server Express in the past and used connection strings in Access (using TempVars) to connect to the tables, I had no trouble running UPDATE queries in Access to change the contents of the tables on the back end. Now I'm working on a project that has a SQL Server backend (not Express) that was set up by the customer, who has much experience setting up SQL Server instances. When I try to run the UPDATE query like before, I get the error message "Operation must use an updateable query." I've tried the following things:


    1) Gave the role assigned to the user db_datareader and db_datawriter rights
    2) Assigned the table in question as a Securable with Update, Select, and Insert rights
    3) Used the owner login and password in the connection string


    In all cases, I'm able to run Select queries on the tables. I don't want to use ODBC connections. Is what I'm trying to do possible with a SQL Server backend? The SQL Server admin said it was an Access issue, not a SQL Server issue.



    James A. Fortune

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. some joins in the query cannot update, depending. Some ,not all.

    2. you cannot run a summation query (sum/count/etc) AND do an update in the same query.
    you must put the sums in a temp table, then update from that.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If it is an Access issue, maybe this will help
    http://allenbrowne.com/ser-61.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us your Update query SQL? What exactly do you mean by
    I don't want to use ODBC connections
    ? Do you have the SQL tables linked in the front-end (in which case you are using ODBC) or are you only opening ADO recordsets and binding forms to those?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    The error message "Operation must use an updateable query." is indeed no SQL message but originates from Access. It can be the result of not enough rights. If the SQL user doesn't have the right to update a table, Access might consider this as a not updateable query. So you can ask your DBA to check your rights.

  6. #6
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    When I have mysterious query errors with SQL Server, I just turn the query into a stored procedure in SQL Server. Basically, Access often doesn't do a job as well as SQL Server so it's better to move as much operation as you can to SQL Server.

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you have Yes No fields in the access tables and converted them SQL allows Nulls in Bit fields whereas Access does not in Yes No fields.

    Any update where there are nulls values in the bit fields will fail.
    Make sure you have a default value for all bit fields or change them to integers.

    Another Allen Browne link - http://allenbrowne.com/NoYesNo.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Moving backend to SQL Server
    By Jayshe9999 in forum SQL Server
    Replies: 7
    Last Post: 02-27-2019, 09:48 AM
  2. Replies: 2
    Last Post: 03-07-2018, 04:30 PM
  3. Replies: 4
    Last Post: 09-05-2013, 08:02 AM
  4. .MDB Backend on a 64bit 2012 server
    By chrispl in forum Access
    Replies: 1
    Last Post: 08-09-2013, 09:29 AM
  5. Replies: 7
    Last Post: 04-05-2012, 07:38 AM

Posting Permissions

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