Dealing with Non-Updateable Queries and the Use of Temporary Tables

by Luke Chung
President of FMS Inc.
http://www.fmsinc.com

Overview
Example 1: Editing a Table with a Summary Field
Example 2: Updating a Field with a Summary Query
Workaround: Use a Temporary Table
Make-Table vs. Append Queries
Programmatic Approach
Multi-User Issues
Conclusions

Sample database: nonupdte.zip (140KB)

Back to Main Technical Papers Page

Overview

The queries in Access are fantastic. They allow you to perform a wide variety of very powerful analysis and actions and are used throughout 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.

The most obvious are queries on one table with totals (Group By, Sum, Count, etc) that summarize 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, the returned records are not editable.

Example 1: Editing a Table with a Summary Field

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. The problem is that 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 is not updateable.

Example 2: Updating a Field with a Summary Query

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:

Operation Failed

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. A better way is to use temporary tables.

Workaround: Use a Temporary Table

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 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 will put 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)

Make-Table vs. Append Queries

An alternative to Make-Table queries is an Append query. Append queries let you put 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.

• Append queries let you create the table in advance with specific field names and field types. Make-Table queries assigns its own field types for expressions, which may be a problem for the text width of text fields and number fields. This is particularly important if you need to use those fields to link to other tables.
• Append queries can be used to insert records into a table with fields not in the query. The fields may be updated later with other queries.
• Append queries let you accumulate results from multiple queries in one table.

For these reasons, you should become very familiar with Append queries. Follow these steps to setup your application to use Append queries:

1. Create the target table. I usually use a Make-Table query to do this, then manually modify the field names, types, settings, and descriptions.
2. Convert your Make-Table query to an Append query. When designing the query, select Append Query from the Access Query menu. In the query grid, specify the fields in the "Append To:" row. Notice how the Append To field names do not need to match the field names or expressions in the query:

3. 
   Append Query: Notice how field Expr1 goes into the nicely named "Sales" field

4. Create forms, reports, or other analysis based on the data in the target table.

   

   

Programmatic Approach

To use these techniques programmatically, you need to do two things:

• Empty the target table
• Run the Append query to fill the table

Both are quite simple. From Total Access SourceBook, here’s a procedure to empty a table:

Function EmptyTable_TSB(strTableName As String) As Boolean
  ' Comments : deletes all records from the named table
  ' Parameters: strTable - name of the table
  ' Returns : True if successful, False otherwise
  '
  Dim dbsTmp As Database
  Dim strSQL As String

  On Error GoTo PROC_ERR

  Set dbsTmp = CurrentDb()
  strSQL = "DELETE * FROM " & strTableName
  dbsTmp.Execute strSQL

  EmptyTable_TSB = True

PROC_EXIT:
  Exit Function

PROC_ERR:
  EmptyTable_TSB = False
  Resume PROC_EXIT
End Function

The code below lets you run any action query including Update, Delete, Make-Table, and Append queries:

Dim dbsTmp As Database
Set dbsTmp = CurrentDb()
dbsTmp.Execute "QueryName"

Multi-User Issues

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.

Conclusions

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 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 Access CodeTools, Total Access Detective, Total Access SourceBook, and Total Access Statistics. He has spoken at a variety of conferences and user groups, and can be reached at LChung@fmsinc.com. Their web site (www.fmsinc.com) offers a variety of Access resources including technical papers, utilities, and demos.

Back to Main Technical Papers Page

Copyright © 1998, 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.