Delete query does not run

[jkaurloto]

To the group:

When I run the following delete query:

DELETE tblStrJobs.*, tblServiceJunction.OrderNum
FROM tblServiceJunction RIGHT JOIN tblStrJobs ON tblServiceJunction.OrderNum
= tblStrJobs.OrderNum
WHERE (((tblServiceJunction.OrderNum) Is Null));

I get a message box that says "Could Not Delete From Specified Tables"
Help claims Error 3086 - read only...

The db is not read only. There is no security. I am the only user. The db
is not on a network. I can delete records from either table individually.
I have tried compact and repair. I am using Access 2000 and have loaded all
Office 2000 service pack updates.

Does anyone have any ideas or suggestions as to why this particular query
fails to execute.

All assistance greatly appreciated.

Thank you.

John

 

[Guest]

JK, can you design a simple select query that shows the records you want to
delete? It may be easier to do that way, then you can modify the query to be
a Delete Query.

Destin Richter
(e-mail address removed)

 

[John Spencer (MVP)]

As far as I know you can only delete from one table at a time. Which means you
can only have a reference to one table in the Delete clause.

This should work.

DELETE DISTINCTROW tblStrJobs.*
FROM tblServiceJunction RIGHT JOIN tblStrJobs
ON tblServiceJunction.OrderNum = tblStrJobs.OrderNum
WHERE (((tblServiceJunction.OrderNum) Is Null));

If not, post back.

 

[Guest]

Just to add to John, usually when you want to delete a table when you have a
join between two tables you need to change the UniqueRecords property of the
query to TRUE

 

[jkaurloto]

..
Worked like a charm -
Thank you all most kindly - for both solving my problem and expanding my
knowledge.

John K.