In this solution, we look at a non-programmatic way to deal with non-updatable queries in MS Access using Temporary tables
Queries in MS Access are one of the essential features provided by the application. If you take away the query feature from MS Access, there would be very little left in the application. Access provides its users with multiple types of queries that can be used in multiple situations. However, these queries also have their own set of limitations. The most annoying one of which is the non updatable characteristic of these query results. Although most of the queries in MS Access are editable by default, in some cases, things work differently, and you are not allowed to edit query results.
There are different reasons as to why certain queries in MS Access cannot be updated.
- You are making use of Totals (Group By) or Crosstab (Transform) query, so the records in use are not individual, and thus cannot be updated.
- A calculated field is being used, which can’t be edited.
- You do not have enough permission or rights to make changes or edits in the database tables.
- The query makes use of VBA functions which are not allowed to run in the database.
Although there are many solutions that can help you get out of this situation, we will today be focusing on non programmatic solution of using Temporary tables for dealing with non-updatable queries in MS Access.
Using a Temporary Table
For doing away with much of the unnecessary code in MS Access, you can make use of a temporary table.
Make Table Queries
These queries are quite similar to Select queries except for the fact that their results are stored in a new table and not in datasheet view. A table can be created simply by specifying its name if a table by the same name already exists then it is replaced.
- Creating a make table query is easy, all you need to do is open query in design mode, select ‘Make – Table Query’ from query menu, and a dialog box asking for the output table name will appear.
- Once you enter the name of the table, it gets created, and will then be used for storing results of Make-Table query. When you have created this table, you can opt for using it over the Summary query. By making use of this option, you can opt for other solutions like:
- Editing a Table that uses a Summary Field.
- Updating a Field that uses Summary Query.
If you do not wish to use a Make-Table query, one of the best alternatives available to you is the Append query. By using an append query, you will be able to insert records in a table from a query. Append queries should ideally be used in a situation where the process is longer and involves multiple steps. For a short process, with not too many steps, there will not be much difference, and a Make – Table query will help you achieve the goal as much.
MS Access is one of the most popular database applications in vogue today in small business landscape. Yet despite its popularity, it still has yet to perfect its recovery features. Dealing with a corrupt accdb or mdb file can be a challenge and it is prudent to opt for a specialized tool like DataNumen Access Repair that offers complete data recovery.
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com