• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1190
  • Last Modified:

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?


1 Solution
DELETE Products.*
FROM Products
WHERE Products.ID IN (SELECT ID FROM tblProductsToDelete)
Do you have access to the db on the server.? Check your access rights with sys admin.
appelqAuthor Commented:
This did exactly what I needed.
I wonder why it works this way and not with the Join?
Oh well, thanks for your help.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now