Article ID: 108439
Article Last Modified on 1/26/2005
APPLIES TO
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
This article was previously published under Q108439
SYMPTOMS
If you have a query based on an attached Oracle synonym that has a sort on a column name, and the owner of the synonym has read-only permissions on the table on which the synonym is based, you will receive the error messages:
-and-
CAUSE
This error occurs because the owner of the synonym has read-only permissions on the Oracle table on which the synonym is based.
RESOLUTION
There are four ways to correct this problem:
- Create a Public synonym in Oracle.
- In Microsoft Access, attach directly to the table on which the synonym is based.
- In Oracle, assign the owner of the synonym permissions greater than read-only.
- In Microsoft Access, remove the "order by" clause from the query.
MORE INFORMATION
The following is an example of an SQL statement that will cause this error:
Note that the same errors will occur if this command is issued in SQL*DBA or SQL*PLUS, which are Oracle's interface tools. If you remove the ORDER BY clause, the statement will run. The statement will run with the ORDER BY clause if you remove the <owner>. clause instead. The statement will run with all the clauses included if you increase the owner's permissions on the table on which the query is based.
Steps to Reproduce Behavior
- In Oracle, create a table. Assign all permissions on the table to user A, and read-only permissions to user B.
- Log into Oracle as user A. Create synonym A based on the new table.
- Log into Oracle as user B. Create synonym B based on the new table.
- Start Microsoft Access. Attach synonym A with user A's login account. Create a query based on synonym A with a sort on one of the columns.
- Run the query. Note that the query runs correctly.
- Attach synonym B with user B's login account. Create a query based on synonym B with a sort on one of the columns.
- Run the query. The error messages stated above will occur.
NOTE: You can reproduce this behavior in Oracle's SQL*DBA as well. To do so, connect as user A and issue the following command:
SELECT * FROM userA.synonymA ORDER BY userA.synonymA.column1;
Note that the command runs correctly. Next, connect as user B and issue the following command:
SELECT * FROM userB.synonymB ORDER BY userB.synonymB.column1;
An error message will occur.
Additional query words: adk
Keywords: kberrmsg kbinterop kbprb KB108439