How to troubleshoot "Could not delete from specified tables"

[Phil Smith]

OCDB connection to a MYSQL database, the username and pasword I am using
has full access, read and write. Where should I start looking to solve
this problem?

DELETE item_prebook_link.*, item_prebook_link.item_id,
item_prebook_link.prebook_id, item.active
FROM item_prebook_link, item
WHERE (((item_prebook_link.item_id)=[item]![item_id]) AND
((item_prebook_link.prebook_id)=131) AND ((item.active)="M"));

 

[Krzysztof Naworyta]

Juzer Phil Smith <[email protected]> napisa³

| OCDB connection to a MYSQL database, the username and pasword I am using
| has full access, read and write. Where should I start looking to solve
| this problem?
|
| DELETE item_prebook_link.*, item_prebook_link.item_id,
| item_prebook_link.prebook_id, item.active
| FROM item_prebook_link, item
| WHERE (((item_prebook_link.item_id)=[item]![item_id]) AND
| ((item_prebook_link.prebook_id)=131) AND ((item.active)="M"));

1.
Try to understand SQL syntax.
You try to delete records from item_prebook_link.
So you could uncheck fields in QBE that have nothing to do with DELETE
command:

DELETE item_prebook_link.*
FROM item_prebook_link, item
WHERE
item_prebook_link.item_id=item.item_id
AND
item_prebook_link.prebook_id=131
AND
item.active="M"

2.
Use joins and aliases for tables:

DELETE
l.*
FROM
item_prebook_link l
inner
item i
on
l.item_id = i.item_id
WHERE
l.prebook_id=131
AND
i.active="M"


3.
I think your table "item" has no primary key or access can not recognize
it.

To delete records from it use DISTINCTROW word:

DELETE DISTINCTROW
l.*
FROM
item_prebook_link l
inner
item i
on
l.item_id = i.item_id
WHERE
l.prebook_id=131
AND
i.active="M"

4.
It is better to use EXISTS statement:


DELETE FROM
item_prebook_link l
Where
l.prebook_id=131
AND
EXISTS
(Select * from item i
Where
l.item_id = i!item_id
AND
i.active="M"
)

 

[Phil Smith]

1. Makes sense.
2. Joins makes sense, (actualy started with them, deleted when it did
not work) but I am not sure why using an alias would help?
3. Item_id is an indexed, unique field, but this table comes from MYSQL,
so it is not a primary key as far as Access is concerned.
4. Distinct Row makes sense, don't understand "EXISTS", so I will ahve
to do a little research on that one.

Thank you very much.

Phil

 

[Krzysztof Naworyta]

Juzer Phil Smith <[email protected]> napisa³



| 2. (...) but I am not sure why using an alias would help?

Aliases are theme of readability, aesthetics, and brevity
Nothing else.