Solved

Query can't Delete records from Linked Table

Posted on 2001-08-15
7
1,161 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamically Reorder List Box 4 39
Macro to import XML in Access 2013 2 40
Allow user to edit an outgoing email 2 25
Criteria for Date for DCount 4 26
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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