Sample database: nonupdateable.zip (114KB)
The queries in Microsoft Access are fantastic. They allow you to perform a wide variety of very powerful analysis and actions. They can be chained together (one query uses another query), and are used throughout MS Access forms and reports. There are many types of queries. Most users are familiar with queries that generate results: select queries, select queries with totals, and crosstabs (which Excel users appreciate the most).
By default, Access Select queries are updateable (editable). When you view the datasheet of a Select query, you can edit the results and your changes are saved in the underlying table. This works even when the query returns records from more than one table. Unfortunately, there are situations where the query results are not updateable.
When you try to edit the query, this message appears in the status bar: "This Recordset is not updateable." and the data cannot be modified:
Operation must use an updateable query
There are many reasons why your data may not be updateable. Some are pretty obvious:
Some reasons are less obvious but can't be avoided:
Less obvious are these situations:
The most obvious and annoying situations are queries on one table with totals (Group By, Sum, Count, Average, etc.) that aggregate multiple records. You wouldn’t expect to edit that since you are not viewing individual records. However, if any portion of your query contains a summary (GROUP BY) field, all the fields of the returned records are not editable (not just the summary fields). This paper discusses some ways around this limitation.
For example, a query listing all the customers ranked by how much they purchased: The first query provides the sales summary for each customer:
qryCustomerSales query: Total Sales by Customer
The second query uses the first query to show the customers:
qryTopCustomers query: Customers with their Total Purchases
Query Results Showing Sales by Customers
The query results are very nice and can be used in forms and reports. However, it is not updateable. You wouldn’t expect to edit the Sales field, but you’d think Access would let you edit the customer fields (except for the Customer ID). No such luck. Because there is a summary in the query, the entire query and all its fields are not updateable.
Similarly, if your Customer table has a field to store summarized information, an Update Query should do the trick:
qryUpdateCustomer query: Attempt to update field with data from a Summary query
If you run this query, you get this error message:
Query Failed: Operation must use an updateable query
This is very annoying. One way to resolve this is to use module code to open two recordsets: one for the Customers table and one for the qryCustomerSales query, then manually update the Customers records. That’s a lot of work and a pain to maintain or understand. A better way is to use temporary tables.
By using temporary tables, you can eliminate a lot of unnecessary code.
Make-Table queries are just like Select queries except their results are put into a new table rather than a datasheet view. You specify the table name and it is created. If the table exists, it is replaced. To create a Make-Table query, open the query (qryCustomerSales) in design mode, and choose Make-Table Query from the Query menu. A dialog lets you specify the name of the output table:
Make Table Query puts its results in table: tblTempCustomerSales
Once you create the temporary table, using it rather than the summary query allows the two previous examples to work:
Displays Updateable Records (example 1)
Updates the [TotalSales] Field (example 2)
An alternative to Make-Table queries is an Append query. Append queries let you insert records from a query into an existing table. If you just have a one step process, there usually is not much difference. However, if you have multiple steps, Append queries have a clear advantage.
For these reasons, you should become familiar with Append queries. Follow these steps to setup your application to use Append queries:
Append Query: Notice how field Expr1 goes into the nicely named "Sales" field
Here's more information on Microsoft Access Append and Insert Queries.
To use these techniques programmatically, you need to do two things:
Both are quite simple. From Total Visual SourceBook, here’s a procedure to empty a table using VBA and DAO:
Function EmptyTable(strTableName As String) As Boolean ' Comments : Delete all records from a table in the current database ' Params : strTable Name of the table ' Returns : True if successful, False otherwise Dim dbs As DAO.Database Dim strSQL As String On Error GoTo PROC_ERR Set dbs = CurrentDb() strSQL = "DELETE * FROM " & strTableName dbs.Execute strSQL EmptyTable = True PROC_EXIT: Exit Function PROC_ERR: EmptyTable = False Resume PROC_EXIT End Function
The code below lets you run any action query including Update, Delete, Make-Table, and Append queries:
If you use temporary tables, you need to consider multi-user issues. You cannot have two people creating and using the same temporary table in the same database.
Each user must have their own database where temporary tables are created and used. The database can be linked to a shared database containing the data, but your application must be in a separate database. This is the proper way to design an Access application anyway.
For more information on this type of architecture, read our paper on Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability
Make sure you spend the time to understand how to use Access queries. They are extremely powerful and eliminate a lot of unnecessary coding. They are also much easier to debug than module code. The use of temporary tables is fundamental to working around the annoying non-updateable queries problem, and also for performing advanced data analysis.
Luke Chung is the president and founder of FMS, Inc., a database consulting firm and the world's leading developer of Microsoft Access add-in products. He is the designer and co-author of several Access add-ins including Total Access Analyzer, Total Visual CodeTools, Total Access Detective, Total Access Emailer, Total Visual SourceBook, and Total Access Statistics. He has spoken at a variety of conferences and user groups.
Copyright ©FMS Inc. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc. The information provided in this document is provided "as is" without warranty of any kind.