Solved

Query can't Delete records from Linked Table

Posted on 2001-08-15
7
1,164 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 50 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

732 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