appelq
asked on
Query can't Delete records from Linked Table
I have an Access 2000 DB with two tables:
"Products" is a linked table to an Informix Database on another server.
"tblProductsToDelete" is a table in the current database with ProductID's that we want to DELETE from the linked table.
My SQL statement is
DELETE Products.*
FROM Products INNER JOIN tblProductsToDelete ON Products.ID = tblProductsToDelete.ID;
When I run this I get an error:
"Could not Delete from Specified Table"
The HELP file explains that this is a READ-ONLY permissions issue with the linked table.
However, when I execute a more specific DELETE query:
DELETE Products.*
FROM Products
WHERE Products.ID = "123"
This query successfully deletes the specified record from the Linked table.
By adding the Join, the Linked table somehow becomes READ-ONLY.
Do you have a suggestion for overcoming this?
Thanks,
Chuck
"Products" is a linked table to an Informix Database on another server.
"tblProductsToDelete" is a table in the current database with ProductID's that we want to DELETE from the linked table.
My SQL statement is
DELETE Products.*
FROM Products INNER JOIN tblProductsToDelete ON Products.ID = tblProductsToDelete.ID;
When I run this I get an error:
"Could not Delete from Specified Table"
The HELP file explains that this is a READ-ONLY permissions issue with the linked table.
However, when I execute a more specific DELETE query:
DELETE Products.*
FROM Products
WHERE Products.ID = "123"
This query successfully deletes the specified record from the Linked table.
By adding the Join, the Linked table somehow becomes READ-ONLY.
Do you have a suggestion for overcoming this?
Thanks,
Chuck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
devtha
Do you have access to the db on the server.? Check your access rights with sys admin.
ASKER
This did exactly what I needed.
I wonder why it works this way and not with the Join?
Oh well, thanks for your help.
I wonder why it works this way and not with the Join?
Oh well, thanks for your help.
no problem. Access is kind of tight when processing delete queries against a table when joined with another table. The method I gave you uses a subquery to obtain the correct records. It is a little less efficient than a join but sometimes its the only way to go.
cjswimmer:
I just had the same problem, and you've got the answer. I'm just curious to know if there's any logical reason why the join query wouldn't work.
I just had the same problem, and you've got the answer. I'm just curious to know if there's any logical reason why the join query wouldn't work.
When you try to run an action query like an UPDATE or DELETE, Access needs a unique row identifier to know exactly which line(s) in the table needs to be updated. Once you include a second table in a JOIN you now possibly are no longer presented with a one-to-one relationship between the records shown as the query results and the records that exist in the table you wanted to delete from. Access (at least up to v97 I think) was designed to not be able to handle this kind of query. Does this make sense? Its early and I haven't had my coffee yet so I may be rambling.
Makes sense to me. Thanks for the update.