cannot delete from specified tables

[Hank Rouse]

Delete query reports it cannot delete from specified tables. I checked the
Datasheet view, and the records I want to delete are in fact displayed.

However delete functions are grayed out in Datasheet as well as being unable
to simply select one or more rows and delete.

What's wrong?

Delete query is:

DELETE DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] Like "*" & [TBL3].[COL1];

Thanks in Advance.

 

[giorgio rancati]

Hi Hank,

This query returns a read and write recordset,
----
SELECT DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] = [TBL3].[COL1];
----
therefore this query deletes the records.
----
DELETE DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] = [TBL3].[COL1];
----

This query returns a read only recordset,

 

[Hank Rouse]

Thanks Giorgio.

That worked great!

Hi Hank,

This query returns a read and write recordset,
----
SELECT DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] = [TBL3].[COL1];
----
therefore this query deletes the records.
----
DELETE DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] = [TBL3].[COL1];
----

This query returns a read only recordset,
-----
SELECT DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] Like "*" & [TBL3].[COL1];
-----
therefore your query doesn't delete the records.

For workaround try this
----
DELETE TBL1.*
FROM TBL1
WHERE EXISTS (SELECT 1 FROM TBL3 WHERE TBL1.COL1 Like "*" & TBL3.COL1 )
----

bye
--
Giorgio Rancati
[Office Access MVP]

Delete query reports it cannot delete from specified tables. I checked the
Datasheet view, and the records I want to delete are in fact displayed.

However delete functions are grayed out in Datasheet as well as being unable
to simply select one or more rows and delete.

What's wrong?

Delete query is:

DELETE DISTINCTROW TBL1.*
FROM TBL1 INNER JOIN TBL3 ON [TBL1].[COL1] Like "*" & [TBL3].[COL1];

Thanks in Advance.