[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query can't Delete records from Linked Table

Posted on 2001-08-15
7
Medium Priority
?
1,174 Views
Last Modified: 2008-02-26
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


0
Comment
Question by:appelq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Accepted Solution

by:
cjswimmer earned 200 total points
ID: 6388894
try:
DELETE Products.*
FROM Products
WHERE Products.ID IN (SELECT ID FROM tblProductsToDelete)
0
 
LVL 6

Expert Comment

by:devtha
ID: 6388912
Do you have access to the db on the server.? Check your access rights with sys admin.
0
 

Author Comment

by:appelq
ID: 6389312
This did exactly what I needed.
I wonder why it works this way and not with the Join?
Oh well, thanks for your help.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:cjswimmer
ID: 6389338
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.
0
 
LVL 1

Expert Comment

by:chensler
ID: 8277879
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.
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 8278934
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.
0
 
LVL 1

Expert Comment

by:chensler
ID: 8279057
Makes sense to me.  Thanks for the update.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question