5 Most Common “Access Query Errors” Along with Their Fixes

Querying a database involves retrieval of some specific or all data from one or more tables or views. Well Microsoft Access offers a powerful guided query function that helps you to easily build a query even if you don’t know how to write a Structured Query Language Script.

But sometime this access queries runs through various errors and issues. So, regarding that here in this post we have configured out some most commonly encountered Access query errors. Take a look over them and know the best fixes to resolve them correctly without any trouble.

Common Encountered MS Access Query Errors :

• Operation must use an updatable query (Error 3073)
• Error message when you run a query in Access: “Cannot group on fields selected with ‘*'”
• “System Resource Exceeded” error message when you perform a query in Access 2010
• “ORA-01013 User Requested Cancel of Current Operation.”
• “Query Cannot Be Completed”

Error#1 Operation must use an updatable query (Error 3073)

Update query is an action query (SQL statement) that changes a set of records according to criteria (search condition) you specify. It’s a powerful feature and  very important fundamental portion of relational database as it allow you to modify a huge number of records at just one time.

Example:

UPDATE Query SQL Syntax

UPDATE table

[join]

SET newvalue

WHERE criteria

Error Description:

Well this particular ”Operation must use an updatable query” error message mainly encounters when you tries to update data in query or in a form. You may receive one of the following errors:

Operation must use an updatable query.

-or-

This Recordset is not updateable.

Error number: Error 3073

Here is the screenshot of the following error:

Resolutions To Fix “Operation Must Use An Updatable Query” Error

To resolve this access error 3073 the very first you need to do is just verify that the underlying table is updateable or not. for this , you just need to open the table and manually try to edit the field. If you are unable to do it manually then the query can’t make the changes either. Here are the reasons for having this issue, so try to fix them:

• May be the database file is set to ReadOnly. So, none of the data can be modified. Change this at windows level if the database is on CD and copy it to your hard disk.
• If you are using the Access workgroup security then you don’t have the privilege to edit the data. In this case login as administrator or with user name and password that allows you the authority to do modifications.
• You can’t modify the data with the back-end data source, if the table is linked.

Also Read

Access Query Designing: How To Run The Query Wizard In Access

• If you are linked to a sql server table or having an Access Data Project (ADP) and that table doesn’t have the primary key, then you can’t edit any of the table’s field from access.
•  Add the join field from the “many” side to your query so you can add new records to it.
• You must also enable the cascading updates in between the two tables.

Error #2 “System Resource Exceeded” Error

 Symptoms:

“System Resource Exceeded” access query error encounters when you perform a complex query that applies to many records.

Resolution To Fix “System Resource Exceeded” Error

To resolve this “System Resource Exceeded” error, apply the following update: 

2760394 Description of the Access 2010 hotfix package (ace-x-none.msp.

Error#3 “ORA-01013 User Requested Cancel of Current Operation”

Error Description:

 This specific Access error “ORA-01013 User Requested Cancel of Current Operation” encounters when running a query on a table in access using a data source ODBC. Users also caught into this error when they didn’t cancel the query.

Resolution To Fix “ORA-01013 User Requested Cancel Of Current Operation.”

To resolve this error, you need to adjust ODBC Driver settings. Here are the steps for how to do this, so just follow it properly:

1. Tap to the Start > Settings >Control Panel > Administrative Tools > Data Sources (ODBC).

2. Hit the “System DSN” tab in the ODBC Data Source Administrator Window.

3. Assign name of the Banner/Oracle data source from the list.

4.Click the Configure button.

5. From the Application tab of the Oracle ODBC Driver Configuration window.

ALSO READ

How To Fix MS Access Run-time 3146 ODBC Call Failed Error?

6. Un-check all checkboxes with the exception of Read-Only Connection make un-check for all Enable Failover.

8. Tap to the OK option to save all the changes.

9. Reboot the PC.

Error #4: “Cannot Group On Fields Selected With ‘*'”

Symptoms

You may get this Access “Cannot Group On Fields Selected With ‘*'” error when you run a query in Microsoft Access database. That uses one of the following aggregate functions:

• Sum
• Min
• Max

The error message that you receive is as follows:

Cannot group on fields selected with ‘*’.

Cause

This particular Access Query Error occurs when you use aggregate functions in the query and you don’t set the Output All Fields property of the query to No. 

When you set the Output All Fields property of the query to Yes, a wildcard character (*) is get added to the select clause of the access query. This * represent all the columns of the database tables.  However you can’t have wild character together with an aggregate function in the select clause of the access query.

 That’s the reason when you run the Access Query, you may receive the error message “Cannot Group On Fields Selected With ‘*'”

Resolution For “Cannot Group On Fields Selected With ‘*'”

 To fix this error set the Output All Fields property of the query to No, and run the query that uses an aggregate function.  For this just follow down these steps:

1. Open the Access database which having the problem query.
2. In the Database window, click Queriesunder the Objects
3. Click the Queriesgroup in the left Navigation Pane. Make a right-click to the query which you needs to modify, and then click Design View.
4. On the Viewmenu, click Properties.
5. Tap to the Designtab, and click to the Property Sheet in the Tools
6. In the Query Propertiesdialog box, set the value of the Output All Fields query property to
   No.
7. Click the Stored Proceduretab in the Property dialog box. Make sure that the Output all columns option is not selected.
8. On the Querymenu, click Run.
9. Hit the Designtab, and then click Run in the Tools

Error #5 Access “Query Cannot Be Completed” error

In order to remotely retrieve data from SQL server database on the network, the Access database uses linked tables. The error is caused due to complex Make Table Query which joins two local Access tables with a linked SQL Server table.

The SQL server table has been increasing up its size dramatically so the query uses more temporary resources on the local PC. As, to process the query until it couldn’t bear anymore with 2 GB maximum limit reached.

 Here is the screenshot of the error:

Resolution For “Query Cannot Be Completed” Error

To solve this specific issue, it’s good to convert the query to a Pass-Through query. But this also has a negative point, as it is impossible to pass local Access tables to the remote SQL Server for data processing as used in SQL join. Access can fetch the remote data easily but can’t the local data.

Solution To Retrieve Lost/Deleted Access Query

If in case while fixing the Access query error you lost up your access queries then also you don’t have to worry. Just try the Access Repair and Recovery Tool that is designed to repair any type of corruption issue encountered in .mdb or .accdb database file.

This tool has the ability to fix almost all minor and major corruptions, the tool restores damaged database objects like, tables, linked tables, indexes, modules and macros etc.

Conclusion:

Now you can easily handle any of these access query errors. As, now you are armed with perfect fixes to resolve queries related errors n issue of your Access Database.