Solved

Query can't Delete records from Linked Table

Posted on 2001-08-15
7
1,160 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
7 Comments
 
LVL 6

Accepted Solution

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

Expert Comment

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

Author Comment

by:appelq
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Makes sense to me.  Thanks for the update.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now