Home News To Get Rid Of The Operation, You Should Use An Editable Query...

To Get Rid Of The Operation, You Should Use An Editable Query Error In Case Of Access Problems

66
0

 

It looks like some readers have come across an error message that requires the use of an updatable query for access. Several factors can cause this problem. Let’s take a look at them below.

By default, Access Select queries can be updated (modified). When you view the table for the selected query, you can edit the results and your changes will be saved in the base table. This works even if the query returns records from multiple tables.

 

 

I get the error “The operation must use an updatable query” when I try to run my SQL. As I understand it, this happens when connections are used in update / delete requests in MS Access. However, I am a little confused as I have another query in my database that is almost identical and works great.

Here is my problematic query:

  UPDATE [GS] INTERNAL CONNECTION [views] ON    ([Views] .Hostname = [GS] .Hostname)    AND ([GS] .APPID = [Views] .APPID)    SETUP        [GS] .APPID = [Views] .APPID,        [GS]. [Name] = [Views]. [Surname],        [GS] .Hostname = [Views] .Hostname,        [GS]. [Date] = [Views]. [Date],        [GS]. [Unit] = [Views]. [Unit of measurement],        [GS]. [Owner] = [Views]. [Owner]; 

As I said before, I am confused because I have a similar query that works great. Here is the request:

  UPDATE [views] INTERNAL CONNECTION [GS] ON[Views] .APPID = [GS] .APPIDSETUP    [GS] .APPID = [Views] .APPID,    [GS]. [Name] = [Views]. [Surname],    [GS]. [Critical?] = [Views]. [Critical?],    [GS]. [Unit] = [Views]. [Unit of measurement ya],    [GS]. [Owner] = [Views]. [Owner]; 

What’s wrong with my first request? Why does the second query work and the first one doesn’t?

Reasons Why A Query Or Recordset Could Not Be Updated

operation must use an updateable query error in access

There are many reasons why your data cannot be updated. Somepretty obvious:

  • The query is a summary query (uses GROUP BY) or a crosstab query (uses TRANSFORM), so the records are not separate records.
  • This is a calculated field, so it cannot be changed.
  • You do not have permission / rights to modify the table or database.
  • The request uses VBA or UDF functions and the database is not enabled (not trusted) to execute the code.

Some reasons are less obvious, but cannot be avoided:

  • The table being modified is a linked table with no primary key.
    • For some back-end databases (for example, Microsoft SQL Server) Access / Jet requires the table to be keyed in order to make changes. This has been useful since Accesswants to execute a SQL query about fromchanges, but cannot uniquely identify the entry.

These situations are less obvious:

  • Queries with specific summary fields associated with individual records and individual records still cannot be edited.
  • Queries with joins of multiple tables that are not in key fields.
  • Union Queries

The most obvious and annoying situations are queries against a table with totals (grouping, sum, number, average, etc.) that summarize multiple records. You would not wantExpect changes because you are not viewing individual entries. However, if part of your query contains a GROUP BY field, all fields inreturned records cannot be edited (not just summary fields). This document explains several ways to work around this limitation.

Resolution

To solve this problem, use the following methods:

  • Make sure the LockType property of the Recordset is not set to ReadOnly.

  • Make sure the file you are trying to open is not saved as read-only.

  • If you are connecting via DSN, do the following:

    1. Open Control Panel and click ODBC Data Source Administrator.
    2. Double click your DSN.
    3. In the Microsoft Excel ODBC Customization dialog box, click Options.
    4. Make sure the Read-only checkbox is unchecked.
  • If you are using a connection without a DSN, make sure the connection string contains the “ReadOnly = 0” parameter. For example:

      cn.Open "Driver = {Microsoft Excel Driver (* .xls)}; DBQ = C:  MyDoc.xls; ReadOnly = 0;" 
  •  

     

    How do I make an updateable query in access?

    Find the “Security” tab. If you don’t see it, you need to go to My Computer, then click Tools and select Folder Options. Then click the “View” tab. Scroll down and uncheck “Use basic file sharing (recommended)”. Back on the Security tab, you must add the appropriate account in the Group or Username field.

    How do you edit data in an Access query?

    Edit data in a text box or field
    1. Open a table or query in table view or a form in form mode.
    2. Click the field, or use the Tab or arrow keys to move to the field, then press F2.
    3. Place the cursor where you want to enter information.
    4. Enter or update the text you want to insert.