Access gurus -- updating a linked table ...

Factual Questions
1

I am trying to run an update query (part of an Access form) against a table that is linked to the MDB from an Oracle database. The idea is to populate one column of the Oracle table with a constant value throughout.

Unfortunately, this has to be done from the Access end – doing it in SQL*Plus or something like that is not an option for developmental reasons (end users need to do the whole shebang through an Access form).

The syntax would seem simple enough:

UPDATE [WQ DATA]
SET PROJNUM = ‘WQ1958001’
WHERE PROJNUM is NULL;

But no – Access returns an error that a “single-row update is being applied to multiple rows. Unique index contains duplicate records.”

OK, so WTF? The Oracle table has no contraints or indexing. And why does Access think this is a single-row update when the query clearly is meant to update all the rows in the table?

:mad:

Thanks in advance for any advice.

2

Have you tried…

UPDATE [WQ DATA]
SET [WQ DATA].PROJNUM = ‘WQ1958001’
WHERE ((([WQ DATA].PROJNUM) is NULL));

Using the Table name and column name combination when referencing the column?

Just a thought……

3

It was originally written like so:

UPDATE [WQ DATA]
SET [WQ DATA].PROJNUM = “WQ1958001”

(note use of double quotes around the constant value).

When I looked this stuff up on Access Help, they showed sample update queries that all had defined WHERE clauses. So I thought that might be the issue – that I needed to put some kind of a WHERE clause in there.

The reason I removed the table name references from the column names is that I was trying to see if making the SQL more like Oracle-style SQL would help (yes, I know Oracle is cool with referencing columns as table_name.column_name – but it’s not obligatory, and I thought maybe it was obligatory in Access SQL).

I am interested in the many parentheses you added to the WHERE clause. At this point, I’ll try anything … whether I understand it or not.

4

Fudge – no dice.

Why would Access think this is a single-row update? Is there some alternate way to let Access know this update is meant to update every row in the table?

5

Instead of SQL, have you tried using the query builder? Use “Is Null” as the criteria.

6

Try using an SQL Pass-Through query. This will cause the Oracle server to process the query without any involvement from the JET engine. So you will have to be sure to use Oracle specific SQL statements rather than Access SQL.

In the query designer you can define your query as SQL Pass-Through using the Query | SQL Specific | Pass-Thorugh menu option.

7

I know that I have encountered weird problems whenever trying to update a linked table that does not have a primary index. Access wants the table to have a key, and Access wants to know what that key is.

I know, that this doesn’t necessarily make sense, and that you should get an error telling you what the problem is…

but this is computing and specifically Microsoft so it’s all part the the “fun”.

Try putting a primary index on the Oracle table, and then re-link it in Access.