IT Employment



Problem with ODBC on DELETE from ORACLE using MS Access ORA-01460?

By rdoumouras ·
Recently when attempting to manually or programically delete more than a single line from a linked table Oracle in MS Access I have been getting the error ORA-01460.

This is a new error and may be because we have upgrade the Oracle database (11g) that is being linked to or it might have to do with the upgrade of my computer to Windows 7 with new ODBC links.

We are using MS Access (XP Pro) to link (via ODBC) to tables in the Oracle database via ODBC (Oraclient 11g). I am able to perform a delete command using SQL Tools 1.5 on the same computer. So I believe that the problem must be in the ODBC connection but I am unable to work out why or find anyone who has had a similar problem.

I could understand (to a degree) if I were to try and delete something and the "WHERE" criteria were inconsistant with the table syntax, however this should produce a different error. The error occurs when I select two or more records using the table view in Access and hit delete. I am also getting the error: "The Microsoft Jet engine stopped the process because you and another user are attempting to change the same data at the same time." Which is basically nonsense as I am the only one logged into the database!

Any help would be greatly appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Missing primary key at a guess

by Tony Hopkinson In reply to Problem with ODBC on DELE ...

You get that error when the selected information won't uniquely identify the records.
This could be no key on that table at all.. Or the table is a view, a union or a join.
Worth a look anyway.
I've seen people get caught out a few times on this one. Everything is golden, as long as you don't try to delete a record, where the values of all the columns are the same as another.

Collapse -

Reponse To Answer

by rdoumouras In reply to Missing primary key at a ...

Thanks Tony, I'll check the properties of the tables, it is possible that they may have changed. I am sceptical though I must admit. I have deleted from these tables numerous times for years now, so I am pretty sure that it has nothing to do with the table structure.
I am planning to check the ODBC settings and ensure that MS Access is completely up to date with updates for bugs. As I have a new installation on a new OS it is more likely that somehow the problem lies with this. I have been having problems with MS Access 2003 since the upgrade.

Collapse -

Same here.

by magnetkopp In reply to Problem with ODBC on DELE ...

I have the same problems here. After i updated to 11.2 i get some really strange errors.
A simple DELETE FROM SERVERTAB WHERE key_1 = 'key1' AND key_2 = 'key2' will cause a ORA-01460. (The PK of the table is key_1, key_2, key_3). If i add the 3rd key to the WHERE clause, the SQL works fine. But that's not what i want, because the 3rd key contains random values. If i fire this SQL on a machine with a 11.1.07 client on the same database (!) everything works fine. So, this is NOT a missing key or anything else in the structure - it is just the next oracle bug we can wait some years before it will be fixed or not.

Related Discussions

Related Forums